Hide all rows except for the two most recent dates

Question

EDIT: For an even more effective solution, see this post.

I have a Google Sheet (example here) with thousands of rows of data where dates are entered into Column A in order from top to bottom, with varying numbers of rows for each date, starting on Row 9. I have created a button to run a script (thanks to @Rene Olivo for the help given here) which hides all rows except for today and yesterday's dates.

What I'm trying to accomplish is to hide all entries except for the last two entered dates on the sheet, as opposed to today and yesterday's dates, but I am unsure how to specify that. This is my current script:

function hideRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("Example");
    var v = s.getRange("A:A").getValues();
    var today = new Date();
    var helper = new Date();
    var yesterday = new Date(helper.setDate(helper.getDate()-1));
    today.setHours(0, 0, 0, 0);
    yesterday.setHours(0, 0, 0, 0);
    for (var i = s.getLastRow(); i > 8; i--) {
        var t = v[i - 1];
        if (t != "") {
            var u = new Date(t);
            if (u < yesterday) {
                s.hideRows(i);
            }
        }
    }
}

What would the best way to handle this be? I'm also curious to know whether there is a more efficient way to do what this script is doing, as this one is taking a lot of time to complete.

Answer 1

I had to make some big changes to make it more efficient as it really was getting super slow but I'm glad to say it's working now, here is the working script, the comments explain each change made:

function hideRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("Example");
    var v = s.getRange("A9:A").getValues(); //Modified the range to start directly from where the data starts
    var lastDate = s.getRange("Example!A"+ s.getLastRow()).getValue(); //Getting the value of the latest row on column A
    var beforeLast = new Date(lastDate-1); // getting the second to last date based on the previous date
    beforeLast.setHours(0, 0, 0, 0);
        for(var i = s.getLastRow(); i > -1; i--) { //Changed 8 to -1 to account for the changed range
               if(new Date(v[i]).toString() == beforeLast.toString()){  //the values are being changed to strings to be able to compare them,
                                                                        //here is where the code determines the cell where it should stop hiding rows.
            var row = i //Variable where the number of the last cell to hide is kept
          }
           };
         s.hideRows(9,row) //Hiding rows starting from the 9th row as that's where the data begins
    }

This one should be able to recognize the last two days dynamically and take much less time to process the main difference is that this time all rows are hidden at once after the code determines which cell is the one where the hiding should stop.

Here is a code that would work even if the dates are not consecutive:

function hideRowsV2() {
    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 lastDate = s.getRange("Example!A"+ s.getLastRow()).getValue(); //Getting the value of the latest row on column A
    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) 
}

Answer 2

UPDATE TO INITIATE FILTER FROM CODE:

Here is some sample code to trigger the inbuilt filter from code. It can be saved in macro.gs (and should appear in the macro list) or in code.gs and assigned to a control. This is the same as the manual filter I used before, but you can assign it to a button for the convenience of the user. I would be surprised if this weren't much faster than any code you can come up with, since the built-in filter was optimized by the makers of Google Sheets.

function dateFilter() {

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

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

};

This is using the ranges for my sample data, so you will have to adjust them. Also, I just used applied it to the active sheet, but you can use whatever method to get the reference to the spreadsheet that needs to have the filter applied. You can use the name or gid to obtain it.

---

ORIGINAL:

Maybe you could just use a filter with a formula as the condition. Here is some pretend data:

And then I'll filter column A by condition:

using formula

This does allow for multiple entries on one date:

but you do have to retrigger the filter when data is added.

Answer 3

function hideRows() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sh = ss.getSheetByName("Example");
    const vs = sh.getRange("A9:A" + sh.getLastRow()).getValues()
    let arr = sh.getRange("A9:A" + sh.getLastRow()).getValues().flat().sort((a,b) => {return new Date(b).valufOf() - new Date(a).valueOf();}); 
    vs.forEach((e,i) => {
      let dtv = new Date(e).valueOf();
      let v0 = new Date(arr[0]).valueOf();
      let v1 = new Date(arr[1]).valueOf();
      if( dtv != v0 && dtv != v1) {
        sh.hideRows(i + 9);
      }
    })
}