2

I'm trying to adjust this code so that I can jump to today's date which are all across row 2 in my spreadsheet.

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 answer is from Marshmallow

I tried changing sheet.getRange("A:A") to sheet.getRange("2:2") and sheet.getRange("C2:PB2") but neither work.

4

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:

  1. sheet.getRange("2:2"); as you did
  2. values = values[0]; get the first row of data
  3. sheet.setActiveRange(range.offset(0,i,1,1)); offset on columns
  • Thanks that works. The problem I now discover is it doesn't work in view only mode. I'm using my spreadsheet to show people's rotas, so ideally I'd like this to work in view only mode. i have a messy solution to that by giving editing rights to everyone then protecting the whole sheet with the exception of the date row, but it's not ideal. – London Rabbit Apr 15 '16 at 12:29
  • @LondonRabbit onOpen trigger only runs for editors, not for viewers, actually viewers can't run scripts. – Rubén Sep 4 '18 at 14:07
2

If your dates are in column A, and you want your file to be opened on today's row:

  1. In your google sheet: add this in the cell A1: =match(TODAY();A2:A;0)+1 It will return the row number which contains today's date (ex: row 154).

  2. In script editor write this code that will automatically place your cursor on the cell A154:

    function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = ss.getSheetByName("Sheet1"); // change to sheet containing dates
      var r = s.getRange("A1").getValue(); //change A1 to cell containing =match formula
      s.setActiveSelection(s.getRange("A" + r )); 
    }

This simple method avoids a lot of parsing and date conversions (All the glory to GimelC)

protected by Community Feb 8 at 23:33

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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