0

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.

  • Checkout the solution shared on this other answer. Does something like that works for you? – Rubén Oct 4 '18 at 2:08
  • Thank you - but unfortunately, this won't work. The code you refer to depends on finding today's exact date in the date column. My date column skips many dates - so on some days the code would work (when that date happens to be in the date column), while on others it would fail due to that day's date not being listed. I need code that finds the closest date to today (=> today's date). – user196554 Oct 4 '18 at 6:03
  • You could improve the formula on A1 (=match(TODAY();A2:A;0)+1 ) to return today or the next upcoming date instead of today() – Rubén Oct 4 '18 at 16:11
  • Can you give an example of code that would do this? – user196554 Oct 4 '18 at 21:29
  • Take a look to this question which contains a couple of formulas that you could use instead of =match(TODAY();A2:A;0)+1 – Rubén Oct 4 '18 at 21:50
1

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

=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 )); 
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.