Airtable to Google Sheets script

[Filipa_Didier]

Hi all!

I’m in desperate need of a way to get the data I have in a table into Google Sheets. I would prefer not to use Integromat, but a script.

I’m not a developer… I found a script (https://www.optimi.co.nz/blog/2019/05/11/you-too-can-make-dashboards-how-to-visualise-your-airtable-...) but it pulls all the columns of the table and I would like it to pull only the ones that are not hidden in a specific view.

Also, since I will be referring to those columns in other sheets, I need their order not to chance when it updates.

Any suggestions? @Bill.French?

Thank you!!

[Bill_French · Accepted solution]

Filipa_Didier:

it pulls all the columns of the table and I would like it to pull only the ones that are not hidden in a specific view.

Yep - this is how that script approaches the integration challenge; it assumes you want all of the fields. But who really cares if it pulls too much information?

Filipa_Didier:

I need their order not to chance when it updates.

Well, you now have two reasons to do the following:

1. Since the free script you found pulls in all of the data, let’s assume you have that working in a sheet tab named “RawData”.
2. Given the requirement that you don’t need all of the data fields for analytical purposes, simply create another sheet in the same spreadsheet document named “VizReadyData” and use spreadsheet array functions to pull over the fields that you actually need.
3. Given the requirement to order the fields specifically, establish your array formulas such that they place the columns in exactly the order you want.

By doing this, you will have a VizReadyData sheet with exactly what you want.

The formula for pulling records from one column in RawData to another column in VizReadtData is likely to be ArrayFormula() which need only exist in the first cell of each column in the VizReadyData sheet. For example, if you wanted column A in RawData to be updated to column G in VizReadydata, this is probably the formula you would place in the first cell of VizReadyData (i.e., A1).

ArrayFormula(VizReadyData!A2:A = RawData!A2:A)

Rinse-repeat for all the columns you need and you should be ready to perform your dashboard activities.

[Filipa_Didier]

Thank you, Bill! I was curious to know how to do this in the script, but your solution will do fine!

[Filipa_Didier]

Just one question, @Bill.French.
I used your solution from another post:

Recommendation - Build all updates for sheets into a 2-dimensional array and update the entire sheet with one call … ergo, instead of this …

// for each record in our Airtable data...
for (var i = 0; i<airtableData.length; i++) {
  //  iterate through each field in the record
  for (var field in airtableData[i].fields) {
    sheet.getRange(i+2,fieldNames.indexOf(field)+1) //find the cell we want to update
      .setValue(airtableData[i].fields[field]); //update the cell 
  }
}  

…do this:

sheet.getRange(entireUpdateRange).setValues(aData);

but I’m getting this error:
ReferenceError: entireUpdateRange is not defined

What could be the problem?

[Bill_French]

Filipa_Didier:

sheet.getRange(entireUpdateRange).setValues(aData);

Have you defined the entire update range for the array you want to insert?

The array aData has a shape - it is y rows by x columns. The update range in the sheet has to match that shape and specify the columns and rows like this:

"A1:G300"

[Filipa_Didier]

no, i have no idea how to do that… i know nothing about scripts, but i’m trying to learn… how do i do that?

[BenFortunato]

I need to pull in data from a google sheet and convert a flat table into linked fields. For example a property row might have a contact field with all the contact details, but our airtable base has a contact table so I would need to create a contact record and then link that to the property record.

I've done some basic scripting in airtable but found that its limited by a 30 second run time. The data coming in would have 2000+ rows and I anticipate that this would take longer than 30 seconds. I supose I could break up the google sheet ito smaller chunks and do one at a time, but wanted to see if anyone had anyexperience with creating a more complicated script

The other option would be to create an integromat . make.com script. In that case I don't have any time limits on the workflow but I would need to deal with Airtables API rat limits which are also fairly strick, only 10 records per request and 5 requests per second. Its also a lot easier to script this in Javascript than try to create a complex nested JSON object with no-code tools. 

Does anyone have any tips for the best way to work around all these limits that airtable places on scripting and API requests? Not sure if there are any memory restrictions as well. Ideally I would be able to write a script and have that run for however long it takes, and have access to some storage to write temporary data if required.