I'm writing a Google Sheets add-on that copies some data from one spreadsheet to another and then reformats it. The data sets involved are often large (~100k rows), so to avoid hitting the 6-minute timeout limit I break the data into chunks and then run the data-copying function in parallel on each chunk using google.script.run calls from the client side.
On my sample data set of ~100k rows, the first couple of chunks to complete are copied successfully, and the rest are throwing the error "Service Spreadsheets timed out while accessing document with id [spreadsheet id]."
And here's what it looks like in the Apps Script dashboard:
I'm confused by the timeout errors because:
- I've successfully run the script on a dataset that contains 5000 rows
- Apps Script dashboard shows the executions failing before 6 minutes (more like 4-5 minutes)
- Apps Script dashboard logging shows the failed (timed out) executions logging successfully. Logging happens after the setValues() operation (see code below); the only thing that comes after the logging is the return, so I don't understand how it could log successfully and then time out (I thought Apps Script was synchronous... but maybe I'm wrong?)
I'm also not sure about those "Uncaught" errors, but they seem to be showing up on the dashboard as "Document [spreadsheet id] is missing (perhaps it was deleted, or you don't have read access?)"
This is the document I'm copying to, and I've confirmed that it still exists on my Drive and I can open it and see the data that was successfully copied. Can a document go "missing" if too many instances of a script are trying to access it simultaneously?
I've experimented with smaller chunk sizes (1000 and 2000 rows) and get the same types of errors.
Here's what my client-side Javascript looks like:
// This function is the success handler that runs after another function (which grabs the total # of rows
// from the sheet to be copied, and then creates the new spreadsheet to be copied into) completes
function dataParamsSuccess(dataParameters) {
// dataParameters = [busHrs, outputSsUrl, lastRow, maxRows, maxColumns]
var busHrs = dataParameters[0];
var outputSsUrl = dataParameters[1];
var lastRow = dataParameters[2];
var maxRows = dataParameters[3];
var maxColumns = dataParameters[4];
console.log(maxRows);
console.log(maxColumns);
// Set chunk size
var chunkSize = 5000; // number of rows in chunk
// Determine number of chunks
var numChunks = Math.ceil(lastRow / chunkSize);
var lastChunkSize = lastRow % chunkSize;
if ((numChunks-1) * chunkSize + lastChunkSize == lastRow) {
console.log("Math checks out");
} else {
console.log("oops, check your math");
}
// Generate status message
var statusHtml = numChunks + " chunks to be copied";
for (i=0; i<numChunks; i++) {
var chunkNum = i+1;
var chunkNumStr = chunkNum.toString();
statusHtml += "<div id=\"chunk" + chunkNumStr + "Status\"></div>";
}
document.getElementById("statusMsg").innerHTML = statusHtml;
var startRow = 1;
// Call copyData once for each chunk
for (i=0; i<numChunks; i++) {
var chunkNum = i+1;
var chunkNumStr = chunkNum.toString();
var chunkDivId = "chunk" + chunkNumStr + "Status";
if (chunkNum==numChunks) { // if this is the last chunk, chunk size is smaller
chunkSize = lastChunkSize;
}
var copyParams = [chunkNum, chunkSize, startRow, outputSsUrl];
google.script.run
.withSuccessHandler(copyChunkSuccess)
.copyData(copyParams);
document.getElementById(chunkDivId).innerHTML = "Chunk " + chunkNumStr + " copying in progress";
startRow += chunkSize;
console.log("startRow: " + startRow.toString());
}
// Haven't gotten to the part where I figure out what to do after all chunks are complete yet
}
And here's the server-side Apps Script function being called:
function copyData(copyParams) {
try {
// copyParams = [chunkNum, chunkSize, startRow, outputSsUrl]
var chunkNum = copyParams[0];
var chunkSize = copyParams[1];
var startRow = copyParams[2];
var outputSsUrl = copyParams[3];
var lastRow = startRow + chunkSize;
// Get input and output sheets
var dataSheet = SpreadsheetApp.getActiveSheet();
var outputSpreadsheet = SpreadsheetApp.openByUrl(outputSsUrl);
var outputSheet = outputSpreadsheet.getActiveSheet();
// Copy values
var values = dataSheet.getRange(startRow, 1, chunkSize, 22).getValues();
outputSheet.getRange(startRow, 1, chunkSize, 22).setValues(values);
// Logging
var dataSpreadsheetId = dataSheet.getParent().getId();
var outputSpreadsheetId = outputSpreadsheet.getId();
console.log("Chunk " + chunkNum.toString() + " (rows " + startRow.toString() + " through " + lastRow.toString() + ") copied successfully");
return [chunkNum, startRow, lastRow, "success"];
} catch(e) {
return [chunkNum, startRow, lastRow, e.message]; // Return error to client-side; server-side logging is taking too long
}
}


