1

I have a Google Sheet 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 which hides all rows except for today's date.

What I'm trying to accomplish now is to hide all entries except for the last two dates on the sheet. This is as far as I got with my current script, but it is still only hiding everything except for today's date:

function hideRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName("Example");
    var v = s.getRange("A:A").getValues();
    var today = new Date();
    var yesterday = new Date(today.setDate(today.getDate()-1));
    today.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);
            }
        }
    }
}

Why is this not working?

J. Kubassek
  • 205
  • 2
  • 14

1 Answers1

1

After some testing I believe I figured it out, there are 2 things that are happening here, when trying to get the variable yesterday you are using this:

var yesterday = new Date(today.setDate(today.getDate()-1));

there the function setDate() actually modifies the variable today, to avoid this I included a new variable that you could modify, here are the three variables interacting with each other:

    var today = new Date();
    var helper = new Date();
    var yesterday = new Date(helper.setDate(helper.getDate()-1));

This will still modify the helper variable but since it isn't used anywhere else in the code it won't affect us.

The second problem is the most impactful, the line:

if (u < yesterday)

compares u and yesterday however u has 0 hours while yesterday has the current value of hours (meaning almost always not 0), you had already fixed this problem with "today" like so:

    today.setHours(0, 0, 0, 0);

so all I did was copy the same solution and apply it to yesterday, here it is:

    yesterday.setHours(0, 0, 0, 0);

here is the full code that I got to work:

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

Hope this helps!

Rene Olivo
  • 526
  • 1
  • 10
  • I'm marking this as solved as it addresses my initial post, thank you so much! I do have a follow-up question if I may - what would I need to do in order to show the last 2 dates if neither today or yesterday are found on the sheet? – J. Kubassek Apr 28 '23 at 14:54
  • I really like that question! I tried to give it a quick answer but it seems like it might take a while, could you please create a new question for this and share it here? I will do my best to help you there too! – Rene Olivo Apr 28 '23 at 15:53
  • I posted this new question [here](https://stackoverflow.com/questions/76132162/hide-all-rows-except-for-the-two-most-recent-dates), thank you again! – J. Kubassek Apr 28 '23 at 18:02