Apps Script Basics (4) – Loops

Here, 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. 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 in the code below:

4Loops - 30

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, there are 2 sheets, one called Numbers and the other called Names.


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

4Loops - 4

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

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 (i.e. the current one we’re using), 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. 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.

4Loops - 5

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.

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++).

All of this is contained in normal brackets. 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.

4Loops - 5

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, we need to select loop1 to run it.

4Loops - 18

So, from the toolbar where it says “Select function”, click on “loop1”.

4Loops - 19

Then click the play button to run the code.

4Loops - 2

The first time you run it, it will ask for authorisation, just click “Review permissions”.

4Loops - 3

Then click the blue “Allow” button.

4Loops - 20

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. We’re going to add the numbers 1 to 20 in column A.

4Loops - 6

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

4Loops - 7

Line 14: Same as above

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.

4Loops - 21

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


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.

4Loops - 8

Lines 20-21: As before.

4Loops - 9

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.

As we can see it’s filled the cells in blue.

4Loops - 22


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.

4Loops - 10

Lines 29-30: As before.

4Loops - 11

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.

4Loops - 23

As we can see, it added both the colour and the numbers.


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

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 column across the page.

4Loops - 12

Lines 39-40: As above.

4Loops - 13

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 constant 1, I’ve added the variable c (for column), which will change as the loop goes around.

Line 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 program, 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).

4Loops - 24

So, how does this work exactly?

4Loops - 13

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 Document5

This time 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.

4Loops - 14

Lines 51-52: As above.

4Loops - 15

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

Line 55: Every time it loops, we want it to create a document and name it “Document” plus the current number from the variable r. 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 56-57: We close the loop and function as before.

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

4Loops - 25


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.

4Loops - 16

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

4Loops - 27

4Loops - 17

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

Line 64: 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 and then 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 65: Then using DocumentApp.create we create a new document which will be titled “Document-” plus the student’s name.

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

4Loops - 26

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.


Here’s the link to the sheet, which contains the code:

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

Here’s the complete code with all 7 loops:

4Loops - 284Loops - 29


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Google+Flipboard / Twitter)


Advertisements

4 thoughts on “Apps Script Basics (4) – Loops

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s