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

问题描述

我想从工作表中获取一个范围.按照最佳做法中的建议，我正在尝试获取数组并对其进行操作，但我感到困惑:

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

日志显示

[[1,2,3]]

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

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

为什么?

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

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

错误是

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

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

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

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

推荐答案

文档摘录:

来自官方文档，

返回二维值数组，

Returns a two-dimensional array of values,

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

It returns a two dimensional array of values. One dimensional array is

[1,2,3]

二维数组是

[[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包含3行，每行包含1列元素.这表示为[[1],[2],[3]].同样，以下范围表示以下数组:

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       | Number  | Number     | Array Structure     |
| Notation | of rows | of columns |                     |
|----------+---------+------------+---------------------|
| A1:A3    | 3       | 1          | [[1],[2],[3]]       |
| A1:C1    | 1       | 3          | [[1,2,3]]           |
| A1:B2    | 2       | 2          | [[1,2],[2,3]]       |
| B1:C3    | 3       | 2          | [[2,3],[3,4],[4,5]] |
| A2:C3    | 2       | 3          | [[2,3,4],[3,4,5]]   |
|----------+---------+------------+---------------------|

请注意二维如何提供方向.

Note how the two dimension provides direction.

根据单元格的值，值的类型可以为Number，Boolean，Date或String.

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

在上面的示例中，我们将电子表格数字类型元素转换为JavaScript数字类型.您可以使用 =TYPE() 检查电子表格的类型.相应的JavaScript类型引用在此处

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.

使用

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

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

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

鉴于二维数组结构，要访问一个值，需要两个格式为array[row][column]的索引.在上表中，如果检索到A2:C3，则要访问C3，请使用values[1][2]. [1]是范围A2:C3中的第二行. [2]是第三列.

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. [2]is third column.

• 从范围中检索的值始终是二维二维，无论范围的高度还是宽度(即使仅为1)也是如此. getRange("A1").getValues()将代表[[1]]
• setValues()将接受与要设置的range相对应的相同数组结构.如果尝试一维数组，则错误

• 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

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

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

被抛出.

• 如果数组与设置的范围不完全对应，即内部数组的每个长度与该范围内的列数不对应，或者外部数组的长度与行数不对应在所设置的范围内，将引发类似于以下的错误:

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

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.

• indexOf /包含使用严格的类型检查.当您将基元与数组对象进行比较时，它们将不起作用.您可以使用 Array.flat 将2D阵列展平为1D阵列.或者，使用普通的旧for循环来检查某些内容.
  • 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

  • 基本阅读
  • MDN阵列指南
  • Basic reading
  • MDN Arrays guide

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