Google Apps脚本函数getActiveSheet()不返回活动工作表 [英] Google Apps Script function getActiveSheet() does not return active sheet

问题描述

如果我有三个表，分别是Mary，Moe和Joe，然后将Mary表显示(因此，它是活动表)，然后运行以下简单脚本:

  function myFunction(){var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getActiveSheet();Logger.log(sheet.getName());ss.setActiveSheet(ss.getSheets()[2]);Logger.log(ss.getActiveSheet().getName());} 

我希望输出说出来

  [18-10-26 18:41:45:724 EDT]玛丽[18-10-26 18:41:45:730 EDT]乔 

但是，实际上它说:

  [18-10-26 18:49:14:752 EDT]萌[美国东部时间18-10-26 18:49:14:755] 

如果我第二次不触摸表格就运行它，我希望输出会显示

  [18-10-26 18:50:51:930 EDT]乔[18-10-26 18:50:51:933 EDT]乔 

但是实际上它说的和以前一样

  [18-10-26 18:50:51:930 EDT]萌[18-10-26 18:50:51:933 EDT]乔 

换句话说，设置活动工作表确实确实会更改Spreadsheet UI中显示的工作表，但是似乎并没有在内部将新工作表注册为活动工作表.GAS始终将Moe视为活动表，即使将活动表设置为其他表也是如此.

我希望能得到一个解释和一种解决此问题的方法.基本上，我只需要 getActiveSheet()和 getActiveRange()(存在类似问题)，即可为我提供Spreadsheet UI中当前可见的表单，其定义与开发人员指南.

我找到了解决此问题的方法.我要从评论中引用自己.

大家好，谢谢大家的投入.我才发现getActiveSheet()确实按照我希望的方式工作，但是仅当您通过使用以下命令构建的自定义菜单UI运行该功能时SpreadsheetApp.getUi().createMenu(...)，需要一个onOpen()触发(感谢@I'-'I提示).从脚本编辑器引起了启发我原始帖子的问题.

但是，虽然我很高兴找到了解决方案，但感觉更像是一种解决方法，无法解决核心问题.我觉得 getActiveSheet()在菜单UI和脚本编辑器中都应按照《开发人员指南》中的定义进行操作.它不能在一个中起作用，而在另一个中也没有意义.

解决方案

显示所有表格10秒钟

此脚本将循环遍历电子表格中的所有工作表，一次激活一个工作表并显示大约10秒钟.

 功能cycleThroughAllSheets(){var ss = SpreadsheetApp.getActive();var shA = ss.getSheets();var start = new Date();for(var i = 0; i< shA.length; i ++){shA [i] .activate();SpreadsheetApp.flush();ss.toast('活动表为'+ shA [i] .getName());做{//没有什么} while(new Date().valueOf()-start.valueOf()< 10000);start = new Date();}ss.toast('脚本结尾.')} 

The Google Apps Script Developers' Guide's definition of an "active sheet":

The active sheet in a spreadsheet is the sheet that is being displayed in the spreadsheet UI.

If I have three sheets, named Mary, Moe, and Joe, and leave the Mary sheet on display (hence, it's the active sheet) and run this simple script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  Logger.log(sheet.getName());

  ss.setActiveSheet(ss.getSheets()[2]);
  Logger.log(ss.getActiveSheet().getName());
}

I expect the output to say

[18-10-26 18:41:45:724 EDT] Mary
[18-10-26 18:41:45:730 EDT] Joe

But, in fact, it says:

[18-10-26 18:49:14:752 EDT] Moe
[18-10-26 18:49:14:755 EDT] Joe

And if I run it a second time without touching the sheets, I would expect the output to say

[18-10-26 18:50:51:930 EDT] Joe
[18-10-26 18:50:51:933 EDT] Joe

But it actually says the same thing as before

[18-10-26 18:50:51:930 EDT] Moe
[18-10-26 18:50:51:933 EDT] Joe

In other words, setting the active sheet does indeed change the sheet that's on display in the Spreadsheet UI, but it doesn't seem to internally register the new sheet as the active sheet. GAS is always recognizing Moe as the active sheet, even after setting the active sheet to a different one.

I would appreciate an explanation and a way to fix this problem. Basically, I simply need getActiveSheet() and getActiveRange() (which has similar issues) to give me the sheet that is currently visible in the Spreadsheet UI, exactly as defined in the Developer Guide.

Edit: I found a solution to this problem. I am quoting myself from the comments.

Hi everyone, thank you all for your input. I just discovered that getActiveSheet() does indeed work the way I was hoping it would, but only if you run the function through a custom menu UI built using SpreadsheetApp.getUi().createMenu(...), which needs an onOpen() trigger (thanks to @I'-'I for this tip). Running the function from the script editor causes the problems that inspired my original post.

However, while I am glad I found a solution, it feels more like a workaround leaving the core problem unresolved. I feel as though getActiveSheet() should act as defined in the Developer Guide in both cases, in the Menu UI and the script editor. It doesn't make sense that it works in one and it doesn't in the other.

解决方案

Display All Sheets for 10 seconds

This script will cycle through all of the sheets in a spreadsheet activating them one at a time and displaying them for approximately 10 second.

function cycleThroughAllSheets() {
  var ss=SpreadsheetApp.getActive();
  var shA=ss.getSheets();
  var start=new Date();
  for(var i=0;i<shA.length;i++){
    shA[i].activate();
    SpreadsheetApp.flush();
    ss.toast('Active Sheet is ' + shA[i].getName());
    do{
      //nothing
    }while(new Date().valueOf()-start.valueOf()<10000);
    start=new Date();
  }
  ss.toast('End of Script.')
}

这篇关于Google Apps脚本函数getActiveSheet()不返回活动工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！