Apps Script Basics – Loops

In this post, we’re going to look at loops and how they can make repetitive tasks really easy and how they can save you so much coding. One of the things computers are good at is doing repetitive tasks quickly and accurately.

For example, if we want to print the word Hello! a number of times down a column without a loop, we would have to repeat the same line of code for each of the rows, as you can see in the code below:

1.	function printHello() {
2.	  const ss = SpreadsheetApp.getActiveSpreadsheet()
3.	                           .getSheetByName("Numbers");
4.	  ss.getRange(1,1).setValue("Hello!"); 
5.	  ss.getRange(2,1).setValue("Hello!"); 
6.	  ss.getRange(3,1).setValue("Hello!"); 
7.	  ss.getRange(4,1).setValue("Hello!"); 
8.	  ss.getRange(5,1).setValue("Hello!"); 
9.	  ss.getRange(6,1).setValue("Hello!"); 
10.	  ss.getRange(7,1).setValue("Hello!");  //etc
11.	
12.	}

The only thing changing each time is the row reference in the getRange() method. Imagine if we wanted to write this 100 times, that’s a lot of code! With a loop, as we’ll see in the examples below, we can simplify this to just 1 loop and 3 lines of code. A loop just allows you to repeat a section of code as many times as you like.

Below, we’re going to look at seven simple examples of how loops can be used. In the spreadsheet linked to this chapter, there are 2 sheets, one called Numbers and the other called Names.

Loop 1 – Print “Hello!” 20 times down column A

1.	//Print Hello!" 20 times down column A
2.	function loop1() {
3.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Numbers");

Open the script editor in your Google Sheet and replace the default code with the above one.

Line 1: We can write comments in our code to help us remember what it does. To make the computer ignore them, we use two back slashes // at the start. In the script editor, you’ll see the text is in grey, to show it’s a comment and will be ignore when the program is run.

Line 2: We start with the function and here let’s just call it loop1.

Line 3: Now, we want to get the sheet called Numbers. So, we use the SpreadsheetApp class, then get the active spreadsheet, then get the particular sheet we want, in this case called “Numbers”. For that we use the getSheetByName() method and add the name between quotes in the brackets. I’ve stored it in the variable ss, as we’re going to refer to the sheet again in the next part, so instead of writing out the SpreadsheetApp… part again, we can just use the variable name ss.

Now we want to set up a loop, which will add the word Hello! to cell A1, cell A2, and so on, down to cell A20. Here, we’re going to use the common for loop.

for loop

There are two main parts: the counter, in this case counting from 1 to 20, and what you want the loop to do each time it goes around.

for (counter) {

  Stuff you want to happen every time around the loop

}

5.	  for (let r = 1; r < 21; r++) {
6.	   ss.getRange(r, 1).setValue("Hello!"); 
7.	  }
8.	}

Line 5: Start with the keyword for then open the brackets. There are 3 parts within the brackets:

1. Starting point of the loop.
2. Condition you want to check; if it’s true it continues the loop, if it’s false it stops the loop and continues on with the program.
3. How much you want to increase the counter by, each time it goes around the loop.

The counter needs something to keep count, so we use a variable to do that. A variable is a pot to store something, like a number, but it can change during the script.

So, here we have the variable r (for rows) and we’re starting at 1, as we want to start at row 1.

Then, we want to continue until 20, so the condition will be to continue while the variable r is less than 21.

As we want to put the name in all the rows, we want to increase r by 1 each time, so that it will print Hello on row 1, row 2, row 3, etc. Here I’ve used the common shorthand way to increase a value by 1, i.e. using a double + after the variable (r++).

As we want to put the name in all the rows, we want to increase r by 1 each time, so that it will print Hello! on row 1, row 2, row 3, etc. Here, I’ve used the common shorthand way to increase a value by 1, i.e. using a double + after the variable (r++). This is the same as saying r+=1.

All of this is contained in parenthesis and this is what controls the counting within the loop.

What happens in each loop?

Then, we need to tell it what to do, during each loop. To do this, we use the curly brackets, so we open the curly bracket ready to put some instructions in it.

5.	  for (let r = 1; r < 21; r++) {
6.	   ss.getRange(r, 1).setValue("Hello!"); 
7.	  }
8.	}

Line 6: Now, we want to get the range and set its value to the word Hello!. So, first we get the sheet we want using the variable ss, then use the getRange() method. Here, we’re going to use the getRange() method, that needs a row and column, to determine which cell it is.

In the brackets, we have two arguments: (row number, column number). The column will always be the same, so we write 1, for the first column, A. But the row we want to change each time we go around the loop, so we use the variable r from our for loop. Then we set the value of that cell with the word Hello!.

Line 7: We close the for loop with a closed curly bracket.

Line 8: We close the function in the same way, with a closed curly bracket. So, you can see that the function does everything within its curly brackets, and the for loop does everything within its curly brackets.

When we run the code, the first time around the loop, r will be 1, so the range it will get will be row 1, column 1 (i.e. cell A1). Then, it will add Hello! in that cell. The counter r will go up by one, then the second time it goes around the loop r will be 2, so it will get row 2, column 1 (i.e. cell A2), and so on, until it reaches row 20. Then the condition will be false, as r will be 21 and isn’t less than 21, so it will continue down the program.

Let’s run the code and see what happens. As I’ve already set up the 7 different loops in this project, I need to select the function loop1 to run it. If you’re using the my file, you may need to select the loop1 function.

So, from the toolbar to the right of where it says debug, if it doesn’t say loop1, “click on the menu to select the loop1 function.

Then click the Run button to run the code.

The first time you run it you’ll have to authorize the script as explained at the end of the post on variables.

As we can see it’s written Hello! in cells A1 to A20, just as we wanted.

Loop 2 – Print numbers 1 to 20 down column A

This time let’s use the numbers in the loop and add them to the sheet, i.e. write numbers 1 to 20 in the cells in column A.

10.	//Print numbers 1 to 20 down column A
11.	function loop2() {
12.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Numbers");

Line 11 to 12: Same as loop 1, except call it loop 2.

14.	  for (let r = 1; r < 21; r++) {
15.	   ss.getRange(r, 1).setValue(r); 
16.	  }
17.	}

Line 15: This time, we want to set the value of the cells to be the current number in the loop. So, we get the range as before, and this time set the value to be the variable r. So, the first time around the loop, r will be 1, so it will put 1 in row 1. the second time it will be 2, so it will be 2, etc.

Lines 16-17: We close the loop and function as before.

Select loop 2 from the toolbar and run the program.

As we can see it added the numbers 1 to 20 to column A.

19.	//Fill cells A1 to A20 in blue
20.	function loop3() {
21.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Numbers");

Loop 3 – Fill cells A1 to A20 in blue

It’s not just values that we can use loops with. Let’s add some colour to our sheet.

Lines 20-21: As before.

23.	  for (let r = 1; r < 21; r++) {
24.	   ss.getRange(r, 1).setBackground("blue");
25.	  }
26.	}

Line 23: As before.

Line 24: This gets the range as before, but this time set the background colour to blue, using the setBackground() method.

Select loop3 from the toolbar and run the program. As we can see it’s filled the cells in blue.

Loop 4 – Fill cells A1 to A20 in blue and print numbers 1 to 20

We can carry out more than one instruction within our loop. Let’s fill the cells with blue and add the numbers 1 to 20.

28.	//Fill cells A1 to A20 in blue and print numbers 1 to 20
29.	function loop4() {
30.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Numbers");

Lines 29-30: As before.

32.	  for (let r = 1; r < 21; r++) {
33.	   ss.getRange(r, 1).setBackground("blue")
34.	                    .setValue(r);
35.	  }
36.	}

Line 32: As before.

Line 33: Similar to loop3, we set the background colour, but this time, don’t add a semi-colon at the end of the line. This allows us to add other instructions to the same range.

Line 34: Type dot and the method you want, in this case, setValue(). Here, we’re going to add the variable r, i.e. the current number in the loop, to the cell. This time, add a semi-colon at the end.

Note, the setBackground() and setValue() methods are both linked to the getRange() method, as we didn’t use a semi-colon after the setBackground() one.

Select loop4 from the toolbar and run the program. As we can see, it added both the colour and the numbers.

Loop 5 – Print the numbers 1 to 20 across 10 different columns

38.	//Print 10 columns of numbers 1 to 20
39.	function loop5() {
40.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Numbers");

So far, we’ve just added values and colours down the same column but we can of course move across the columns too. Here, we’re going to look at how two for loops can be used together, to add numbers down the columns and to add them to different columns across the page.

Lines 39-40: As above.

42.	  for (let c = 1; c < 11; c++) {
43.	    
44.	   for (let r = 1; r < 21; r++) {
45.	    ss.getRange(r, c).setValue(r);
46.	   }
47.	  }
48.	}

Lines 44-46: This is pretty much the same loop from loop2, i.e. it will add numbers from 1 to 20 down a column. The only exception is that instead of keeping the column value as a variable 1, I’ve added the variable c (for column), which will change as the loop goes around.

Lines 42 and 47: Wrapped around the above loop, we have another loop which will change the column number from 1 to 10, or in other words from column A to J. Here, I’ve called the variable c and it starts at column 1, then increase by 1 until it reaches the tenth column (i.e. c less than 11).

Running the loop5 script, we can see that it added the numbers 1 to 20 to column A, then to column B, and so on until column J (the tenth column).

So, how does this work exactly?

42.	  for (let c = 1; c < 11; c++) {
43.	    
44.	   for (let r = 1; r < 21; r++) {
45.	    ss.getRange(r, c).setValue(r);
46.	   }
47.	  }
48.	}

At the start, c is 1 and r is 1, so it gets the cell A1, and adds the number 1 (r) in the cell. It then gets trapped in the r loop, and goes around that one a second time. So, this time c is still 1, but r is now 2, so it adds 2 (r) to cell A2. It continues around the r loop until it hits 20, then it goes down a line and hits the end curly bracket of the c loop (line 47).

It then goes back to line 42 and goes around again. This time c is now 2, and the r loop is reset back to 1. It goes around the r loop like before until it hits 20, then it goes back to the c loop, which will now be 3, and so on, until the c loop hits 10. At which point it finishes.

Loops within loops can be complicated to follow and if I’m struggling to follow what’s happening, I usually look at it step by step and see what’s happening to the variables each time they go around the loop.

Loop 6 – Create 5 documents and name them Document1 to 5

50.	//Create 5 documents naming them Document1 to Document5
51.	function loop6() {

Now we’ve covered the real basics, let’s see what else you can do with a loop. Adding numbers and colours to a sheet is fine, but you can do so much more with them. Here, let’s create some Google Docs and number them individually from our loop.

Line 51: Open the function.

53.	  for (let r = 1; r < 6; r++) {
54.	   DocumentApp.create("Document"+r);
55.	  }
56.	}

Line 53: I want to create 5 documents, so let’s set the condition to less than 6.

Line 54: Every time it loops, we want it to create a document and name it “Document” plus the current number from the variable r, i.e. Document1, Document2, etc. To create a new Google Doc we use the DocumentApp class and the create() method. In the brackets, we state the name we want to give the document. So, it will be “Document” plus the number from r.

Lines 55-56: We close the loop and function as before.

Running the loop6 script, we will see in our My Drive, 5 new documents, all titled individually, 1 to 5.

Loop 7 – Create 4 documents each with names from the sheet

It’s not just numbers we can add to our documents, but we can also get text from our sheet and name the documents accordingly. Here, we’re going to create documents for 4 students and append each document title with their name.

58.	//Create 4 documents each with student's name (use sheet called Names)
59.	function loop7() {
60.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Names");

Line 60: Use the Names sheet for this one, where I’ve already added the names of the 4 students.

62.	  for (let r = 1; r < 5; r++) {
63.	   let studentName = ss.getRange(r, 1).getValue();
64.	   DocumentApp.create("Document-"+studentName);
65.	  }
66.	}

Line 62: Set the for loop up to count from 1 to 4.

Line 63: First, we need to get the student’s name from the sheet. We’ll get a different name every time we go around the loop. Set up a variable called studentName. Instead of using the keyword const, like we’ve been using, we use the keyword let, as this will change as it goes round the loop.

Back to Line 63: Get the range which is r, 1. So, the first time around the loop it will get the name in row 1, column 1, i.e. “Joan”, the second time it will get row 2, column 1, i.e. “Paula”, and so on. then get the value in that cell, i.e. the name.

Line 64: Then using DocumentApp.create() we create a new document, which will be titled “Document-” plus the student’s name.

Line 65-66: As always we close the loop and function.

Run the loop7 script. As we can see, in our My Drive, it’s created 4 documents individually titled for each of our students.

The important thing here is that we are getting information from a sheet, using a loop to move around the sheet to get different bits of information and then using it elsewhere. This is extremely useful and allows you to do all sorts of things.

To make a copy of the file that contains the code above, click on this link:

https://docs.google.com/spreadsheets/d/1Dt3n4-vvSY1OFLrHXsATfHJBVjbG7JyoG1jgOLWwBFU/copy

a