3

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.

5

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
| improve this answer | |
  • 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
3

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)

| improve this answer | |

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