Apps Script Basics – Arrays, Execution log

In this post, we’re going to look at another key area in JavaScript and Apps Script and indeed in many coding languages, that of arrays, which are just special variables which allow you to store multiple elements in a single variable.

I’ll also introduce you to the Execution log, where you can see what’s happening in your code without for example, having to print it on a sheet. Plus, see how long a script took in the Execution log and then see how we can speed up our scripts.

Introducing the Execution log

Let’s take a look at some looped data in the log. Here we have some data in a sheet. We’re going to loop down the data and put it one by one into the log.

1.	//Gets the numbers from the sheet and logs them in the log
2.	function logNumbers(){
3.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

With all the examples in this chapter, 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.

5.	  for (r=1; r<21; r++) {
6.	    let numberFromSheet = ss.getRange(r,1).getValue();

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 numberFromSheet.

8.	    Logger.log(numberFromSheet);
9.	  }
10.	}

Line 8: Now, still within the loop we log the numbers in the log. 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 numberFromSheet.

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 may need to select the function you want to run. If necessary click on the function drop-down menu from the toolbar and click “logNumbers“. Then, click the Run button to run the function.

Underneath your code, you will see the Execution log appear.

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 log.

The log 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 where we can see our data, let’s move on to look at arrays. In this example, we’re going to look at storing individual and multiple items in variables. Note, we could use variables too.

12.	//Show a variable with 1 item and a variable with an array of items
13.	function array1a(){
14.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
15.	  
16.	  const item = "Fred";
17.	  const items = ["Joan", "Paula", "Dingo", "Georgina"];

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 are 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 (strings) we’ll need to use quote marks.

19.	  Logger.log(item);
20.	  Logger.log(items);
21.	}  

Lines 19-21: Now, let’s log the two variables in the logger and then close the function.

Choose “array1a” from the toolbar and click Run.

As we can see, it shows the text string “Fred” and then the array below it. In the log, 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.

23.	//Showing the difference between setting a single value and an array
24.	function array1b(){
25.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
26.	  
27.	  const item = "Fred";
28.	  const items = ["Joan", "Paula", "Dingo", "Georgina"];

Lines 24-28: The same code as the previous example, except I’ve called this function array1b.

30.	  ss.getRange("D1").setValue(item);

Line 30: Let’s add the content of item to the cell D1.

32.	  ss.getRange("E1").setValue(items);

Line 32: Now, let’s do the same with the content of items to cell E1.

34.	  ss.getRange("F1").setValue(items[1]);  
35.	}  

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 our 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 arrays 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. [1].

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. 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 log 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.

37.	//Uses getValues to get an array of numbers in one go; How to add them to a sheet
38.	function array2(){
39.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
40.	  
41.	  const listOfNumbers = ss.getRange(1,1,20).getValues();

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. The numbers in the brackets refer to: start row number, start column number, number of rows. So, it’s row 1, column A (first column, and 20 rows. As we are only getting one column, we don’t need to state the number of columns.

Then, as there is more than one value, we use getValues() to get all the values in one go and store them in listOfNumbers.

43.	  Logger.log(listOfNumbers);

Line 43: Then, log the contents of listOfNumbers.

45.	  ss.getRange(1,8,20).setValues(listOfNumbers);

Line 45: Now, let’s add that range of numbers to our sheet. Here, I’m going to put them in column H (8^th column). 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.

47.	  ss.getRange(1,9).setValue(listOfNumbers[1]);
48.	}

Line 47-48: Finally, let’s extract a number from listOfNumbers and add it to cell I1. Similar to above, we’ll need to use setValue() then state the variable name we want and the position in 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.

In 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.

One way to think of this in relation to a spreadsheet is in terms of rows and columns. In a single array, the square brackets is one row, and the items in the array are different columns. In the example above, every pair of square brackets is a row, wrapped up in one pair of square brackets, to make it one array. This makes sense as the original data was indeed on different rows, with every number on a different one.

How arrays relate to rows and columns on a spreadsheet

Let’s look how an array is related to a spreadsheet more closely. In this example, we’re going to add one row of data, and then add two rows of data. The important thing is to notice the format of the arrays to do this and what it produces on the sheet.

50.	//Showing how arrays relate to rows and columns
51.	function array3(){
52.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
53.	  const oneRowOfItems = [["Joan", "Paula", "Dingo", "Georgina"]];
54.	  const twoRowsOfItems = [["Wilma", "Fred", "Betty", "Barney"], [35, 45, 30, 40]];  
55.	  ss.getRange(3,4,1,4).setValues(oneRowOfItems);  
56.	  ss.getRange(5,4,2,4).setValues(twoRowsOfItems); 
57.	}

Lines 50-52: We get our sheet as before.

Line 53: This first array contains four names and they are stored in one array. That one array will be one row on the sheet. To be able to add that to the sheet, we need to wrap it up in another set of square brackets, so it’s all one element for setValues() to recognize and use.

Line 54: This time we have two sets of data, one group of data with their names, and another with their ages. The two sets of data will be on two separate rows. In the array we show this by grouping the names in one array, adding a comma, then adding a second array. Then we wrap it all up in a pair of square brackets, so it becomes one array. This is what we saw in example array2, with the rows of data.

Line 55: To add it to the sheet, we need to get the range. Here, I’m going to add it D3:D7, which is row 3, column 4, there’s only 1 row, and there are 4 items (or columns). Then we use setValues() to add the array contents.

Line 56: Similarly, we do the same for the second array, but this time add it to row 5. Plus, there are 2 arrays so there will be 2 rows, so change the third number in the getRange brackets to 2. The structure is exactly the same, despite this being an array of arrays.

Run the code and you will see the first row of data, and then underneath, the two rows of data.

This is an important concept to understand, as it’s key to adding and getting multiple data to and from a sheet.

Also, remember that setValues() expects the data to be wrapped up in two sets of square brackets, i.e. to be an array of arrays, even if it’s just one row of data.

Looping through an array

A useful technique is to loop through an array, as we may want to get the individual elements, or even change the format of the array. In this example, we’re going to loop through an array of names and add those names one by one to a sheet, with each name on a different row.

59.	//How to loop through an array
60.	function array4(){
61.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
62.	  const items = ["Joan", "Paula", "Dingo", "Georgina"];

Lines 60-62: The same as array1a, we set up an array with 4 names.

64.	  for (i=1; i<5; i++) {
65.	    ss.getRange(i,11).setValue(items[i-1]);
66.	  } 
67.	}

Line 64: 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 65: Now, we want to get a cell on the sheet (we’re going to use column K (11^th column)) and set its value to one of the names in the array. So, first we get the range and get the cell location (11,1 > 11,2 > etc).

Then, we will set the value by getting the array items and getting one word at a time, starting at 0 (as arrays are zero-based, whereas rows and columns aren’t), so we need to subtract 1 to get the array position, e.g. [0], [1], etc.

Lines 66-67: We close the loop and the function.

Run the code and as we can see, on the sheet, it has taken the contents of the array and added the names in each of the cells. This also shows us how we can change the format of the original data, as the four names were in one row, but we’ve changed it so it’s one name across four rows.

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. This will show you how we create empty arrays, and how we can add items to an array.

69.	//Getting values one by one from a sheet and one by one adding them to a sheet
70.	function array5a(){
71.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
72.	  let listOfNames = [];

Line 72: 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. Note, I’ve used let to show that this isn’t variable and will change as we add data to it.

74.	  for (r=1; r<5; r++) {

Line 74: Then, let’s set up our for loop, to count from 1 to 4.

76.	    let name = ss.getRange(r,11).getValue();
77.	    listOfNames.push(name);

During each loop, we will get the value from the cell on the sheet then add it to the array.

Line 76: 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 r in the row part. The column will remain fixed as there is only one column (K the 11th column). Then we get its value. Make sure there is data in this column on your sheet.

Line 77: 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 we want to add to, then 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 name.

79.	    Logger.log(listOfNames);

Line 79: I’m going to log the list of names as it’s going around the loop, so we can see what’s happening in the array.

81.	    ss.getRange("M"+r).setValue(listOfNames[r-1]);
82.	  }
83.	}

Line 81: Now, we get the cell we want on the sheet, this time in column M and the current row number r, and add the latest name in the array.

Note, we would normally just add the variable name here, but as we’ll see in the next example, we can use the array to add the names in one go.

As we can see, it’s copied the names from column K 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 one or to another spreadsheet or even a completely different place like a Google Doc. So, 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.

When we run the function, we can find out how long it took to run. Go to the menu on the left, and select Executions.

This will show you all the times you’ve run functions in your code. It shows you how long the function took to complete and the status. Completed means it finished, but sometimes you might see Failed if there was a problem in script.

Here it took 1.677 seconds. It doesn’t sound long, but as we’ll see in a moment, this can be reduced. Normally, scripts have 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 its 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.

85.	//Getting a range of values in one go and adding them to a sheet in one go
86.	//Note the different in execution time between array4a and 4b
87.	function array5b(){
88.	  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

We start off as before.

90.	  const originalList2 = ss.getRange(1,11,4).getValues();

Line 90: Let’s get the range of values in one go, by using the getRange() method which needs 3 arguments: start row, start column, number of rows, (as we’re only getting 1 column, we don’t need to get the number of columns). So, here we have row 1, column 11(K), 4 rows. Then get the values, as we saw in array2.

92.	Logger.log(originalList2);
93.	  
94.	ss.getRange(1,15,4).setValues(originalList2);
95.	}

Line 92: Let’s log the content of the array. This isn’t needed for the code to work.

Line 94: 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 executions, we see it’s much shorter than the previous example and due to that it is also quicker. It took only 0.787 seconds, over 2 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. You should always try to minimize the number of calls you’re making, try to do things in one go to speed up your programs.

Here’s the link to the sheet with the code in it: LINK

Have a play around with the arrays, it’s the best way to learn how they work and what can be done with them.

If you want to know more about arrays and array methods, I recommend checking these two links out: ARRAYS and ARRAY METHODS

a