This very simple Apps Script in in a Google Sheet. The sheet has one row and five columns; the sheet is essentially empty.
The Apps Script is deployed as a web app to run as me and be runnable by anyone with the URL. The URL is called by only one caller (me) and is called maybe once every 5 seconds while testing.
Post to the URL and it returns (example code) WORKED!
but maybe 1 in 10 times or 1 in 20 or sometimes 1 in 4 times it will return (example code) ERROR: Error: Service Spreadsheets failed while accessing document with id 1m6qLk8zHLsSSIHBNQBSEaj-80s25xxxx <<REMOVED REST OF ID FOR PRIVACY>>
var deploymentType = "(example code)";
function doPost(e) {
var output = 'init';
try {
var mappingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Devices');
output = deploymentType + " WORKED! ";
} catch(error) {
output = deploymentType + " ERROR: " + error;
}
return ContentService.createTextOutput(output);
}
I think if it works once, then it should work every time. Note that all this sample code is doing is getSheetByName. There is no heavy processing going on. This is really simple.
I have also tried deploying this as a separate web app calling SpreadsheetApp.openByUrl
but the intermittent failure still occurs.
I have tried making two calls, one to getActiveSpreadsheet then a Utilities.sleep(10000)
before calling getSheetByName. That does not make any difference.
I have tried calling SpreadsheetApp.flush
after getActiveSpreadsheet. That does not make any difference.
I reported it to Google https://issuetracker.google.com/issues/388557169 but they closed it out as a duplicate of another problem that has been open since 2020. That problem has to do with timing when the spreadsheet is doing some heavy lifting. I believe this problem is different since the spreadsheet is empty.
I have found an answer: Turn off "Enable Chrome V8 runtime" for your Apps Script. When this is enabled the failure occurs within 10 transactions, often within the first 3. When this is disabled I have run 400 transactions without a single failure.
After changing this setting you will need to Deploy your script again, even though you have not changed any code.