为什么当链接到类表getActiveRange时，类范围getValues有时返回[[]]? [英] Why Class Range getValues sometimes returns [[]] when chained to Class Sheet getActiveRange?

问题描述

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

注意:[[]]是日志/脚本执行"页面中显示的内容.这些事物"被称为事物".不显示字符串的引号字符.

---

这是从在行中获取选定的值派生的，我在其中发布了答案以及一些替代方法来获取这些值有效范围的范围.

在这里，我专门询问以下代码随机失败的原因

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

发生故障时我遵循的步骤

1. 创建一个新的电子表格
2. 在行中添加一些值
3. 选择行
   单击相应的行标题
4. 点击工具>脚本编辑器 这将创建一个有界项目，该项目使用新的运行时(Chrome V8)和默认的Google Cloud Project for Google Apps脚本
5. 添加一个简单的功能:

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

5. 运行myFunction来授权脚本
6. 运行myFunction实际执行myFunction
7. 按 Ctrl + Enter 打开日志
8. 在此问题的开头添加引用的函数(myFunction2)
9. 运行myFunction2
10. 使用引用的键盘快捷方式打开日志(步骤7) 记录了[[]]而不是预期值.

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). 现在已记录了期望值.

以下是使用类表getActiveRange()与某些类范围方法(例如getRow()，getValues())链接在一起的问题，但是当前答案没有提及问题的原因，它们只是提供了替代代码

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

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

---

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

我刚刚发现了另一个有关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.

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