JSON API GET Call with FilterByFormula Function

Development and integrations API
#1

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!

#2

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
```

#3

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}}
#4

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

#5

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
#6

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>')
#7

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

#8

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?

#9

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.

#10

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?

#11

Hey! Dexter doesn’t have any documentation on how to do this, but I have the ability to take the e-mail address from the variable and send it through a Javascript function before inserting it into the URL. Would you know how I could write a little function to encode the e-mail address?

And yes definitely, I’m more than happy to share the solution.

#12

JavaScript has two native functions for url encoding. I used encodeURIcomponent() in my example, as you are encoding only part of the entire URI.

encodeURIComponent() and encodeURI()

#13

Okay, the issue I’m running into is that both of these are escaping the full stop in the email address which is messing up the API call. How can I encode an e-mail address without losing the full stop?

#14

Neither encodeURIComponent() nor encodeURI() should encoding the full stop (.).

Can you show the string before and after the url encoding, and the code used do the url encoding?

#15

I figured it out! Dexter has a built in function to strip punctuation from user triggers so I had to get around that with the punctuation stripping snippet at the top. I’m getting a proper response from the API now, here’s the code inside Dexter.

# punctopic nostrip

> object parseApiCallResults javascript

  return JSON.stringify(this.httpData, null, 2);
  
< object

> object create_getrequest javascript

  var email_encode = encodeURIComponent(args[0]); 

  var url_finish = "Primary%20Sheet?fields%5B%5D=First+Name&fields%5B%5D=Strength&fields%5B%5D=IP+Address&filterByFormula=%7BEmail+Address%7D%3D%22" + email_encode + "%22";
    
  return url_finish;
  
< object

+ *
$ GET https://api.airtable.com/v0/<base id>/<call>create_getrequest <star></call> {"headers": {"Authorization": "Bearer <bot airtable_api_key>", "Content-Type": "application/json"}}
* ${{__status}} != 200 => Error: ${{error.message}}
- <call>parseApiCallResults</call>

What I’m having trouble with now is pulling a specific value from the GET request. The following doesn’t work.

${{records.id.fields.First Name}}, ${{records.id.fields.Strength}}, ${{records.id.fields.IP Address}}

Here is the response I’m getting from the API (removed confidential information). Any idea on how to pull the data into Dexter using these mustache tags? My suspicion is that the id is the issue here.

Okay. {

  "records": [

    {

      "id": "REMOVED",

      "fields": {

        "First Name": "Catiya",

        "Strength": "b-trial-standard",

        "IP Address": "REMOVED"

      },

      "createdTime": "2020-05-25T17:03:31.000Z"

    }

  ],

  "__text": "{\"records\":[{\"id\":\"REMOVED\",\"fields\":{\"First Name\":\"Catiya\",\"Strength\":\"b-trial-standard\",\"IP Address\":\"REMOVED\"},\"createdTime\":\"2020-05-25T17:03:31.000Z\"}]}",

  "__status": 200

}
#16

Glad you are getting the data you expect now. Could you click the “Solution” check box for the post that has the answer to your solution so that anyone else reading this can more easily find the answer?

The Airtable API is returning a JSON string. Parse the string using JSON.parse(), and then you can get to the data as a normal JavaScript object.

#17

Done!

So I’m getting this error. It’s telling me it’s already been parsed into a Javascript object.

Error when executing JavaScript object: Unexpected token o in JSON at position 1]
#18

If it’s already a JavaScript object, just use JavaScript notation to access the values. Note that id and fields are at the same level, not nested, so you wouldn’t access them together. records is also an array, so you need to say which element of the array has the item you want.

records[0].fields["First Name"]
#19

Got it! Thanks so much!

#20

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.