Filter overdue tasks
getValues on the Tasks range, loop rows where due date is before today, and collect assignee emails into an array for MailApp.
What is getValues in Google Sheets Apps Script?
getValues reads every cell in a range into a two-dimensional JavaScript array. The outer array is rows and the inner arrays are columns. It is the standard way to pull a table from a sheet, loop through it, filter rows, and write results elsewhere in one efficient pass.
When to use it
Use getValues when you process batches: scan an entire data tab, find rows matching a condition, build email lists, or transform values before one setValues write back.
When to skip it
Skip getValues when you only need one cell, or when the range is enormous and you should filter on the server with QUERY or read a named subset. Reading millions of cells can time out.
How it works
-
1
Define a range with getRange('A2:E') or getDataRange() on the sheet.
-
2
Call range.getValues() to get a 2D array like [[a1,b1],[a2,b2]].
-
3
Loop with for loops or array methods. Row index 0 is the first row in the range, not row 1 on the sheet.
-
4
Adjust column indexes when your range does not start at column A.
-
5
After processing, write back with setValues on the target range of the same dimensions.
-
6
Use getDisplayValues if you need formatted text instead of raw values.
Examples in Google Sheets
Normalize phone numbers
Read column C with getValues, strip non-digits in JavaScript, and setValues back to the same range in one write.
Build JSON for an API
Map each row to an object using header names from row 1, then JSON.stringify the array for UrlFetchApp.
Build this without starting from a blank cell
Use a Better Sheets tool for getValues, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Using getValue inside a double loop over the same data getValues already returned.
- Off-by-one errors when the range starts at row 2 but code assumes sheet row numbers match array indexes.
- Writing setValues with an array shape that does not match the target range row and column count.
- Calling getDataRange on sheets with stray formatted cells far below the data, pulling thousands of blank rows.
- Mutating the array and forgetting setValues only changes the sheet when you explicitly write.
Frequently asked questions
- What shape does getValues return?
- A 2D array. One row with five columns is [[v1,v2,v3,v4,v5]]. One column with three rows is [[v1],[v2],[v3]].
- How do I skip the header row?
- Start your range at row 2, or read from row 1 and loop from index 1 instead of 0.
- Is getValues faster than many getValue calls?
- Yes. One read per range is much faster than cell-by-cell access in loops.
- What about getDisplayValues?
- Same shape, but each cell is the displayed string. Use it when formatting matters for export or matching.
- Can getValues read multiple non-adjacent columns?
- Not in one call. Read one contiguous range, or make multiple getValues calls and merge in code.
- How do I find blank rows?
- Check if key columns are empty strings in your loop, or filter in Sheets first with QUERY into a smaller range.
- Do formulas appear in getValues?
- No. You get calculated results. Use getFormulas for formula text.
- Why does setValues fail after getValues?
- The output array must match the target range size exactly. Log array.length and row[0].length against the range dimensions.
Related Tutorials
Watch how getValues works
Iterate Numbers with a Simple Apps Script
Spreadsheet Automation 101 Lesson 2: Get Values - Introduction
Scrape Google Maps
Tips to Navigating Thousands of Lines of Code In Apps Script
Automate Google Sheets With Zero Experience
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 →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 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 →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 getValues?
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.