CODEWITHSUNDEEP
google-apps-scriptoptimizationtimeoutdelete-row
Kelly Danielle
Kelly Danielle

Reputation: 103

Delete filtered rows in google sheets script (10000+ rows)

I need help deleting the filtered row in a sheet without timing out. There are over 10,000 rows in the sheet so running a for loop with DeleteRow() is timing out.

The larger goal of the project is to basically archive data from the main sheet to an archive sheet automatically if the date is more than 31 days ago.

This is the current code I currently have to do this but the loop is working really slow with as much data as I have in the sheets. If there are any other major performance improvements feel free to make those suggestions as well. This is my first real time working with google app scripts

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copy_sheet = ss.getSheetByName("upload");
  var paste_sheet = ss.getSheetByName("archive");
  const day = 24 * 60 * 60 * 1000;
  var date = new Date();
  var remove_before_date = new Date()
  remove_before_date.setDate(date.getDate()-31);

  var prev_filter = copy_sheet.getFilter();
  if (prev_filter !== null) {
    prev_filter.remove();
    return;
  }
  //create filter
  var range = copy_sheet.getRange("A:I");

  var filter = range.createFilter();

  var Filter_Criteria = SpreadsheetApp.newFilterCriteria().whenDateBefore(remove_before_date);

  var add_filter = filter.setColumnFilterCriteria(1,Filter_Criteria);

  //copy and paste
  var range = copy_sheet.getDataRange().offset(2,0);
  var last_row_archive = paste_sheet.getDataRange().getNumRows();
  var last_row_upload = copy_sheet.getDataRange().getNumRows();

  paste_sheet.insertRowAfter(last_row_archive)
  range.copyTo(paste_sheet.getRange(last_row_archive+1,1));
  

  //delete from upload
  for (var i = last_row_upload; i > 2; i--) {
    if (!copy_sheet.isRowHiddenByFilter(i)) {
      copy_sheet.deleteRow(i);
    }
}

  //remove filters
  filter.remove();

}```

Upvotes: 3

Views: 2133

Answers (1)

Muhammet Yunus
Muhammet Yunus

Reputation: 589

The execution will be much faster if you

  • get all values of source sheet
  • filter them as you wish
  • split them into two arrays: array to remove, array to stay
  • place values of these arrays to source sheet and target sheet respectively.
function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('upload');
  var target = ss.getSheetByName('archive');
  
  var data = source.getDataRange();
  var range = source.getRange(3,1,data.getNumRows()-2, data.getNumColumns());
  
  var values = range.getValues();
  var datesCol = 'I'; //replace this with where your dates are
  var datesColIndex = source.getRange(datesCol + ':' + datesCol).getColumn()-1;
  var lastRowArchive = target.getDataRange().getNumRows();

  //this filters data and gets all rows, the date of which earlier then 31 days.
  var valuesToRemove = values.filter(function(row) {
    var dayInMs= 24 * 60 * 60 * 1000;
    return new Date().valueOf() - new Date(row[datesColIndex]).valueOf() >= dayInMs*31;
  })
  
  //this creates a new array containing only the remaining rows
  var valuesToStay = values.filter(row => !valuesToRemove.includes(row));
  
  //if there are values to remove
  if (valuesToRemove.length) {
    //clear the range in the source sheet
    range.clearContent(); 
    
    //place valuesToStay array to the source sheet
    source.getRange(3,1,valuesToStay.length,valuesToStay[0].length)
    .setValues(valuesToStay);

    //place valuesToRemove array to end of the target sheet
    target.getRange(lastRowArchive+1,1,valuesToRemove.length, valuesToRemove[0].length)
    .setValues(valuesToRemove);
  }
}

Upvotes: 3

Related Questions