How do loops work in Google Sheets Apps Script?

How it works

1. 1

   Open Extensions > Apps Script and define a function that gets the target range with getRange().

2. 2

   Call getValues() once to load data into a two-dimensional JavaScript array.

3. 3

   Use for (let i = 0; i < rows.length; i++) or rows.forEach() to read each row and build an output array.

4. 4

   {:"Apply your logic inside the loop"=>"trim text, compute totals, skip blanks, or collect matches."}

5. 5

   Write the output array back with setValues() in one range update instead of many setValue() calls.

6. 6

   Test on a small sample range first, then add Logger.log counts and guard rails for empty data.

Examples in Google Sheets

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 glossary terms