JSON API GET Call with FilterByFormula Function

[Adam_Bergeman]

Hi there! I’m trying to set up an GET function to pull data from Airtable into Dexter (chatbot) based on a user inputting their e-mail address. I’m not getting an error message, but the response is coming back as empty. Any idea what’s going wrong here? is the user e-mail variable.

$ GET https://api.airtable.com/v0//Sheet&20Name/ {“headers”: {“Authorization”: "Bearer "},“body”: {“records”: {“filterByFormula”: “({Email Address} = ‘’)”}}}

• ${{records.fields.First%Name}} //The field value I am trying to pull from Airtable.

Thanks!

[kuovonne]

Could you try retyping your code? I’m not seeing the variable for the user e-mail in your code get requuest.

Also, you can format your code by using backticks. It makes it easier to read on these forums:
```
Put your code here
```

[Adam_Bergeman]

My bad! The e-mail address is in the variable. I’m currently getting back a response with the below code, but it’s retrieving all records rather than just the one I’m looking for.

$ GET https://api.airtable.com/v0/<bot primary_database>/Primary%20Sheet/ {"headers": {"Authorization": "Bearer <bot airtable_api_key>"},"body": {"records": {"filterByFormula": "({Email Address} = '<call>encode_uri <star></call>')"}}}

${{records}}

[JonathanBowen]

Hi @Adam_Bergeman - I don’t really understand the call you are making there (what language/libraries are you coding with?), but using Javascript, I would do something like this:

var get_options = {
    'method': 'get',
    'headers': {
        Authorization: 'Bearer keyABC123XYZ567'
    },
}

var url = 'https://api.airtable.com/v0/' + appId + '/Estimates?filterByFormula=ProjectID%3D%22' + project_for_url + '%22';
var get_response = UrlFetchApp.fetch(url, get_options);

In my example, I’m using filterByFormula on ProjectID where it equals some value (passed in elsewhere in the script). I might well be missing something in your script, but this doesn’t look like a variable on the RHS:

{Email Address} = '<call>encode_uri <star></call>'

Not sure if this helps any - feel free to post more details

JB

[kuovonne]

Can you output the GET request to the console to see the actual message that is being sent to the API?

Note that with a GET curl request, the filterByFormula would a parameter in the url, not part of the body of the message.

If the filterByFormula request is not setup properly, the API simply ignores that part of the request and doesn’t perform any filtering. This would explain why you are seeing all records.

Before url encoding, I would expect a url parameter that looks like

filterByFormula={Email Address} = 'someone@example.com'

After url encoding, I would expect something that looks like

filterByFormula%3D%7BEmail%20Address%7D%20%3D%20%27someone%40example.com%27

[Adam_Bergeman]

Hey @kuovonne and @JonathanBowen!

Will try and clarify my question here. I’m working off of the Dexter platform - a chatbot-builder. I’m following their example on how to pull data from the Airtable API, but I’d like to pull just a few fields from a certain record based on it’s e-mail address. The <star> variable is a user-entered variable that helps to pull their info based on their e-mail address.

This is the Dexter documentation example.

+ get person #
$ GET https://api.airtable.com/v0/<bot airtable_base>/People/?api_key=<bot airtable_api_key>
* ${{__status}} != 200 => Error: ${{error.message}}
- ^link("${{records.<star>.fields.LinkedIn Profile}}","${{records.<star>.fields.Name}}")

I’ve tried to use the following encoded URL with the variable in it, to pull the fields I’m looking for, but I got nothing back using this. I made this URL using the Airtable encoder on codepen. The variables contain the base/API key information, which is stored in Dexter.

https://api.airtable.com/v0/app3XfrHg3W4uC6A1/Primary%20Sheet?fields%5B%5D=First+Name&fields%5B%5D=IP+Address&fields%5B%5D=Strength&filterByFormula=%7BEmail+Address%7D%3D%3Cstar%3E?api_key=<bot airtable_api_key>

@kuovonne This URL is also not working for me.

https://api.airtable.com/v0/<bot primary_database>/Primary%20Sheet/filterByFormula%3D%7BEmail%20Address%7D%20%3D%20%27adam%40iwantgoodness.com%27%29?api_key=<bot airtable_api_key>

Let me know if the URL looks valid! I think it’s going wrong with the filterByFormula, which needs to look like this before encoding.

filterByFormula=({Email Address} = '<star>')

[JonathanBowen]

OK, so this is a variation on the Airtable API that they use on their side (by variant, I mean the same Airtable API but it looks like they throw in some of their own things). A couple of suggestions, not sure if they will work:

<star> looks like their own built-in variable, so I wouldn’t enclose this in quotes as this will make it a string, I’m guessing. You could try the filter by formula of

filterByFormula={Email Address}=<star>

(not sure you need the brackets you have above).

Looking at their docs, I think they have a mistake as the getPerson and getPeople api urls are the same. To get a person (or an individual record of any sort), you need to pass an Airtable ID, which I’m guessing you don’t have on your side. But getPeople then filtering by something unique (email as you have) should work. I think you might want something like:

$ GET https://api.airtable.com/v0/<bot airtable_base>/People/?filterByFormula=%7BEmail+Address%7D%3D%3Cstar%3E&api_key=<bot airtable_api_key>

I’m speculating a bit, as I haven’t used Dexter before, but trying to match the “regular” API calls with their docs, this feels like it is along the right lines.

Let us know if it works or not, would be interested to see the version that works.

JB

[kuovonne]

Sorry, I had a typo in my formula–an extra closing parenthesis %29 that doesn’t belong there.
If you take out the %29, does it work?

[Adam_Bergeman]

Hey folks! I managed to get it working on a specific e-mail address with the below URL, pulling exactly the fields I’m looking for.

https://api.airtable.com/v0/app_id/Primary%20Sheet?fields%5B%5D=First+Name&fields%5B%5D=Strength&fields%5B%5D=IP+Address&filterByFormula=%7BEmail+Address%7D%3D%22adam%40iwantgoodness.com%22

But the URL using the variable below yields an empty response.

https://api.airtable.com/v0/app3XfrHg3W4uC6A1/Primary%20Sheet?fields%5B%5D=First+Name&fields%5B%5D=Strength&fields%5B%5D=IP+Address&filterByFormula=%7BEmail+Address%7D%3D%22%3Cstar%3E%22

{

  "records": [],

  "__text": "{\"records\":[]}",

  "__status": 200

}

I am encoding the following filterbyFormula using the code-pen set up.

{Email Address}="<star>"

@JonathanBowen trying your URL gets me this response:

Error: The formula for filtering records is invalid: Invalid formula. Please check your formula text.

I think I’m on the right track with my URL, just need to figure out why the response is coming back as empty.

[kuovonne]

I’m glad you were able to get the filter working with the specific e-mail address. That means that you just need to figure out how to convert <star> to the actual email address. You’ll need to get the email address from the variable, then url encode it, then put the url encoded version in the query parameter. Does the Dexter documentation tell you how to do those steps?

---

When you get your filter to work, could you please mark as a solution whichever post contains the answer?