17

I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened.

In other words: The date column has the entire 365 days. I want to jump to the corresponding row which has today date when I open the file.

1
  • Please accept an answer. Commented Feb 8, 2021 at 18:38

5 Answers 5

15

You can use the following script (see Tools > Script Editor). It executes whenever the spreadsheet is opened, and searches column A for the current date. It then sets the cursor to the cell with that date. You can use another column instead of A by editing the command sheet.getRange("A:A")

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("A:A");
 var values = range.getValues();  
 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i][0].getTime()/day;
   }
   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(i,0,1,1));
     break;
   }    
 }
}

This script borrows from this answer by Serge insas.


Unfortunately, it seems impossible to bind a script to the event of switching between sheets. So, if you'd like to invoke it on more than one sheet, you'll need to do this manually. The following version, in addition to doing what's described above, also adds a new menu item Custom > Jump to today's date, which you can use on any sheet. This also allows you to return to the date if you wandered off in the process of working with the sheet.

function onOpen() {
 var menu = [{name: "Jump to today's date", functionName: "jumpToDate"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
 jumpToDate();
}

function jumpToDate() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("A:A");
 var values = range.getValues();  
 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i][0].getTime()/day;
   }
   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(i,0,1,1));
     break;
   }    
 }
}
1
  • 1
    Thanks! It works. If any possibility to highlighted current cell?
    – user122266
    Commented Apr 27, 2016 at 3:23
6

If you are open to use filters and to hide the other rows, use a filter / filter view with Filter by condition set to Date is > Today

Comparison to other alternatives

  • This will work for editors and viewers while the script based solutions only work for editors
  • A URL based solution, like including &range=someting or using filter view's url only work when using the corresponding URL
2
  • 1
    Solution still working \o/
    – marcanuy
    Commented Jan 20, 2022 at 14:08
  • 1
    I chose "Date is after" and "yesterday", and that's what I wanted. Thanks!
    – Ryan
    Commented May 29, 2022 at 14:27
5

It's much simpler now in 2017 :)

Simply add "&range=A6" after the URL of the Google Sheet.

The "&range" is the range that you want the cursor to be at, and the "A" is for the column, and the "6" for the row.

So the URL would look something like this: https://docs.google.com/spreadsheets/d/JrKpzRQVXptmRfuE/edit#gid=0&range=A6

Alternative method to find the URL: If you are on a specific cell, left click on it, and select the "get link to this cell" option. It will be copied to your clipboard and you can paste it, or modify it to suit your needs.

4
  • 5
    Ok, but is it possible to link to something like &range=today's_date ?
    – JinSnow
    Commented Sep 4, 2018 at 7:41
  • 1
    Please elaborate how this answer the question. Commented Oct 4, 2018 at 2:10
  • 1
    @Ruben I have a spreadsheet with 1172 rows, a new one is added everyday. I've just added &range=U1172 to the URL and now it opens at the bottom. Sure, I'll update in a couple of weeks to move down 14 rows but it beats all the scrolling in the meantime.
    – Ewan
    Commented Feb 13, 2023 at 9:55
  • @Ewan If you think th you can elaborate how the answer answers the question., please edit it. Commented Feb 13, 2023 at 10:03
2

Loop through all days column and then:

if (day == new Date()) {

//then get cell

sheet.setCurrentCell(cell);

}

using this method

2

Adding to gdesmedt1's post and answering the following question:

Ok, but is it possible to link to something like &range=today's_date ? – JinSnow

The answer is yes!

  • First, right click on any cell in the sheet and click Get link to this cell GetLinkToThisCell

  • Second, write the following formula in the cell that you wish the hyperlink to be placed -- pasting the URL you copied into the first parameter location

         =HYPERLINK(
      "https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=352488775&range=D766"
      "Jump to Today's Date")
    
  • Third, you will edit the URL from the specific range (i.e. range=D766) to a dynamic range using the address and match formulas

     =HYPERLINK(
      "https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=352488775&range="&address(
      match(today(),[Insert the Column Range here i.e. B2:2],[Insert 0 here for the next closest match OR Insert 1 here for an exact match])+1,
      [Insert Your Column Number Here i.e. 2],
      4),"Jump to Today's Date")
    
  • Now anytime you want to jump to the closest date to today -- you just left click the hyperlink enter image description here

For a more in depth tutorial, see Prashanth's writeup.

How to make Google Sheets jump to today's row when opened?

To the OP, you can use the exact same method to solve your problem. After you follow the steps above to store the constructed URL in a cell, in your google script on page load access the cell and retrieve the URL, then redirect the page to the URL which will take you to the latest DateTime stamp to today().

Note: You will need to store some kind of information (i.e. counter or time stamp in a cell) to check to see if you just redirected to the page -- otherwise your page will be stuck in an infinite loop of redirects :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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