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. – pjmg 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
  • Just share a sample. – contributorpw Sep 1 at 19:16
1

This answer can be adapted to the layout used by the OP, as well as to a trigger.

There are usually many ways that a given problem can be solved; this should be considered as just one alternative.

Aspects to note:

  1. used the Javascript method getDay() to return the day of the week.
    This is the equivalent of the WEEKDAY function in Google Sheets except that the Javascript method is zero-based. Then calculated the number of days (nowadj) that needs to be adjusted to get to the previous Monday.
  2. used getRange and getValues to get the date range; but then converted the data to a 1D array; then converted those values to strings.
  3. used a small sub-routine described by Serge inasas to subtract a given number of days from today's date. This returns the date for last Monday.

  4. used indexOf to find last Monday's date in the list of calendar dates. The result gives the column number, but note that this is zero-based (so add 1) and the data range started at Column B (so add 1 to allow for Column A).

  5. used code described by Serge insas in "UPDATE: When sheet is opened, would like to auto-jump to certain cell based on date" as best alternative to scroll to the cell.


function wa13258002() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);

  // get today's date and the day of the week
  var now = new Date();
  // Javascript method to get the day from the date
  var nowday = now.getDay();
  //Logger.log("DEBUG: now = "+now+", this is day of the week = "+nowday);

    // test for the day of the week; if Monday then do nothing, otherwise calculate how many days to adjust
  if (nowday != 1) {
    //today is not a monday
    // adjust day =1, so day -1
    var nowadj = nowday - 1;
  } else {
    // today is monday
    // do nothing
  }

  // find today in all the dates
  var LC = sheet.getLastColumn();
  var datesRange = sheet.getRange(4, 2, 1, LC - 1);
  // Logger.log("// DEBUG: the Dates range is "+datesRange.getA1Notation());
  var datesValues = datesRange.getValues();
  // make it a 1D array
  var calendardates = datesValues[0];
  // convert the dates to strings
  var newdates = calendardates.map(function(element) {
    return element.toDateString();
  });
  //Logger.log(calendardates);
  //Logger.log(newdates);


  // get the adjusted date for "last Monday"
  var targetdate = subDaysFromDate(now, nowadj);
  // convert it to a string (makes it easier to search
  var targetdatestring = targetdate.toDateString();
  Logger.log("DEBUG: today= " + new Date() +
    "  and last Monday is 5 days ago is " + targetdate +
    ", revised format = " + targetdatestring);

  // find the column for last Monday's date in all the dates
  // note1: this is zero-based
  // note2: the range starts at column B, so that will also require adjustment when working out the range
  var posn = newdates.indexOf(targetdatestring);
  // Logger.log("DEBUG: Column = "+posn)
  var result = newdates[posn];
  //Logger.log("DEBUG: result = "+result);

  // get the cell containing last Monday's date
  var gotorange = sheet.getRange(4, +posn + 2);
  Logger.log(gotorange.getA1Notation());

  // scroll to cell
  // start by going to the furthest corners of the spreadsheet
  var depths = sheet.getRange(sheet.getMaxRows(), sheet.getMaxColumns());
  sheet.setCurrentCell(depths); //gets end of sheet
  SpreadsheetApp.flush(); //update sheet
  Utilities.sleep(500); //pause 1/2 sec
  // now go the cell for last Monday
  sheet.setCurrentCell(gotorange); //activate on right date

}

function subDaysFromDate(date, d) {
  // by Serge ingas
  // StackOveflow "Trying to subtract 5 days from a defined date - Google App Script"
  // https://stackoverflow.com/a/20059508/1330560
  // d = number of day ro substract and date = start date
  var result = new Date(date.getTime() - d * (24 * 3600 * 1000));
  return result
}

Calendar layout used in this answer
Screenshot

Your Answer

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

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