Debounce or throttle event handler in Google Apps Script

user_2211429 0
Viewed 671

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.

webhooks google-apps-script google-sheets javascript
2 Answers

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: comma 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)
  });
}
user_22114290
user_49319970
Related