Google Sheet webhook on cell/sheet/row edit
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

Id like to get a working demo of a google sheet that will make webhooks calls when it gets updated (cell update, new row, delete row etc..)

from this answer

https://stackoverflow.com/questions/47690332/trigger-webhook-when-google-sheet-edited
It seems we can use an onEdit trigger to make this happen

https://developers.google.com/apps-script/guides/triggers/events

and

https://stackoverflow.com/questions/52721158/python-gspead-on-change-listener

(there might be other better ways to do this since the SO post is a few years old now)

here is a endpoint you can use for your demo

https://googlesheet-test.free.beeceptor.com

it will let us inspect the events on the sheet. and here is a test sheet you can use for this bounty. (copy it to your account for testing ad configuration https://docs.google.com/spreadsheets/d/1NGoSbZ1Zamev7Gk84_13IPBUsnSY3o8SMViV_yacpJo/edit?usp=sharing

The final bounty should be the settings/scripts/details on the google sheet or account that are needed to make this work, and update a sheet that will hit the beeceptor endpoint so we can get a feel for what sort of data is sent with the change notification. Ideally it includes the change itself and where its from but if its just a notification of change we can see maybe what the change details were/are.

Are okay with using a third party service with the sheets called zapier or the drive api
ST2-EV 2 months ago
Hi Qdev, any update on this... anything you want added or modified on the solution, expiry is near...
mashtullah 2 months ago
@Qdev, hope you are good, you went mute on this...
mashtullah 2 months ago
2 months ago

Crowdsource coding tasks.

1 Solution


I did copy your google sheet and created a solution to your problem please allow me to put the code here and i will guide you on the steps to create the triggers yourself, its a pretty simple task. You will have to create two functions and two triggers for this to work.

On your google sheet open the script editor, delete everything then paste the code below.

function onEdit(e){
  var range = e.range; 
  var formData = {
        'CELL_UPDATED': range.getA1Notation(),
        'NEW_VALUE': range.getValue(),
       'SHEET':range.getSheet().getName()
  };
var options = {
    'method' : 'post',
    'payload' : formData
  };
 UrlFetchApp.fetch('https://googlesheet-test.free.beeceptor.com', options);

}
function onChange(e){
 var formData = {
    'CHANGE_TYPE':e.changeType,
 };

var options = {
  'method' : 'post',
  'payload' : formData
  };
 UrlFetchApp.fetch('https://googlesheet-test.free.beeceptor.com', options);
}

Creating the triggers

While still on the script editor click the Edit menu on the top left then click Current Project's triggers.
It will open a new window, on the bottom right you will see a blue button Add Trigger, click it.
Then make sure Choose which function to run is onEdit then Select event source is From spreadsheet then Select event type is On edit then click Save!
Repeat the same process, add a new trigger this time choose this Choose which function to run is onChange then Select event source is From spreadsheet then Select event type is On change then click Save

Your triggers will now work as you expect, if you go edit any cell and as soon as you leave that cell this trigger will run and it will hit your endpoint with the data!

That is the best way i could explain but if you haven't understood any part let me know.

Update: created a working demo

I used another account and created a working demo.
First open your beeceptor console

Then open the Google sheet

Do some edits then go back and check your beeceptor console.

The onChange trigger will handle the structural changes like deleting a row or adding a column, you will get the updates of any structural changes.
Try it out!