Moving google spreadsheet cursor to current date cell on open

Question

I've been trying to move the view of my spreadsheet when it's opened to the current date.

It's a scheduling spreadsheet with a sheet for each month, with dates listed in the first column going down.

I've successfully got the sheet to change to the current month with the following code:

function gotoCurrentMonth() {
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  sheet.activate();
}

This works with no problems when added to the onOpen function.

However the following code only works if it's triggered manually through the script editor, it doesn't seem to trigger when it's called after the gotoCurrentMonth function.

function gotoCurrentDay() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  //Get current day value from the MATCH spreadsheet function
  //Example of cell value: 73
  var range = sheet.getRange(2, 2).getValue();

  //Go to bottom cell so that when it goes back up to current day, it's at the top of the screen
  sheet.setActiveCell(sheet.getRange(sheet.getLastRow(), 1));
  Logger.log("Select Last Row: " + sheet.getLastRow());

  //Go back up to current day cell
  sheet.setActiveCell(sheet.getRange(range, 1));
  Logger.log("Select Current Day Row: " + range);
}

What happens is that the spreadsheet will switch to the March sheet when the spreadsheet opens, but the selected cell won't move. Sitting at the top of the March sheet.

If I then move my cursor back to January 1st (like it would normally when opened) and run the onOpen function manually after it's all loaded then it works fine and will select the current date properly.

My onOpen function:

function onOpen() {
  gotoCurrentMonth();
  gotoCurrentDay();
};

Any ideas as to why the onOpen function acts differently if it's activated manually or via the automated trigger?

I guess every sheet in your spreadsheet has cell 2,2 containing the match formula ? right ? — Serge insas
That's correct, however the Match formula will return #N/A on months that aren't the current month. It's only on March that it currently has 73 in it. — bluesatin
maybe try to add a spreadsheetApp.flush() between the 2 calls to force the sheet to go to the right page before trying to get the value in cell 2,2 — Serge insas
The flush didn't seem to help, I put it in the onOpen function between the gotoCurrentMonth and gotoCurrentDay. It's worth noting that the gotoCurrentDay won't even move the active cell to the last cell, which doesn't rely on getting a value from a cell. I move it to the last cell and back up, so that the current date will be at the top of the screen. But as I say, even that doesn't happen. — bluesatin
Ok, thanks for testing. I'll try to find and let you know later if I come up to something. — Serge insas

Serge insas Serge insas · Accepted Answer · 2014-03-12T17:11:07

Try to combine both functions in one... this code seems to be working as you expected :

function gotoCurrentMonthAndDay() {
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  var row = sheet.getRange(2, 2).getValue();
  sheet.setActiveCell(sheet.getRange(sheet.getLastRow(), 1));//gets end of sheet
  SpreadsheetApp.flush();//update sheet
  Utilities.sleep(500);//pause 1/2 sec
  sheet.setActiveCell(sheet.getRange(row, 1));//activate on right date
}

As a bonus for anyone who would like to test, a small script to create the 12 sheets and delete them as well:

function createSheets(){
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];
  for(var n in monthTabs){
    SpreadsheetApp.getActiveSpreadsheet().insertSheet(monthTabs[n]);
    Utilities.sleep(500);
  }
  SpreadsheetApp.getActiveSpreadsheet().deleteSheet(SpreadsheetApp.getActiveSpreadsheet().getSheets()[0])
} 

function deleteSheets(){
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November" ];
  for(var n in monthTabs){
    var del = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(monthTabs[n]);
    try{
      SpreadsheetApp.getActiveSpreadsheet().deleteSheet(del);
    Utilities.sleep(500);
    }catch (err){Logger.log(err)};
  }
 SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].setName('Sheet1');
}

EDIT :

Since it appears that your spreadsheet formula is causing some issues, here is a "pure JavaScript" approach to get the right day in the right sheet. As they say in the tv serie "Fringe" these are 'parallel universes' or alternate reality... not always easy to go from spreadsheet to JavaScript and reverse ;)...

Anyway, a simple function that looks at every cell in col A and gets the day from the date and returns that value can be written as follows :

function getTodayRow(sheet){
  var colContent = sheet.getRange('A1:A').getValues();
  var today = new Date().getDate();
  var val=1;
  for(var n in colContent){
    if(new Date(colContent[n][0]).getDate() == today){val=Number(n)+1;break}
  }
  return val;// the +1 above is because arrays count from 0 and rows count from 1. (Number() is to avoid having 13+1=131 which is the default behavior unfortunately)
}

it takes the sheet object as argument and returns a number (returns 1 if no matching date was found so that in the worst case you go to the top of the sheet).

And your main function should be changed as well, it becomes :

function onOpen() { // I decided to call it simply onOpen to use the simple trigger that is sufficient in this case. Note that the user must have edit right for the script to run. 
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  var row = getTodayRow(sheet);
  sheet.setActiveCell(sheet.getRange(sheet.getLastRow(), 1));//gets end of sheet
  SpreadsheetApp.flush();//update sheet
  Utilities.sleep(500);//pause 1/2 sec, I saw your comment about timing... In my tests it works with 500 millisecs but adjust to your needs.
  sheet.setActiveCell(sheet.getRange(row, 1));//activate on right date
}

Moving google spreadsheet cursor to current date cell on open

1 Answers

EDIT :