Google-sheets – JavaScript To Dynamically & Daily Select The Next Worksheet In An Open Google Sheets File

google sheetsgoogle-apps-script

At work using Chrome, I have a Google Sheets file saved on my Google Drive folder that's shared with me with edit privileges that I keep open on my MacBook Pro.

After I've originally opened the file and selected the current day's worksheet, I would like a JavaScript that dynamically selects the next day's worksheet at (12:00 AM – 12:15 AM) every 24 hours. Each (1 – 31+) worksheets has daily information for the current month.

I've never written javascript, but have used VBA in MS Excel and have started with the following from the brief Javascript reading I've done. This script errors on the VBA ActiveSheet…conditional line and I don't know if the Trigger will actually be generated. I couldn't find the equivalent javascript line for the conditional line.

 function NextCalendarDay() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; 

if (ActiveSheet.Index = Worksheets.Count) {
   Worksheets(1).Select
} else {
   Worksheets(ActiveSheet.Index + 1).Select
}

function createTimeDrivenTriggers() {
  // Trigger every 24 Hour(s).
  ScriptApp.newTrigger('NextCalendarDAy')
      .timeBased()
      .everyHours(24)
      .create();
}

}

Best Answer

While it's possible to use a time triggered function to get the desired result, I suggest to use an on open event instead.

The following code assumes that

  1. there is a sheet named Config and that the cell A1 has the following formula =DAY(TODAY()).
  2. there are 31 sheets named from 1 to 31 (one for each day of a 31 days month.

It will be ran every time that the spreadsheet is opened, and will make that the sheet specified on Config!A1 be set as the active sheet.

function onOpen(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var todayNumber = ss.getRange('Config!A1').getValue();
  var todaySheet = ss.getSheetByName(todayNumber + '');
  ss.setActiveSheet(todaySheet);
}

The line 3 could be replaced by JavaScript code that calculates the current day but for debug/testing purposes I prefer to use an auxiliary sheet.

Related Topic