20392
<h3>Question</h3>
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++;
}
}
<h3>Answer1:</h3>
In your situation, I understand as follows.
<ul><li>There are a lot of rows which are deleted.</li> <li>The rows are not continued. So the rows have to be deleted row by row usingdeleteRow().</li>
</ul>If my understanding is correct, how about this modification?
<h3>Modification points :</h3> <ul><li>Use batchUpdate of Sheets API. <ul><li>By using Sheets API, the discrete rows can be deleted by one API call.</li> </ul></li> <li>Create the list of rows which are deleted as the request body for Sheets API.</li> <li>Delete the rows by batchUpdate of Sheets API with the request body.</li> </ul>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.
<h3>Modified script :</h3>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.</li>
<li>Before you run this, please input spreadsheet ID and sheet ID having the rows you want to delete.</li>
</ul>If this was not result what you want, I'm sorry.
<h3>Answer2:</h3>
try
var result = values.filter(row => row[0] !== 'delete' && row[0] !== '');
var rowsDeleted = values.length - result.length;
来源：https://stackoverflow.com/questions/50638014/delete-rows-based-on-values-within-cell-optimising
