1

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
1

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)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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