More Efficient Script for Delete Row If Cell Contains "DEL"

Question

I have a 50,000+ row Google Sheet that I update every day with new data. At the end of the day I run an "IF" function that helps me determine if I want to delete that row.

I then wrote a script that looks through the sheet and deletes any row that has "DEL" in the specific Column. The problem is that since I have so many rows, the script takes too long to run. Anyone have any ideas of a more efficient way to delete/clear a row if a cell has the letters "DEL"?

    function deleteRows() {
      var sheet = SpreadsheetApp.getActive().getSheetByName('DEL_test');
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();

      var rowsDeleted = 0;
      for (var i = 0; i <= numRows - 1; i++) {
        var row = values[i];
        if (row[9] == 'DEL') { // Search cells in Column "J" and delete row if cell is equal to "DEL"
          sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
          rowsDeleted++;
        }
      }
      Browser.msgBox('COMPLETE');
    };

For the time being I have removed hope of using "deleteRow" as it looks to be a slow to operate function. That has lead me to using this script which works for the time being. Thank you to everyone gave me their time. — Kevin Hall, Feb 13 '19 at 15:58
Use the "erase-remove" pattern: move all things to be deleted to one location (like the end of your sheet), find the bounds of this location, and then mass-delete. — tehhowch, Feb 13 '19 at 16:21
@Kevin Hall I apologize that my answer was not useful for your situation. I would like to study more. — Tanaike, Feb 13 '19 at 23:11

Tanaike · Answer 1 · 2019-02-13T00:15:57.843

You want to efficiently delete rows with DEL in the column "J".

How about using Sheets API? When Sheets API is used, several rows can be deleted by one API call. By this, the process cost will be able to be reduced. I think that there are several workarounds for your situation. So please think of this as just one of them.

When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Modified script:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DEL_test');
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  var spreadsheetId = ss.getId();
  var sheetId = sheet.getSheetId();
  var reqs = values.reduceRight(function(ar, e, i) {
    if (e[9] == 'DEL') {
      ar.push({"deleteDimension":{"range":{
        "sheetId": sheetId,
        "dimension": "ROWS",
        "startIndex": values.length - i - 1,
        "endIndex": values.length - i,
      }}});
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({"requests": reqs}, spreadsheetId);  
  Browser.msgBox('COMPLETE');
}

References:

If I misunderstood your question, please tell me. I would like to modify it.

Edit:

If you don't want to use Sheets API and you want to use clearContent(), how about this sample script? The flow of this sample script is as follows.

Retrieve all values.
Retrieve only the rows which have no "DEL" in the column "J".
Put the values.

Sample script:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DEL_test');
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  sheet.clearContents();
  var val = values.filter(function(e) {return e[9] != 'DEL'});
  sheet.getRange(1, 1, val.length, val[0].length).setValues(val);
  Browser.msgBox('COMPLETE');
}

Note:

I'm not sure about your actual Spreadsheet. So if this was not the result you want, can you provide a sample Spreadsheet? By this, I would like to modify the script.

Very much appreciate your speedy response! In my initial research on Sheets API, I was scared off by the words "free trial" which leads me to the assumption that it will cost me money eventually. I am ideally looking for a no-cost option. Right now my workaround is to use "clearContent()" instead of deleteRow which seems to be a much faster function. If anyone has ideas on how to improve the script though I'm still very interested. Thanks! — Kevin Hall, Feb 12 '19 at 17:10
@Kevin Hall Thank you for replying. Although ``I'm not sure about your situation``, about I am ideally looking for a no-cost option., don't worry, you can use Sheets API without the cost. For example, you can see the document at here. But if you don't want to use Sheets API, how about the added sample script? The added sample script uses ``clearContents()``. If both sample scripts were not what you want, I apologize. — Tanaike, Feb 13 '19 at 00:17

score 1 · Answer 2 · answered Feb 13 '19 at 16:02

For the time being I have removed hope of using "deleteRow" as it looks to be a slow-to-operate function. That has lead me to using this script which works for the time being. Thank you to everyone gave me their time.

  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('DEL_test');
  var rows = spreadsheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues(); 
  var lr = spreadsheet.getLastRow();

  for (var i = 0; i < numRows; i++) {
    if (values[i][9] == 'DEL') {
      spreadsheet.getRange(i+1, 1, 1, 10).clearContent();
    }
  }