Google spreadsheet / docs , jump to current date cell on Open

Question

I've searched for this topic and have found a few threads - however, none of the answers (usually in the form of scripts) in those topics have proven to be successful to me yet. I guess I'm messing up some variables.

I have a spreadsheet in Google Docs that holds a work roster. In row B1:OI1 is just over a years worth of dates, i.e., B1 = Friday May 1st 2015, B2 = Saturday May 2nd 2015, OI = Wednesday June 1st 2016.

I want the sheet to jump to the roster of either today, or to the monday of the current week, whenever the sheet is opened.

How do I accomplish this?

Thanks in advance for the help!

Answer 1

I suppose you have seen this post where the OP wanted to change the background color of today's date in a sheet, your case is very similar except that - if I understood you well - today is not necessarily present in the sheet.

So what we need is to find the closest date to today ? You mention it has to be a Monday, I didn't go that far, the script below finds the closest date in column A, you will adapt it to your needs by simply adapting the index in the array. (don't forget arrays count from 0)

code :

function onOpen() { // runs automatically
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var today = new Date().setHours(0,0,0,0);
  var diffref = today;
  var diff;
  var idx;
  for(var n=0;n<data.length;n++){
    var date = new Date(data[n][0]).setHours(0,0,0,0);
    diff=today-date;
    if(diff==0){break}
    Logger.log("diffref = "+diffref+" today-date = diff = "+diff);
    if(diff < diffref && diff > 0){idx=n ; diffref=diff}
    }
  if(n==data.length){n=idx}
  n++;
  sh.getRange(n, 1).activate();
}

---

Edit :

To check the day of the week (yes, I'm curious by nature ;-) you can try to change the condition like this :

Logger.log("diffref = "+diffref+" today-date = diff = "+diff+" day = "+new Date(date).getDay());
if(diff < diffref && diff > 0 && new Date(date).getDay()==1){idx=n ; diffref=diff}

From my tests it seems to work as expected.

---

EDIT 2 :

Following your comment, it seems what you're looking for is much simpler :

function onOpen2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var today = new Date().setHours(0,0,0,0);
  for(var n=0;n<data[0].length;n++){
    var date = new Date(data[0][n]).setHours(0,0,0,0);
    if(date==today){break};
  }
  n++;
  sh.getRange(1,n).activate();
}

---

EDIT 3

For some reason that I ignore (please anyone give advise !!) your sheet does not return date values from the date in cells but rather the native spreadsheet values which are integers corresponding to the number of days since december 30,1899...

So what I did is to subtract this offset value from the javascript today variable, divide it by the number of milliseconds in a day (JS counts in milliseconds) and take the integer part of the result. A bit cumbersome I admit but I didn't find a simpler way...

What is really weird is that in any other sheet I try dates are always returned as dates...

Anyway, for the time being, here is a working code for your spreadsheet :

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var offsetToSS = new Date('1899/12/30').setHours(0,0,0,0);
  var today = parseInt((new Date().setHours(0,0,0,0)-offsetToSS)/86400000,10)+1;
  for(var n=0;n<data[0].length;n++){
    var date = data[0][n];
    Logger.log("date = "+data[0][n]+" =? "+today);
    if(date==today){break};
  }
  n++;
  sh.getRange(1,n).activate();
}

Last note : for a better user experience, add this line of code right after var sh = ss.getActiveSheet();

sh.getRange(1,sh.getLastColumn()).activate();

this will select the last column before activating today's column and will place the selection on the left (near the frozen column in your sheet) which is more "natural".