Reading and writing sheets Updated November 2026

setValue and setValues

range.setValue(x) writes one value to the top-left cell; range.setValues(array2D) writes an array of rows, and the array must have exactly the same number of rows and columns as the range. Build the array in memory, then write it in one call: sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows).

Syntax

range.setValue(value) | range.setValues(values2D)

Part	What it is
`value`	Number, string, boolean or Date; a string starting with = is written as a formula
`values2D`	Array of rows; every row must have the same length and match the range

Examples

const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue("Done");
sheet.getRange("B1").setValue(42);

Result: A1 reads Done, B1 holds the number 42

const rows = [["Pens", 12], ["Paper", 5], ["Clips", 300]];
sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);

Result: A2:B4 filled with the three rows in one write

const data = sheet.getRange("A2:C10").getValues();
const out = data.map(r => [r[0], r[1], r[1] * 1.1]);
sheet.getRange("A2:C10").setValues(out);

Result: Column C becomes column B plus 10 percent for every row

Read, transform, write: the standard pattern.

sheet.getRange("D2").setValue("=B2*C2");

Result: D2 shows a formula result; setFormula does the same more explicitly

Common mistakes

The number of columns in the data does not match the number of columns in the range: the range is A2:C10 but rows have 2 or 4 items, so size the range from the array (rows.length, rows[0].length).
The number of rows in the data does not match: a trailing empty row or a header was added; check rows.length before writing.
setValues(['a', 'b']) throws because it is a 1D array: wrap it as [['a', 'b']] for one row or map each value to [v] for one column.
Writing a Date as a string stores text, not a date: pass a Date object or use setValue(new Date(...)) so the cell stays sortable.

Practice setValue and setValues

These drills check your answer on live data, free, no account needed.

Full namesEach user looks like `{ first, last }`. Return an array of full names: first and last joined by one space. Some people have no last name (an empty string, or no `last` field at all); for them return just the first name, with no trailing space.
Split into chunksSplit `arr` into arrays of `size` items each. The last chunk holds whatever is left, so it can be shorter. `size` is at least 1. Do not change `arr`.
Zip two arraysPair up the items of `a` and `b` by position: `[[a[0], b[0]], [a[1], b[1]], ...]`. Stop at the end of the shorter array. Items can be any value, including `null`, `0` and `false`.

Questions

How do I write a single column?

Make each value its own row: const col = list.map(v => [v]); sheet.getRange(2, 1, col.length, 1).setValues(col).

Does setValues overwrite formulas?

Yes, it replaces whatever is in those cells. Read getFormulas first if you want to keep some, or write only the columns you change.

Why do my changes not appear until the script ends?

Writes are batched for speed. Call SpreadsheetApp.flush() when you need them applied immediately, for example before reading a formula result.

getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.
appendRowAdd a row to a Google Sheet with Apps Script: appendRow for a single row after the last row with data, and setValues for many rows at once, with examples.
Clear and format cellsClear and format Google Sheets cells with Apps Script: clear content or formats, set background and font color, bold, number and date formats, borders.
Performance and batchingMake Google Apps Script faster: read and write in one getValues and setValues call, avoid getRange in loops, use flush and caching, beat the 6 minute limit.