2

I am trying to make it so when anyone who is in this weekly calendar opens the doc, it locks to the week we are currently in.

Our dates are in row 4, going across the top of the doc and run from June through December. I need the calendar to open to the week we are currently in when it is opened.

I've seen scripts for locking to today's date, but because we only have the Monday of each week listed, these do not work. Can it lock to a certain cell?

This is the code I tried already:

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;
   }    
 }
}
  • Can you share a sample and edit your question, I didn't understood what's your need. FYI, calendar.google.com/calendar/r/week open current week. You can add a date on the url like this /week/YYYY/MM/DD who'll direct you to the corresponding week. Dunno if this is what you need. – PaulJ Aug 7 at 20:12
  • It's very good that you already made some search/research efforts but the description of that should with more details like links to the related questions that you already reviewed and your attempts to adapt at least one of the suggested solutions to your needs. – Rubén Aug 7 at 21:53
  • @PaulJ I am not using calendar though, we have a spreadsheet in Sheets that lists plans for June through December. The top of the doc has the weeks across in Row 4, then below in each corresponding column, is the event or plan for each week. Right now when you open the doc, it opens to June 1 every time. I would like to to slide over so it opens on the column where Aug 5th is. – rock922 Aug 8 at 17:38
  • @Rubén This is the code I tried before. 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; } } } – rock922 Aug 8 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.