Google-sheets – Opening Google Sheets to A Certain Cell or Column

google sheetsgoogle-apps-script

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;
   }    
 }
}

Best Answer

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

Related Topic