Jump to a cell containing today's date in Google Sheets

Question

I am trying to create a function in Google Sheets that will jump to the cell containing today's date.

The following function works if the dates are in one column and formatted as mm/dd/yyyy.

function gotoToday() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("G:G");
 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;
   } } 
}

However, the cells containing the dates in my sheet are populated as "=A1+1" etc, and spread across columns as follows:

How can the function above be edited so it can find and match today's date?

Thank you

UPDATE: I have managed to make the function work across multiple columns. Now I just need to know how to read the date when the cell is populated as =A1+1, A2+1 etc

Answer 1

Two things:

1. • Your range is defined as var range = sheet.getRange("G:G");
   • This means that your function looks for the date of today only in column G.
   • If you want to extend its functionality over all columns, then you need to define your range e.g. as var range = sheet.getDataRange();
   • Consequently, it will not be enough to loop only through rows (ssdate = values[i][0].getTime()/day;), but you'll need two nested for loops to loop through both rows and columns: ssdate = values[i][j].getTime()/day;
2. • Your spreadsheet has certain settings for formatting of dates - depending on your Locale.
   • If you paste into your spreadsheet a date that is formatted differently than your spreadsheet expects - it will not recognize it as a date object and consequently, the method getTime() cannot be applied to it.
   • If you want to specify that a cell content is a date - you have to select this cell(s) in the spreadsheet and go to Format ->Number -> Date

References:

• getDataRange()
• Locale