Google Sheets + Google Scripts + Zapier Webhooks?


#1

Zapier gurus:

I’m using Zapier to integrate Google Sheets with Airtable for a client. Initially, I planned to use the standard Zapier ‘updated spreadsheet’ trigger, until I read the fine print about it not triggering on second or subsequent updating of a field to a previous value. As this is a scheduling app for a dynamic marketplace, I thought it quite likely a monitored due date would be changed back to a date to which it had been set earlier. It seemed imprudent to base this critical integration around a trigger that seemed destined to fail in an early stage of testing.

Instead, I built an installable trigger-driven function in Google Scripts. Triggered by an onEdit() event, the function first checks to see if the trigger involves a monitored field; if so, it uses UrlFetchApp.fetch() to POST to a custom Zapier webhook. Once triggered, Zapier retrieves the appropriate values from Google Sheets and then locates and updates the corresponding Airtable record. Based on my admittedly non-exhaustive testing, it appears to work fine, and it has no problem reliving old dates.

My question is this: Is anyone aware of any 'gotcha’s with this scenario? Admittedly, mine is an edge use: Essentially, I only care about changes to a single column, and the rule for mapping Sheets rows to Airtable records is reasonably simple. But considering I’d never concerned myself with Google Scripts (and only barely with Google Sheets or Zapier webhook-based triggers), it was a reasonably painless exercise, and the finished implementation seems to sidestep a number of limitations of the default Zapier trigger.

Given the demonstrable upside with minimal downside (as an installed trigger, it can’t be run anonymously but must run from someone’s Google account), I have to wonder: What’s the catch? Warnings, caveats, advice, and tales of horror all gratefully accepted.

Thanks!


#2

nice workaround. congrats! I’ve used Google scripts before (although not for any too serious) and haven’t had any issues. Let me know how if any issues down the road.


#3

So far, nothing but operator error (e.g., if you copy a Google Sheet – for instance, if you fat-finger a ‘cc:’ and send the test ling to the wrong client [ahem], and need to revoke the misdirected share – triggers installed on the original sheet are not automatically installed on the copy; stuff like that).

Interestingly, though, what started as a 5-step Zap is now a 4-stepper — and I should be able to cut it to 3 steps, all by leveraging Google Scripts to avoid having to perform additional lookups or data conversions (e.g., of dates) as part of the Zap. It won’t really change the client’s monthly costs — I can’t get it below 3, since it requires both an Airtable lookup and an update — but the fewer transactions needed, the fewer the chances for something to go wrong…