Skip to main content

Working with Data

Scalar Values​

Reading and Writing Values​

ICellRange.setValues and ICellRange.getValues is used to access values.

setValues takes a 2d array of data.

// set some values
workbook.getRange('Sheet1!A1:B2').setValues([[100, 200], [300, 400]]);

// get some values
workbook.getRange('Sheet1!A1:C3').getValues();

Clearing or Ignore Values​

The following conventions are used when setting or updating data

This convention allows users to update specific cells within a range without affecting others. If you want to update a subset of cells in a range, you can use undefined for the cells you want to leave untouched.

Clear using null​

  • When a value is set to null, the corresponding cell is cleared. This means that any existing content in the cell is removed, and the cell will appear empty after the operation.

Ignore using undefined​

When a value is set to undefined, the corresponding cell is ignored. This means its content remains unchanged. The cell’s previous value is left intact, and no updates are made to that cell.

Combined Example​

In this example:

  • 100 and 200 are set in the first row.
  • The first cell in the second row is ignored (undefined), leaving its previous value untouched.
  • The second cell in the second row is cleared (null), removing any existing value.
A1B2
1020
3040
range.setValues([[100, 200], [undefined, null]]);
A1B2
100200
30

Inserting/Removing Cells​

Inserting and removing cells or headers (rows/columns) is supported via the ICellRange interface using ICellRange.insert and ICellRange.delete.

Inserting/Removing Rows/Columns
// insert column
workbook.getRange('Sheet1!B:E').insert();

// remove row
workbook.getRange('Sheet1!2:2').delete();