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.
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.
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:
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
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.
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.
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?
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.
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?
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.
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.
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.
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.