Pulling data from Airtable into Google Sheets via Google Apps Script

Pulling data from Airtable into Google Sheets via Google Apps Script - Zweisprachige Untertitel

Thanks for watching!

Hey, we want thank you for joining me.

I'm a guy called Joe and this is bootstrapping tools.

Let's build where we help scrappy bootstrappers just like yourself figure out how to overcome some of the hurdles that they're facing in their business or just for them to make themselves better.

Now, before we dive into today's topic, make sure to check out our YouTube channel.

We got lots of videos up there that talk about all sorts of low code and no code solutions out there,

including retail, Google Sheets, Google Apps Scripts, Data Studio, air tables, as as many others.

If you don't see what you're looking for here, feel free to shoot an email over to feedback at bootstrapping.tools.

It be happy to take a look at that application for you and possibly make a video just for you.

Now, in today's video, we're going to be going over how you can grab your air table data and

And you might be thinking, what can I do in Google sheet that I can't do an air table?

Well, the number one thing that I would say is that air table, great glorified spreadsheet, well, in like the best sense, right?

But it has this feature called apps, which you can do some automation with.

However, you need to have a probe plan in order to access that feature.

Now, if you don't have the budget for that, one of the options that you can do is Google Apps Scripts.

But in order to do that, you need to pull your data out of air table more to actually apply the automation against it.

So, by using this tutorial, I'm going to you how to do this in a couple of minutes here.

By using this technique,

you're going to be able to get all the benefits of AirTable,

being able to manipulate data in AirTable with your beautiful UI and grouping and all the features that they have,

but still also create scripts to automate some of your workflows through Google Apps Scripts by taking that data from AirTable in real time and then applying

your scripts logic to that data set in Google.

Now, let's go over here and jump over to our air table.

Alright, so here we have a sample air table base created.

We're actually using one of air tables templates for event planning.

And within here you get different tables, one for schedule, one for speakers, one for attendees, So we were to say hypothetically, right?

You wanted to take get all the emails and then send them out like drip campaigns But you don't necessarily have a good way to take that data and then put it into your email service provider

Like Sangrid or MailChamp or something like that

Here table has this feature called apps taking use however apps are only available in the pro plan.

Now there's also another thing called automations that you can try to do but it isn't necessarily

to everything that you might want it to do.

There's now a lot of flexibility.

Well there's a lot of flexibility but not specifically for what we're doing.

And one of the that we're gonna be doing is we're gonna be trying to take this data from the speakers and then the attendees

Right one from what we're gonna create a list from each one of them because you'll likely want to send out different email campaigns each

And then we're gonna shove it into a spreadsheet like this one air table

We're going to two different tabs and then within our script we're going to iterate

through them so that you can actually do something with that data.

So what I'm going to show you in this video is just how to pull the data and then how

to get the data or write the data into this kubushi and then how you can read that data again.

What you do with that data afterwards and leave it up to your imagination or your

own needs but I'm going to show you from Airtable, put it into a Google Sheet and then read off that Google Sheet.

So, first things first, you're going to want to take a look at your API docs.

What you can do is up on the upper right, there's a little help icon that you can do.

You can click on that.

And then now that's going to show your API documentation, which is all the way at the bottom over here.

My head is covering it.

Once you click on that, that's going to open up the documentation for Airtable.

And then within here, you're going to notice on the left hand side a of options here.

And this is all curated to your app base on Airtable.

So if you had something that was called something else, email list or something like that, you're going to see that's a email list.

table.

So right now ours, the speakers and attendees.

And we're going to click on the list records in order to get the call that we want here.

So it's going to be this URL.

And then our API key is shown here.

You can activate your, you can show her how your API key by clicking on this checkbox.

If you need it.

But we're just playing with sample But essentially we're going to take this and this is how we're going to grab the data from

this particular table on an air table.

So going over to our script editor and if you don't know how to access this essentially within your Google sheets,

there's an option for tools up top and a script editor.

When you open that up for the very first time,

it's going to be untitled and everything and you're going to get an empty,

what you can call my function, we're going to go ahead and rename this.

We're going to create a typical one called request there table, right?

And then within here, we're going to determine the URL that we're going to hit.

And then we're also going to determine a of other things like the header.

which we're going to open up into an object.

Authorization something that we're going to need because we need to be able to provide the authentication,

which in this case is going to be that API key.

Then we also are going to pass through some options over here where one of those options is the headers.

And then another one of those options is going to be method, which in this case, we're going to be getting information from it.

Now the cool thing about using Google Apps Script is that it provides you with a couple of libraries that you can do a lot of your work with.

The one that we're going to be using today is called URL FetchApp.

So you can see here as soon as you type in URL it kind of pops down in the dropdown.

on that and hit the period, there's three functions that you can call here, fetch, fetch all and get requests.

One that we're going to be using is fetch.

And within fetch, you can grab the URL, and then there's also a for objects.

So options actually, but it accepts an object.

So options just like we named over here we created an object that that's what we're going to pass through and that's how we're going to

say Use the method get so we're going to make a get request and then use the headers that we identified before or authorization is going to be our API key

So going back over to our API documentation for air table

I'm going to notice here that this header is the authorization bear

So bear authentication and then our API key just copy that That's all we're going to need.

We need to do anything special with it.

And then the URL is going to be the URL that you see here in this coral robust.

Copy that.

Go back to your script.

Then go ahead and paste that in.

So you'll notice that there's a couple of parameters here that we might not need,

like the max records,

the view is going to be important because that's how air table is set up, but we don't necessarily need this max record.

So go ahead and highlight that until we do that.

Now within the URL fetch app, let's assign this to a response.

So we're going to say, get context text.

And then we're also going to parse that.

So we're going to say result equals response.

JSON.parse response.

So I'm going to do console log just so we can see what the result is.

And this should be everything we need.

So we got the URL, we got the headers where authorization is better.

We can actually put into content type is application JSON.

I don't think you necessarily need this, but we can put it in there anyway.

And then our options are going to poll those headers.

And we're going to signal that we're using a get request for this API request.

So, save that.

Let's ahead and run it and see what we I'm going to time Unown never has occurred.

Sometimes it comes with issues like this.

Hold a second.

Let refresh the page first.

Okay, I'm going try the one more time.

Start it, and as you can see here, we're getting information back from the API.

So there's records, there's an ID, there's a bunch of fields in there, and then the created time for it.

So if we go back to the air table documentation,

can see that underneath records each one of those has information and then the field is what we actually want to grab.

So there's the email, the name, phone number, as well as the role in the company.

That's probably what we're going to pull in.

The number is actually the same but we'll pull it in any way just so you can see what that looks like.

So going back over to our Let's actually save this as its own function because we're probably going to reuse it in the future.

I'll show you why.

But over here, let's return result.

And what we can actually do is we can do one for the results and one for the records.

Now let's actually just return the records.

I don't think we care about anything else in there.

We're going to that.

So we're going to create another function now.

I'm going to say get speakers.

And then this get speakers function is going to request the her table function that we just made.

So we're going to set that the speakers stack.

And then we're going to do a console log speakers, and we can actually just go ahead and hide that console log.

We don't need that anymore.

So we're going to run the getspeakers function now.

And then what we expect is that we'll only get the records.

Okay.

Good.

So we go through here.

Let me speak.

We don't have the create or the.

is just that and then the fields.

So what we want is each fields column.

So what we'll do here now is we're going to do a simple for loop.

So i equals zero is less than speakers dot length.

And then within here, we're going to take the info.

So we're just gonna let's let's call this fields equal speakers, I dot fields.

Alright, and then within those fields, what we want is we want to get the email, the name and all that jazz.

so outside of this let's actually go ahead and say speaker info and we'll set this to an open array

and then speaker info we're just going to shove into it so we're going to do a dot push

and then we're going to do open brackets to set an array here i'm going to say

fields dot and then if we look back at the air table documentation we have email

remember air table is very specific with with this capitalization so make sure

that you are using case sensitivity here so it's actually grab the name first so

we're gonna save fields that name And then we'll copy that.

We're going to email.

And then I think there were two other fields that we once could grab, role and company, and then also phone.

So let's say role company.

Oops, copy that.

And then let's say phone.

And then outside of this folder, let's go ahead and console log the speaker info.

Okay, so let's comment out this speaker's console log so that we don't get duplicate and run this.

We should see a nice little hash of information now.

So now that we have this, we blend the channel and all that.

Let's also put into it the first row of the speaker info should be the header of each column.

So we're going to do speaker info just right underneath for when we first declare

we're going to push into it just everything that we have down here.

So we're going to say name and make sure you put in these in quotes because they're going to be headers, phone, role and company.

All right,

so once we save this from running again,

we should expect the first position in this array to be the headers and Then underneath of that all the information that we care about for each

Individual person we found on that air table base

So now that we have get speakers what we're going to do is we're going to create another function

which is going to serve as our updater.

So import speakers.

So here, what we're going to do is we're going to set the spreadsheet.

So s as sure for spread sheet, we're going to get the spreadsheet app not get active spreadsheet.

And the reason you can use the get active spreadsheet here is because it's already attached to the

spreadsheet since we opened up the script that are through the Google sheet.

Once we have that, we're going to set the sheet equal to get sheet by name.

And then our sheet name over here is sheet one, but let's call this speakers.

And then let's also create another one called attendees because we're going to do that in a little bit so speakers

And let's call this speakers sheet So the speaker sheet dot get range So, get range.

It's a pretty simple feature here.

It allows for four parameters.

The one is the starting row.

The parameter is the starting column.

And the third one is the number of rows that you want the grid to be.

And then the fourth one is the number of columns that you want it to be,

so the number of width and the height of your grid.

So what we don't know yet is the actual length,

so let's actually hold off on that just one second because what we're going to do now is We're going to say the speaker data is equal to the ket speakers and within here.

We're going to go ahead and return speaker info Here we're going to save our speakers equals get speakers.

And then so this is going to be speakers dot length.

And then the column width is actually going to be speaker zero position dot length.

Alright, so once we have that range, we can also say

And the values is going to be the speakers so when I run this is going to import the speakers into this spreadsheet right here Okay,

so let's go ahead and run that Wait, no That is get speakers.

I don't want that.

I import speakers It's also gonna ask you to review some permissions because now we're uh,

we're starting to write data into it Quickly authorize that run it

Speakers not to find out the typo somewhere here right there so speakers fix that and then run it again import speakers

So execution we go back to our spreadsheet.

And you can see, we have the data from Airtable now.

So, now what we're gonna do is we're gonna do the same thing for attendees.

And you notice here in our code, we have a request Airtable, but then all this information over here.

is unique specifically to the speaker.

So what we're going to do instead is we're going to pass through a table value and then a view value.

That way when we request the air table data we can make that we can share the code but look up different values for it.

And what we're going to do now is we're going to take where it says speakers, we're going to cut that part out.

And the same thing for the view, we're going to cut that part out.

And then what we're going to do is we're going to add to it the view and then over here I'm going to say table.

That way we can just pass through the information through our function.

So when we're requesting the air table over here for speakers, what we're going to want is speakers and then all speakers.

So I'm not sure if we need the %20, which is just the URL encoding, let's actually test this out first together.

So import speakers, truncated view table not found, okay, so that's fine, so we do %20 over here.

So that's already encoded, actually false speakers.

because it is lowercase speakers of course so let's try that one more time

let's do the percent 20 actually when we test this

out we get data back and let's see if we need that percent 20 in there.

It'd be great if we didn't.

We don't.

Okay, awesome.

So we're constantly updating data in there now.

So the thing that you need to keep in mind is that if for what a reason your data is

shown in the other you're going to have bad data.

So what we can do here is once we get the sheet, and the range.

What we can do is let's go ahead and just clear out the content.

So speaker sheet dot get data range, clear content.

That way when we run it, you'll see that it removes all the data and then replace it.

run, go here, this should flash.

It's done.

It fast.

Okay, let's comment this out.

Let me just show you what I mean here.

We're gonna go run.

So you start execution, end it.

And then this data is gone.

So it's refreshing it, essentially.

So let's go ahead and just put this Save, run, run go ahead, or no error.

Okay, great.

So now that we have this working,

we can take this information this whole function actually,

and then just not replace but duplicate so we can say get attendees this is going to be

attendees we go back to our air table go over to our attendees table get the list

records it's also the view is called all attendees with lowercase a So attendees,

we go over to our air table data, our has named company role email and phone.

So basically everything the same, just that the speakers had more data than before.

So we actually don't need to change anything with our code here, except for just changing attendees.

So say attendees.

I'll say attend the info.

This is going to turn into attendees, then the info.

that.

So we're going to have the import speakers.

And now we're also going to have import attendees.

So it's actually organized this just a little bit better so that we have the import code all together for each one,

sneakers, because speakers in the request area table.

Okay, so in here, now we're going to change this to be attendees, and it's at a speaker sheet.

It's going to be attendees sheet, put that in here, it's going to be get attendees instead.

Attendees.

and then we're going to change this function name to attendees.

Okay, so I'm going to And now let's run an import attendees.

And it runs when we come here.

So now we have speakers and we also have attendees.

All in their own middle areas.

And the data is unique to each one because you're pulling from those specific air table tables.

Um, the next thing that we're going to do now is we're going to pull the data.

So we're going to read the data off of those and you can do whatever you need with it.

That's really starting a new script file.

So we're going to call this, um, let's say, what's this called the sheet?

cheese data, and then we'll call it, we'll rename this one to air table API.

So within the sheets, let's say, get attendees.

So what you're going to do here is we're going to do the same thing, we're a bar spreadsheet, spreadsheet app to get active spreadsheet.

All And then now you're going to get the sheet for the attendees.

So you're going to get sheet by name.

This is attendees.

And then you're going to get the data range.

So that's going to be sheet dot get data range And you're going to do get values from there

Make sure to do an open and close parentheses.

We're going to Just so you can see what the data looks like.

So now what we have is we're calling the spreadsheet,

getting the sheet name,

getting the data range out of that sheet,

and then getting the values from it, and then we're going to console log this data so that you can see what it looks like.

So hit the save button,

now you have the attendees in the drop down,

hit run, and then what expect to see is all the attendees that we have in our spectrum.

spreadsheet.

And if you want to adjust the emails for that,

so let's say you were going to get emails,

so let's return this data, go make a new function here, get attendee emails, and then in here get attendees.

And for each one, you're going to get emails.

So let's say,

let's open up a ray with emails,

and then do a simple for loop,

i equals zero,

i is less than data plus data I think it was in the third position that's in the

second so that's a zero one so that's a the first position,

well, the second position just one instead of zero, we're gonna get that and then emails, we're gonna push it into it.

So emails.push, initially we don't need it to be in a nested array.

and now what we'll do is we'll console log emails and we run this you should be

able to see that we just have all the emails on this and now we have a list

of all the

and if you were running some type of campaign automation what you can do here is you can just run

this uh once a day or whatever you need to get the attend the information grab all the emails

and then iterate through each one of these emails to send an email out using sengrid or mailchamp or

whatever api or even just the gmail app if you wanted to but that's essentially it If you ran into any issues,

feel free to drop a comment in the section below.

We're happy to help you.

Don't be shy.

If did like this video, make sure to hit that like button.

It's the best way to support this channel and help us continue to make content to help you get better,

to learn new tricks and hacks to make your daily operations.

Of course,

we got lots of videos coming out,

so make sure to check out our YouTube channel got all sorts of videos out there Talking about Google Sheets Google Apps Scripts,

the Shopify API,

AirTable, Retool, Data Studio, as well as many others If you don't see what you're looking for, feel free to shoot an email over to feedback at bootstrapping.tools

Always happy to help.

Of so don't be sure and because we have so many videos make sure to hit that subscribe button and

also the bell so that you get notified when we release the next video.

But I'm a guy called Joe this is bootstrapping tools that let's build.

It's been a pleasure and we're out.

Übersetzungssprache

Auswählen

Zusammenfassung Exportieren Mündliche Übung

Freischalten weiterer Funktionen

Installieren Sie die Trancy-Erweiterung, um weitere Funktionen freizuschalten, einschließlich KI-Untertiteln, KI-Wortdefinitionen, KI-Grammatikanalyse, KI-Sprechen usw.

Kompatibel mit den wichtigsten Video-Plattformen

Trancy bietet nicht nur zweisprachige Untertitelunterstützung für Plattformen wie YouTube, Netflix, Udemy, Disney+, TED, edX, Kehan, Coursera, sondern auch KI-Wort-/Satzübersetzung, Volltext-Immersivübersetzung und andere Funktionen für reguläre Webseiten. Es ist ein echter All-in-One-Sprachlernassistent.

Alle Plattform-Browser

Trancy unterstützt alle Plattform-Browser, einschließlich iOS Safari-Browsererweiterung.

Mehrere Anzeigemodi

Unterstützt Theater-, Lese-, gemischte und andere Anzeigemodi für ein umfassendes zweisprachiges Erlebnis.

Mehrere Übungsmodi

Unterstützt Satzdiktat, mündliche Bewertung, Multiple-Choice, Diktat und andere Übungsmodi.

KI-Videozusammenfassung

Verwenden Sie OpenAI, um Videos zusammenzufassen und den Kerninhalt schnell zu erfassen.

KI-Untertitel

Generieren Sie in nur 3-5 Minuten genaue und schnelle YouTube-KI-Untertitel.

KI-Wortdefinitionen

Tippen Sie auf Wörter in den Untertiteln, um Definitionen mit KI-gesteuerten Definitionen nachzuschlagen.

KI-Grammatikanalyse

Analysieren Sie die Satzgrammatik, um Satzbedeutungen schnell zu verstehen und schwierige Grammatikpunkte zu beherrschen.

Weitere Web-Funktionen

Neben zweisprachigen Video-Untertiteln bietet Trancy auch Wortübersetzung und Volltextübersetzung für Webseiten.