Script to open "Today" in multi-sheet Google Sheet

Question

Sample Sheet- So, I am new to this part. Our work schedule is in by-weekly format, each two-week pay-period is on a separate sheet.

I need a script to cause it to open to the current date whenever a user accesses the sheet from their Google Drive.

Similar to this, but something isn't right:

function onOpen() {
  var today = new Date();
  var ss = SpreadsheetApp.getActive();
  ss.setActiveSheet(ss.getSheets()[today.getMonth()]);
}

How are your sheets (tabs) named ? Date format ? Month names ? — JPV
They are named by year and pay period, i.e. PP1501- PP1526. Thank you — Daniel Kimes
So the current date should somehow be matched between two dates ? Do the numeric parts of the sheet names refer to dates ? — JPV
Unfortunately, no. However there are dates within the sheet like a calendar. Is there a way to open the current sheet based on the current date contained within? Sorry to be basic, but I am rather new at scripting. — Daniel Kimes
I have added a link to a sample of the sheet to the original post. Unfortunately I cannot share the actual document outside my companies Google domain, so it is very simplified, without the conditional formatting and data validation. — Daniel Kimes

JPV JPV · Accepted Answer · 2014-12-10T12:20:32

This script should bring you to the sheet with the current date..

function onOpen() {
var today = format(new Date());
var ss = SpreadsheetApp.getActive();
var sheets = ss.getSheets()
    .forEach(function (s) {
        var val = s.getRange("C2:P2")
            .getValues()[0];
        for (var i = 0, len = val.length; i < len; i++) {
            if (format(val[i]) === today) {
                ss.setActiveSheet(s)
                break;
            }
        }
    });
}

function format(date) {
return formattedDate = date.getMonth() + 1 + "/" + date.getDate()
}

See if this works ?

Script to open "Today" in multi-sheet Google Sheet

1 Answers