Google-sheets – How to make Google Sheets jump to closest date to today when opened

dategoogle sheetsgoogle-apps-scriptjavascript

I have a Google sheet containing a list of events, with event dates listed in one column. I'd like to have my Google Sheets spreadsheet jump to the closest date to today's date on opening (i.e., either today or the next upcoming date).

Can someone help me with the script to do this?

I have tried using Script Editor to jump to the current date in my Google sheets spreadsheet (using "function onOpen" solution posted previously). However, since my date column does not include every day of the year (e.g., 09/25/18, 09/29/18, 09/30/18, 10/03/18 etc), the spreadsheet doesn't focus to the current period unless today's date happens to be one of those listed in my date column.

Best Answer

I was able to piece together a solution thanks to the help of @Rubén who provided the elements. Perhaps there is a more elegant solution (with a single formula?), but this working for me.

This involves using formulas in 2 different cells, as well as a script.

Formula #1 (in cell A1 in this case) finds the closest date on or after today from the list of dates in column A.
Formula #2 (in cell B1 in this case) then returns the row number within column A that contains the date from cell A1.
Finally, the script (pasted into Tools>Script Editor) focuses the page upon loading to that row number.

Formula #1 ##

Placed in cell A1 in this case

=ArrayFormula(TO_DATE(MIN(if(A2:A>today(),A2:A))))     
//where A column contains dates. Looks for closest date on or after today.

Formula #2

Placed in Cell B1, references Formula #1.

=match(A1,A2:A,0)+1      
"match ()" returns the row number, where A1 is the closest date on or after today, and A column contains the list of dates.

Script - in script editor:

Focuses the sheet to the row indicated in formula 2 when the sheet is opened

 function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");
  var r = s.getRange("B1").getValue();
  s.setActiveSelection(s.getRange("A" + r )); 
}

Related Solutions

Google-sheets – How to make Google Sheets tell me how many of each date are in a column

You can reproduce the required table using a formula (this will only work on the newest version of Sheets):

=QUERY({A:A,A:A},"select Col1, count(Col2) where Col1 is not null group by Col1 label Col1 'Date', count(Col2) 'Number'",0)

And then reference that output for your graph.

If you are on the old version:

=ArrayFormula(QUERY(IF({1,1},A:A),"select Col1, count(Col2) where Col1 is not null group by Col1 label Col1 'Date', count(Col2) 'Number'",0))

Google Sheets – How to Hide Columns with Certain Dates on Open

This requires a script with a function onOpen(). Since you are asking about dates and not times, I'm going to break my own advice "do not use .getValue on date/time cells".

Here is a script that does what you described: grabs the dates in the first row, compares them to the present time (ignoring the potential timezone issues: UTC in Google Script vs local time in Sheets), and picks the closest. Then hides the columns to the left of the closest.

function onOpen(e) {
  var sheet = e.source.getSheetByName('Sheet1'); // or .getSheets()[0]; to apply to the first sheet.
  var width = sheet.getDataRange().getWidth();
  var dates = sheet.getRange(1, 1, 1, width).getValues().valueOf();  
  var today = Date.now();
  var minDiff = 1e9;
  var imin = 0;
  for (var i = 0; i < dates[0].length; i++) {
    if (Math.abs(dates[0][i]-today) < minDiff) {
      imin = i;
      minDiff = Math.abs(dates[0][i]-today);
    }
  }
  sheet.hideColumns(1, imin);
}

Whether some of the columns were previously hidden doesn't matter.

You may want to play it safer by hiding one column less, with sheet.hideColumns(1, imin-1); to provide a cushion against timezone discrepancy. Also, nearest date could be before or after today's; this is also worth considering.