打开时将Google电子表格光标移动到当前日期单元格 [英] Moving google spreadsheet cursor to current date cell on open

问题描述

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

I've been trying to move the view of my spreadsheet when it's opened to the current date.

这是一个调度电子表格，每个月都有一个工作表，第一列中列出的日期向下.

It's a scheduling spreadsheet with a sheet for each month, with dates listed in the first column going down.

我已成功使用以下代码将工作表更改为当月:

I've successfully got the sheet to change to the current month with the following code:

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函数时，不会有任何问题.

This works with no problems when added to the onOpen function.

但是，以下代码仅在通过脚本编辑器手动触发时才起作用，但是在gotoCurrentMonth函数之后调用时似乎没有触发.

However the following code only works if it's triggered manually through the script editor, it doesn't seem to trigger when it's called after the gotoCurrentMonth function.

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

发生的情况是，当电子表格打开时，电子表格将切换到3月工作表，但是所选单元格不会移动.坐在三月表的顶部.

What happens is that the spreadsheet will switch to the March sheet when the spreadsheet opens, but the selected cell won't move. Sitting at the top of the March sheet.

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

If I then move my cursor back to January 1st (like it would normally when opened) and run the onOpen function manually after it's all loaded then it works fine and will select the current date properly.

我的onOpen函数:

My onOpen function:

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

关于onOpen函数是手动还是通过自动触发器激活的原因为何有所不同的任何想法?

Any ideas as to why the onOpen function acts differently if it's activated manually or via the automated trigger?

推荐答案

尝试将两个功能合并为一个...此代码似乎按预期工作:

Try to combine both functions in one... this code seems to be working as you expected :

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个工作表并将其删除:

As a bonus for anyone who would like to test, a small script to create the 12 sheets and delete them as well:

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并进行反向转换并不总是那么容易；)...

---

EDIT :

Since it appears that your spreadsheet formula is causing some issues, here is a "pure JavaScript" approach to get the right day in the right sheet. As they say in the tv serie "Fringe" these are 'parallel universes' or alternate reality... not always easy to go from spreadsheet to JavaScript and reverse ;)...

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

Anyway, a simple function that looks at every cell in col A and gets the day from the date and returns that value can be written as follows :

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，以便在最坏的情况下转到工作表的顶部).

it takes the sheet object as argument and returns a number (returns 1 if no matching date was found so that in the worst case you go to the top of the sheet).

您的主要功能也应更改为:

And your main function should be changed as well, it becomes :

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
}

这篇关于打开时将Google电子表格光标移动到当前日期单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！