Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Update API Key to Access Token - Google Sheets Script

672 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Clara_Wells
4 - Data Explorer
4 - Data Explorer

I'm trying to update a Google Sheets script that syncs an Airtable Base to Google Sheets. The script uses an API Key and I need to update it to use the Access Token.

Anyone know how to update this code to use the Access Token instead of the API Key? Thanks!

 

var api_key = "API KEY";
var baseID = "BASE ID";
var tablesToSync_fromSheetRange = "A14:B16";

////////// 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 = [
      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;
}

////////////////////////////////////
 
2 Replies 2
Mario_Granero
6 - Interface Innovator
6 - Interface Innovator

Hello @Clara_Wells just show your issue when i just update my code to new Token.

First, create a new personal key, with the permission of read records and bases. Give the scope base...

After that change this line of the script:


var api_key = "API KEY";

var
Token = "Bearer YOUR_TOKEN_HERE"
 
And these ones:
 
// Specify the URL to call.
    var url = [
      baseID,
      "/",
      encodeURIComponent(tableName),
      "?",
      "api_key=",
      api_key,
      "&view=",
      viewID,
      "&offset=",
      offset
      ].join('');
    var options =
        {
          "method"  : "GET"
        };
   
 
// Specify the URL to call.
var url = [
baseID,
"/",
encodeURIComponent(tableName),
"?",
"&view=",
viewID,
"&offset=",
offset
].join('');
var options =
{
"headers" :
{
"Authorization" : Token,
},
"method" : "GET",
};
 
And that's all
Barak_Almog
4 - Data Explorer
4 - Data Explorer

This looks like my code (I'm the creator of the Apps Script app). Here's the new version - https://drive.google.com/drive/u/1/folders/1J6A2EfrpmxoVziXPNz0xwwWP_-Ry0evO

Feel free to make a copy, use, enjoy. No strings attached. I created it for myself, and happy to share with the community.

Feel free to reach out with any questions - barak@sellerframe.com