Has Google Apps Script slowed down in the last few years?-CodePudding

I have a Google Apps Script that uses onEdit() to add a datestamp to Column B any time Column A is edited. It's pretty much the exact scenario asked in How to ensure onEdit functions do not miss-fire.

Even with a completely empty/new spreadsheet and no other processes in the script, the execution duration is about 1 second per event trigger. And it actually takes close to 2 seconds before I see the datestamp appear in Column B.

In the solution provided in the above linked question (from 2019), the runtime was reported to be about 0.06 seconds. Almost 20 time faster than I'm experiencing. I experience the same slow (~1sec/event) speed, even when using the exact code supplied in that solution (see below).

Has GAS slowed down in the last few years? Is there something else that might be going on that would cause the slower runtime? I know 1 second isn't exactly "slow", but Column A is frequently edited--sometimes faster than once/second.

function onEdit(event) {
  var sh = event.source.getActiveSheet();
  if (sh.getName() === 'Dolly Returns') {
    var col = event.range.getColumn();
    if (col === 2) {
      var row = event.range.getRow();
      sh.getRange(row, 1).setValue(new Date());
    }
  }
}

CodePudding user response：

Google Apps Script has not slowed down over the years.

Apps Script runs on Google's servers, and the resources available on those servers vary all the time. Further, the "0.06" seconds you quote was most likely timed through a script that runs on a server, while the "2 seconds" you mention is likely the time you perceive when you are looking at the Google Sheets user interface. It takes time for script updates to show up in your browser. That probably explains almost all of the difference.

Apps Script is nowadays based on the V8 JavaScript engine, which is much faster than the Rhino engine of the days of yore. However, SpreadsheetApp and Sheets API calls remain very slow. Those calls are what a Sheets script project would typically spend almost all of its runtime with.

The onEdit(e) function you quote is inefficient because it calls two API methods every time any value in the spreadsheet is edited. When the edit happens on the 'Dolly Returns' sheet, it calls yet another API method, and when it happens in column B in that sheet, it calls yet another API method before doing its thing.

To optimize it, use the event object, like this:

function onEdit(e) {
  let sheet;
  if (e.range.columnStart !== 2
    || (sheet = e.range.getSheet()).getName() !== 'Dolly Returns') {
    return;
  }
  sheet.getRange(e.range.rowStart, 1).setValue(new Date());
}

This way, the function will not call any API methods for edits that happen outside of column B. See these onEdit(e) best practices.

CodePudding user response：

Try this:

function onEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() == 'Dolly Returns' && e.range.columnStart == 2) {
    sh.getRange(e.range.rowStart, 1).setValue(new Date());
  }
}

Using the event object as opposed to function to get row and column is much faster since the data is already in the event object