google sheets Hide rows based on today's date

Question

Within Google Sheets I need a script to hide rows automatically in sheet 1 when the date in column A is equal to or before today's date.

so if today is August 29, 2018 and the date in cell A3 is August 28, 2018 all of Row 3 will be hidden

But if the date was August 30, 2018 in cell A3 then all of Row 3 would b visible until the current date is August 31, 2018.

thank you for any help you can provide.

I have been working with this code which I found which hides rows, not in the current month I can't seem to Figure out how to modify it to look at the current day and hide rows which are older than the current date.

  function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuItems=[{name: 'HideRows', functionName: 'hideRows'}];
  ss.addMenu('Script', menuItems);    
  };

 function hideRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");
  var v = s.getRange("A:A").getValues();
  var today = new Date();
  var m = today.getMonth();
  for(var i=3;i<v.length;i++)
    if(v[i][0]=="" || v[i][0].getMonth()>=m) break;
  if(i>3) s.hideRows(4,i-3)
  };

BobRodes I posted the code I have been beating myself up with. Thanks for looking — matt
This seems perhaps harder than needed. Are you wedded to a script, or could live with a formula within the spreadsheet that looks like =filter(A3:K17,A3:A17>=today()) ? — Jeremy Kahan
so that code appears to be assuming things are already sorted by date. You are making no such assumption, so it will not translate quite so smoothly. — Jeremy Kahan
I do have a simpler excel code which works good in excel I do not know how to convert it to google — matt

Jeremy Kahan Jeremy Kahan · Accepted Answer · 2018-08-30T04:06:40

So as I noted in my comment above, a filter could work. But if you want to get used to hideRows and scripting, the below should do it:

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

You could clean up the variable names, and probably condense it a little. Today takes the current time, and I set it back to today at midnight to avoid thinking that I ought not to display 8/29/2018 because it is now 11 pm. I also worked from the bottom up to be safe. It was also a bit subtle that the array from the range is 0 based but row numbering is 1-based.

google sheets Hide rows based on today's date

1 Answers