Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Creating a google app script, to sync one-way from a google sheet to an Airtable Table

663 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_at_Easy_La
7 - App Architect
7 - App Architect

I am trying to create a google App Script, which contains columns mapping to an Airtable table. I would like to sync one-way from the Google Sheet, into Airtable.  The first column is the primary key, and is used to match. If the record exists in Airtable, it will update. Else, it w

ill create a new row.

I am trying to execute the below function, to find out if the record exists already by primary key value (Note - NOT record ID).

 

function upsertAirtableRecord(apiKey, baseId, tableName, primaryKey, fields, primaryKeyFieldName) {
  var url = 'https://api.airtable.com/v0/' + baseId + '/' + encodeURIComponent(tableName);

  // Search for the record to update
  var searchUrl = url + '?filterByFormula=(' + encodeURIComponent(`{${primaryKeyFieldName}}='${primaryKey}'`) + ')';
  var options = {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + apiKey,
    },
  };

 

This is returning an error:

 

Exception: Request failed for https://api.airtable.com returned code 403. Truncated server response: {"error":{"type":"INVALID_PERMISSIONS_OR_MODEL_NOT_FOUND","message":"Invalid permissions, or the requested model was not found. Check that both you... (use muteHttpExceptions option to examine full response)

 

First of all, what is the 'use muteHttpExceptions' referring to? I am using the 'tableName', not ID.  

The PAT I've created should have read/write scopes, and also set as 'current and all future bases'.

 

Any idea what could be wrong? It is my first time connecting directly with the API - so any help would be greatly appreciated 🙂

Regards,
Karl

1 Reply 1
jamesnevada
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey Karl -

Assuming that your Base ID, Table Name, and PAT are all written in your code correctly (no extra characters, spelling mistakes, trailing/leading spaces), one issue could be this:

var searchUrl = url + '?filterByFormula=(' + encodeURIComponent(`{${primaryKeyFieldName}}='${primaryKey}'`) + ')';

- The equals sign needs to be URI encoded (%3D)
- The parentheses are not needed

If that does not work - you can try to change the Table Name to Table ID, see if that does anything.

Also, regarding muteHttpExceptions, it just allows you to see an expanded error message from Airtable - error messages are truncated on default.

  var options = {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + apiKey,
    },
    muteHttpExceptions: true
  };

Hope this helps!

James