In this post, we’re going to look at another key area in Apps Script and indeed many coding languages, that of arrays, which are just special variables which allow you to store multiple items in a single variable. I’ll also introduce you to the logger, where you can store see what’s happening in your code without for example, having to print it on a sheet. Plus, the execution transcript, which shows you the steps your code is taking and how long they take.
Introducing the logger
Following on from my post on loops, let’s take a look at some looped data in the logger. Here we have some data in a sheet. We’re going to loop down the data and put it one by one into the logger.
With all the examples in this post, we’re going to get the active sheet, so line 3, you’ll see repeated for each of the functions below. You’ll use this line repeatedly for a lot of programs you write, where you’re working with a spreadsheet and the current sheet.
Now we want to loop down the list on the sheet.
Line 5: Here we know we have 20 numbers, so we set the loop to start at row 1 and finish at row 20 (<21). Here I’ve used the variable r as the counter.
Line 6: Then we get the cell at that point in the loop using getRange() and a row and a column number. The row will be the variable r and the column will always be 1 as it’s the first column, A. Then we get its value and store it in the variable listOfNumbers.
Line 8: Now, still within the loop we log the numbers in the logger. This is easy to do, just use Logger.log() and in the brackets state what you want to add to it. In this case, it’ll be the contents of the variable listOfNumbers.
Lines 9 and 10: We close the loop with the curly bracket and then the function with another one.
If you’re using a copy of this file I’m using, you’ll need to select the function you want to run. Just click on “Select function” form the toolbar and in this case “logNumbers“. Then press the play button to run the function. The first time you run it you will have to authorise it.
Once run, you’ll want to open the logger. Go to the “View” menu and click “Logs”.
As we can, it’s logged the values each time in went around the loop. So, it got “2” from row 1, then “4” from row 2, and so on. Each time placing them in the logger.
The logger is an excellent way to debug your code and to see what’s happening in your code and any point in it. You can put Logger.log() on any line and it’s really useful to see what’s going on, especially with what’s being stored in your variables.
Single items and multiple items
OK, now we have a place we can see our data, let’s move on to look at arrays. In this example, we’ll look at the storing individual items and multiple items in variables.
Lines 13-14: As above.
Line 16: Here, I’ve stored the text string “Fred” in the variable item.
Line 17: This time I want to store 4 different names in the variable items. To do this, I need to set up an array. The contents of an array is shown by whatever is in between the square brackets. So here we have four names all within one array called items. To separate them, you need to use commas and as these are words we’ll needs to use quote marks.
Lines 19-21: Now, let’s log the two variables in the logger and then close the function.
Choose “array1a” from the toolbar and press play.
As we can see, it shows the text string “Fred” and then the array below it. In the logger, it displays the array with square brackets, so you know it’s an array.
Accessing values in arrays
We often want to get at the information stored in an array. Let’s see how we do that. Here we’re going to access the two variables item and items and add them to our sheet.
Line 24-28: The same code as the previous example.
Line 30: Let’s add the content of item to the cell D1.
Line 32: Now, let’s do the same with the content of items to cell E1.
Finally, let’s get a specific item from our array and put it in cell F1. To do so, we need to state in which position in out array is the name we want. In arrays, each item of data has a position, starting in position 0. So, “Joan” is at position 0, “Paula” is in position 1, and so on. A common mistake is to forget that array are zero-based, i.e. they start at 0 not at 1.
Line 34-35: So, for example, to get “Paula” we need to get the array items at position 1. We do that by stating the variable name and in square brackets stating its position, e.g. .
Run the code and let’s see what we get.
“Fred” has been added in cell D1 as expected.
However, in cell E1 we were expecting to add the contents of items but we only have the first name in the array! This is because we’re using setValue() which is expecting a single item. You may then think that we could use setValues() to get multiple values, but it doesn’t work like that with the array we’ve set up. This is because the variable items is still a single array, it just happens to contain multiple values (mini arrays). This is why we need to state the position of the items within the array. As we’ll see below there are of course ways to access multiple data.
Finally, in cell F1 we wanted to add the second name on the list and we have indeed, so the index we used has worked.
Using get and setValues() to get a range of values in one go
Here, we’re going to get a range of values, add them to the logger to see what they look like. Then add those values to our sheet and pick out a value from that range and add it to the sheet.
Line 41: Let’s get the range of numbers we had in the first example from column A. Here, I’m using getRange() and stating the range in which those numbers are (using cell notation). Then, as there is more than one value, we use getValues() to get all the values in one go and store them in listOfNumbers.
Line 43: Then, log the contents of listOfNumbers.
Line 45: Now, let’s add that range of numbers to our sheet. Here, I’m going to put them in column H. I get the range of cells I want to add to. Note, this has to be the same number of cells as the original data, otherwise it will throw an error. Then, similar to getValues() we’re going to use its opposite, setValues() to add the values to those cells.
Line 47-48: Finally, let’s extract one of the numbers from listOfNumbers and add it to cell I1. Similar to above, we’ll need to use setValue() then state the variable we want and the position of the data we want. So, here I want to get the second item, so I need to state position 1. Then close the function.
Run the code and you’ll see the list of numbers has been added to column H and that we have picked the second number out and added it to cell I1.
Open the log and we’ll see that line 43 logged our data.
Note, that this time every number has a set of square brackets around it and that all the data is enclosed within a second set of brackets. This shows that the data is stored in lots of little arrays within 1 larger array, what is known as an array of arrays. This is why, we can use setValues() as here we have multiple items and we can add them to our sheet in one go. Whereas, in line 32 we couldn’t use setValues() as the data was one item, as we can see in the log output below:
This can all sound a bit complicated, but basically, we either have an array on its own. So, 1 array with multiple items in it. Or we have arrays within an array (an array of arrays). So, the code knows this array has multiple items, whereas the other one it treats it as a single item.
Looping through an array
A useful technique is to loop through an array. In this example, we’ll loop through an array of names and add those names one by one to a sheet, with each name on a different row.
Lines 51-53: The same as array1a, we set up an array with 4 names.
Line 55: First, we set up a for loop. This will go from 1 to 4. Here, we’re using the variable i to keep count. Note, that i is often used in loops, so you’ll see it a lot when you look at code examples.
Line 56: Now, we want to get a cell on the sheet (we’re going to use column K) and set its value to one of the names in the array. So, first we get the range and get the cell location, by adding the cell letter “K” to the current number in the loop (i), e.g. K1, K2, etc. Then we will set the value by getting the array items and getting one word at a time, starting at 0, so we need to subtract 1 to get the array position, e.g. , , etc.
Lines 57-58: We close the loop and the function.
As we can see, on the sheet, it has taken the contents of the array and added the names in each of the cells.
Setting up an empty array and adding to it
Here, we’re going to loop down one list, add the values one by one to an array, then add those values one by one to another part of the sheet. We’re going to see how we create empty arrays, and how we can add items to an array.
Line 63: First, let’s set up an empty array. We do that, simply by assigning a pair of square brackets to it. So, at the moment the variable listOfNames is empty but it’s an array.
Line 65: Then, let’s set up our for loop, to count from 1 to 4.
During each loop, we will get the value from the cell on the sheet then add it to the array.
Line 67: First, we get the cell using the getRange() method using the row and column. As we want to go down the rows from 1 to 4, we add the variable a in the row part. The column will remain fixed as there is only one column (K the 11th column). Then we get its value.
Line 68: Now, we need to add it to the array. To do this, we use the array method push(). So, we state the name of the array, add a dot then the word push. In the brackets we state what we want to add to the array. In this case, it will be the value we just got, which is in the variable originalList.
Line 70: I’m going to log the names, so below we can see what’s happening in the array.
Line 72: Now, as in the previous array example, we get the cell we want on the sheet, this time in column M, and add the names one by one.
As we can see, it’s copied the names from column L to column M. Not the most exciting thing to do, but we can use a similar technique to copy data from one sheet to another or to another spreadsheet or even completely different place like a Google Doc, you can start to see the power of this.
The log clearly shows us what’s happening, each time we go around the loop. It’s adding one word at a time at the end of the array.
Also, in the View menu, there is the Execution transcript. Click on that to open it.
This shows you every action the code is taking, right from getting the active spreadsheet to adding the last name on the sheet. Along with the log, this is a vital tool to find out what is going on in your code and will help you troubleshoot. If the code comes across an error and stops, it will show you where it stopped by showing you the line number. It’s important to remember that this doesn’t always mean the problem is in that line, it may be due to something else not running correctly elsewhere but because there is some reference here to that, it is stopping here.
The other thing that is useful is to see how long the steps are taking and the overall time the code took to complete. Here it took 0.821 seconds. It doesn’t sound long, but as we’ll see in a moment, this can be reduced. There is a maximum runtime limit of 6 minutes, so you can imagine a program of hundreds of lines long will mean every second counts. Plus, you want your program to run as quick as possible, as you don’t want to sit there twiddling your thumbs while your program works it’s magic!
Reducing the execution time
The above code is fine and is useful in certain circumstances, but for the job we just did, that of copying one set of data and pasting it somewhere else, we can do it quicker.
We start off as before.
Line 81: Let’s get the range of values in one go, by using the getRange() method which needs 4 arguments: start row, start column, number of rows, number of columns. So, here we have row 1, column 11(K), 4 rows, and 1 column. Then get the values, as we saw in array2.
Line 83: Then, we get the range we want to add the values to (column O) (remembering it has to have the same number of cells as the original data), then set the values from the variable originalList2.
As we can see, it added the 4 names.
Looking at the execution transcript, we first of all see it’s much shorter than the previous example and due to that it is also quicker. It took only 0.152 seconds, over 5 times faster.
The difference being that in the previous example, we got a value and set it, then got another value and set it, etc, four times. Whereas, in this example, we got a group of values and set them in one go.
Here’s the link to the sheet with the code in it:
Have a play around with the arrays, it’s the best way to learn how they work and what can be done with them.
Here’s the full code:
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” - iBooks store / Kindle store
- "Beginner's Guide to Google Sheets" - iBooks store / Kindle store
- "Beginner's Guide to Google Docs" - iBooks Store / Kindle store
- "Beginner's Guide to Google Slides" - iBooks Store / Kindle store
- "Google Sheet Functions - A step-by-step guide" - iBooks Store / Kindle Store