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.

  1. Formula #1 (in cell A1 in this case) finds the closest date on or after today from the list of dates in column A.
  2. Formula #2 (in cell B1 in this case) then returns the row number within column A that contains the date from cell A1.
  3. 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


//where A column contains dates. Looks for closest date on or after today.

Formula #2

Placed in Cell B1, references Formula #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 ));