For Loop in Google App Script

In this tutorial, we’re going to look at the For loop and how to use it in Google App Script.

Sometimes we want to repeat an action a number of times.

For example, let’s imagine we want to print “Hello!” ten times down a column.

The poor way of doing that is to use the .setvalue() method for each of the rows, changing each time the row reference in the .getRange() method, as you can see in the code below:

function printHello() {
	const ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
	ss.getRange(1, 1).setValue("Hello!");
	ss.getRange(2, 1).setValue("Hello!");
	ss.getRange(3, 1).setValue("Hello!");
	ss.getRange(4, 1).setValue("Hello!");
	ss.getRange(5, 1).setValue("Hello!");
	ss.getRange(6, 1).setValue("Hello!");
	ss.getRange(7, 1).setValue("Hello!");
	ss.getRange(8, 1).setValue("Hello!");
	ss.getRange(9, 1).setValue("Hello!");
	ss.getRange(10, 1).setValue("Hello!");
}

Imagine if we wanted to write this 100 times, that’s a lot of code!

There is a better way to achieve the same result with just a few lines of code using a “for loop”, as you can see in the code below:

function printHello() {
	const ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
	for (i = 1; i <= 10; i++) {
		ss.getRange(r, 1).setValue("Hello!");
	}
}

So, Let's see the for loop syntax and how its works.

For loop syntax #

The for loop starts with the word for and includes three optional statements, separated by semicolon, and wrapped in parentheses.

These three statements are followed by a set of open and close curly brackets that contain the code block to be looped.

A For loop has four steps:

1. Initialize the counter variable.

2. Check if the specified condition is met.

3. If the condition is met, run the statement inside the loop (within the curly brackets).

4. Update (Increment/decrement) the counter each time an iteration is completed and proceed to the next iteration.

It's kind of like saying “start here” (initialize), “stop here” (condition) and “do this after each completed loop” (run statement and update the counter).

For loop flow #

Here the flow of execution of the for Loop:

For loop example #

Let’s go back to the code above.

function printHello() {
	const ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
	for (i = 1; i <= 10; i++) {
		ss.getRange(i, 1).setValue("Hello!");
	}
}

• I am using the variable i as loop counter.

• I am starting i from 1 and incrementing its value after each iteration by 1.

• I am telling App Script to run this loop while i is less or egual 10.

• This loop will run 10 times and will stop as soon as i turns to 11

The table below shows all the interactions of the for loop above: