Script fails to delete a row which is hidden by a filter-CodePudding

I have a google spreadsheet with a table where its headers has filters activated in them to be able to hide some rows to see just a group of related data. I've developed an Apps script which iterate over the rows of the table to copy some data and to delete some rows which match a pattern.

The problem is that the script is generating errors and the logs say that the execution time limit is being reached. I've debugged the script patiently and I've found that it fails when it tries to delete a row which is hidden by a filter. Is it normal? Or, is it a bug in Apps script?

I've created a simple test spreadsheet with table like the mentioned above and a simpler Apps script which reproduce the problem. The script copies values from first column to third column and deletes a row if it finds the character 'c' in the second column. I've added three button in the spreadsheet: the first one executes the previous script, the second one deletes the data in the third column and the third one add at the end of the table a row with the character 'c' in the second column. You can access the doc here.

The code of the test script is the following:

function CopyValues() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  let row = 4;

  while(sheet.getRange(row,1).isBlank() == false){
    let character = sheet.getRange(row,2).getValue();
    if (character=='c') {
      sheet.deleteRow(row);
    } else {
      let id = sheet.getRange(row,1).getValue();
      sheet.getRange(row,3).setValue(id);
      row  ;
    }
  }
}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In your script, how about the following modification?

Modified script:

function CopyValues() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  let row = 4;

  // I modified below script.
  const range = sheet.getRange(row, 1, sheet.getLastRow() - 3, 2);
  const values = range.getValues().reduce((ar, [a, b]) => {
    if (b != "c") ar.push([a, b, a]);
    return ar;
  }, []);
  range.clearContent().offset(0, 0, values.length, values[0].length).setValues(values);
  
  // If you want to update the range of the basic filter, please use the following script.
  var filter = sheet.getFilter();
  if (filter) {
    var newRange = filter.getRange().offset(0, 0,values.length   1, values[0].length);
    filter.remove();
    newRange.createFilter();
  }
}

In your script, getValue and setValue are used in a loop. In this case, the process cost becomes high.
In this modification, at first, the values are retrieved and filtered the values, and then, put values to the sheet. By this, it reduces the process cost of the script.

Modified script:

References: