Seeking More Elegant Way to Watch Changes in Multiple Google Sheets

How To
1

Hi Makers,

I’ve got a client who needs to watch changes in a bunch of Google Sheets. I’m trying to think through more elegant ways to grab row updates once or twice a day from a group of Sheets, other than making a separate scenario for each one triggered by a Watch Changes module. I think this approach would also require a different webhook be added to the Make add-on in each Sheet, which would be a real hassle for the client, who understandably is not about to give me access to their Google account! Especially since they will be adding new Sheets all the time.

I’ll keep updating this as I think of things, but if anyone else has clever ideas I’d love to hear them!

Best,

Robert McKay

2

This sounds interesting and challenging, as not being able to access sheets lets you with just one part of the solution.

With access to the sheets, I would suggest you do the following:

Create a Webhook that will receive Workbook and SheetId with the row information being modified and then you could route in one scenario what to do depending on the workbook and sheet case.

but how to get a webhook fired for each sheet row CRUD operation?

You will need to add functions to each Google sheet to send the row operation to your webhook.

you can use the Google Apps Script’s onChange trigger combined with the UrlFetchApp service to send webhook requests. Here’s an example code snippet that sets up the desired functionality:

function sendWebhook(eventType, range, sheetName, editedBy) {
var webhookUrl = “YOUR_WEBHOOK_URL”;
var payload = {
eventType: eventType,
range: range,
sheetName: sheetName,
editedBy: editedBy,
timestamp: new Date().toString()
};

var options = {
method: “POST”,
contentType: “application/json”,
payload: JSON.stringify(payload)
};

UrlFetchApp.fetch(webhookUrl, options);
}

function onChange(e) {
var eventType;
var range;
var sheetName;
var editedBy;

if (e.changeType == “INSERT_ROW”) {
eventType = “row_added”;
range = e.source.getActiveRange().getA1Notation();
} else if (e.changeType == “REMOVE_ROW”) {
eventType = “row_deleted”;
range = e.source.getActiveRange().getA1Notation();
} else if (e.changeType == “EDIT”) {
eventType = “row_edited”;
range = e.source.getActiveRange().getA1Notation();
editedBy = e.source.getActiveSheet().getRange(e.range.rowStart, 1).getValue();
}

sheetName = e.source.getSheetName();

sendWebhook(eventType, range, sheetName, editedBy);
}

Make sure to replace "YOUR_WEBHOOK_URL" with the actual URL you want to send the webhook requests to. The sendWebhook() function formats the payload data and sends a POST request to the provided URL.

To set up the trigger, follow these steps:

  1. Open your Google Sheets document.
  2. Click on the “Extensions” menu.
  3. Select “Apps Script”.
  4. Replace the default code with the above code.
  5. Save the project.

Now, whenever a row is added, deleted, or edited in any worksheet under your Google account, the onChange() function will be triggered and a webhook request will be sent to the specified URL.

3 Likes