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.

index

0

1

2

3

4

5

value

red

blue

green

black

orange

purple

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.

Screenshot of a table containing information about 5 students.

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

Screenshot of a table containing information about 5 students.

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.

Screenshot of a range in a Google Sheets spreadsheet where the second row is highlighted.

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

Screenshot of a range in a Google Sheets spreadsheet where the second row and the fourth column are highlighted.

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.

Array method

What it is used for

join()

Concatenates the values in the array and returns a string. Learn more

reverse()

Reverses the order of values in the array. Learn more

sort()

Sorts the values in the array. Learn more

push()

Adds new values to the end of an array. Learn more

pop()

Removes the value at the end of the array. Learn more

shift()

Removes the value at the beginning of the array. Learn more

unshift()

Adds new values to the beginning of an array. Learn more

splice()

Usually used to insert or remove values in the middle of an array. Learn more

concat()

Used to join multiple arrays together. Learn more

indexOf()

Searches the array for a value and returns the first index where it is found. Learn more

lastIndexOf()

Searches the array for a value and returns the last index where it is found.

every()

Used to check if every value in the array meets a certain condition. Learn more

filter()

Returns a new array containing the values from the original array that meet a certain condition. Learn more

forEach()

It calls a function that you specify once for every value in the array. Learn more

map()

It calls a function that you specify once for every value in the array and returns a new array containing the resulting output values.

some()

Used to check if there is at least one value in the array that meets a certain condition. Learn more

reduce()

Used to convert the values in an array into a single value.

reduceRight()

Used to convert the values in an array into a single value.


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!