问题描述
我已经搜索了该主题并找到了一些线索-但是，这些主题中的所有答案(通常以脚本的形式)都没有被证明对我来说是成功的.我想我弄乱了一些变量.
I've searched for this topic and have found a few threads - however, none of the answers (usually in the form of scripts) in those topics have proven to be successful to me yet. I guess I'm messing up some variables.
我在Google文档中有一个电子表格，其中包含工作清单. B1:OI1行中的日期值已超过一年，即B1 = 2015年5月1日星期五，B2 = 2015年5月2日星期六，OI = 2016年6月1日星期三.
I have a spreadsheet in Google Docs that holds a work roster. In row B1:OI1 is just over a years worth of dates, i.e., B1 = Friday May 1st 2015, B2 = Saturday May 2nd 2015, OI = Wednesday June 1st 2016.
无论何时打开工作表，我都希望工作表跳到今天或本周的星期一.
I want the sheet to jump to the roster of either today, or to the monday of the current week, whenever the sheet is opened.
我如何做到这一点?
提前感谢您的帮助！
推荐答案
我想您已经看到，其中OP希望更改工作表中今天日期的背景颜色，具体情况请参见与我非常相似，不同之处在于-如果我理解得很好-今天不一定会出现在工作表中.
I suppose you have seen this post where the OP wanted to change the background color of today's date in a sheet, your case is very similar except that - if I understood you well - today is not necessarily present in the sheet.
那么我们需要找到最接近今天的日期吗?您提到它必须是星期一，我还没走那么远，下面的脚本在A列中找到最接近的日期，您只需通过调整数组中的索引就可以根据需要对其进行调整. (不要忘记数组从0开始计数)
So what we need is to find the closest date to today ? You mention it has to be a Monday, I didn't go that far, the script below finds the closest date in column A, you will adapt it to your needs by simply adapting the index in the array. (don't forget arrays count from 0)
function onOpen() { // runs automatically
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var data = sh.getDataRange().getValues();
var today = new Date().setHours(0,0,0,0);
var diffref = today;
var diff;
var idx;
for(var n=0;n<data.length;n++){
var date = new Date(data[n][0]).setHours(0,0,0,0);
diff=today-date;
if(diff==0){break}
Logger.log("diffref = "+diffref+" today-date = diff = "+diff);
if(diff < diffref && diff > 0){idx=n ; diffref=diff}
}
if(n==data.length){n=idx}
n++;
sh.getRange(n, 1).activate();
}
要检查星期几(em，我天生好奇；-)您可以尝试更改这种情况:
To check the day of the week (yes, I'm curious by nature ;-) you can try to change the condition like this :
Logger.log("diffref = "+diffref+" today-date = diff = "+diff+" day = "+new Date(date).getDay());
if(diff < diffref && diff > 0 && new Date(date).getDay()==1){idx=n ; diffref=diff}
从我的测试来看，它似乎可以正常工作.
From my tests it seems to work as expected.
在您发表评论之后，您似乎正在寻找简单得多的东西:
Following your comment, it seems what you're looking for is much simpler :
function onOpen2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var data = sh.getDataRange().getValues();
var today = new Date().setHours(0,0,0,0);
for(var n=0;n<data[0].length;n++){
var date = new Date(data[0][n]).setHours(0,0,0,0);
if(date==today){break};
}
n++;
sh.getRange(1,n).activate();
}
编辑3
出于某种原因，我忽略了(请任何人提供建议！).您的工作表不会从单元格中的日期返回日期值，而是返回原始电子表格值，该值是与自1899年12月30日以来的几天...
For some reason that I ignore (please anyone give advise !!) your sheet does not return date values from the date in cells but rather the native spreadsheet values which are integers corresponding to the number of days since december 30,1899...
所以我要做的是从javascript today变量中减去此偏移量值，将其除以一天中的毫秒数(JS计数以毫秒为单位)，然后取结果的整数部分.我承认有点麻烦，但我没有找到更简单的方法...
So what I did is to subtract this offset value from the javascript today variable, divide it by the number of milliseconds in a day (JS counts in milliseconds) and take the integer part of the result.A bit cumbersome I admit but I didn't find a simpler way...
真正奇怪的是，在我尝试的任何其他工作表中，日期总是作为日期返回...
What is really weird is that in any other sheet I try dates are always returned as dates...
无论如何，这暂时是您的电子表格的工作代码:
Anyway, for the time being, here is a working code for your spreadsheet :
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var data = sh.getDataRange().getValues();
var offsetToSS = new Date('1899/12/30').setHours(0,0,0,0);
var today = parseInt((new Date().setHours(0,0,0,0)-offsetToSS)/86400000,10)+1;
for(var n=0;n<data[0].length;n++){
var date = data[0][n];
Logger.log("date = "+data[0][n]+" =? "+today);
if(date==today){break};
}
n++;
sh.getRange(1,n).activate();
}
最后一点::为了获得更好的用户体验，请在var sh = ss.getActiveSheet();
Last note : for a better user experience, add this line of code right after var sh = ss.getActiveSheet();
sh.getRange(1,sh.getLastColumn()).activate();
这将在激活今天的列之前选择最后一列，并将所选内容放在更自然"的左侧(工作表中冻结的列附近).
this will select the last column before activating today's column and will place the selection on the left (near the frozen column in your sheet) which is more "natural".
这篇关于Google电子表格/文档，在Open上跳至当前日期单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！