打开工作表时，想根据日期自动跳转到某些单元格 [英] When sheet is opened, would like to auto-jump to certain cell based on date

问题描述

更新:根据先前的回答，我加入了要求Google重新启用此功能的人们的呼声很高，他们显然已经遵守了.

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.

由于我对Google表格中的脚本一无所知，现在想弄清楚如何实现它.

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

我有一个电子表格，其中包含许多不同的工作表，需要每天输入数据.例如，有一张名为US-Sales的工作表，每年的每一天都有一行.还有其他几张相同的表，例如UK-Sales，US-Rentals等.

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?

推荐答案

通常可以通过带有onOpen()函数的简单脚本(在打开电子表格时执行)并激活所需的单元格(或工作表)来实现.

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.

现在，此问题已于2014年10月16日修复，并且上面的代码也适用于新版本的电子表格. 要自动激活与一年中的某天相对应的行，可以使用以下代码:

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

这篇关于打开工作表时，想根据日期自动跳转到某些单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！