Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Update API Key to Access Token - Google Sheets Script

Topic Labels: API Scripting
1787 2
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