Only use data from Column A when determining getLastRow

Question

In my example sheet, I'm using this script provided by @Rene Olivio to hide all rows containing older dates than the rows containing the two most recent dates in Column A:

function hideRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("Example");
    var v = s.getRange("A:A").getValues();
    var lastDate = s.getRange("Example!A"+ s.getLastRow()).getValue();
    var previous = lastDate
    var counter = 0
     for(var i = s.getLastRow(); 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) 
}

While this is working great when there is no data in any of the rows without dates in the first cell, I require all cells in both Columns P and Q to contain empty checkboxes. When this is the case however, I get the error message: TypeError: Cannot read properties of undefined (reading '0').

How would I best update the getLastRow function to avoid looking at cells other than Column A?

https://stackoverflow.com/a/72842856/7215091 – Cooper May 08 '23 at 18:13 — Cooper, May 08 '23 at 18:13

score 1 · Accepted Answer · answered May 08 '23 at 18:54

Sorry I have not been around lately, I was able to find a solution:

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) 
}

function dateFilter() {

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A:AB').activate();
  spreadsheet.getRange('A:AB').createFilter();
  spreadsheet.getRange('A1').activate();

  var criteria = SpreadsheetApp.newFilterCriteria()
  .whenFormulaSatisfied('=MAX(A:A)-2')
  .build();
  
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria);

};

I left the rest of your code there, I really didn't know what it was doing as I didn't go through it, the main change has been commented on the code.

Ah, no worries, I'm grateful for your help! Could you help explain one thing in the code, so I can update it accordingly if the amount of header rows change? I understand that 9 is the first row in the range that gets hidden, but what do the -7 and the 4s represent? — J. Kubassek, May 08 '23 at 19:21
Sure thing! the places where the 4 is included are there because the code is not really looking at rows specfically but rather the data contained by them so value 0 = "HIDE ROWS EXAMPLE", value 1 = "EXAMPLE DATA A EXAMPLE DATA B EXAMPLE DATA C" so the first four values (which are contained between rows 1 through 8 and are part of the sheet's header) are the reason for the 4s in the formula — Rene Olivo, May 08 '23 at 21:15
the -7 has a similar explanation, but a bit more complex, here we are comparing an amount of data to an amount of rows, we start by the fact that the row where the dates start is row 9 (A9) so -9 however since the calculations on the script are done through data values and not data rows we still need to consider that +4 at we mentioned at the beginning so that gives us a -6 however since we are counting the data from an array and the array always starts at 0 then in this one instance is not a +4 but rather a +3 which results in the -7 you see in the formula — Rene Olivo, May 08 '23 at 21:19

Only use data from Column A when determining getLastRow

1 Answers1

Linked