Lesson 7: Arrays

7.1 Lists of Values

A variable holds one value. Real data comes in lists: every member's name, every shift's hours, every row of a sheet. JavaScript holds a list in an array:

let firstNames = ["Maya", "Ava", "Ben", "Cam"]
console.log(firstNames)

An array is written in square brackets, with its items separated by commas. The items can be any type: strings, numbers, booleans, or a mix.

To get one item, write the array's name followed by the item's position in square brackets. The position is called its index, and indexes start at 0, not 1:

let firstNames = ["Maya", "Ava", "Ben", "Cam"]
console.log(firstNames[0])
console.log(firstNames[1])
console.log(firstNames[3])

So firstNames[0] is "Maya", the first item, and firstNames[3] is "Cam", the fourth. Starting at 0 feels strange at first, and it's the source of many "off by one" mistakes. When you read code with an index, count from zero.

Try firstNames[4]. There's no fifth item, so you get undefined, JavaScript's way of saying "there's nothing here." It doesn't stop with an error, which means a wrong index can slip by unnoticed.

Three more things you'll use constantly:

let firstNames = ["Maya", "Ava", "Ben"]

// length is how many items the array has
console.log(firstNames.length)

// push adds an item to the end
firstNames.push("Cam")
console.log(firstNames, firstNames.length)

// you can change an item by assigning to its index
firstNames[0] = "Dev"
console.log(firstNames)

Notice that length has no parentheses. It's a property, a value that belongs to the array, not a method that does something. push("Cam") is a method, so it has parentheses to carry the value to add.

Because indexes start at 0, the last item's index is always one less than the length. With 4 items, the last one is firstNames[3], which you can also write as firstNames[firstNames.length - 1].

7.2 Arrays of Arrays

An array's items can themselves be arrays. That's how JavaScript represents a table: an array of rows, where each row is an array of values:

let members = [
  ["First Name", "Last Name", "Hours"],
  ["Maya", "Thompson", 24],
  ["Ava", "Lopez", 12],
  ["Ben", "Okafor", 4]
]

console.log(members[1])
console.log(members[1][0])
console.log(members[2][2])

Read members[1][0] from left to right: members[1] is the second row, ["Maya", "Thompson", 24], and [0] is the first item in that row, "Maya". So the first index picks the row and the second picks the column. members[2][2] is row index 2 (Ava's row), column index 2 (hours): 12.

Writing the array over several lines, one row per line, doesn't change it. JavaScript ignores the line breaks. It just makes the rows easy to see.

7.3 Reading a Whole Sheet

Here's the club's Members sheet again:

{"sheetName": "Members", "rows": 16, "columns": 8,
 "data": [{"range": "A1:E13", "values": [["First Name", "Last Name", "Email", "Dues Paid", "Volunteer Hours"], ["Maya", "Thompson", "maya.thompson@example.com", true, 24], ["Ava", "Lopez", "ava.lopez@example.com", true, 12], ["Ben", "Okafor", "ben.okafor@example.com", false, 4], ["Cam", "Nguyen", "cam.nguyen@example.com", true, 9.5], ["Dev", "Patel", "dev.patel@example.com", true, 15], ["Elena", "Rossi", "elena.rossi@example.com", false, 6.5], ["Farah", "Haddad", "farah.haddad@example.com", true, 11], ["Gabe", "Martinez", "gabe.martinez@example.com", false, 2], ["Hana", "Kim", "hana.kim@example.com", true, 18.5], ["Isaac", "Cohen", "isaac.cohen@example.com", true, 7], ["Jordan", "Lee", "jordan.lee@example.com", false, 0], ["Keisha", "Brown", "keisha.brown@example.com", true, 13.5]]}],
 "formats": [{"range": "A1:E1", "fontWeight": "bold"}]}

getValue reads one cell. getValues, with an s, reads a whole range at once and gives you an array of arrays, one inner array per row:

function readMembers() {
  let sheet = SpreadsheetApp.getActiveSheet()
  let range = sheet.getRange("A1:E13")
  let data = range.getValues()
  console.log(data.length)
  console.log(data[0])
  console.log(data[1])
  console.log(data[1][2])
}

Run it and compare the output with the sheet:

• data.length is 13, one for each row, including the header row.
• data[0] is the header row. Rows in the sheet are numbered from 1, but array indexes start at 0, so sheet row 1 is data[0] and sheet row 2 is data[1].
• data[1][2] is row index 1 (Maya), column index 2 (column C, the email). Columns are counted from 0 too: A is 0, B is 1, C is 2, and so on.

Keeping these two numbering systems straight is the main skill in this lesson. When you see data[5][4], translate it: row index 5 is sheet row 6, and column index 4 is column E.

7.4 Your Assistant Can't See Your Sheet

When you ask an assistant about your data, remember that it can't see your spreadsheet. It only knows what you tell it. Here's a request that tells it almost nothing, sent in a new chat with your learner profile from Lesson 6. Making Decisions:

The assistant didn't know where the last names were, so it assumed: a header in row 1 and last names in column B. It said so in its first sentence, and asked you to correct it at the end. That's good behavior, and it's also a warning. The code is only right if the assumptions are right.

In the same chat, you can say you've learned arrays and ask for a version that reads all the data:

Read. You can follow this with what you've learned: data is an array of arrays, data[1] is the second row (the first person, after the header), and firstPersonRow[1] is the second item in that row, column B. The comments even translate the indexes for you.

One method is new: getDataRange(), which gets the range covering all the data on the sheet, so you don't have to type something like "A1:E13". The assistant explained it, but didn't point out that it wasn't on your list.

The assistant repeated its assumptions at the end: a header row, and last names in column B. For this Members sheet, both are right, and the code logs "Thompson." But imagine a sheet with last names in column A and first names in column B, which is a common layout for a roster. The code would run without an error and log a first name, and nothing would tell you something was wrong. When an assistant states an assumption, check it against your sheet before you run the code.

7.5 Describing Your Sheet

Better than checking guesses is not making the assistant guess. Describe your data in the prompt:

• the sheet's name,
• whether there's a header row, and which rows hold the data,
• what each column holds, and what type of value is in it.

Here's a description of the Members sheet, sent in the same chat along with a slightly bigger request:

Read. With the description, there's nothing left to assume. The code pulls four values out of firstMemberRow using the column indexes you gave: 0 for First Name, 1 for Last Name, 2 for Email, 3 for Dues Paid. Because you said Dues Paid is true or false, the if compares it with true. And because you named the sheet, the code now asks for the sheet named "Members" instead of whichever sheet is active, which is safer in a file with several sheets.

That last change uses a method you haven't seen, getSheetByName. Everything else looks right. Run it:

function logFirstMemberInfo() {
  // Get the sheet named "Members"
  let sheet = SpreadsheetApp.getSheetByName("Members")

  // Get the range that covers all the data on the sheet
  let dataRange = sheet.getDataRange()

  // Read all the values into an array of arrays (rows and columns)
  let data = dataRange.getValues()

  // Get the first member's row (index 1, since index 0 is the header row)
  let firstMemberRow = data[1]

  // Pull out each piece of info using the column indexes
  let firstName = firstMemberRow[0]
  let lastName = firstMemberRow[1]
  let email = firstMemberRow[2]
  let duesPaid = firstMemberRow[3]

  // Combine first and last name into one string
  let fullName = `${firstName} ${lastName}`

  // Display the full name and email in the Execution log
  console.log(`${fullName}, ${email}`)

  // Check the dues paid value and log a readable message
  if (duesPaid === true) {
    console.log(`${fullName} has paid their dues`)
  } else {
    console.log(`${fullName} has not paid their dues`)
  }
}

It stops on the first line inside the function, with a TypeError. The code came from the assistant, but the error is yours to understand, so paste it back and ask:

The explanation narrows the problem to two possibilities: the method doesn't exist, or it exists but belongs to something else. Your code has used SpreadsheetApp.getActiveSheet() many times, so asking SpreadsheetApp for a sheet looks reasonable. The question is whether getSheetByName works the same way. Now ask for the fix:

So the second possibility was right. getSheetByName belongs to a spreadsheet, the whole file, which you get with SpreadsheetApp.getActiveSpreadsheet(). A spreadsheet contains sheets, so you ask the spreadsheet for one by name. getActiveSheet() happens to work on both SpreadsheetApp and a spreadsheet, which is probably how the assistant mixed them up.

Notice what the reply doesn't say: that the mistake was its own. It explains what was wrong as if the code had come from somewhere else. That's not unusual, and it doesn't matter much, but it's a reminder that an assistant's code is a draft, not a finished product. It looked right, the explanation of each line was confident, and it still didn't run. Reading catches a lot, and running catches the rest.

Here's the corrected function. Run it now:

function logFirstMemberInfo() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = spreadsheet.getSheetByName("Members")
  let dataRange = sheet.getDataRange()
  let data = dataRange.getValues()
  let firstMemberRow = data[1]
  let firstName = firstMemberRow[0]
  let lastName = firstMemberRow[1]
  let email = firstMemberRow[2]
  let duesPaid = firstMemberRow[3]
  let fullName = `${firstName} ${lastName}`
  console.log(`${fullName}, ${email}`)
  if (duesPaid === true) {
    console.log(`${fullName} has paid their dues`)
  } else {
    console.log(`${fullName} has not paid their dues`)
  }
}

Change data[1] to data[3] and run it again. Which member is that? Check your answer against the sheet.

A sheet description is worth saving somewhere you can copy it from, such as a note or a document. You'll paste it into any chat where you ask about that sheet, right after your learner profile.

7.6 Writing Several Cells at Once

setValues is the partner of getValues: it writes an array of arrays into a range. Continuing the same chat:

Read. The important line is let labels = [["High"], ["Medium"], ["Low"]]. Even though there's only one column, each label is inside its own inner array, because setValues always takes rows, and each row is an array. Three rows of one column each fit the range F2:F4, which is also three rows of one column.

Run it, and watch column F:

function writeVolunteerLabels() {
  // Get the spreadsheet you're currently working in
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet()

  // Get the sheet named "Members" from that spreadsheet
  let sheet = spreadsheet.getSheetByName("Members")

  // Get the range F2:F4, where the labels will go
  let labelRange = sheet.getRange("F2:F4")

  // Build an array of arrays: one row per label, one column per row
  let labels = [["High"], ["Medium"], ["Low"]]

  // Write all three labels into the range at once
  labelRange.setValues(labels)
}

Now add a fourth label for Cam in row 5, by changing the labels line to let labels = [["High"], ["Medium"], ["Low"], ["Medium"]], and run it again. You'll get an error. Here's what the assistant made of it:

The message says exactly what's wrong, once you know how to read it: four rows of data, three rows of range. setValues needs them to match exactly. Fix it yourself: change the range so it covers four rows, then run the code again. (The answer is at the end of this section.)

setValues also fails if you forget the inner arrays and write ["High", "Medium", "Low"]. That's one row of three columns, not three rows of one column, and Apps Script says the parameters "don't match the method signature."

(The fix: change "F2:F4" to "F2:F5".)

7.7 Reading Code from Elsewhere

To finish, here's the kind of code you'll find online and in replies from an assistant that doesn't have your profile. The request describes the sheet, but no profile was sent:

You can read nearly all of it now. It uses const and semicolons, as expected, and a short variable name, ss, for the spreadsheet. It chains getDataRange().getValues(). And it uses Logger.log instead of console.log. In Apps Script, Logger.log also writes to the Execution log, so for your purposes the two work the same way.

The instructions at the end are the part to check. They say to see the output under View, then Logs. The current Apps Script editor has no View menu; the Execution log opens by itself below the code when you run a function. That menu was in an older version of the editor, and plenty of instructions written for it are still online. As in Lesson 4. Getting Started with Google Apps Script in Google Sheets, check an assistant's directions against what's on your screen.

7.8 Your Learner Profile

Here's the update for this lesson:

Five new items, and no new rules this time.

Your sheet description doesn't go in the profile. The profile describes you: where you work, what you know and how you want code written. A sheet description describes one set of data, and you'll work with several. Keep each description handy, and paste it into a chat, after your profile, whenever you ask about that sheet.

7.9 Summary

An array is an ordered list of values in square brackets, and each item has an index that starts at 0. length tells you how many items there are, and push adds one to the end. An array of arrays represents rows and columns, read as data[row][column]. getValues reads a range into an array of arrays, and setValues writes one, as long as its rows and columns match the range exactly. Remember that sheet rows and columns count from 1, but array indexes count from 0. An assistant can't see your spreadsheet, so it guesses at anything you don't describe, and even when it tells you its assumptions, you have to check them. Describing your sheet in the prompt removes the guesswork. Even then, code from an assistant can contain mistakes like calling a method on the wrong thing, which is why you read it, run it, and understand any error before you ask for a fix. Next, you'll learn to repeat steps for every row of a sheet.