为什么 Class Range getValues 在链接到 Class Sheet getActiveRange 时有时会返回 [[]]? [英] Why Class Range getValues sometimes returns [[]] when chained to Class Sheet getActiveRange?

问题描述

Tl;博士.将 getValues() 链接到类表 getActiveRange() 是一个好习惯吗?什么可能导致有时返回 [[]] 而不是预期值?

注意:[[]] 是在日志/脚本执行页面中显示的内容.这些东西"不显示字符串的引号字符.

<小时>

这源自

11. 添加第三个函数

function myFunction3(){var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');var range = sheet.getActiveRange();var values = range.getValues();Logger.log(values);}

12. 运行myFunction3
13. 使用引用的键盘快捷键打开日志
14. 再次运行myFunction2
15. 使用引用的键盘快捷键打开日志(第 7 步).现在记录了预期值.

以下是使用 Class Sheet getActiveRange() 与一些 Class Range 方法(如 getRow()、getValues())链接的问题，但是当前的答案没有提到问题的原因，他们只是提供了一个替代代码

• 在一天中的特定时间通过电子邮件发送电子表格中的数据
• 将行和列数据转换为仅列数据
• 尝试从另一个选项卡复制值，但一直出现错误函数 getValues() 不能用作左侧";

我已经搜索了问题跟踪器.虽然有一些与 getActiveRange 相关的问题，例如 调用 .getActiveRange() 不会返回正确的单元格，如过滤视图，getActiveRange() 在某些列/行操作的 onChange 触发器中不正确等，它们似乎与此问题没有直接关系.

<小时>

截至 2020 年 8 月 9 日 (UTC)，相同的电子表格/有界脚本正在返回预期值，无论如何我添加了另一个函数来测试 getValues()、getRow()、getColumn()、getA1Notation() 和 getGridI().都返回了预期值.

我刚刚发现另一个关于 getActiveRange() 类似问题的问题 ->getActiveRange 不返回当前选择.这个问题不是重复的，因为那个问题只提到在 24 小时后它再次工作，但没有提到将 Class Range 方法链接到 getActiveRange() 是否是一个好习惯，而当前的答案没有解释了为什么会发生这种情况.

解决方案

到目前为止，我的结论基于对问题的评论以及使用链接的相关问题，答案使用等效代码但不使用链接我认为避免使用带有active"的链接更安全.方法.

我的意思是主动方法

• 类电子表格getActiveSheet()
• 类电子表格和类表getActiveRange()

相关聊天室

• 聊天由我创建
• 聊天由 Oleg Valter 创建
• 聊天由 ♦ mod 创建.

Tl;Dr. Is a good practice chaining getValues() to Class Sheet getActiveRange() ? What could cause that sometimes returns [[]] instead of the expected values?

NOTE: [[]] is what is being displayed in the Log / Script executions page. These "things" doesn't show quotation characters for strings.

---

This is derived from Get selected values in row where I posted an answer with a couple of alternatives to get the values of the active range.

Here I'm specifically asking for the reasons of the randomly failure of the following code

function myFunction2(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var values = sheet.getActiveRange().getValues();
  Logger.log(values);
}

Steps that I followed when the failure ocurred

1. Create a new spreadsheet
2. Add some values to a row
3. Select the row
   Click on the corresponding row heading
4. Click on Tools > Script editor This creates a bounded project that uses the new runtime (Chrome V8) a default Google Cloud Project for Google Apps Script
5. Add a simple function:

function myFunction() {
  var values = SpreadsheetApp.getActiveRange().getValues();
  Logger.log(values);
}

5. Run myFunction to authorize the script
6. Run myFunction to actually execute myFunction
7. Press Ctrl + Enter to open the Log
8. Add the referred function at the beginning of this question (myFunction2)
9. Run myFunction2
10. Open the Log using the referred keyboard shortcut (step 7) [[]] were logged instead of the expected values.

11. Add a third function

function myFunction3(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = sheet.getActiveRange();
  var values = range.getValues();
  Logger.log(values);
}

12. Run myFunction3
13. Open the Log using the referred keyboard shortcut
14. Run again myFunction2
15. Open the Log using the referred keyboard shortcut (step 7). Now the expected values were logged.

The following are questions that use Class Sheet getActiveRange() chained with some Class Range methods like getRow(), getValues() but the current answers doesn't mention the cause of the problem, they just offer an alternative code

• Emailing data from a spreadsheet at a specific time of day
• Convert row and column data to column-only
• Trying to copy values from another tab but keep getting error "function getValues() can not be used as the left-hand side"

I already searched the Issue Tracker. While there are some issues related to getActiveRange like Calls to .getActiveRange() don't return correct cells as seen in filter views, getActiveRange() incorrect in onChange trigger for some column/row operations, etc., they don't appear to be directly related to this issue.

---

As of Aug, 9, 2020 (UTC) the same spreadsheet / bounded script are returning the expected values, anyway I added another funtion to test getValues(), getRow(), getColumn(), getA1Notation() and getGridI(). All returned the expected values.

I just found another question about a similar problem with getActiveRange() -> getActiveRange not returning current selection. This question is not a duplicate because that question only mentions that after 24hours it worked again but didn't mentions if it's a a good practice to chain Class Range methods to getActiveRange() and the currents answers doesn't explains why this happens.

解决方案

So far my conclusion based on the comments to the question and on the related questions that used chaining and the answers uses equivalent code but that doesn't use chaining I think that is safer to avoid the use of chaining with "active" methods.

By active methods I mean

• Class Spreadsheet getActiveSheet()
• Class Spreadsheet and Class Sheet getActiveRange()

Related chat rooms

• chat created by me
• chat created by Oleg Valter
• chat created by a ♦ mod.

这篇关于为什么 Class Range getValues 在链接到 Class Sheet getActiveRange 时有时会返回 [[]]?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！