Help

Re: Airtable to Google Sheets script

Solved
Jump to Solution
2592 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Filipa_Didier
6 - Interface Innovator
6 - Interface Innovator

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!!

1 Solution

Accepted Solutions
Bill_French
17 - Neptune
17 - Neptune

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?

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.

See Solution in Thread

5 Replies 5
Bill_French
17 - Neptune
17 - Neptune

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?

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.

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

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?

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"

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?