Create webhooks from Google sheets
Google have an awesome and often overlooked capability in their Google scripts offering. I have always shied away from it with a kind of PTSD from the days of using VBscript in MSOffice. But recently I had a reason to create a custom function; the goal of which was to use Google vision API over an image fetched from a URL and put the results in a cell… but that’s a post for another time.
This requirement meant I started to explore Google scripts in more depth, I was honestly shocked at the capabilities that had been hiding from me for so long (https://developers.google.com/apps-script/guides/sheets/functions) so I figured given all these scripts, can I make a row by row webhook from a Google script, the answer is YES, and it’s super easy!
Webapp first
I didn’t go straight to the webhook, I first wanted to try out something I read about making the Google sheet into a web app and receiving webhooks, that was pretty simple:
Create a new sheet with some data
The sample one used below is public and available at here (make a copy to use it yourself)
Add some scripts
Open up the script editor from Tools -> Script Editor, and add a doGet and doPost function:
function doGet(e) {
var ss = SpreadsheetApp.getActive();
var rng = ss.getActiveSheet().getRange(2, 1, 10, 3)
var vals = rng.getValues()
Logger.log(vals)
return ContentService.createTextOutput(JSON.stringify(vals)).setMimeType(ContentService.MimeType.JSON);
}function doPost(e) {
return HtmlService.createHtmlOutput(“post request received”);
}
The example above will just return row 2 -10 as json
Publish as Webapp
Settings should be customised for you here, but I just set it to open for anyone), If you wanted to have this open but secure it yourself you could check the request header for some token
Go to the published url to get the JSON data
One downside here as there appears to be a redirect, so if using this from curl or wget you might need to mess with it a bit
OK thats neat, but what about…
The Webhook
To make the webhook you create a custom function for the gsheet, a custom function is basically like =LOOKUP, =IF except that you define the functionality.
Custom functions are fired whenever the input data is changed, this is the mechanism I am exploiting to get our webhook.
Create the custom function
Back in the script editor add the following code (this will post to a request bin the input from the row parameter):
function currentTime() {
var d = new Date();
var currentTime = d.toLocaleTimeString()
return currentTime;
}function CELL_CHANGED(row) {
var options = {
'method' : 'post',
'payload’ : JSON.stringify(row)
};
UrlFetchApp.fetch('http://requestbin.net/r/1hqonni1', options);
return "UpdatedAt: "+ currentTime()
}
Call the function in the sheet
Now you update the sheet to have a call to the function that takes the entire row
The row will update to say something like “UpdatedAt: 17:08:28 EEST”
When you see a successful message from the function return, the webhook has been sent with the row data as an array.
This has another awesome advantage, if you were to bake validation into your script you can return to the user a useful error message why some input data is invalid, preventing bad data from ever getting to your webhook.
The best part is, I was able to achieve all of this in < 30 mins!!!
