2

New to scripting. Have a planner in Google sheets which is displayed on a monitor in the office. I have a script to make it jump to today's date on open (found here). The planner is organized with 1st of January on top, 31st of December bottom.

I want to make the sheet open to today's date +10 (row), like an offset if possible.

Script:

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

2 Answers 2

1

I think what you were looking for was:

sheet.setActiveRange(range.offset(i+10,0,1,1));
0

try: sheet.setActiveRange(range.offset(i,10,1,1))

or: sheet.setActiveRange(range.offset(i,0,11,1))

or: sheet.setActiveRange(range.offset(i,0,1,11))

1
  • Thank you. It seems these alternatives refer to markings in the spreadsheet. Alt. 3 marks the entire row, but the pages only jumps down to today's date.
    – runquest
    Feb 25, 2019 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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