How would I modify this script when the header data changes?

Question

I am using this script to hide all rows on my Example sheet that contain dates in Column A which are older than the two most recent dates in Column A of the sheet. It is ignoring any empty checkboxes on the sheet and is quite efficient.

My goal is to understand how to update this script so that it continues to function correctly if the number of header rows change, or the number of cells containing data in the A cells of the header change. For example, if the header rows increase to 10, and the number of A cells in the header change from 3 to 2, I change the two number 4's to 3 instead and update s.hideRows(9,row-7) to s.hideRows(11,row-10), but that does not hide enough data.

Here is the current script, which only works with 8 rows of headers and if 3 of those rows contain data in the A cells:

    function hideRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("Example");
    var v = s.getRange("A:A").getValues(); //Modified the range to start directly from where the data starts
    var rowCount = 0 //replacement for .getLastRow
         for(var i = s.getLastRow(); i > -1; i--) { //going through all rows and finding the ones with no empty value
      if (v[i] != "")
      rowCount = rowCount + 1
         }
    rowCount = rowCount +4
    var lastDate = s.getRange("Example!A"+ rowCount).getValue(); //Getting the value of the latest row on column A, getLastRow is replaced by rowcount
    var previous = lastDate
    var counter = 0

     for(var i = rowCount; i > -1; i--) {
      if ((v[i][0]).toString() !== previous.toString()){
        counter = counter + 1
        previous = (v[i][0])
        var row = i
        
        if (counter == 4){
          break
        }

      }
     }
s.hideRows(9,row-7) 
}

How do I correctly update this script when the header information changes?

I would add something to the header in currently row 8 that is guaranteed to be immediately before all the dates. Call it "Dates," I don't care. Then look for that value and use its position to set your other number checks. — Spencer, May 12 '23 at 17:26

score 2 · Accepted Answer · answered May 13 '23 at 03:07

Although I'm not sure whether I could correctly understand your expected result, how about the following modified script?

When I saw your provided Spreadsheet, I noticed that the boundary of the header row and data row is frozen. If the boundary between the header row and data row is always frozen in your actual situation, how about using this? When this is reflected in the sample script, how about the following sample script?

Sample script:

function hideRows() {
  // ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Example");
  var headerRow = sheet.getFrozenRows();
  var rowCount = sheet.get1stNonEmptyRowFromBottom(1);
  var values = sheet.getRange(headerRow + 1, 1, rowCount - headerRow).getDisplayValues().map(([e]) => e).reverse();
  var showDates = [...new Set(values)].splice(0, 2);
  sheet.hideRows(headerRow + 1, rowCount - headerRow - values.findIndex(r => !showDates.includes(r)));
}

In this modified script, even when the number of header rows is changed, the 1st data row can be retrieved by the number of the frozen rows.

Note:

This modified script uses the number of frozen rows between the header rows and the data rows. When your actual situation didn't use the frozen rows, this script cannot be used. Please be careful about this.

How would I modify this script when the header data changes?

1 Answers1

Sample script:

Note:

References:

Linked