Apps Script Course: Sheets and Cells

Now that you know what variables and arrays are and that you are capable of performing simple operations, we can start performing actions on the cells of a Google Sheets document.


Retrieve the Value of a Cell

Start by declaring a constant that will retrieve the active sheet:

const sheet = SpreadsheetApp.getActiveSheet();

Then a constant for cell A1 (of the sheet sheet):

const cell = sheet.getRange('A1');

And finally, use the getValue method to retrieve the value:

const value = cell.getValue();

To assist you, the editor displays the different methods of this object when you start writing it:

apps script object methods list cell values

To test it, enter a value in A1 and then try to retrieve it with the following code:

const sheet = SpreadsheetApp.getActiveSheet();
const cell = sheet.getRange('A1');
const value = cell.getValue();

console.log(value); // Displays the value of cell A1 of the active sheet

The value is then correctly displayed.

You can also enter the row and column number (instead of "A1") in the getRange method:

const sheet = SpreadsheetApp.getActiveSheet();
const cell = sheet.getRange(1, 1);
const value = cell.getValue();

console.log(value); // Displays the value of the cell at row 1 and column 1 of the active sheet

If you want to access the value of A1 from another sheet, enter:

const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2');
const cell = sheet.getRange(1, 1);
const value = cell.getValue();

console.log(value); // Displays the value of the cell at row 1 and column 1 of the sheet "Sheet2"

Retrieve Values of a Range of Cells

To retrieve an entire range of values (for example A1 to E2), it's very similar to the previous codes but here you must use getValues to retrieve multiple values (instead of getValue):

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A1:E2');
const array = range.getValues();

console.log(array);

For this example, the cells contain numbers:

cells cell values

In this case, console.log will display this result:

[ [ 1, 2, 3, 4, 5 ], [ 6, 7, 8, 9, 10 ] ]

Here are the same data displayed differently:

[
  [ 1, 2, 3, 4, 5 ],
  [ 6, 7, 8, 9, 10 ]
]

The getValues method returns an array, containing an array for each row of the range.

To display, for example, only row 2 (the values of the array at index 1), enter:

console.log(array[1]); // Displays: [ 6, 7, 8, 9, 10 ]

And since array[1] corresponds to the array of row 2, to display only the first value of this array, specify the value you want to obtain by adding [0]:

console.log(array[1][0]); // Displays: 6
It's a bit like a grid where you enter the row number in the first [] and the column number in the second [] (remembering that the first value is at 0).

In some cases, it may be more practical to "flatten" all this to obtain a single array with all the values, using the flat method:

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A1:E2');
const array = range.getValues().flat();

console.log(array); // Displays: [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ]