Trigger webhook from Google Sheets when button in cell pressed

Question

We have our sprint capacity planning in Google Sheets, as JIRA wasn't able to efficiently break out tasks up into sprints as per number of story points per task.

We're bringing in a list of all tasks in JIRA, and their associated story points, and with some spreadsheet magic are building the sprints according to the number of available story points.

The spreadsheet updates via Zapier every time there's an edit in JIRA in the specific board, but at times when lots of things are changing it fires a lot of events, as there's so many steps in the Zapier Zap.

I was wondering if it's possible to have a button in a Google Sheets cell that can fire a webhook, perhaps with something as trivial as the date/time that can trigger the Zap to update the spreadsheet?

This means it's less 'expensive' on tasks in Zapier and it's only updating when a user clicks the button in the spreadsheet.

Answer 1

Hello tectomics I believe I have worked out a solution that should work for you.

The solution at a high level is as follows:

Create a script, located in your Google Sheet, that is triggered by a button press. The script makes an HTTP POST request to a Zapier webhook which is listening for any incoming traffic. Once the webhook is caught on Zapier's end it triggers all of your downstream steps located in the zap. Note that for this to work you will require a premium subscription to Zapier as Zapier's webhook app is premium only access.

And now for the details:

The google script is as follows:

function fireZap() {

  var endpoint = "ZAPIER_WEBHOOK_URL";

  var payload = {
    "uuid" : Utilities.getUuid()
  };

  var options = {
    "method" : "post",
    "payload" : payload
  };

  UrlFetchApp.fetch(endpoint, options)

}

NOTE: The endpoint variable is just holding an example, you will have to update that value with the endpoint URL provided by Zapier when you first create your webhook trigger.

The google script makes use of the UrlFetchApp class. Zapier's webhook endpoint only seems to trigger on unique payload data (at least in testing mode, could be different for a live zap) so we generate a unique UUID for the payload.

Once the script is written you must link it to a button located in your workbook. There are many tutorials such as this one that describe the straight forward process of doing this and so I will save myself the trouble.

Once we have all of this in place we head over to Zapier and create a new zap with the Webhooks zap for a trigger. For the trigger event select 'Catch Hook'. Proceed to the 'Customize Hook' section. Here Zapier will provide you with a unique URL endpoint, place this in the endpoint variable back in the google script.

With this complete setup whatever downstream zaps you would like and it will trigger whenever you hit the button located on your sheet.

Hope this helps,

Any questions please let me know.