Formulas make SpreadsheetApp work slower in Google Sheets Script

user_5372400 0
Viewed 125

This problem appeared recently ~August 25, 2022.

My onEdit trigger relies on a sheet name and the script runs too long to get it. This is the smallest script to reproduce an error in my enviroment:

function onEdit(e) {
  var t = new Date();
  console.log('we are in onEdit!');
  var sheetName = e.range.getSheet().getName();
  console.log(sheetName);
  console.log('We got sheet name. Time past = ' + (new Date() - t));
}

The function range.getName() works too slow, and the script is timed out.

I did not see this behavior any time before. Other sample: SpreadsheetApp. In my other file this line takes 30 sec.:

SpreadsheetApp.openById(id);

Reproduce the error

Here's the file with minimal code needed to reproduce the error:

onEdit Crash Test ➡️ e.range.getSheet().getName()

Please make a copy.

To Reproduce the onEdit Error

  1. Print 1 in the first cell
  2. Immediately press enter and Print 2
  3. Repeat it a few times
  4. Go to the script editor and see execution time

Original

It has a few formulas and the script works fast.

My goal is to understand why formulas cause sheet.getName() method to slow down.

Error

Exceeded maximum execution time

The max. time for onEdit is 30 sec. Tests showed that sheet.getName() worked too long. I cannot see the edited sheet name without the getName() function as it is inside a trigger.

My Fix

Even Lock and Cache did not resolve this:

Injection

  /**  Injection. Use Cache to Speed Up `getName()` method */ 
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(30000); // wait 30 seconds for others'
  } catch (e) {
    throw 'Could not get Sheet Name in 30 sec. :(';
  } 
  var sheetNameCacheKey = 'sheetNameforThoseWhoForgotten';
  var c = CacheService.getUserCache();
  var sheetNameFromCache = c.get(sheetNameCacheKey);
  if (sheetNameFromCache) {
    sets.sheetName = sheetNameFromCache;  
    console.log('Prolongated Cache Life!');
    c.put(sheetNameCacheKey, sets.sheetName, 5);
  } else {
    // This function is actually needed, 
    // but does not work fast
    //               ⏳
    sets.sheetName = sets.sheet.getName(); 
    // Write Sheet Name to Memory for 5 seconds
    c.put(sheetNameCacheKey, sets.sheetName, 5);
    console.log('Added Sheet Name to Cache!');
  }
  lock.releaseLock();
  /**  End of Injection */

This code makes my trigger more stable, but it still fails occasionally.

Speed Tests

time to read sheet name is: 151766

This is almost 3 minutes! But this number changes and occasionally it may take ~150 ms to run.

Notes

My guess is this error caused by the latest big update and new functions for Google Sheets:

I think so because this problem comes in hand with IMPORTRANGE error:

Import Range internal error.

google-sheets-formula google-apps-script google-sheets triggers performance
3 Answers

Issue and workaround:

I have experienced the same situation as you. It seems that when the large calculations by the formulas are included in the Spreadsheet, such a situation occurs. In this case, this is also reflected in the trigger and the direct execution of the script. I'm not sure whether this situation is the current specification or a bug.

At that time, I noticed that there are differences in process costs for the methods of Google Apps Script under this condition. For example, when I saw your provided script in your sample Spreadsheet, I found the following script.

function onEdit(e) {
  var t = new Date();
  console.log('we are in onEdit!');
  var sheetName = e.range.getSheet().getName(); // times out ⏳!
  console.log(sheetName);
  console.log('We got sheet name. Time past = ' + (new Date() - t));
}

When this script is used as a sample, in this case, the process cost of e.range.getSheet() is much lower than that of e.range.getSheet().getName(). By this, as a sample, in order to retrieve the sheet name of the active sheet, it is required to use the methods of low process cost. In this workaround, as a sample situation, using your provided Spreadsheet, I would like to propose a modified script from your above script.

In this workaround, I use e.range.getSheet().getSheetId() instead of e.range.getSheet().getName(). And, Sheets API is used. Because the process cost of e.range.getSheet().getSheetId() is lower than that of e.range.getSheet().getName(), and, in order to retrieve the sheet name from the sheet ID, when the Spreadsheet service (SpreadsheetApp) is used, the process cost becomes high.

When this workaround is reflected in your above script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of your provided Spreadsheet. And, please enable Sheets API. And, please install the OnEdit trigger to the function of installedOnEdit. When you use this script, please put a value to a cell. By this, the script works and you can see the sheet name of the active sheet in the log.

function installedOnEdit(e) {
  var t = new Date();
  console.log('we are in onEdit!');
  var sheetId = e.range.getSheet().getSheetId();
  var ssId = e.source.getId();
  const sheetName = Sheets.Spreadsheets.get(ssId).sheets.find(s => s.properties.sheetId == sheetId).properties.title;
  console.log(sheetName)
  console.log('We got sheet name. Time past = ' + (new Date() - t));
}

  • When a value is put to a cell, you can see the sheet name in the log. In my test, the process time was about 3 s.

  • In my case, as a workaround like this, when OnEdit trigger and script are used in a Spreadsheet that the large calculations by the formulas are included, I investigate the low-cost methods and use them.

Note:

  • In this workaround, when the function is executed OnEdit trigger, the formulas are continued to be calculated. So, please be careful about this.

Additional information:

As additional information, when I tested this situation, it was found that the cell values are not retrieved, and the process is not affected by the calculation of formulas. When the above sample script is seen, the cell values are not retrieved. It seems that by this, the process cost can be reduced during the calculation of formulas.

On the other hand, when the cell values are put to the cells using Sheets API, it was found that the process is not affected by the calculation of formulas. And also, it was found that when the cell values are put using the Spreadsheet service (SpreadsheetApp), the process is affected by the calculation of formulas.

user_71086530

What I would do is wrap your function with a timer to see what is causing the delay.

class Timer {
  constructor() {
    this.start = new Date();
  }
  stop() {
    let end = new Date();
    end = (end.valueOf()-this.start.valueOf())/1000;
    console.log("elapsed time: "+end.toFixed(3)+" sec" );
  }
}

function testTimer() {
  try {
    let time = new Timer();
    let a = 0;
    for( let i=0; i<10000; i++ ) {
      a = a+i;
    }
    time.stop();
  }
  catch(err) {
    Logger.log(err);
  }
}

5:49:09 AM  Notice  Execution started
5:49:10 AM  Info    elapsed time: 0.001 sec
5:49:10 AM  Notice  Execution completed

For example in your onEdit_DVL_(e):

function onEdit_DVL_(e) {
  let time = new Timer();
  var sets = getConfigDvl0_();
  time.stop();
.
.
.
    time = new Timer();
    sets.sheet = e.range.getSheet();
    sets.sheetName = sets.sheet.getName(); 
    sets.range = e.range,
    sets.value = e.value;
    time.stop();

And on any other section of code you think is causing the delay.

user_36567390

Nowadays the Google Sheets UI shows a progress bar having a tooltip showing the task being done in the client-side.

  1. Loading...
  2. Calculating formulas...
  3. Loading cells...

Each sheet might show a different progress (bar size and tooltip text).

On my environment when opening your spreadsheet it takes a lot of time (more than a couple of minutes) for the progress bar to disappear and the cell Formulas!H1 shows #REF and Error > Import Range Internal Error.

Maybe a change made by Google to the Google Sheets app might affected the spreadsheet performance making not only the Google Apps Script service be slower than what you have experienced in the past but also making at least one formula return an error. You might report this to Google by using the Help > Help Sheets Improve button and/or the official Google Sheets community, or if you are Google Workspace admin by submitting a support ticket. In meantime you could try to reduce the calculation time of your spreadsheet, i.e. by replacing calculations that aren't really needed to be done every time that the spreadsheet is opened and / or on every recalculation by their results by doing a copy paste as values only or by using Google Apps Script in order to have control on when the "calculations" are done.

Another thing that might help is to avoid the use of open references. I.E. your spreadsheet has =INDEX(VLOOKUP(A:A,{F:F,G:G},2,)) in Formulas!B1 that is returning a lot of #N/A at the bottom of the copy that I made of your spreadsheet. Open references might cause that the spreadsheet have extra blank rows that when there were to many, affect the spreadsheet opening time.

user_15954510
Related