UPDATE: When sheet is opened, would like to auto-j

Question

UPDATE: Based on earlier answers, I joined the chorus of people asking Google to re-enable the ability to do this, and they have apparently complied. Thx!

Now to figure out how to implement it since I know nothing about scripting in Google Sheets. :)

---

ORIGINAL QUESTION BELOW

I have a spreadsheet with a number of different sheets that require daily entry of data. For example, there's a sheet called US-Sales, with a row for each day of the year. There are also several other identical sheets, like UK-Sales, US-Rentals, etc.

I would like focus to auto-jump to the proper row, based on the current date, whenever one of these sheets is opened.

Possible? If so, how?

Huge thanks in advance!

Chop

Answer 1

This could normally be achieved by a simple script with an onOpen() function (that executes on spreadsheet opening) and activates the desired cell (or sheet)...

but, due to a change in the new version of spreadsheets (that you are most probably using) this is not possible anymore (for now), see this issue (3928) and feel free to star it so that hopefully Google team will change their mind and make it possible again ;-)

---

edit : this simple code works in old version of spreadsheets, it does not in new version.

function onOpen() {
  SpreadsheetApp.getActive().getSheets()[0].getRange('B6').activate();// an arbitrary cell
  // not worth trying more complex cell selection (on date or anything else) while this is blocked by design .
}

Details on this change here and below.

---

EDIT 2 :

On october 16 2014 this issue is now fixed and the code above works also in new version of spreadsheets. details of issue here

to automatically activate the row corresponding to the day of the year you can use a code like below :

function onOpen() {
  var day = new Date().getDOY();// this uses a custom date method that returns the day of the year and is defined below
  SpreadsheetApp.getActive().getSheets()[0].getRange(day,1).activate();// in cloumn 1 for example. Add an offset if necessary (if headers...)

}

Date.prototype.getDOY = function() {
var onejan = new Date(this.getFullYear(),0,1);
return Math.ceil((this - onejan) / 86400000);
}