Arrays in Apps Script

An array is a special type of object that is used to store a list of values. You will use arrays extensively while working with Google Sheets using Apps Script.

Here is how you declare and initialize an array called colors. You list a number of values within square brackets ([ and ]).

var colors = ["red", "blue", "green", "black", "orange", "purple"];
Logger.log(colors); //[red, blue, green, black, orange, purple]

Arrays have an indexing system to help you access values stored at specific positions. The indexing system starts at 0 and not 1. The color at index 0 is "red", 1 is "blue" and so on. Since the indexing system starts at 0, the last value in the array will have the index N - 1, where N is the number of values in the array.

You can access the value at a specific index by using the brackets notation [index].

var colors = ["red", "blue", "green", "black", "orange", "purple"];
Logger.log(colors[0]); //red
Logger.log(colors[1]); //blue
Logger.log(colors[2]); //green
Logger.log(colors[3]); //black
Logger.log(colors[4]); //orange
Logger.log(colors[5]); //purple

If you try accessing a value that does not exist in the array, Apps Script will return the value undefined. In the above example, there are only six colors in the array colors. The maximum index is 5 (6 - 1). So, Logger.log(colors[6]); will return undefined. Try it out.

All arrays in Apps Script have certain properties and methods to help you do more with the values stored in them.

The property length will tell you the number of values in the array.

var colors = ["red", "blue", "green", "black", "orange", "purple"];
Logger.log(colors);
Logger.log(colors.length);

Using arrays to store tabular data

An array can also contain other array values. For example, consider the table below. There are 5 rows in the table and each one contains information about a student.

We are going to create an array that stores all of this information. First, we are going to create an array per student to store their information.

var student1 = ["Ryan", "Chiu", 3, "Preschool"];
var student2 = ["Mark", "Sanders", 4, "Pre-K"];
var student3 = ["Abigail", "White", 3, "Preschool"];
var student4 = ["Mary", "Lee", 4, "Pre-K"];
var student5 = ["Rakesh", "Yadav", 5, "Pre-K"];

Then, we're going to create another array called students using the five arrays we just created.

var students = [student1, student2, student3, student4, student5];
Logger.log(students);
/*
Logger output (an array containing 5 array values):
  [
      [Ryan, Chiu, 3.0, Preschool],
      [Mark, Sanders, 4.0, Pre-K],
      [Abigail, White, 3.0, Preschool],
      [Mary, Lee, 4.0, Pre-K],
      [Rakesh, Yadav, 5.0, Pre-K]
  ]
*/

The array students contains five arrays. Each of those arrays contains 4 values (first name, last name, age and grade). Let's try accessing the values stored in the students array.

Logger.log(students[0]); //[Ryan, Chiu, 3.0, Preschool]
Logger.log(students[1]); //[Mark, Sanders, 4.0, Pre-K]
Logger.log(students[2]); //[Abigail, White, 3.0, Preschool]
Logger.log(students[3]); //[Mary, Lee, 4.0, Pre-K]
Logger.log(students[4]); //[Rakesh, Yadav, 5.0, Pre-K]
Logger.log(students[5]); //undefined (there are only 5 students so Apps Script returns undefined when we try and access the 6th value)

As you can see above, the values stored in the array students are themselves arrays. Let's say we want to access the second student's grade (ie, the selected cell below).

We need to first get the second student's info and then get their grade.

var student = students[1]; //Since array indices start at 0, the value at index 1 is the second student's info
var grade = student[3]; //The student's grade is the 4th value.
Logger.log(grade); //Pre-K

You can also get the second student's grade using a single statement.

var grade = students[1][3];
Logger.log(grade); //Pre-K

In the above statement, students[1] is the second student's info.

Then students[1][3] is that student's grade.

Here are a few more examples:

Logger.log(students[0][0]); //Ryan
Logger.log(students[0][1]); //Chiu
Logger.log(students[2][3]); //Preschool
Logger.log(students[4][3]); //Pre-K
Logger.log(students[3][2]); //4

You can also change the value at a specific location. Let's imagine that Rakesh's age is actually 4 and you entered 5 by mistake. You can fix it by assigning the correct value to students[4][2].

Logger.log(students[4][2]); //5 (incorrect age)
students[4][2] = 4; //Assign the correct age
Logger.log(students[4][2]); //4 (Rakesh's age is now correct)

You can also create these tabular arrays with a single statement like the one below. Essentially you're creating an array whose values are also arrays. Remember to separate the values within an array with a comma (this also applies to the three inner arrays which are the values contained within the outer array).

var students = [
    ["Jack", "Ryan", 3, "Preschool"],
    ["Leslie", "Castro", 3, "Preschool"],
    ["Adam", "Lopez", 4, "Pre-K"]
];

In Apps Script, you will be working with tabular data like this a lot. This is because the data you're working with will usually be stored in Google Sheets.

How to check if a value is an array

You can check if a value is an array by using the Array.isArray(valueToCheck) function. This function returns true if valueToCheck is an array and false otherwise.

Array.isArray(valueToCheck); //checks if valueToCheck is an array

var colors = ["red", "blue", "green", "black", "orange", "purple"];
Logger.log(Array.isArray(colors)); //true

var age = 3;
Logger.log(Array.isArray(age)); //false

Array methods

Arrays are a special type of object. This means they can have properties and methods. We already discussed the length property of arrays.

A method is a property that has a function value associated with it. The table below lists the array methods available in Apps Script along with a brief description of what it is used for.

There is no need to memorize all of these methods at once. You'll become familiar with them as you write code and build apps.