Help

Re: FilterbyFormula not working with template literals

1121 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Osher_Ezra
4 - Data Explorer
4 - Data Explorer

Hey guys thank in advance, I’m trying to fetch a single record base on email Address column.

  1. I’m using Express and Axios from the front end to parse the req.body request and pass it in to the select method with the filterByFormula option, but it seems that every time I use template literals the response brings back all values in the email column. any idea why is that. I’v tried several variation, but with no luck.
 const record = await sheet.select({ filterByFormula: `"{Email} =${email}"`}).firstPage()
  1. plus I was wondering if I can fetch a record with the find method, but based on a column and the id is that possible?

cheers to all

1 Reply 1

Welcome to the community, @Osher_Ezra! :grinning_face_with_big_eyes: I see the problem with your first issue. Say that the email variable is set to bob@gmail.com. Once inserted into that template, the resulting filter formula becomes this, which isn’t a valid formula:

{Email} = bob@gmail.com

The email needs to be represented in the formula as a string, so you’ll need to wrap quotes around the inserted variable value. Try this alternate version:

const record = await sheet.select({ filterByFormula: `"{Email} =\"${email}\""`}).firstPage()

That will turn the above filter formula into this, which is valid:

{Email} = "bob@gmail.com"

I’m not sure that I understand your second question. Could you please elaborate?