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!

[Adam_Bergeman]

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?

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.

[kuovonne]

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()

[Adam_Bergeman]

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?

[kuovonne]

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?

[Adam_Bergeman · Accepted solution]

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

}

[kuovonne]

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.

[Adam_Bergeman]

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]

[kuovonne]

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"]

[Adam_Bergeman]

Got it! Thanks so much!