Help

Re: Google Sheets + Google Scripts + Zapier Webhooks?

1184 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

4 Replies 4
openside
10 - Mercury
10 - Mercury

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.

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…

nck_capo
4 - Data Explorer
4 - Data Explorer

Did you ever hash out a solution for this besides what you put together ? I am having trouble doing a similar task. and then return an updated field from Airtables back to google sheets. I could explain more in depth and am about to just write a custom Zap and and serve it up but then bottle necking becomes the issue.

Nope – everything continues to work fine. I was able to leverage Google Scripts by adding logic to the client’s Sheet, which cut back on the number of internal Sheets triggers that ultimately turned out to be NOPed. I don’t know if it applies here, but the guys at Openside have a suite of products designed for working with Airtable and Zapier that might help…