Delete Cells Based on Date

Question

I need a help with a cell-deletion script. In general, I want to run a reset script that clears out all of the data up to the day I run it. Because I am statically inputting values into those cells that are matching up with information from a filter, I believe I need to delete those cells to properly line up my inputs with where the filter information will be after I delete the expired rows from the exporting page.

Here's what I want to do in my script: If the Column F value < today's date, then delete the cells in I, J, and K and shift the cells below them up. I think I found code to do this, but it takes so long to run that the program times out before it can get through more than a few rows. I will use a for loop to run it over 73 pages, so if it is lagging out on one...yeah, I need help!

function deleteEntries() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var datarange = ss.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues();

  var currentDate = new Date();

  for (i = lastrow; i >= 5; i--) {
    var tempdate = values[i-1][5];

    if (tempdate < currentDate)  
    {
      ss.getRange(i-1, 8).deleteCells(SpreadsheetApp.Dimension.ROWS);
      ss.getRange(i-1, 9).deleteCells(SpreadsheetApp.Dimension.ROWS);
      ss.getRange(i-1, 10).deleteCells(SpreadsheetApp.Dimension.ROWS);
}}}

Answer 1

In accordance with Apps Script "best practices", you will want to limit the use of the Spreadsheet Service to improve execution times. There are two "immediate" optimizations that can be considered:

1. Delete more than 1 cell at a time in a row To do this, simply select a 1-row x 3-column range: ss.getRange(i-1, 8, 1, 3) instead of selecting (i-1, 8), (i-1, 9), (i-1, 10) and calling deleteCells on each of the three Ranges.
2. Sort your sheet before deleting such that only 1 delete call is necessary (e.g. the C++ stdlib "erase-remove" idiom). If your data is sorted based on column F, such that all data that should be removed is at the end, then you simply need to iterate the in-memory array (a very fast process) to locate the first date that should be removed, and then remove all the data below & including it.

An implementation of option 2 would look like this (I assume you use frozen headers, as they do not move when the sheet or range is sorted).

function sortDescAndGetValuesBack_(s, col) {
  return s.getDataRange().sort({column: col, ascending: false}).getValues();
}
function deleteAllOldData() {
  const sheets = SpreadsheetApp.getActive().getSheets()
      .filter(function (sheet) { /** some logic to remove sheets that this shouldn't happen on */});
  const now = new Date();
  const dim = SpreadsheetApp.Dimension.ROWS;

  sheets.forEach(function (sheet) {
    var values = sortDescAndGetValuesBack_(sheet, 6); // Col 6 = Column F
    for (var i = sheet.getFrozenRows(), len = values.length; i < len; ++i) {
      var fVal = values[i][5]; // Array index 5 = Column 6
      if (fVal && fVal < now) { // if equality checked, .getTime() is needed
        console.log({message: "Found first Col F value less than current time",
                     index: i, num2del: len - i, firstDelRow: values[i],
                     currentTime: now, sheet: sheet.getName()});
        var delRange = sheet.getRange(1 + i, 8, sheet.getLastRow() - i, 3);
        console.log({message: "Deleting range '" + sheet.getName() + "!" + delRange.getA1Notation() + "'"});
        delRange.deleteCells(dim);
        break; // nothing left to do on this sheet.
      }
    }
    console.log("Processed sheet '" + sheet.getName() + "'");
  });
}

References:

• Array#filter
• Array#forEach
• Range#sort
• Range#deleteCells