arrow_back arrow_forward

7 Loops and Repetition

7.1 Why Loops Matter

When you’re working with Google Sheets, you rarely want to perform an action just once. More often, you want to repeat something many times:

• Check every value in a list
• Process each row in a dataset
• Build a new array from an existing one
• Apply the same rule to a whole column

Doing this manually—one step at a time—would be slow and error‑prone. Doing it in code without loops would be even worse, because you’d have to write the same line over and over again.

Loops solve this problem by letting your script repeat an action automatically. Instead of writing the same instruction dozens or hundreds of times, you write it once and let the loop handle the repetition.

7.1.1 Loops Let You Work at Scale

Imagine you have an array of 500 names. Without loops, you’d need 500 separate lines of code to process them. With loops, you can handle all 500 with a single, compact structure.

This is why loops are essential for spreadsheet automation: they let your script move through data the way your eyes move down a column.

7.1.2 Loops and Arrays Go Hand in Hand

Loops become especially powerful when combined with arrays. Arrays give you a collection of values; loops give you a way to visit each value in turn. Together, they form the backbone of almost every real‑world Apps Script project.

7.1.3 What You’ll Learn in This Chapter

In the sections ahead, you’ll explore:

• The basic structure of a loop
• How to repeat an action a specific number of times
• How to move through the items in an array
• How to stop a loop early or skip certain values
• How to apply loops to real spreadsheet tasks

By the end of this chapter, you’ll be able to write scripts that handle entire lists, columns, and datasets with ease. Loops are one of the biggest leaps in programming power you’ll make — and once you understand them, your automations will feel dramatically more capable.

7.2 The while Loop

A while loop is the simplest kind of loop in JavaScript. It repeats an action as long as a condition remains true. You can think of it as a way of saying:

“Keep doing this until something changes.”

This makes the while loop perfect for situations where you don’t know in advance how many times something needs to happen. Instead of repeating a task a fixed number of times, a while loop repeats based on a condition.

7.2.1 How a while Loop Works

A while loop has two parts:

1. A condition that JavaScript checks
2. A block of code that runs if the condition is true

The structure looks like this:

while (condition) {
  // code that runs repeatedly
}

As long as the condition evaluates to true, the code inside the braces keeps running.

7.2.2 7.2.2 A Simple Example

Here’s a small example that counts upward until a limit is reached:

function incrementShorthandNotation(){
  let x=0
  while(x < 5){
    x = x + 1
    console.log(x)
  }
  console.log("The loop is done.")
}

This loop logs the numbers 1 through 5. Once x becomes 5, the condition x < 5 becomes false, and the loop ends, allowing execution to proceed to the statement following the loop. Here’s the output:

7.2.3 Shorthand Increment Notation

Because incrementing a variable’s value by one is such a common operation, JavaScript has a shorthand notation to accomplish it. It’s called the increment operator. To use it, the statement is simply the variable name preceded by two plus signs: ++x .

Here is the prior example adjusted to the increment operator:

function incrementShorthandNotation_2(){
  let x=0
  while(x < 5){
    ++x
    console.log(x)
  }
  console.log("The loop is done.")
}

This does not change how the code executes, so the output will be the same as the prior example. However, the increment operator allows us to increase the value of the variable within a statement that uses the variable’s value. So, we can combine the statements that increment the value of the variable with the statement that logs its value to the console as follows:

function incrementShorthandNotation_2(){
  let x=0
  while(x < 5){
    console.log(++x)
  } 
  console.log("The loop is done.")
}

Again, this modification will not change the output of the function because the “++” increments the value before it has been used in the “console.log” statement. However, it is possible to use the increment operator to modify a variable’s value after it is used in the statement. To accomplish this, we just put the “++” after the variable name as follows: x++ . When we use the operator in this way, it is called “post-incrementing.” Of course, using the increment operator before a variable is called “pre-incrementing.” If we modify the prior example to post-increment the variable, it will change the output.

function incrementShorthandNotation_2(){
  let x=0
  while(x < 5){
    console.log(x++)
  }
  console.log("The loop is done.")
}

Here, we see that the value of x is changed after it is displayed, so the logged values are 0-4 instead of 1-5.

So far, we have not decreased a variable’s value. It should come as no surprise that it is very similar to increasing. Here’s a statement to reduce the value of x by one:

x = x - 1

and we can use a similar shorthand, decrement operator (

x--

or

--x

)in exactly the same way as the increment operator. The increment and decrement operators only increase or decrease a variable’s value by one. If you want to change it by a different value, you must use the longhand form.

7.2.4 Why the Condition Matters

A while loop continues until its condition becomes false. That means you must make sure something inside the loop eventually changes the condition. If not, the loop will run forever, which is called an infinite loop.

For example, this loop never ends:

while (true) {  
 // This will run forever  
}

And this one never ends because count never changes:

let count = 1


while (count <= 5) {
  console.log(count)   // count stays 1 forever
}

You’ll learn more about avoiding infinite loops later in the chapter, but the key idea is simple: the loop must move toward a stopping point.

Here’s a clean, focused example that does exactly what you asked:

• Read all the data from the sheet at once
• Store it in an array of arrays
• Use a while loop to move across that array
• Log the first value of each row
• Use let for all variable declarations

7.3 Example: Using a while Loop to Process data in a Sheet

Let’s begin by getting some data to work with. Run this code to build a set of data for this example to use.

Note: This code will replace any data you have on the active sheet of your spreadsheet, so be careful.

function writeDataToActiveSheet() {
  data= [
  ['First Name', 'Last Name', 'Birthdate', 'Death Date', 'Birthplace'],
  ['George', 'Washington', 'February 22, 1732', 'December 14, 1799', 'Westmoreland County, VA'],
  ['John', 'Adams', 'October 30, 1735', 'July 4, 1826', 'Quincy, MA'],
  ['Thomas', 'Jefferson', 'April 13, 1743', 'July 4, 1826', 'Albemarle County, VA'],
  ['Benjamin', 'Franklin', 'January 17, 1706', 'April 17, 1790', 'Boston, MA'],
  ['Alexander', 'Hamilton', 'January 11, 1755', 'July 12, 1804', 'Charlestown, Nevis'],
  ['James', 'Madison', 'March 16, 1751', 'June 28, 1836', 'Port Conway, VA'],
  ['John', 'Jay', 'December 12, 1745', 'May 17, 1829', 'New York City, NY'],
  ['Samuel', 'Adams', 'September 27, 1722', 'October 2, 1803', 'Boston, MA'],
  ['Patrick', 'Henry', 'May 29, 1736', 'June 6, 1799', 'Studley, VA'],
  ['George', 'Mason', 'December 11, 1725', 'October 7, 1792', 'Fairfax County, VA']
]
  const sheet = SpreadsheetApp.getActiveSheet()  
  const range = sheet.getRange(1, 1, data.length, data[0].length)
  range.setValues(data)


  // Set the number format to plain text 
  //(the "@" symbol signifies plain text)
  //this will make the data easier for you to read later on
  sheet.getRange("C2:D11").setNumberFormat("@")
}

Once you have activated a sheet with no data on it, execute the writeDataToActiveSheet function. This should give you a sheet that looks as follows:

With this data in place. We are ready to build an example to read the data from the sheet and log the first and last names. Let’s start with an empty function.

function logFirstValues() {  
}

Now let’s add the code to pull the information out of the Google Sheet and into the memory of our program.

function logFirstValues() {

  // get a reference to the active sheet of the spreadsheet
  let sheet = SpreadsheetApp.getActiveSheet()

   // Read all rows and columns in the data range
  let data = sheet.getDataRange().getValues()


  // Print all the data
  console.log('All Data:', data)
}

Now, instead of printing all of the data as an array of arrays, let’s print each row of data separately.

function logFirstValues() {

  let sheet = SpreadsheetApp.getActiveSheet()
  let data = sheet.getDataRange().getValues()

  // Print each row of the data
  console.log('row', 1, data[0])
  console.log('row', 2, data[1])
  console.log('row', 3, data[2])
  console.log('row', 4, data[3])
  console.log('row', 5, data[4])
  console.log('row', 6, data[5])
  console.log('row', 7, data[6])
  console.log('row', 8, data[7])
  console.log('row', 9, data[8])
  console.log('row', 10, data[9])
  console.log('row', 11, data[10])
}

With a table that only has one header and ten rows of data, this is not terrible, however, if you wanted to change this code to display the first name instead of the whole row, you would need to then change all 11 rows of code. Let’s use a loop to print the data instead.

The “while” loop requires a condition to control when the interpreter should exit the loop. For now, we’ll just use the boolean value “true” to control the loop. This will result in an endless loop; we’ll fix that later. For now, we are focusing on how to manipulate a variable to access a different part of the array with each iteration of the loop. To do this, we will create a variable, give it an initial value and change its value in the loop.

function logFirstValues() {

  let sheet = SpreadsheetApp.getActiveSheet()
  let data = sheet.getDataRange().getValues()

  // Print each row of the data
  let index = 0
  while(true){  
    console.log('row', index+1, data[index])
    index++             //  increase the value held in the index 
                  //  variable at the end of each iteration
  }
}

If you choose to run this example, the interpreter will print the data from the spreadsheet and then keep printing “row” followed by the increasing value of the “index” variable. For “data[index]”, it will display “undefined” because it is trying to access an element of the array that does not exist:

To stop the code from executing, just click the “Stop” button as seen here:

To make this loop end once it has displayed all data from the array, we’ll change the control condition from “true” to a condition that evaluates to “true” while accessing data and switches to “false” when there is not more data to access as seen here:

function logFirstValues() {

  let sheet = SpreadsheetApp.getActiveSheet()
  let data = sheet.getDataRange().getValues()

  // Print each row of the data
  let index = 0
  while(index < data.length){  
    console.log('row', index+1, data[index])
    index++                   
  }
}

Here is a partial output of from the code above:

On the first iteration of the loop, the variable named “index” holds a value of 0 and the “data.length” evaluates to 11. So, the condition evaluates to true because zero is less than 11. As “index” increases with each iteration, the control condition remains true as “index” equals 1, 2, and 3…all the way to when “index” equals 10. However, once the value in the “index” variable equals 11, the statement is 11<11, which is a false statement. Because the control condition has become false, the interpreter discontinues the loop and moves on to process any statements that follow the loop. In this case, there are no more statements to process, so the function ends.

Let’s add another parameter for our loop. The “index” will be used to keep track of what element of the array “data” the loop is on, and the “numberOfRows” variable the value of the number of elements in the “data” variable, which correlates to the number of rows in the Sheet. As you’ve just seen above, “numberOfRows = data.length” will be 11.

Now, if we want to change what is displayed, we need only modify one line of code. To make this code display just the last name and the death date, we can modify the code as follow:

function logFirstValues() {

  let sheet = SpreadsheetApp.getActiveSheet()
  let data = sheet.getDataRange().getValues()

  // Print each row of the data
  let index = 0
  while(index < data.length){  
    console.log('row', index+1, data[index][1], data[index][3])
    index++                   
  }
}

Now, instead of printing the whole array that represents each row, we are isolating just two values of the array, the last name and the death date, as seen here:

To conclude this example, let’s introduce one change to make the code more readable. In the expression “data[index][1]” we are identifying an element of an array that is itself an element of
an array. To improve readability, let’s add a variable at the beginning of the loop to hold one row’s data, then use that variable to access individual columns from the row as seen here:

function logFirstValues() {

  let sheet = SpreadsheetApp.getActiveSheet()
  let data = sheet.getDataRange().getValues()

  // Print each row of the data
  let index = 0
  while(index < data.length){  
    let values = data[index]
    console.log('row', index+1, values[1], values[3])
    index++                   
  }
}

Here, the variable named “values” holds the array of values from one row at a time, so the expression “values[0]” refers to the first name of any given row, and “values[1]” refers to the last name, etc.

The While loop relies on a single condition to control how many times it executes its code: It examines the condition just before it begins each iteration. If the condition is true, it executes the code in the loop. To use this kind of loop to effectively process each value in an array, we need three bits of code:

• A variable to keep track of which element of the array are examining in each iteration of loop:

  let index = 0

• A statement to increment the value in the variable each time the code in the loop executes:

  index++

• The condition that evaluates to True as long as we have data to process and switches to False when we are out of data:

  index < data.length

Although there are many other approaches to designing a while loop to accomplish different tasks, this one is so common that there is another kind of loop in JavaScript that is specifically designed to work with these three control elements. It’s called the “For” loop

7.4 The for Loop

Let’s begin our discussion of the For loop by examining a simplified version of our most recent example of the While loop:

function logFirstValues() {

  let data = ["George", "John", "Thomas", "James", "Samuel"]

  let index = 0

  while(index < data.length){  
    console.log(index,data[index])
    index++                   
  }

}

This loop iterates over the values in the “data” array and logs the following:

Let’s review and name the three control elements that manage the execution of the loop:

1. The Initialization Statement:

   let index = 0

   This statement executes only once in this example because it precedes the loop entirely, so it is not a part of the loop.

2. The Control Condition:

   index < data.length

   This condition is evaluated before the first execution of the code in the loop’s body and is evaluated again prior to each subsequent execution of the loop’s body to determine if it is time to exit the loop

3. The Increment Statement:

   index++

   This statement is inside the body of the loop, so it will run for each iteration of the loop.

Let’s rewrite the example using the For loop with the minimum number of changes to get it working, Then we’ll adjust it to the standard format in which a For loop is written.

function logFirstValues() {

  let data = ["George", "John", "Thomas", "James", "Samuel"]

  let index = 0

  for(      ; index < data.length;      ){  
    console.log(index,data[index])
    index++                   
  }

}

All we have done here is change the keyword “while” to the keyword “for” and put semicolons before and after the control condition. This code executes exactly the same as the prior example.

Because the For loop requires all three of the control structures in this example, it has a place built into its syntax to accept them. The added semicolons delineate the three control elements for the For loop as follows: initialization statement; control condition; increment statement , as seen here:

Here is the resulting For loop written in the standard format:

function logFirstValues() {

  let data = ["George", "John", "Thomas", "James", "Samuel"]

  for(let index = 0; index < data.length; index++){  
    console.log(index,data[index])                  
  }
}

The important point to recognize here is that making this change does not change the order of execution. Each of the three preceding examples executes exactly the same because they are logically equivalent.

A reasonable student might ask, “if they are exactly the same, why do we need them both?” The answer is simple. All computer programming languages strike a balance between the complexity of the language itself and the simplicity of the programs that can be written using the language. In this case, the designers of JavaScript felt that because this kind of control structure was so common that it was worth adding a type of loop that expressly requires these three control elements and places them where they are reality accessible to a human reader, making the code easier to understand.

In this example of the For loop, we are beginning a variable at specified value (0) and adding a value to it (1) with each iteration of the loop. As we begin each iteration of the loop we check the control condition and only continue the loop if it evaluates to True. This is the most common use of the for loop, to move an integer across a specified range. However, the control structures need not deal only with integers, or numbers. Here’s an example that

The for loop is one of the most common and useful loop types in JavaScript. Unlike a while loop—which repeats until a condition changes—a for loop is ideal when you know exactly how many times you want something to repeat. This makes it perfect for working with arrays, because arrays always know their own length.

A for loop gives you a clean, predictable way to move through each item in an array, one step at a time.

7.4.1 How a for Loop Works

A for loop has three parts:

function forLoopOfStrings() {

  for (start; condition; step) {
    // code that runs each time
  }
}

• start — where the loop begins
• condition — how long the loop should keep going
• step — what happens after each repetition

A simple example:

function forLoopOfStrings() {

  for (let index = 0; index  < 5; index++) {
    console.log(index)
  }
}

This logs the numbers 0 through 4.

This loop simplifies the code in that it places the three parts of the loop’s control mechanism together where they are readily accessible to a human reader. However, it makes the code a bit more confusing for beginning programmers because the order in which these statements execute i

In this example, notice that the “start” statement ( let index = 0;) is executed only once: when the interpreter begins the loop.

7.4.2 Example: Using a For loop to access data in an array

Let’s modify this loop to display elements from an array of string values.

function forLoopOfStrings() {
  
  let data = ["George", "Abigail", "Thomas", "Martha", "Penelope"]
  
  for (let index = 0; index < 5; index++) {
    console.log(index, data[index])
  }
}

In this example we have added an array called “data” and we are using the loop to log each of the values.

7.4.3 Example: Accessing Sheet Data with a for Loop

Let’s write a For loop that logs the first and last name of each person in the dataset we used for our While Loop example.

function logNames() {
  let sheet = SpreadsheetApp.getActiveSheet()
  let data = sheet.getDataRange().getValues()

  // Start at index 1 to skip the header row
  for (let rowIndex = 1; i < data.length; rowIndex++) {
    let row = data[rowIndex]     // the current row (an array)
    let first = row[0]           // first name
    let last = row[1]            // last name

    console.log(first + " " + last)
  }
}

7.4.4 How This Works

• rowIndex = 1 Starting at 1 instead of 0 skips the header row
• rowIndex < data.length ensures we stop after the last row
• rowIndex++ moves to the next row each time
• row becomes the current inner array
• row[0] and row[1] give us the first and last names

This pattern—looping through an array and working with each row—is one of the most common tasks for Apps Script in Google Sheets.