Using Google Apps Script to pull data from Airtable in specific Field Order

[Mohamed_Swella1]

Hey everyone!

Im using a script (that I found here in the community, thanks btw!) to pull data from Airtable to Google Sheets in order to make some weekly reports for employees in a company. Problem is, it seems each time the script runs it retrieves the Fields in a random-ish order, which is a problem because those fields are referred to in the reports.

Using Zapier is not an easy option since we have more than 400 fields.

Here is the script

var api_key = "XXXXXX"; //ADD YOUR API KEY FROM AIRTABLE HERE
var baseID = "XXXXXX"; //ADD YOUR BASE ID HERE
var tablesToSync_fromSheetRange = "A14:B15"; //UPDATE CELL RANGE HERE (for tables that you want to sync)

////////// add items to UI menu ///////////
function onOpen(e) {
   SpreadsheetApp.getUi()
       .createMenu('Airtable to google sheets sync')
       .addItem('Manually sync all data', 'syncData')
       .addToUi();
 }

////////// function to trigger the entire data syncing operation ///////////

function syncData(){
  //fetch table names from the control panel of the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tablesToSync = ss.getSheetByName("Control panel").getRange(tablesToSync_fromSheetRange).getValues();
    
  //sync each table
  for (var i = 0; i<tablesToSync.length; i++){
    var tableName = tablesToSync[i][0];
    var viewID = tablesToSync[i][1];
    var airtableData = fetchDataFromAirtable(tableName, viewID);
    pasteDataToSheet(tableName, airtableData);
    
    //wait for a bit so we don't get rate limited by Airtable
    Utilities.sleep(201);
  }
}

/////////////////////////////

function saveFormulas(dataSheets){
  //add the control panel to our list of data-related sheets
  dataSheets.push("Control panel");
  
  //initialise the object which will hold all formulas
  var formulas = {};
  
  //get all sheets in spreadsheet
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  //iterate through the sheets - if they're not used for Airtable data stuff, then save all their formulas
  for (var i in sheets){
    var sheetName = sheets[i].getSheetName();
    if (dataSheets.indexOf(sheetName) == -1){
      formulas[sheetName] = sheets[i].getRange("A:Z").getFormulas();
    }
  }
  return formulas;
}

////////// take airtable data and paste it into a sheet ///////////////////////////

function pasteDataToSheet(sheetName, airtableData){
  
  //define field schema, which will be added to the row headers
  var fieldNames = ["Record ID"];
  //add every single field name to the array
  for (var i = 0; i<airtableData.length; i++){
    for (var field in airtableData[i].fields){
      fieldNames.push(field);
    }
  }
  //remove duplicates from field names array
  fieldNames = fieldNames.filter(function(item, pos){
    return fieldNames.indexOf(item)== pos;
  });
  
  //select the sheet we want to update, or create it if it doesn't exist yet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet; 
  if (ss.getSheetByName(sheetName) == null){
    sheet = ss.insertSheet(sheetName);
  } else {
    sheet = ss.getSheetByName(sheetName);
  }

  //clear data from sheet
  sheet.clear();
  
  //add field names to sheet as row headers, and format the headers
  var headerRow = sheet.getRange(1,1,1,fieldNames.length);
  headerRow.setValues([fieldNames]).setFontWeight("bold").setWrap(true);
  sheet.setFrozenRows(1);
  
  //add Airtable record IDs to the first column of each row
  for (var i = 0; i<airtableData.length; i++){
    sheet.getRange(i+2,1).setValue(airtableData[i].id);
  }
  
  //// add other data to rows ////
  //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 
    }
  }  
}

////////////// query the Airtable API to get raw data ///////////////////////

function fetchDataFromAirtable(tableName, viewID) {
  
  // Initialize the offset.
  var offset = 0;

  // Initialize the result set.		
  var records = [];

  // Make calls to Airtable, until all of the data has been retrieved...
  while (offset !== null){	

    // Specify the URL to call.
    var url = [
      "https://api.airtable.com/v0/", 
      baseID, 
      "/",
      encodeURIComponent(tableName),
      "?",
      "api_key=", 
      api_key,
      "&view=",
      viewID,
      "&offset=",
      offset
      ].join('');
    var options =
        {
          "method"  : "GET"
        };
    
    //call the URL and add results to to our result set
    response = JSON.parse(UrlFetchApp.fetch(url,options));
    records.push.apply(records, response.records);
    
    //wait for a bit so we don't get rate limited by Airtable
    Utilities.sleep(201);

    // Adjust the offset.
	// Airtable returns NULL when the final batch of records has been returned.
    if (response.offset){
      offset = response.offset;
    } else {
      offset = null;
    }
      
  }
  return records;
}

////////////////////////////////////

Thanks !

[Bill_French · Accepted solution]

Mohamed_Swellam:

it seems each time the script runs it retrieves the Fields in a random-ish order, which is a problem because those fields are referred to in the reports.

Can you explain to me why order matters? (perhaps rhetorical based on my findings below)

When I build integration systems, the order of the fields is typically irrelevant because the field names themselves provide discrete identification of values - e.g., record.fields.accountID is unambiguous. The trouble is this script allows ambiguity in the sheets update process.

Observation - Despite Airtable’s [seeming] random order of fields in API results, it is not something Airtable needs to apologize for; developers have a responsibility to anticipate things like this.

Mohamed_Swellam:

  // add every single field name to the array
  for (var i = 0; i<airtableData.length; i++){
    for (var field in airtableData[i].fields){
      fieldNames.push(field);
    }
  }

The problem is in this part of the script - it attempts to smartly cut a big (400 field) corner to elegantly encapsulate the transformation from 400 field names into one array of values. The trouble is that these columns may not actually match the order that the data values will be pulled from the API’s result set. Furthermore, it’s not sustainable - any slight change in column order of the sheet will likely result in a catastrophic labeling failure.

Recommendation - The column headers of the target sheet should contain a note (comment) that explicitly indicates which field’s data belongs in that column. Using this approach, the data schema is unambiguous - your script can know precisely where each data value should be saved. There will be no dependencies on the sheet nor the API result set.

Off Script… (a little rant)

One thing about this script is the lack of optimized array handling to minimize API calls and I/O. The following code snippet suggests updates are occurring cell-by-cell.

Mohamed_Swellam:

.setValue(airtableData[i].fields[field]); //update the cell

Imagine a table with 400 fields and 200 records to update; that’s 80,000 cells. Now imagine you are making a separate API call for each update - yes, that’s right; 80,000 API calls. That’s precisely what the above line of code is doing. A single cell update is tantamount to an API call into the Google Sheets API.

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);

[Mohamed_Swella1]

Thanks for your reply @Bill.French

Bill.French:

Can you explain to me why order matters? (perhaps rhetorical based on my findings below)

It is because we are using this data to create reports of the employees’ KPIs, if the column order changes, the numbers in the reports will be messed up.

Thank you so much for such detailed reply. As I have no idea about scripting yet, Im unable to rectify that (or even check the code for error if that matters), but I understand the logic behind what you are saying.

I have managed to get someone to build a custom script for the import and it is working like a charm. Will have to learn scripting soon though …

[pbaron]

Hi Mohamed - can you share your script?