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.