Google-sheets – How to open a specific tab based on today’s date (Google Sheets)

dategoogle sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I've made a 'Google Sheets' with 31 tabs for the dates of the month (1-31)
How do I write a script that makes the spreadsheet open to that days tab?
e.g. open tab 25 if today is Aug 25th

I imagine (from a few other scripts I've seen) that it's something like:

function onOpen() {
  var date = getDate(now,'d')
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheets()[date]);  
}

but I've never used this programming language and have no idea.

also, the tabs are named 1, 2, 3.. etc if that's any easier to code.

Best Answer

Assume that you have 31 sheets, named 1 to 31 respectively.

This script works when the spreadsheet is opened by using a simple onOpen trigger.

new Date(): gets today's date
now.getDate(); is a shorthand way of getting the day of the month. This is a number
ss.getSheetByName(sheetname);: assigns the day number to the sheet name
sheet.getRange("A1").activate();: goes to the specific sheet and to cell A1. The effect is to open the sheet equal to the day of the month.

function onOpen() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var now = new Date();
  var dayofmonth = now.getDate();
  var sheetname = dayofmonth;
  var sheet = ss.getSheetByName(sheetname);
  sheet.getRange("A1").activate();
  return;
  
}

Related Solutions

Google-sheets – How to make Google Sheets jump to today’s row when opened

You can use the following script (see Tools > Script Editor). It executes whenever the spreadsheet is opened, and searches column A for the current date. It then sets the cursor to the cell with that date. You can use another column instead of A by editing the command sheet.getRange("A:A")

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("A:A");
 var values = range.getValues();  
 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i][0].getTime()/day;
   }
   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(i,0,1,1));
     break;
   }    
 }
}

This script borrows from this answer by Serge insas.

Unfortunately, it seems impossible to bind a script to the event of switching between sheets. So, if you'd like to invoke it on more than one sheet, you'll need to do this manually. The following version, in addition to doing what's described above, also adds a new menu item Custom > Jump to today's date, which you can use on any sheet. This also allows you to return to the date if you wandered off in the process of working with the sheet.

function onOpen() {
 var menu = [{name: "Jump to today's date", functionName: "jumpToDate"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
 jumpToDate();
}

function jumpToDate() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("A:A");
 var values = range.getValues();  
 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i][0].getTime()/day;
   }
   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(i,0,1,1));
     break;
   }    
 }
}

Google-sheets – How to make Google Sheet jump to today’s column when opened

try this code:

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("2:2");

 var values = range.getValues(); 
 values = values[0];
 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 Logger.log(today);
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i].getTime()/day;
   }
   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(0,i,1,1));
     break;
   }    
 }

}

What was changed:

sheet.getRange("2:2"); as you did
values = values[0]; get the first row of data
sheet.setActiveRange(range.offset(0,i,1,1)); offset on columns

Best Answer

Related Solutions

Google-sheets – How to make Google Sheets jump to today’s row when opened

Google-sheets – How to make Google Sheet jump to today’s column when opened

Related Topic