Add row to Airtable when update made in google sheets

Hello,

I’m wanting to know if it is possible to add a row when things are added to google sheets. I’ve tried using Zapier, but it doesn’t seem to work the way I want it to.

It is not letting me add a picture of it to this post, so here is a link to it: imgur[dot]com/XadQ88y

I want to be able to put businesses names in each of the Business 1, 2, 3… sections and their email in the corresponding spot.

When I use Zapier, if I put business 1 and business 2 in at the same time, they end up in the same cell in Airtable. I want them to get their own row.

Is there a way for me to do that?

Well, the easy answer is, “Of course!”

The answer to your next question, “How?” is more difficult, because it depends on how you’re triggering Zapier, and what you’re doing once it’s triggered.

It’s been a while since I worked with the Google Sheets/Zapier/Airtable triumvirate, but I found I had more flexibility when I used Google Scripts to initiate a webhook-triggered event in Zapier than when I used Zapier’s built-in Sheets trigger. In my case, I needed to support the likelihood a Sheets cell would be changed from Date 1 to Date 2 and then back to Date 1, when the second instance of Date 1 wouldn’t cause a trigger. In your case, you need a way to break down a trigger containing changes to multiple fields in a way that identifies each changed field.

Presumably, there’s a way to do so in Zapier that allows for a process to loop through the trigger data and initiate a separate Airtable action for each data value — but, again, ‘if’ and ‘how’ depend upon the way you have triggers and actions defined, so I can’t address that. However, I can say that if you use a Google Scripts onEdit() event to send a webhook to Zapier, you can reliably trigger on each field update. This would allow a 1-to-1, trigger-to-action Zap design, where each trigger would result in a new row being added.

Unfortunately, I built this integration for a client who managed Congressional and presidential primary and general election campaigns, and both their Google Sheet and Airtable base contained scads of proprietary information — versions of television ads currently being focus grouped, for instance. Once the project was done, they asked I delete all my copies of their data, and I complied; unfortunately, this includes my working copy of the webhook-generating Google script, which was stored as a Sheet-specific script. As it didn’t contain any data more sensitive than cell numbers and field names, I probably have a copy of the script or of my working notes backed up somewhere, but not where I can immediately put hands on…

Still, search for onEdit() as a Google Scripts function, to see how to associate it with a monitored row, column, or range of fields. I used the Webhooks by Zapier app integration to receive the webhook and the Google Scripts UrlFetchApp.fetch() function to send it. As I recall, it was relatively simple to implement, and it held up through my and the customer’s tests — and, presumably, their production use.


Edit: I lied; the notes were right where they should be — a first.

Here’s the syntax of the Script I used — at least, I think it’s the most recent version. As I recall, it needs to be associated with the current project’s triggers. Also as I recall, it’s not the most elegant implementation: It triggers any time a cell is updated and then checks the cell’s row and column; if the cell falls in the appropriate column, it bundles together a message payload and send the webhook. In a high-volume environment, this would prove, um, non-optimal. :wink:

function myOnEdit(e) {
  
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  if (col == 7) {
    var dateValue = range.getValue();
    range = range.offset(0,3);
    var recordValue = range.getValue();
    if (recordValue !== "") {
      var sheetUpdate = {"row": row, "column": col, "dateValue": dateValue, "recordValue": recordValue}
      var myJSON = JSON.stringify(sheetUpdate);
      var options = {
        'method' : 'post', // specify the request type
        'contentType': 'application/json', // specify the Media Type
        'payload' : myJSON // my JSON stringified object
      };
// send the request
//    Logger.log(Session.getActiveUser().getEmail());
      UrlFetchApp.fetch('https://hooks.zapier.com/hooks/catch/#######/xxxxxx/', options);
    }
  }

}

It’s been a year+ since I worked on this, but I think onEdit(e) causes a trigger whenever an edit is performed on any cell in the sheet, where the variable e is some sort of Script object that contains cell and range address info. If I read the rest of the script correctly, the process worked something like this.

  1. When an edit occurs, check row/column.
  2. If the edited cell is in column H (which is presumably the ‘Date’ column)
    1. Store the value of the cell in dateValue.
    2. Store the value of column K (or possibly C; I don’t remember how range.offset() works) in the same row in recordValue.
    3. If recordValue isn’t null, assemble and send the webhook.
      1. Create an array sheetUpdate containing indices and values for row, column, dateValue, and recordValue.
      2. Convert the sheetUpdate array to the JSON object myJSON.
      3. Create a webhook definition called options.
      4. Using UrlFetchApp.fetch(), send the webhook to the defined Zapier webhook receptor. (When you configure a webhook, Zapier provides you with the specific ‘#######/xxxxxx’ part of the URL to use.)

Again, it’s been a while, so I’m not sure, but I think my Zapier Airtable integration performed an ‘update or create’ action against the appropriate Airtable record. I don’t recall exactly how the record was named; presumably it was based on some permutation of dateValue and recordValue. I also don’t remember why I passed row and column ID in the trigger; perhaps it was an idiot-check to make sure I was triggering only on a valid edit; otherwise, it may have been part of record-identification or -naming logic — or it may simply have been debugging code I thought unobtrusive enough not to bother to delete.

That’s probably about 12 times as much info as you want or need — but should you ever need it, you now know where to find it. :wink:

@W_Vann_Hall, that seems to be a really clean approach. However, if we go to that trouble to push to Zapier and then push to Airtable, why not just go straight to Airtable (via the API)?

The Google Apps Script code to write to the base to add records is almost identical to the approach you created for Zapier.

@Bill.French, I am trying to modify the code above to POST from my google sheet to airtable directly via the API when a new record is added in sheets. I have been trying to modify the above code without any success. can you please help with this?

(note, i have successfully been able to pull data from airtable to google sheets with a pull request, but can’t seem to do figure out the Put request.

When trying to run the code, i get the following error:

TypeError: Cannot read property ‘range’ of undefined (line 13, file “CodePost2”)

This is my code:

function myOnEdit(e) {
  
  var API_KEY = 'XXXXXXXXXXXX';
  var root = 'https://api.airtable.com/v0';
  
  var baseId = 'XXXXXXXXXXX';
  var tableName = encodeURIComponent('Test Form 1');
  
  var endpoint = '/' + baseId + '/' + tableName;
  
  
  
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  if (col == 7) {
    var dateValue = range.getValue();
    range = range.offset(0,3);
    var recordValue = range.getValue();
    if (recordValue !== "") {
      var sheetUpdate = {"row": row, "column": col, "dateValue": dateValue, "recordValue": recordValue}
      var myJSON = JSON.stringify(sheetUpdate);
      var options = {
        'method' : 'post', // specify the request type
        'contentType': 'application/json', // specify the Media Type
        'payload' : myJSON, // my JSON stringified object
        'headers': {
          'Authorization': 'Bearer ' + API_KEY
          }
      };
// send the request
//    Logger.log(Session.getActiveUser().getEmail());
      UrlFetchApp.fetch(root + endpoint, options);
    }
  }

}

Thanks for any help in advance!
-Dom

I can’t really make an accurate assessment without line numbers. I can only guess where line 13 is in your Google Apps Script file.

But, I can say that this error is unrelated to Airtable’s API; rather, it’s a simple mismatch of cell values into your code which [apparently] relies on a range selection such as a selected row in a spreadsheet.

Once you remedy the script error involving the range issues, the most important debugging step you could make is to inspect the value of sheetUpdate to see exactly what data is being arrayed for posting into Airtable.

Ranges…

Without seeing the actual sheet, this also presents a debugging challenge. This code suggests the update is based on a single cell in the sheet that happens to lie in column 7.

var range = e.range;
var row = range.getRow();
var col = range.getColumn();

And the scary part is the abrupt use of the range variable again for an entirely different purpose which appears to be an array unlike the first use where it looks like it’s a date in a specific cell.

range = range.offset(0,3);
var recordValue = range.getValue();

Bottom line - your issues are likely related to the code as it interacts with your spreadsheet cells.

@Bill.French,

Thanks for the prompt reply. I’m relatively new to programming in appscript so i’m a little over my head…Let’s go one step back and do a simple POST command to airtable.

Airtable API says i can use the following Javascript code:

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('XXXXXXXX');

base('Test Form 1').create([
  {
    "fields": {
      "Pass": "Yes",
      "name": "Dom"
    }
  },
  {
    "fields": {
      "Pass": "No",
      "name": "Mike"
    }
  }
], function(err, records) {
  if (err) {
    console.error(err);
    return;
  }
  records.forEach(function (record) {
    console.log(record.getId());
  });
});

This can’t just be cut and pasted into appscript(tried, didn’t work). I have also tried the following:

function apiCall2(){
  
  var API_KEY = 'XXXXXXXXXX';
  var root = 'https://api.airtable.com/v0';
  
  var baseId = 'XXXXXXXX';
  var tableName = encodeURIComponent('Test Form 1');
  
  var endpoint = '/' + baseId + '/' + tableName;
  
  
  var data =   
    { "fields": {
         'Pass' :'Yes',
          'name' : 'Cheryl'
          }
    }
  
  var params = {
    'method': 'POST',
    'headers': {
    'Authorization': 'Bearer ' + API_KEY
    },
    'payload' : data
  };
  
 UrlFetchApp.fetch(root + endpoint, params);
 

}

Almost Positive that my var data field is not formatted properly to pass to Airtable. Can you assist in letting me know how to format var data?

I get the following error code when i run my script
Exception: Request failed for https://api.airtable.com returned code 422. Truncated server response: {"error":{"type":"INVALID_REQUEST_UNKNOWN","message":"Invalid request: parameter validation failed. Check your request data."}} (use muteHttpExceptions option to examine full response) (line 27, file "CodePost")

Okay - it appears from the latest response that you have changed gears entirely. This newest attempt is not anything like the earlier attempt, and yet again - no line numbers with your code. I have no clue what line 27 is. Until I do, it’s difficult to provide any assistance in a practical assessment.

I can say this though…

Where exactly do you think Airtable meant for this code to run? Google Apps Script? A web app? NodeJS? You need to be more clear about where you are attempting to run this code. If it’s in Google Apps Script it will likely never work unless you transition to the new V8 runtime system if this line of code needs to run (var Airtable = require('airtable');).

This function (apiCall2()) is suitable for Google Apps Script, however, (as you sensed accurately) the data variable is inadequate for a POST into Airtable’s API. The API docs - among the best in the industry - are very clear on the format of data payloads:

image

@Bill.French Got It to work! Thanks!

1 Like

Okay just to end the scourge of “solved it” without answers, here it is:

var data = {
  'records' : [
    {'fields' : {
      'Category': 'Bananas'
    }}
  ]

}

var params = {
'method': 'POST',
'muteHttpExceptions': true,
'contentType': 'application/json',
'headers': {
  'Authorization': 'Bearer ' + API_KEY,
},
'payload': JSON.stringify(data)

};

Michael, welcome to the community.

Indeed, there are many “solved” but unclear outcomes in this (and every forum), but this particular question is not one of them. At the outset, the user never marked this solved despite an open sharing of the final set of changes that caused the user to have success. However, the solution was largely related to issues in Google Apps Script and unrelated to issues concerning Airtable. Furthermore, the question morphed along the way as the details changed. As such, the “answer” is multi-faceted. Your example is clear and easy to understand, but if you study all of the threads, it does not really address the deeper issues that this user faced.

It’s unfortunate that users sometimes do not take the time to confirm the outcome or mark helpful exchanges as “solved”. It’s also unfortunate when the last post gets a tick mark after hours of conversations to unearth the true issues and requirements.

Oh yeah too right. I should’ve been more clear!

I was referring to that like stereotype of someone coming back and saying “solved it” on experts exchange/stackoverflow et all type sites without posting what they did! Hahah. Sorry I came off as a bit wanky.

No worries; just keepin’ it real. :slight_smile: And thanks for the code.