Why is this function not deleting many rows at once correctly?-CodePudding

This function works with a little bit of data, but not with hundreds of rows and I wonder if I'm missing some Spreadsheet.flush() or something of this nature.

const values = [["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"],["2022-12-31T06:00:00.000Z"]];

function DeleteRows(sheetName, year) {
  sheetName = 'Saved Budgets'//For tests
  year = '2022' //For tests
  var SS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); //Get Open Lines Sheet
  var lastRow = SS.getLastRow();
  var range = SS.getRange(2, 1, lastRow - 1, 1); //get range
  range.sort({ column: 1, ascending: false }) // filter data descending   

  var firstRowToDelete = 0;
  var numOfRows = 1;  // starting row to be increment and become the number of rows       
  var values = range.getValues();//Got it for comparison

  for (let a = 0; a < values.length; a  ) {
    let dt = new Date(values[a]).getFullYear();
    if (dt == year) {
      firstRowToDelete = parseInt(a);
      numOfRows  
    }
  }
  if (numOfRows != 1) {
    numOfRows = numOfRows - 1 // minus 1 to get the last row
    SS.deleteRows(firstRowToDelete, numOfRows);
  }
  range.sort({ column: 1, ascending: true }) // filter data again ascending
}

CodePudding user response：

If you want to delete rows that the column "A" is year = '2022', firstRowToDelete = parseInt(a); is the last index of the rows that the column "A" is year = '2022'. And, numOfRows is the number of rows. In this case, I'm worried that all rows that the column "A" is year = '2022' cannot be deleted. And also, when the values are large, the rows for deleting might be over the bottom of the sheet, and/or range of range.sort({ column: 1, ascending: true }) might be over the bottom of the sheet. I thought that this might be the reason for your issue.

If you want to remove this issue, when your script is modified, how about the following modification?

From:

for (let a = 0; a < values.length; a  ) {
  let dt = new Date(values[a]).getFullYear();
  if (dt == year) {
    firstRowToDelete = parseInt(a);
    numOfRows  
  }
}
if (numOfRows != 1) {
  numOfRows = numOfRows - 1 // minus 1 to get the last row
  SS.deleteRows(firstRowToDelete, numOfRows);
}
range.sort({ column: 1, ascending: true }) // filter data again ascending

To:

for (let a = 0; a < values.length; a  ) {
  let dt = new Date(values[a]).getFullYear();
  if (dt == year) {
    if (firstRowToDelete == 0) firstRowToDelete = a   2; // Modified
    numOfRows  
  }
}
if (numOfRows != 1) {
  numOfRows = numOfRows - 1;
  SS.deleteRows(firstRowToDelete, numOfRows);
}
SS.getRange(2, 1, SS.getLastRow() - 1, 1).sort({ column: 1, ascending: true }); // Modified

As another modification, how about the following modification?

From:

var values = range.getValues();//Got it for comparison

for (let a = 0; a < values.length; a  ) {
  let dt = new Date(values[a]).getFullYear();
  if (dt == year) {
    firstRowToDelete = parseInt(a);
    numOfRows  
  }
}
if (numOfRows != 1) {
  numOfRows = numOfRows - 1 // minus 1 to get the last row
  SS.deleteRows(firstRowToDelete, numOfRows);
}
range.sort({ column: 1, ascending: true }) // filter data again ascending

To:

var values = range.getDisplayValues();
var numOfRows = values.filter(([a]) => new Date(a).getFullYear() == year).length;
if (numOfRows > 0) {
  var firstRowToDelete = values.findIndex(([a]) => new Date(a).getFullYear() == year);
  SS.deleteRows(firstRowToDelete > -1 ? firstRowToDelete   2 : firstRowToDelete, numOfRows);
}
SS.getRange(2, 1, SS.getLastRow() - 1, 1).sort({ column: 1, ascending: true });

CodePudding user response：

Try this:

NOTE: This is all based on the assumption that the values global variable is actually a data in the spreadsheet, and you would want to remove all data with 2022.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //you can change this to specify a specific sheet
  var range = ss.getRange(2,1,ss.getLastRow(), ss.getLastColumn());
  var values = range.getValues();
  var year = /2022/; // change this to filter other years
  var newval = values.filter(x=>year.test(x) ? null : x); 
  console.log(newval); //to check if it populates the correct data during logging. 
  range.clearContent(); //clears the data based on the current range keeping the formatting. 
  var newrange = ss.getRange(2,1,newval.length, ss.getLastColumn()); //creates a new range based on the size of `newval`
  newrange.setValues(newval); 
}

Explanation:

var range = ss.getRange(2,1,ss.getLastRow(), ss.getLastColumn()); gets the current data on the spreadsheet, including the columns.
Using var values = range.getValues(); we get a 2D array structure of the data on the spreadsheet.
Using filter() and test() method on var newval = values.filter(x=>year.test(x) ? null : x); using a ternary operator to test whether an array element contains the year to filter out.
range.clearContent(); to delete the contents of the range.
var newrange = ss.getRange(2,1,newval.length, ss.getLastColumn()); creates a new range based on the new array.
newrange.setValues(newval); sets the new value on the spreadsheet

Screenshots:

NOTE: Multiple columns in the data are for testing to see dynamic deletion even if there is additional data on the columns.

Initial data:

After running the script:

Execution duration:

References:

https://developers.google.com/apps-script/reference/spreadsheet/range#clearContent() https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/RegExp/test