我想从工作表中获取一个范围.按照最佳做法中的建议,我正在尝试获取数组并对其进行操作,但我感到困惑:
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
- 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
更多推荐
范围方法getValues()返回和setValues()接受什么?
发布评论