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);
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
It has a few formulas and the script works fast.
My goal is to understand why formulas cause sheet.getName() method to slow down.
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.
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.
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.
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.
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.
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.
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.