Google Sheets Go to Today button

Question

I have a sheet with horizontal dates (starting at 1 January, ending in 31 Dec). I'm trying to put a button in the sheet which will make it jump to "Today".

This is what I have so far:

function goToSheet(sheetName, row, col) {

  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  SpreadsheetApp.setActiveSheet(sheet);
  var range = sheet.getRange(1, col)
  SpreadsheetApp.setActiveRange(range);
}

function goToSheet2b() {
  goToSheet("2016", 1,299);
}

2016 is the sheet name, 299 is the column number for today's date and it actually jumps to today, but I would need to manually change 299 in order for it to work every day. Is there a simple way of going about this?

Welcome to StackOverflow. Please take the [tour] and checkout [ask]. — Rubén, Oct 24 '16 at 14:44
Related: http://stackoverflow.com/questions/39933878/open-google-spreadsheet-at-date/ — Rubén, Oct 24 '16 at 14:44

score 1 · Answer 1 · answered Aug 23 '19 at 10:48

I'd propose to attach the button to the following "goToTodayKeepRow" function:

function goToTodayKeepRow() {
  var ass = SpreadsheetApp.getActiveSpreadsheet();
  var as = ass.getActiveSheet();
  var today = new Date();
  today.setHours(0,0,0,0);

  var datesRange = as.getRange(1, 1, 1, as.getLastColumn());

  for (var column = 1; column <= datesRange.getNumColumns(); column++) {
    var cell = datesRange.getCell(1, column);
    if (isValidDate(cell.getValue()) && cell.getValue().getTime() === today.getTime()) {
      as.getRange(as.getActiveCell().getRow(), cell.getColumn()).activate();
      break;
    }
  }
}
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

The advantages are:

cells which don't contain a valid date are skipped, it uses the isValidDate function from this post: Run custom function if value in cell is date
the function keeps the active row, it only changes the active column, if that is not desired the line before break can be replaced with "cell.activate();"
the date compare is done with getTime() function

score 0 · Answer 2 · answered Oct 24 '16 at 17:14

Try attaching this script to your button:

function activateToday() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var lc = s.getLastColumn();
  var range = s.getRange(1, 1,1,lc).getValues();
   for(var i=0;i<range[0].length;i++){
    if(range[0][i].getDate()+ range[0][i].getMonth()+1 ==new  Date().getDate()+new Date().getMonth()+1 ){
     s.getRange(1,i+1).activate();
      break;
    }}}

Google Sheets Go to Today button

2 Answers2