Delete rows based on values within cell, optimising

Question

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++;
}
}

Sorry for the late reply. I was out of internet access, I tried your solution and it worked. Thank you very much for your thorough answers, I flagged it as correct too. Cheers! — Look13, Jun 11 '18 at 08:29
Thank you for your response. I'm glad your issue was solved. Thank you, too. — Tanaike, Jun 11 '18 at 08:56

score 0 · Answer 1 · answered Jun 01 '18 at 07:27

0

try

var result = values.filter(row => row[0] !== 'delete' && row[0] !== '');
var rowsDeleted = values.length - result.length;

answered Jun 01 '18 at 07:27

ic3b3rg

14,629
4
30
53

Thanks for your input! Gives me a syntax error on the 'var result' line. Not an expert either therefore couldn't find the problem. – Look13 Jun 01 '18 at 08:58
@Look13 The error occurred on 2018 because Apps Script didn't support arrow functions. The new runtime (Chrome V8) does. – Rubén Jun 05 '20 at 16:37

score 0 · Accepted Answer · answered Jun 01 '18 at 09:04

In your situation, I understand as follows.

There are a lot of rows which are deleted.
The rows are not continued. So the rows have to be deleted row by row using deleteRow().

If my understanding is correct, how about this modification?

Modification points :

Use batchUpdate of Sheets API.
- By using Sheets API, the discrete rows can be deleted by one API call.
Create the list of rows which are deleted as the request body for Sheets API.
Delete the rows by batchUpdate of Sheets API with the request body.

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.

Modified script :

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);

Note :

In this modified script, it supposes that the retrieved values from Spreadsheet is in values.
Before you run this, please input spreadsheet ID and sheet ID having the rows you want to delete.

If this was not result what you want, I'm sorry.

Delete rows based on values within cell, optimising

2 Answers2

Modification points :

Modified script :

Note :

Linked