1

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

}
1

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.

0

Here is an Apps Script function that switches to the next sheet, one after the currently open sheet. When the active sheet is the last one, it will loop back to the first.

function nextSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var numSheets = ss.getSheets().length;                // number of sheets
  var activeSheetIndex = ss.getActiveSheet().getIndex() - 1;   // see below 
  var newSheetIndex = (activeSheetIndex + 1) % numSheets;   // add 1; wrap around
  ss.setActiveSheet(ss.getSheets()[newSheetIndex]);  
}

Explanation of -1 in a formula: getIndex returns a 1-based index of the sheet, so subtracting 1 makes it 0-based (convenient for modular arithmetics and consistent with JavaScript indices).

You can set this function to run every 24 hours from the interface of the Script Editor: go to Resources > Current Project's Triggers.

  • Thanks, the script seems to work, but only if I press the run button! I've created a one minute trigger for testing, but apparently the trigger's not invoking the script. Where should I look? – Panther May 27 '16 at 1:45
  • Try adding Logger.log('running') at the beginning of function, to see if it is executed at all. Also, set email notifications to "immediately", so that you get an email for every failure of the script. – user79865 May 27 '16 at 2:03
  • Added the Logger statement and the notification, but still no only advances when I click Run! Does the name of the project matter? Should the trigger reference the project name or the function name as it currently does? What's a good reference for these type of questions that would also allow me to find and read about the getSheets().length and .getIndex statements? – Panther May 27 '16 at 16:44
  • @Panther: The canonical starting point to learn about Google Apps Script is google.com/script/start – Rubén Jul 27 '16 at 23:04
  • 1
    @ᴡᴏʀᴅs: I think that you should include a remark explaining that the spreadsheet should not be closed, otherwise, every time that a user open the spreadsheet by using a link without gid parameter, it will open on the first sheet. – Rubén Jul 27 '16 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.