How do loops work in Google Sheets Apps Script?
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.
When to use it
Use loops when you need to scan many rows, clean data, build summaries, or call an API for each record. Pair loops with arrays from getValues() for reports, deduplication, and row-by-row rules that formulas cannot express cleanly.
When to skip it
Skip loops when a single QUERY, MAP, or ARRAYFORMULA can do the job in the sheet without script. Avoid looping every cell on large tabs without batching; execution time and UrlFetch quotas add up fast.
How it works
-
1
Open Extensions > Apps Script and define a function that gets the target range with getRange().
-
2
Call getValues() once to load data into a two-dimensional JavaScript array.
-
3
Use for (let i = 0; i < rows.length; i++) or rows.forEach() to read each row and build an output array.
-
4
{:"Apply your logic inside the loop"=>"trim text, compute totals, skip blanks, or collect matches."}
-
5
Write the output array back with setValues() in one range update instead of many setValue() calls.
-
6
Test on a small sample range first, then add Logger.log counts and guard rails for empty data.
Examples in Google Sheets
Flag overdue rows
Loop column B dates against today in a script function. Write "Overdue" to column E only when the date is past and status in column D is not "Paid".
Deduplicate by email
Read column A into an array, track seen emails in a Set, and write "Duplicate" in column B for second and later occurrences.
Paginate an API
Use a while loop with a page token from UrlFetchApp until no next page remains, pushing each record into a master array before flushing to the sheet.
Better Sheets resources
Common mistakes
-
Calling getValue() or setValue() inside a tight loop on thousands of rows, which is slow and brittle.
-
Forgetting that getValues() row and column indexes start at 0 while sheet rows start at 1.
-
Infinite while loops when a break condition never becomes true after an API change.
-
Mutating the sheet range you are iterating without recalculating bounds, causing skipped rows.
-
Running long loops from onEdit instead of a menu action or time-driven trigger.
Frequently asked questions
- for vs forEach in Apps Script?
- Both work on arrays from getValues(). Classic for loops give you the index; forEach is readable when you do not need the row number for writing back.
- Can I loop all sheets in a file?
- Yes. Use SpreadsheetApp.getActive().getSheets() and loop each sheet name or tab object.
- How do I avoid the six-minute timeout?
- Process in chunks, store the last processed row in PropertiesService, and resume on the next run.
- Should loops replace QUERY?
- Prefer QUERY or formulas when the logic is tabular and stable. Use loops for custom steps formulas cannot do.
- Can loops call external APIs?
- Yes with UrlFetchApp, but respect daily quotas and add delays or batch endpoints when available.
- Do loops work with onEdit?
- They can, but keep onEdit loops tiny. Heavy loops belong in manual functions or scheduled triggers.
- How do I debug a loop?
- Logger.log the index and a sample row inside the loop, then check Executions after a test run.
- Can I break out of a loop early?
- Use break in a for loop or return from the function when you found what you need.
Related Tutorials
Watch how Apps Script loops works
10 Google Sheets I Wish Someone Would Make
Iterate Numbers with a Simple Apps Script
Curate Google Sheets Easily - Automatic Bookmarklet Maker
Now Do It Every Damn Day - Learn to Code in Google Sheets Part 5
From Sheet to Script And Back Again - Learn to Code In Google Sheets Part 3
Related blog posts
Guides that explain Apps Script loops in more depth.
Explore Coding in Google Sheets
Did you know you can actually code in Google Sheets?
Read post →What's Better Than Google Sheets?
Nothing is better than Google Sheets. You just need to learn more about the unlimited power of Google Sheets. Functions, Formulas, Apps Script and more.
Read post →Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users
If you know how to code, you'll learn in this step-by-step tutorial how to code in Google Sheets.
Read post →Related terms
onEdit trigger
The onEdit trigger runs a script automatically when someone changes a cell in your spreadsheet. It is one of the simplest ways to make Sheets react to user input without clicking a custom menu. You define the function in Apps Script and bind it to the onEdit event for that file.
Read guide →Apps Script triggers
Triggers tell Apps Script when to run a function: on edit, on open, on a schedule, or when a form is submitted. Simple triggers use reserved names like onEdit. Installable triggers are created in the Apps Script Triggers page and can do more, including running as a specific user or on a timer.
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 →Done reading about Apps Script loops?
Membership unlocks 600+ 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.