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.
${{records.fields.First%Name}} //The field value I am trying to pull from Airtable.
Thanks!
Page 1 / 1
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
```
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
```
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.
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 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:
Not sure if this helps any - feel free to post more details
JB
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.
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.
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>')
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.
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.
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.
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.
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.
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()
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()
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?
Can you show the string before and after the url encoding, and the code used do the url encoding?
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(argsa0]);
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";
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.
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.
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]
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.
recordso0].fieldse"First Name"]
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.