What does the range method getValues() return and setValues() accept?

Question

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?

Answer 1

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. Try to guess the array structure based on the A1 notation:

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