google-apps-script

Keep receiving Sorry, it is not possible to delete all non-frozen rows


I have a script on Google Apps Script, the script is working ok but I keep receiving

Sorry, it is not possible to delete all non-frozen rows line 70

Here is related part of the code:

  //start delete rows
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i+1 <= numRows - 1; i++) {
    var row = values[i+1];
    if (row > '') { // This searches all cells in columns A (change to row[1] for columns B and so on) 
  and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  };
  //end delete rows

  function onOpen() {  
  var menu = [    
    { name: "? Help and Support »",    functionName: "help"},
    null,
    { name: "Step 1: Authorize",   functionName: "init"      },
    { name: "Step 2: Schedule Reports", functionName: "configure" },
    null,
    { name: "? Uninstall (Stop)",    functionName: "reset"     },
    null
  ];  
  SpreadsheetApp.getActiveSpreadsheet()
  .addMenu("? Drive Activity Report", menu);
  }

  function help() {
  var html = HtmlService.createHtmlOutputFromFile('help')
  .setTitle("Google Scripts Support")
  .setWidth(400)
  .setHeight(160);
  var ss = SpreadsheetApp.getActive();
  ss.show(html);
  }

I have added a picture, I don't have any lines left as they are all deleted after the emails are sent, this all works except that I receive a daily report regarding non frozen rows. Is the report perhaps complaining because the last row is blank and cannot be frozen or deleted? image of spreadsheet as requested

I am including a copy of the error report I receive each day, regarding non frozen rows, as stated in my last comment, there are no errors in any of the script logs.image of daily report on non frozen rows

The answer seems to be that the rows have to be frozen before delete, I have added the freeze code but I am not sure if this is now correct

//start delete rows
function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
     var numRowsFr = 1;
     sheet.setFrozenRows(numRowsFr);
  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[0] == 'delete' || row[0] == '') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

//end delete rows

Solution

  • Deleting Rows

    function deletingRows() {
      var sh=SpreadsheetApp.getActiveSheet();
      var v=sh.getDataRange().getValues();
      var d=0;
      v.forEach(function(r,i,a){if(!r[0] || r[0]=='delete'){sh.deleteRow(i+1-d++)}}); 
    }