How to open sheets to a specific tab based on days of the week?

Question

I have a file with 7 tabs or sheets each of them represent a day of the week (Monday to Sunday). In each day (Tab) a have this formula in B2 to show the day of the week if it is today...
=IF(WEEKDAY(TODAY(),2) = 1, "Monday", IF(FALSE, "True", ))
=IF(WEEKDAY(TODAY(),2) = 2, "Tuesday", IF(FALSE, "True", ))
=IF(WEEKDAY(TODAY(),2) = 3, "Wednesday", IF(FALSE, "True", ))
=IF(WEEKDAY(TODAY(),2) = 4, "Thursday", IF(FALSE, "True", ))
=IF(WEEKDAY(TODAY(),2) = 5, "Friday", IF(FALSE, "True", ))
=IF(WEEKDAY(TODAY(),2) = 6, "Saturday", IF(FALSE, "True", ))
=IF(WEEKDAY(TODAY(),2) = 7, "Sunday", IF(FALSE, "True", ))

I would like the document every time is open to open in the correspondent "Day of the week" Tab,
Is this possible with a script?
Here is the link to the Sheet:
https://docs.google.com/spreadsheets/d/1GI2R_KI6ujxK7g_jBEpcCoLZt6Fyqngri5CP3kEJNz8/edit?usp=sharing

Tedinoz · Answer

You have seven tabs named Sunday-Saturday inclusive. When the spreadsheet opens, you want the the tab for that day of the week to be automatically selected.
Your question probably has several solutions. Consider the following answer as just one solution to your question.
Note: onOpen is a simple trigger that will automatically execute when the spreadsheet is opened.
function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var today = new Date();
    
  //Returns a number 0-6. Sunday is 0, Monday is 1, etc.
  var dayOfWeek = today.getDay();
  // Logger.log("DEBUG: the day of the week is "+dayOfWeek);

// create an array for the days of the week/tabs  
  var thedays = ["SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY"]

// get the sheet for today
  var sheet = ss.getSheetByName(thedays[dayOfWeek]);
  
  // select a cell and make it the active cell 
  var cell = sheet.getRange('A1');
  sheet.setCurrentCell(cell);
}

How to open sheets to a specific tab based on days of the week?

One Answer

Add your own answers!

Ask a Question