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

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened.

In other words: The date column has the entire 365 days. I want to jump to the corresponding row which has today date when I open the file.

Best Answer

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;
   }    
 }
}
Related Topic