6

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.

Edit not from OP: "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." - Comment from OP.

migrated from superuser.com Jun 7 '15 at 3:43

This question came from our site for computer enthusiasts and power users.

  • 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. – Marco Jun 8 '15 at 7:57
9

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;
   }    
 }
}
  • Thanks! It works. May I know how to make this work for multiple sheets ? The same setup with column A be the date. I want to see what to change to make the cursor also jump to Today when I read my 2nd sheet/3rd sheet, so on. Thanks – Marco Jun 9 '15 at 4:51
  • It can't work exactly like this, but see the expanded answer. – user79865 Jun 9 '15 at 17:12
  • Thanks! It works. If any possibility to highlighted current cell? – user122266 Apr 27 '16 at 3:23
3

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.

  • 2
    Ok, but is it possible to link to something like &range=today's_date ? – JinSnow Sep 4 '18 at 7:41
  • Please elaborate how this answer the question. – Rubén Oct 4 '18 at 2:10
1

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
0

Loop through all days column and then:

if (day == new Date()) {

//then get cell

sheet.setCurrentCell(cell);

}

using this method

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.