将google电子表格光标移动到打开的当前日期单元格

Question

我一直试图在电子表格打开到当前日期时移动它的视图。

这是一个日程安排电子表格，每个月都有一张表格，第一列中列出的日期向下。

我已成功将工作表更改为当月，代码如下：

function gotoCurrentMonth() {
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  sheet.activate();
}

将其添加到onOpen函数时，不会出现任何问题。

但是，以下代码仅在通过脚本编辑器手动触发时有效，在gotoCurrentMonth函数之后调用时似乎不会触发。

function gotoCurrentDay() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  //Get current day value from the MATCH spreadsheet function
  //Example of cell value: 73
  var range = sheet.getRange(2, 2).getValue();

  //Go to bottom cell so that when it goes back up to current day, it's at the top of the screen
  sheet.setActiveCell(sheet.getRange(sheet.getLastRow(), 1));
  Logger.log("Select Last Row: " + sheet.getLastRow());

  //Go back up to current day cell
  sheet.setActiveCell(sheet.getRange(range, 1));
  Logger.log("Select Current Day Row: " + range);
}

当电子表格打开时，电子表格将切换到三月工作表，但选定的单元格不会移动。坐在三月名单的顶端。

然后，如果我将光标移回1月1日（就像打开时一样），并在全部加载后手动运行onOpen功能，那么它将正常工作，并正确选择当前日期。

我的onOpen功能：

function onOpen() {
  gotoCurrentMonth();
  gotoCurrentDay();
};

如果手动或通过自动触发器激活onOpen功能，有什么想法可以解释为什么它的行为会有所不同？

Answer 1

尝试将这两个功能组合在一个。。。此代码似乎按照您的预期工作：

function gotoCurrentMonthAndDay() {
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  var row = sheet.getRange(2, 2).getValue();
  sheet.setActiveCell(sheet.getRange(sheet.getLastRow(), 1));//gets end of sheet
  SpreadsheetApp.flush();//update sheet
  Utilities.sleep(500);//pause 1/2 sec
  sheet.setActiveCell(sheet.getRange(row, 1));//activate on right date
}

作为任何想要测试的人的奖励，一个小脚本来创建12张并删除它们：

function createSheets(){
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];
  for(var n in monthTabs){
    SpreadsheetApp.getActiveSpreadsheet().insertSheet(monthTabs[n]);
    Utilities.sleep(500);
  }
  SpreadsheetApp.getActiveSpreadsheet().deleteSheet(SpreadsheetApp.getActiveSpreadsheet().getSheets()[0])
} 

function deleteSheets(){
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November" ];
  for(var n in monthTabs){
    var del = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(monthTabs[n]);
    try{
      SpreadsheetApp.getActiveSpreadsheet().deleteSheet(del);
    Utilities.sleep(500);
    }catch (err){Logger.log(err)};
  }
 SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].setName('Sheet1');
} 

由于您的电子表格公式似乎导致了一些问题，因此这里有一种“纯JavaScript”方法，可以在正确的表格中找到正确的日期。正如他们在电视剧《边缘》中所说，这些是“平行宇宙”或交替现实。。。从电子表格到JavaScript和反向转换并不总是那么容易；）。。。

无论如何，一个简单的函数可以查看cola中的每个单元格，并从日期中获取日期并返回该值，可以编写如下：

function getTodayRow(sheet){
  var colContent = sheet.getRange('A1:A').getValues();
  var today = new Date().getDate();
  var val=1;
  for(var n in colContent){
    if(new Date(colContent[n][0]).getDate() == today){val=Number(n)+1;break}
  }
  return val;// the +1 above is because arrays count from 0 and rows count from 1. (Number() is to avoid having 13+1=131 which is the default behavior unfortunately)
}

它将工作表对象作为参数，并返回一个数字（如果没有找到匹配的日期，则返回1，这样在最坏的情况下，您可以转到工作表的顶部）。

你的主要功能也应该改变，它变成：

function onOpen() { // I decided to call it simply onOpen to use the simple trigger that is sufficient in this case. Note that the user must have edit right for the script to run. 
  var monthTabs = [ "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December" ];

  var month = (new Date()).getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(monthTabs[month]);
  var row = getTodayRow(sheet);
  sheet.setActiveCell(sheet.getRange(sheet.getLastRow(), 1));//gets end of sheet
  SpreadsheetApp.flush();//update sheet
  Utilities.sleep(500);//pause 1/2 sec, I saw your comment about timing... In my tests it works with 500 millisecs but adjust to your needs.
  sheet.setActiveCell(sheet.getRange(row, 1));//activate on right date
}