More Efficient Script for Delete Row If Cell Conta

问题:

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

回答1:

• 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:

• spreadsheets.batchUpdate
• DeleteDimensionRequest

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.

1. Retrieve all values.
2. Retrieve only the rows which have no "DEL" in the column "J".
3. 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.

回答2:

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