范围方法 getValues() 返回和 setValues() 接受什么? [英] What does the range method getValues() return and setValues() accept?

问题描述

我想从我的工作表中获取一个范围.按照最佳实践中的建议，我正在尝试获得数组并操作它，但我很困惑:

const ss = Spreadsheet.getActive(),sh = ss.getSheetByName("Sheet1"),rg = sh.getRange("A1:C1"),//有1,2,3值 = rg.getValues();控制台日志(值)；

日志显示

[[1,2,3]]

如您所见，我获得了所有三个元素.但是，当我记录数组的长度(array.length)时，它只是 1(而不是 3).当我使用 .indexOf 或 .includes 测试元素是否存在时，它显示 -1 或 false.

const values =/* 与上面记录的相同*/[[1,2,3]];console.log(values.indexOf(2));//得到 -1 预期 1console.log(values.includes(1));//得到假的预期真

为什么?

我对 setValues() 有同样的问题.

rg.setValues([1,2,3]);//抛出错误

错误是

<块引用>

参数 (number[]) 与 SpreadsheetApp.Range.setValues 的方法签名不匹配."

我的具体问题是:getValues() 到底返回什么?是一种特殊的数组吗?

解决方案

文档摘录:

来自官方文档，getValues() 返回

<块引用>

一个二维值数组，

它总是返回一个二维值数组.

一维数组是

[1,2,3]

二维数组是

[[1,2,3]]//或者[[1], [2], [3]]

数组中有/有数组.

<块引用>

按行索引，然后按列索引.

首先按行索引:即，外部数组将行作为内部数组.然后每个内部数组都有列元素.考虑以下简单的电子表格:

<头>

	A	B	C
1>	1	2	3
2>	2	3	4
3>	3	4	5

A1:A3 包含 3 行，每行包含 1 个列元素.这表示为 [[1],[2],[3]].同样，以下范围代表以下数组:

<头>

A1 符号	行数	列数	数组 结构	array .length	array[0] .length
A1:A3	3	1	[[1],[2],[3]]	3	1
A1:C1	1	3	[[1,2,3]]	1	3
A1:B2	2	2	[[1,2],[2,3]]	2	2
B1:C3	3	2	[[2,3],[3,4],[4,5]]	3	2
A2:C3	2	3	[[2,3,4],[3,4,5]]	2	3

注意二维如何提供方向.请参阅下面的实时可视化:

/**/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</忽略>*/常量测试 = {'A1:A3': [[1], [2], [3]],'A1:C1': [[1, 2, 3]],'A1:B2': [[1, 2],[2, 3],],'B1:C3': [[2, 3],[3, 4],[4, 5],],'A2:C3': [[2, 3, 4],[3, 4, 5],],};Object.entries(test).forEach(([key, value]) => {console.log(`范围是${key}`);控制台表(值)；console.info(`上表的 JavaScript 数组表示法是 ${JSON.stringify(value)}`)console.log(`==================================`);});

<!-- https://meta.stackoverflow.com/a/375985/--><script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

<块引用>

值可以是数字、布尔值、日期或字符串类型，具体取决于单元格的值.

在上面的示例中，我们将电子表格数字类型元素转换为 JavaScript 数字类型.您可以使用 =TYPE() 检查电子表格类型.对应的 JavaScript 类型参考是这里

<块引用>

空单元格由数组中的空字符串表示.

检查使用

console.log(values[0][0]===")//如果A1为空则记录为真

<块引用>

请记住，虽然范围索引从 1, 1 开始，但 JavaScript 数组是从 [0][0] 开始索引的.

鉴于二维数组结构，要访问一个值，需要两个格式为array[row][column]的索引.在上表中，如果检索到A2:C3，要访问C3，请使用values[1][2].[1] 是 A2:C3 范围内的第二行.请注意，范围本身从第二行开始.因此，给定范围的第二行 in 是 row3 [2] 是第三列 C.

注意事项:

• 警告:

从范围中检索的值总是二维无论范围的高度或宽度如何(即使它只是 1).getRange("A1").getValues() 将代表 [[1]]

• setValues() 将接受与要设置的 range 对应的相同数组结构.如果尝试使用一维数组，则会出现错误

<块引用>

参数 (number[]/string[]) 与 SpreadsheetApp.Range.setValues 的方法签名不匹配.

被抛出.

• 如果数组不完全对应于设置的范围，即如果内部数组的每个长度不对应于范围中的列数或外部数组的长度不对应于行数在设置的范围内，抛出类似如下的错误:

<块引用>

数据中的列数与范围内的列数不匹配.数据有 5，范围有 6.

• 上述错误的相关解答:

  • https://stackoverflow.com/a/63770270
  • 相关搜索

• indexOf/includes 使用严格类型检查.当您将基元与数组对象进行比较时，它们将不起作用.您可以使用 Array.flat将二维数组展平为一维数组.或者，使用普通的旧 for 循环来检查某些内容.

  const values = [[1,2,3]].flat();//扁平化console.log(values.indexOf(2));//应为1console.log(values.includes(1));//预期为真

参考:

• 基础阅读
• MDN 数组指南

I want to get a range from my sheet. As recommended in Best practices, I am trying to get a array and manipulate it, but I'm confused:

const ss = Spreadsheet.getActive(),
  sh = ss.getSheetByName("Sheet1"),
  rg = sh.getRange("A1:C1"),//has 1,2,3
  values = rg.getValues();
console.log(values);

The logs show

[[1,2,3]]

As you can see I got all three elements. But, when I log the length of the array(array.length), it is just 1(instead of 3). When I test existence of a element using .indexOf or .includes, It says -1 or false.

const values = /*same as logged above*/[[1,2,3]];
console.log(values.indexOf(2));//got -1 expected 1
console.log(values.includes(1));//got false expected true

Why?

I have the same issue with setValues().

rg.setValues([1,2,3]);//throws error

The error is

"The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues."

My specific Question is: What exactly does getValues() return? Is it a special kind of array?

解决方案

Documentation excerpts:

From The official documentation, getValues() returns

a two-dimensional array of values,

It ALWAYS returns a two dimensional array of values.

One dimensional array is

[1,2,3]

Two dimensional array is

[[1,2,3]]
//or
[[1], [2], [3]]

There is/are array(s) inside a array.

indexed by row, then by column.

It is indexed by row first: i.e., The outer array has rows as inner array. Then each inner array has column elements. Consider the following simple spreadsheet:

	A	B	C
1>	1	2	3
2>	2	3	4
3>	3	4	5

A1:A3 contains 3 rows and each row contains 1 column element. This is represented as [[1],[2],[3]]. Similarly, The following ranges represent the following arrays:

A1 Notation	Number of Rows	Number of columns	Array Structure	array .length	array[0] .length
A1:A3	3	1	[[1],[2],[3]]	3	1
A1:C1	1	3	[[1,2,3]]	1	3
A1:B2	2	2	[[1,2],[2,3]]	2	2
B1:C3	3	2	[[2,3],[3,4],[4,5]]	3	2
A2:C3	2	3	[[2,3,4],[3,4,5]]	2	3

Note how the two dimension provides direction. See live visualization below:

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
const test = {
  'A1:A3': [[1], [2], [3]],
  'A1:C1': [[1, 2, 3]],
  'A1:B2': [
    [1, 2],
    [2, 3],
  ],
  'B1:C3': [
    [2, 3],
    [3, 4],
    [4, 5],
  ],
  'A2:C3': [
    [2, 3, 4],
    [3, 4, 5],
  ],
};

Object.entries(test).forEach(([key, value]) => {
  console.log(`The range is ${key}`);
  console.table(value);
  console.info(`The above table's JavaScript array notation is ${JSON.stringify(value)}`)
  console.log(`=================================`);
});

<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

The values may be of type Number, Boolean, Date, or String, depending on the value of the cell.

In the above example, We have Spreadsheet Number type elements converted to JavaScript number type. You can check spreadsheet type using =TYPE(). Corresponding JavaScript type reference is here

Empty cells are represented by an empty string in the array.

Check using

console.log(values[0][0]==="")//logs true if A1 is empty    

Remember that while a range index starts at 1, 1, the JavaScript array is indexed from [0][0].

Given the two dimensional array structure, to access a value, two indexes of format array[row][column] is needed. In the above table, if A2:C3 is retrieved, To access C3, Use values[1][2]. [1] is second row in range A2:C3. Note that the range itself starts on second row. So, second row in the given range is row3 [2]is third column C.

Notes:

• Warning:

Retrieved values from a range is always two dimensional regardless of the range height or width(even if it is just 1). getRange("A1").getValues() will represent [[1]]

• setValues() will accept the same array structure corresponding to the range to set. If a 1D array is attempted, the error

The parameters (number[]/string[]) don't match the method signature for SpreadsheetApp.Range.setValues.

is thrown.

• If the array does NOT exactly correspond to the range being set,i.e.,if each of the the inner array's length does not correspond to the number of columns in the range or the outer array's length does not correspond to the number of rows in the range being set, The error similar to the following is thrown:

The number of columns in the data does not match the number of columns in the range. The data has 5 but the range has 6.

• Related answers to the above error:

  • https://stackoverflow.com/a/63770270
  • Related Search

• indexOf/includes uses strict type checking. They won't work when you compare primitives against array objects. You can use Array.flat to flatten the 2D array to a 1D one. Alternatively, Use a plain old for-loop to check something.

  const values = [[1,2,3]].flat();//flattened
  console.log(values.indexOf(2));//expected 1
  console.log(values.includes(1));//expected true

References:

• Basic reading
• MDN Arrays guide

这篇关于范围方法 getValues() 返回和 setValues() 接受什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！