Awesome video . This was the first time I used an API and your video made it feel so simple. I just have one question. My field names are not single words. They are separated by space and also sometimes have special characters. How do I put that in the code. I tried using field."Phone no.", but google script wouldn't let me save the script, forget about running it... Please help. Your response is highly appreciated
Thanks again for the reply you left on Danillo's comment. Can you please guide how I can use the field Ids mentioned in Airtable's API documentation as against the Field Name? Since we're setting up the process, I believe the operations team might change column names
Glad to hear you figured it out! For using Airtable's field IDs instead of the field name, that's actually not something that Airtable's API supports at the moment (at least not from what I've found so far). HOWEVER, I do have a workaround for you if you're worried about your users changing up the column names frequently. It's a bit tricky, so I'll try to explain it the best I can. To ensure that you can always reference the correct fields even if your users change the field name, you'll first need to make a request to Airtable's Metadata API to retrieve the fields for a table. This API is kinda new, so you really only have the option to get all of your tables which means you'll need to filter it down for yourself. Using this call, my suggestion is to create a mapping between the Field ID and the Field Name that the Metadata API gives back to you and store it in ScriptProperties so it's cached and ready for usage later. You can also set up a time-driven trigger to have it run periodically so that it's update to date. Otherwise, you can just hardcode the Field IDs you want and do the mapping in memory every time you make a call to Airtable to retrieve the data. To call the Airtable Metadata API to retrieve the tables and their fields, you'll want to make a call to: `api.airtable.com/v0/meta/bases/${baseId}/tables` From there, loop through the fields and pulling the id and name fields to create your mapping. Once you have the mapping of field id to field name - you can proceed normally with the airtable data pulls, using the mapping as a reference to the columns/fields you want to pull in.
Hi there , question for you. Is it possible to transfer airtable record(s) to a google sheet that is set up like a form? The record would need to a specific cell. Ie: record named “company name” copies to google sheet where a template has “company name” in C5: transfer occurs , google sheet shows a.t record in C5. Geoff
hey, thanks for the video. I'm running into an issue where some of my fields contain spaces. For example: If your field is "Name" then my name is "Name of Person". I'm struggling to find documentation that explains the syntax for referencing those fields for the portion of code in the for loop of the getSpeakers function. Do you have any insight on how I should call that?
Hi Matthew, thanks for reaching out! To pull data from fields with spaces in the name, you'll need to wrap it in square brackets and quotes. For example: fields['Name of Person'] Let me know if you still run into issues when doing that. Alternatively, I also have a video where we just pull in all of the fields without having to specify the field names. You can check that out here: ruclips.net/video/5fvP6kYfIkw/видео.html
@@BootstrappingTools Wonderful, thank you for the prompt reply. it worked perfectly. also: thank you for linking the other video. i'm looking forward to working through these, as well as your pagination solution. please keep up the good work!
Hi! It's a very good video but can you write down here the code? it is not visible becouse the image seems to be a little bit pixeled. Thank you so much!!
Thanks for reaching out! I'm not a big fan of copy pasta.. it doesn't really help folks learn when they can do that. I do talk through each line of code I write down in the video. Which part are you having trouble with?
Hello! great tutorial!! I have a couple of question... 1. on the GetSpeakers function, inside the speaker_info.push, how do you handle airtable fields that have the name with space, for example "Speaker Name" instead of simply name? 2. How would you insert all fields from an Airtable view into a Sheet, without having to use the speaker_info.push? Thanks in advance!
Thanks for reaching out, Danillo! Great questions. 1. To handle cases where there's a space in the airtable field's name, you'll just want to wrap it with square brackets and quotes. For example - fields['Speaker Name'] 2. Unfortunately, Google Sheets requires that the data you push into it is formatted as an array - that's why we do the speaker_info.push() function. If you want to avoid having to write out every single field you have in airtable, you might be able to use Object.keys() against the fields variable from the api response. I'll make a video going through that process - in the meantime, give it a try and let me know if you run into any trouble.
@@BootstrappingTools Thank you for the lightining fast reply!! it was super helpful!! both of your tips worked!! now I have ran into another problem...(I'm sorry I'm not a super programmer ) Some of my fields on airtable are links to other records and collaborator, when I console.log the fields they appear as [objects], for example this 'School Name' is a link to another record on airtable...I've tried to do this fields['School Name'][0] but it didnt work...can you help me with that? thanks in advance
@@BootstrappingTools Also not to push my luck, but I found another thing...how to handle the pagination thing? airtable only returns 100 records per page, we can offset but I cannot figure that out, if you could help with that too it would be great!
Haha, I actually have this on the queue to make a video for pagination with airtable's api. I should have that coming out next Tuesday(Dec 21st) - please stay tuned!
Great question - for linked records, those fields will return back with the airtable's record id. To get specific values, you can do one of two things: 1. Make another api call to obtain all of the data from the linked records 2. Add in "Lookup" fields into the airtable table that displays the data you want If adding in Lookup fields won't make your airtable view/table messy, I would suggest doing that. If not, you can pull in all of the records from the other airtable view/table and filter down the data to get the matched record from which you can pull any other fields you need. Give it a try and let me know if you have trouble with it. I'll also put this on the queue to make a video around so you have a visual walk-through.
Hey man how does it work with multiple select string arrays. When I follow this method I only get the first of my multiple select values to appear but I would really need all the options selected. For example a list of skills a organisation can offer. Would really appreciate a starting point.
Unfortunately, the airtable api as it currently stands only gives back data for fields/values that were entered. Getting all of the options available in a field even if it hasn't been selected before isn't supported by Airtable directly. HOWEVER, they have a metadata api which you have to sign up for: airtable.com/shrWl6yu8cI8C5Dh3 I think that's their way to seeing if they can support that feature for some folks. The other way around this is to create a separate reference table that selects all of the options so you can reference it through your API calls.
Hi there - I was able to successfully get most of my data but I am having an issue with some Contacts. Those are being stored in a different sheet in airtable so the data that is being shown in Google Sheets is the record id slug instead of an actual name. I was trying to see if there was a way to import the entire database instead of by sheet but I'm not finding any. Can you let me know if there is a way to do this or if there is a way to import multiple tables from airtable so it will display the names instead of record IDs?
Hi Claire, thanks for reaching out! If I'm understanding you correctly, it sounds like you have a field on your airtable tables that you want to pull down. If that's the case, you'll find those values under the "fields" object in the api response. I have another video that goes through pulling all of your fields data down, without having to specify each one - it might be helpful for your use case: ruclips.net/video/5fvP6kYfIkw/видео.html Regarding how to import the entire database instead of by sheet - there isn't a single API call that'll give you everything, but you can make individual calls for each table and import them in. If you have a lot of tables, it might be a pain to write out all the code but it's totally possible like that. If you do go down that route - I would suggest update your requestApi() code to also take in the table name so that you can reuse that function and just pass through the table name. There's also an Airtable MetadataAPI that is available and would allow you to retrieve the table names within your base. In order to use that, you would have to register for and get approved by Airtable. Here's a link to their registration page: airtable.com/shrWl6yu8cI8C5Dh3 Good luck with this. If you run into any issues, feel free to reach out!
You should be able to find it under the "help" option in the upper right. "API Documentation" should be last option in the list when the right-hand side tray pops up after clicking on the "help" option.
I'm going to have to say, Holy Shit Thank You! I have been searching for this exact tutorial for a week now, keep up the great work I hope your channel does well. Video has great pacing , I know Python and am just learning Java and this helped a lot. I had a question and I'll probably just look it up but is there a way to specify the data format for each column when writing the data? Only thing I need now is a video on how to work through the pagination for the rest of my airtable data and What do you know this guy has that too ruclips.net/video/6fOuDxcbjyg/видео.html
ahh, youtube flagged your comment for review and I didn't notice until now 😢 To format a date in Google Apps Script, you can use their Utilities library. The function itself looks like this: Utilities.formatDate(date, timezone, format) and in practice, it could look like this: Utilities.formatDate(new Date(), 'GMT', 'mm/DD/yyyy')
Very Helpful. Thanks a lot.
Glad it was helpful!
do you have a tutorial for pulling data from google sheets to airtable?
Awesome video . This was the first time I used an API and your video made it feel so simple. I just have one question. My field names are not single words. They are separated by space and also sometimes have special characters. How do I put that in the code. I tried using field."Phone no.", but google script wouldn't let me save the script, forget about running it...
Please help. Your response is highly appreciated
Just went through the comments. I think you've already answered it . Let me try that approach. Will revert soon
Thanks again for the reply you left on Danillo's comment. Can you please guide how I can use the field Ids mentioned in Airtable's API documentation as against the Field Name? Since we're setting up the process, I believe the operations team might change column names
Glad to hear you figured it out!
For using Airtable's field IDs instead of the field name, that's actually not something that Airtable's API supports at the moment (at least not from what I've found so far).
HOWEVER, I do have a workaround for you if you're worried about your users changing up the column names frequently. It's a bit tricky, so I'll try to explain it the best I can.
To ensure that you can always reference the correct fields even if your users change the field name, you'll first need to make a request to Airtable's Metadata API to retrieve the fields for a table. This API is kinda new, so you really only have the option to get all of your tables which means you'll need to filter it down for yourself.
Using this call, my suggestion is to create a mapping between the Field ID and the Field Name that the Metadata API gives back to you and store it in ScriptProperties so it's cached and ready for usage later. You can also set up a time-driven trigger to have it run periodically so that it's update to date. Otherwise, you can just hardcode the Field IDs you want and do the mapping in memory every time you make a call to Airtable to retrieve the data.
To call the Airtable Metadata API to retrieve the tables and their fields, you'll want to make a call to: `api.airtable.com/v0/meta/bases/${baseId}/tables`
From there, loop through the fields and pulling the id and name fields to create your mapping.
Once you have the mapping of field id to field name - you can proceed normally with the airtable data pulls, using the mapping as a reference to the columns/fields you want to pull in.
Oh, you'll also need to use a personal token from Airtable to access the Metadata API. You can do that here: airtable.com/create/tokens
Can you please make a video on how to get next page data? Currently once call can pull only 100 records at max. Thank you!
Hi Rashid, you're in luck b/c I actually have a video that covers pagination! Have a look here: ruclips.net/video/6fOuDxcbjyg/видео.html
Good luck!
Hi there , question for you. Is it possible to transfer airtable record(s) to a google sheet that is set up like a form? The record would need to a specific cell.
Ie: record named “company name” copies to google sheet where a template has “company name” in C5: transfer occurs , google sheet shows a.t record in C5.
Geoff
hey, thanks for the video. I'm running into an issue where some of my fields contain spaces. For example: If your field is "Name" then my name is "Name of Person". I'm struggling to find documentation that explains the syntax for referencing those fields for the portion of code in the for loop of the getSpeakers function. Do you have any insight on how I should call that?
Hi Matthew, thanks for reaching out!
To pull data from fields with spaces in the name, you'll need to wrap it in square brackets and quotes.
For example: fields['Name of Person']
Let me know if you still run into issues when doing that.
Alternatively, I also have a video where we just pull in all of the fields without having to specify the field names. You can check that out here: ruclips.net/video/5fvP6kYfIkw/видео.html
@@BootstrappingTools Wonderful, thank you for the prompt reply. it worked perfectly. also: thank you for linking the other video. i'm looking forward to working through these, as well as your pagination solution. please keep up the good work!
Hi! It's a very good video but can you write down here the code? it is not visible becouse the image seems to be a little bit pixeled. Thank you so much!!
Thanks for reaching out!
I'm not a big fan of copy pasta.. it doesn't really help folks learn when they can do that. I do talk through each line of code I write down in the video. Which part are you having trouble with?
Hello! great tutorial!! I have a couple of question...
1. on the GetSpeakers function, inside the speaker_info.push, how do you handle airtable fields that have the name with space, for example "Speaker Name" instead of simply name?
2. How would you insert all fields from an Airtable view into a Sheet, without having to use the speaker_info.push?
Thanks in advance!
Thanks for reaching out, Danillo! Great questions.
1. To handle cases where there's a space in the airtable field's name, you'll just want to wrap it with square brackets and quotes. For example - fields['Speaker Name']
2. Unfortunately, Google Sheets requires that the data you push into it is formatted as an array - that's why we do the speaker_info.push() function. If you want to avoid having to write out every single field you have in airtable, you might be able to use Object.keys() against the fields variable from the api response. I'll make a video going through that process - in the meantime, give it a try and let me know if you run into any trouble.
@@BootstrappingTools Thank you for the lightining fast reply!! it was super helpful!! both of your tips worked!! now I have ran into another problem...(I'm sorry I'm not a super programmer )
Some of my fields on airtable are links to other records and collaborator, when I console.log the fields they appear as [objects], for example
this 'School Name' is a link to another record on airtable...I've tried to do this
fields['School Name'][0]
but it didnt work...can you help me with that? thanks in advance
@@BootstrappingTools Also not to push my luck, but I found another thing...how to handle the pagination thing? airtable only returns 100 records per page, we can offset but I cannot figure that out, if you could help with that too it would be great!
Haha, I actually have this on the queue to make a video for pagination with airtable's api. I should have that coming out next Tuesday(Dec 21st) - please stay tuned!
Great question - for linked records, those fields will return back with the airtable's record id. To get specific values, you can do one of two things:
1. Make another api call to obtain all of the data from the linked records
2. Add in "Lookup" fields into the airtable table that displays the data you want
If adding in Lookup fields won't make your airtable view/table messy, I would suggest doing that. If not, you can pull in all of the records from the other airtable view/table and filter down the data to get the matched record from which you can pull any other fields you need.
Give it a try and let me know if you have trouble with it. I'll also put this on the queue to make a video around so you have a visual walk-through.
Hey man how does it work with multiple select string arrays. When I follow this method I only get the first of my multiple select values to appear but I would really need all the options selected. For example a list of skills a organisation can offer. Would really appreciate a starting point.
Unfortunately, the airtable api as it currently stands only gives back data for fields/values that were entered. Getting all of the options available in a field even if it hasn't been selected before isn't supported by Airtable directly.
HOWEVER, they have a metadata api which you have to sign up for: airtable.com/shrWl6yu8cI8C5Dh3
I think that's their way to seeing if they can support that feature for some folks.
The other way around this is to create a separate reference table that selects all of the options so you can reference it through your API calls.
Hi there - I was able to successfully get most of my data but I am having an issue with some Contacts. Those are being stored in a different sheet in airtable so the data that is being shown in Google Sheets is the record id slug instead of an actual name. I was trying to see if there was a way to import the entire database instead of by sheet but I'm not finding any. Can you let me know if there is a way to do this or if there is a way to import multiple tables from airtable so it will display the names instead of record IDs?
Hi Claire, thanks for reaching out!
If I'm understanding you correctly, it sounds like you have a field on your airtable tables that you want to pull down. If that's the case, you'll find those values under the "fields" object in the api response. I have another video that goes through pulling all of your fields data down, without having to specify each one - it might be helpful for your use case: ruclips.net/video/5fvP6kYfIkw/видео.html
Regarding how to import the entire database instead of by sheet - there isn't a single API call that'll give you everything, but you can make individual calls for each table and import them in. If you have a lot of tables, it might be a pain to write out all the code but it's totally possible like that. If you do go down that route - I would suggest update your requestApi() code to also take in the table name so that you can reuse that function and just pass through the table name.
There's also an Airtable MetadataAPI that is available and would allow you to retrieve the table names within your base. In order to use that, you would have to register for and get approved by Airtable. Here's a link to their registration page: airtable.com/shrWl6yu8cI8C5Dh3
Good luck with this. If you run into any issues, feel free to reach out!
I am not abl to find the option that u have like "get speakers" on top. How can get it?
You should be able to find it under the "help" option in the upper right. "API Documentation" should be last option in the list when the right-hand side tray pops up after clicking on the "help" option.
I'm going to have to say, Holy Shit Thank You! I have been searching for this exact tutorial for a week now, keep up the great work I hope your channel does well. Video has great pacing , I know Python and am just learning Java and this helped a lot. I had a question and I'll probably just look it up but is there a way to specify the data format for each column when writing the data? Only thing I need now is a video on how to work through the pagination for the rest of my airtable data and What do you know this guy has that too ruclips.net/video/6fOuDxcbjyg/видео.html
ahh, youtube flagged your comment for review and I didn't notice until now 😢
To format a date in Google Apps Script, you can use their Utilities library.
The function itself looks like this:
Utilities.formatDate(date, timezone, format)
and in practice, it could look like this:
Utilities.formatDate(new Date(), 'GMT', 'mm/DD/yyyy')