I have script running on a weekly bases deleting rows from a document and then pasting these values to another sheet for data analysis, however as the document grows (+20,0000 rows) my script times out. Anyway on how to optimise the script to perhaps use less processing/memory and run faster?
var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[0] == 'delete' || row[0] == '') {
data.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
In your situation, I understand as follows.
deleteRow()
.If my understanding is correct, how about this modification?
In order to use Sheets API, please enable Sheets API at Advanced Google Services and API console. About the how to enable them, please check here.
var spreadsheetId = "#####"; // Please input spreadsheet ID.
var sheetId = "#####"; // Please input sheet ID.
var deleteRows = [];
for (var i = values.length - 1; i >= 0; i--) {
if (values[i] == "delete" || values[i] == "") {
deleteRows.push({
"deleteRange": {
"shiftDimension": "ROWS",
"range": {
"startRowIndex": i,
"endRowIndex": i + 1,
"sheetId": sheetId
}
}
});
}
}
Sheets.Spreadsheets.batchUpdate({"requests": deleteRows}, spreadsheetId);
values
.If this was not result what you want, I'm sorry.