API to sheet
UrlFetchApp returns JSON. Map objects to rows, then setValues starting at A2 on the Import tab.
What is setValues in Google Sheets Apps Script?
setValues writes a two-dimensional array into a rectangular range in one operation. Each inner array is a row. It is the fastest way to paste a block of results, copy transformed data, or fill a table from an API response without updating cells one by one.
When to use it
Use setValues when you have a full block ready: dump API results into A2:E, write filtered rows to an Output tab, refresh a staging table nightly, or batch-normalize a column after getValues processing.
When to skip it
Skip setValues for a single cell change or when you must preserve unrelated formulas inside the target rectangle. setValues overwrites every cell in the range.
How it works
-
1
Build a 2D array in JavaScript with consistent row lengths.
-
2
Select a range with getRange(startRow, startCol, numRows, numCols) or A1 notation of the same size.
-
3
Call range.setValues(twoDArray). Mismatched dimensions throw an error.
-
4
Prefer one setValues over the data area instead of many setValue calls in loops.
-
5
Clear the target first with clearContent if old rows may be shorter than the new data.
-
6
Combine with getValues on source data, map in memory, then setValues on the destination.
Examples in Google Sheets
Filtered export
getValues from Data, filter rows in code, setValues on Export with only matching records.
Template fill
Build ten rows of placeholder labels in a 2D array and setValues on B5:F14 for a monthly report skeleton.
Build this without starting from a blank cell
Use a Better Sheets tool for setValues, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Array column count does not match range width, causing Exception dimensions do not match.
- Jagged arrays where some rows have fewer columns than others.
- Overwriting header row because start row was 1 instead of 2.
- Forgetting empty strings for blank cells; undefined values can cause errors.
- setValues on a range that includes cells with formulas users need to keep.
Frequently asked questions
- Must the array match the range exactly?
- Yes. Row count and column count must equal the range dimensions or Apps Script throws an error.
- Can I setValues on one column?
- Yes. Use a 2D array where each row has one element, like [[a],[b],[c]], on a single-column range.
- How do I write starting at row 2?
- Use getRange(2, 1, data.length, data[0].length) or A2 notation sized to your array.
- Does setValues preserve formatting?
- It updates values. Existing number formats often remain unless you change them separately.
- Can I append with setValues?
- You can target the row after getLastRow, but appendRow is simpler for one new row. setValues fits multi-column blocks.
- What about setDisplayValues?
- setDisplayValues writes what users would see as text. Rare for automations; prefer setValues for real data types.
- Will setValues trigger onEdit?
- Script writes can fire onEdit when users have the sheet open. Design handlers to ignore script-driven columns.
- How do I handle large writes?
- Chunk into smaller ranges if you approach execution time limits, or write to a staging tab and swap tabs.
Related Tutorials
Watch how setValues works
Iterate Numbers with a Simple Apps Script
How do I reference a different spreadsheet in Apps Script?
Related glossary terms
Apps Script loops
Loops in Apps Script let you repeat code over rows, columns, sheets, or API pages. You read ranges into arrays with getValues(), process each item in a for or forEach loop, then write results back in one setValues() call when possible. Batch reads and writes beat cell-by-cell updates for speed and quota limits.
Read guide →Google Sheets automation
Automation means work happens without repeating the same clicks every day: imports update, emails send, rows move, and dashboards refresh. In Sheets, automation usually stacks built-in features, Apps Script, and sometimes the Sheets API or add-ons. Start with the lightest tool that still solves the job.
Read guide →Script Editor
The Script Editor is the Apps Script workspace inside your spreadsheet where you write JavaScript that talks to Sheets, Gmail, and other Google services. You open it from Extensions > Apps Script. Every function you save can be run manually, bound to a trigger, or linked from a custom menu.
Read guide →Google Sheets workflows
A workflow is the path work takes through your sheet: intake, review, approval, done. Good workflows use consistent columns, statuses everyone understands, and just enough automation to move tasks forward without hiding steps from the team.
Read guide →Done reading about setValues?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.