I'm looking for a neat solution to debounce or throttle a webhook call used in a Google Sheets Apps Script handler. Creating a simple trigger like onEdit or an "installable trigger" for other changes is straight forward but both will call the handler for every single change. If someone is editing the sheet and updates many rows over a few seconds, I want to fire only one event and not flood my webhook service. The conventional pattern for this in Javascript is to use setTimeout and clearTimeout to ensure the body of the event handler is called only once but setTimeout is not available in the Google Apps Script runtime.

I'm looking for a neat solution to debounce or throttle a webhook call used in a Google Sheets Apps Script handler. Creating a simple trigger like onEdit or an "installable trigger" for other changes is straight forward but both will call the handler for every single change. If someone is editing the sheet and updates many rows over a few seconds, I want to fire only one event and not flood my webhook service. The conventional pattern for this in Javascript is to use setTimeout and clearTimeout to ensure the body of the event handler is called only once but setTimeout is not available in the Google Apps Script runtime.

• javascript
• google-apps-script
• google-sheets
• webhooks

• If your planning on using this in situations where there is a user who has opened up the spreadsheet in standard user mode then I think you could still use onEdit() to collect edits in a buffer and then use setTimeOut from a clientside sidebar to flush that buffer and make your web hook call. I think you might like to store the short term edit buffer using Cache Service. The problem I've seen with using Utilities.sleep() as suggested below is that it plete shuts down apps script during the timeout so I always do a SpreadsheetApp.flush() before running Utllities.sleep() – Cooper Commented May 30, 2020 at 16:35
• Thanks @Cooper, I hadn't thought of using a sidebar to execute "standard" javascript while reading from data saved in the document apps script context. In my specific case, I also have updates ing from form submissions, so I don't think a sidebar would help. How would you remend using the Cache Service? Concatenating a string to a single cache key and wrapped with a lock? – Paul Egan Commented May 31, 2020 at 19:45
• I wasn't thinking of using it for executing standard javascript except for the timeout functions but I would for triggering server side functions. And you can build your buffer in the CacheService. Look at the CacheService they have some simple tutorials in there to show how to use it. – Cooper Commented May 31, 2020 at 19:58

2 Answers 2

Here is my working solution. It uses Utilities.sleep() to wait 10 seconds and check with ScriptProperties to see if this event is the last one called during that time.

Sharing for others to find, if you're looking to solve a similar problem:

/*

  Set the following project properties (File -> Project properties):

  SheetsToWatch: ma separated list of sheets to watch for events
  WebhookUrl: URL of web service to POST update to
  WebhookToken: Authorization bearer token for POST request
  SendLastValue: [optional] set if you wish last value in updated sheet to be posted


  Then create the "installable trigger" (Edit -> Current project's triggers -> Add Trigger):

  Choose which function to run: "handleChangeOrEdit"
  Select event type: "On change" or "On edit"


  A simple trigger like `onEdit` won't work have the privileges to call `UrlFetchApp.fetch`.

 */


function handleChangeOrEdit(event) {
  var sheetId = event.source.getId();
  var sheetUrl = event.source.getUrl();
  var sheetName = (event.range ? event.range.getSheet() : SpreadsheetApp.getActiveSheet()).getName();

  // Trigger only on those sheets we're configured to watch (or all if not specified)
  var sheetsToWatch = PropertiesService.getScriptProperties().getProperty("SheetsToWatch");
  if (sheetsToWatch && sheetsToWatch.split(",").indexOf(sheetName) == -1) {
    return;
  }

  var eventId = Utilities.getUuid();
  setEventTriggerWinner(eventId);
  // OPTIONAL: You might want to save values from each edit here, to be dealt with by the "winner"
  Utilities.sleep(10000);   // Wait to see if another Change/Edit event is triggered
  if (getEventTriggerWinner() == eventId) {
    Logger.log(`Trigger Winner: ${eventId}`);
    callWebhook({eventId, sheetId, sheetUrl, sheetName});
  }
}

function setEventTriggerWinner(value) {
  // Wrapping setProperty in a Lock probably isn't necessary since a set should be atomic
  // but just in case...
  var lock = LockService.getScriptLock();
  if (lock.tryLock(5000)) {
    PropertiesService.getScriptProperties().setProperty("eventTriggerWinner", value);
    lock.releaseLock();
  }
}

function getEventTriggerWinner() {
  return PropertiesService.getScriptProperties().getProperty("eventTriggerWinner");
}

function callWebhook(data) {
  var url = PropertiesService.getScriptProperties().getProperty("WebhookUrl");
  var token = PropertiesService.getScriptProperties().getProperty("WebhookToken");
  UrlFetchApp.fetch(url, {
    headers: {"Authorization": `Bearer ${token}`},
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(data)
  });
}

If you need setTimeout and clearTimeout, include this snippet in your code:

https://script.google./d/1m7GaM7Z7ivgHaAwXcgpsAwg-e7tMDm9GsN5P-xpcAGT_P6cleF_mKGD3/edit?usp=drive_web