monaco

6 Arrays

6.1 Why Arrays Matter in Apps Script

When you start automating Google Sheets with Apps Script, you quickly discover that working with one value at a time isn’t enough. Spreadsheets are built around lists, columns, and tables — all collections of values. To handle that kind of data, you need a structure that can hold many items at once. That’s exactly what arrays are designed for.

This is where the University Store’s data outgrows single variables. Two collections will run through the chapter. The first is the clicker’s demand history — how many sold each month over the past year:

let monthlyDemand = [480, 240, 180, 300, 360, 120, 90, 540, 420, 240, 180, 450]

Those twelve numbers add up to the 3,600-a-year figure from Chapter 4, but notice how they swing: a quiet summer (90 in July), then sharp spikes when each semester starts (540 in August, 480 in January). That movement is exactly why last month’s sales are a poor guide to next month’s — and why, in the next chapter, we’ll forecast from the whole series instead of any single point. The second collection is a slice of the store’s catalog, where each item is a row of its own values:

let catalog = [
  ["classroom clicker", 120, 64800],
  ["whiteboard marker", 480, 9000],
  ["USB drive", 75, 42000]
]

Arrays let your scripts read, process, and transform groups of values like these efficiently. They’re the backbone of almost every meaningful spreadsheet automation you’ll write.

6.1.1 Arrays Hold Multiple Values in a Single Variable

A normal variable stores one value:

let lastMonth = 450
console.log(lastMonth)

An array stores many:

let monthlyDemand = [480, 240, 180, 300, 360, 120, 90, 540, 420, 240, 180, 450]
console.log(monthlyDemand)

This ability to group related values is essential when you’re dealing with spreadsheet data, which naturally comes in lists.

6.1.2 Arrays Are How Google Sheets Returns Ranges

When you read a single cell, you get a single value. But when you read multiple cells, Apps Script returns an array of arrays:

Each inner array represents a row
The outer array contains all the rows you requested

This mirrors the shape of the spreadsheet, but in JavaScript form. Once you understand arrays of arrays, you can process an entire demand column or item catalog at once instead of working cell by cell.

6.1.3 Arrays Make Your Scripts Faster and Cleaner

Apps Script is at its best when you read a whole range into an array, process the data in memory, and write the results back in one step. This is dramatically faster than touching the spreadsheet repeatedly. Arrays make it possible.

6.1.4 Arrays Help You Think in Terms of Data, Not Cells

Once you start using arrays, your mental model shifts. Instead of “read A2,” you think “get the second item in the list.” Instead of “look at each cell in column B,” you think “consider each row and look at index 1.” Instead of “write to row 10,” you think “update the tenth array element.” This is how real applications handle data — by working with collections rather than isolated pieces.

6.1.5 What You’ll Learn in This Chapter

In the sections ahead, you’ll explore how to create and use arrays, how to work with the arrays of arrays that Google Sheets returns, how to reach individual values inside them, and how to write arrays back into the sheet. By the end, arrays will feel natural — and you’ll be ready to handle the store’s demand history and catalog with confidence and speed.

6.2 Understanding Arrays

Arrays are one of the most important data structures you’ll use in JavaScript and Apps Script. If variables let you store a single value, arrays let you store many values in a single place — a list, a sequence, a collection. This becomes essential with spreadsheet data, because Sheets almost always gives you information in groups: rows, columns, or whole tables.

Think of an array as a row of labeled boxes, each holding a value. You can look inside any box, change what’s in it, or look at all of them one by one.

6.2.1 What an Array Looks Like

Arrays are created using square brackets:

let items = ["classroom clicker", "whiteboard marker", "USB drive"]
let monthlyDemand = [480, 240, 180, 300, 360, 120, 90, 540, 420, 240, 180, 450]

Each item has a position called an index. JavaScript starts counting at 0, not 1:

monthlyDemand[0] → 480 (January)
monthlyDemand[1] → 240 (February)
monthlyDemand[7] → 540 (August, the semester-start spike)

This “zero-based indexing” is standard in most programming languages.

6.2.2 Accessing and Changing Array Values

You can read a value by referencing its index:

let january = monthlyDemand[0]

You can also change a value:

monthlyDemand[6] = 100   // revise July's demand to 100

Or add a new value — say, the first month of a new year:

monthlyDemand.push(500)

Arrays grow as needed — you don’t have to declare their size ahead of time.

6.2.3 Arrays Can Hold Any Type of Data

Arrays aren’t limited to one type of value. You can mix strings, numbers, and booleans — which is exactly what a catalog row looks like:

let row = ["classroom clicker", 120, 64800]

In practice, keep each kind of array consistent so your code stays predictable: a demand series is all numbers, a list of item names is all strings, and a catalog is a table of rows that each follow the same column pattern.

6.2.4 Common Mistakes to Watch For

Off-by-one errors. Because arrays start at index 0, a twelve-month series has indexes 0 through 11. Reaching past the end returns undefined:

let demand = [480, 240, 180, 300, 360, 120, 90, 540, 420, 240, 180, 450]
demand[12]   // undefined — there is no 13th month

Empty arrays. An array with no items still exists, but accessing any index returns undefined:

let empty = []
empty[0]   // undefined

Forgetting that arrays are ordered. When order matters — and for a demand history it absolutely does, since January must stay January — be careful adding or removing items.

6.2.5 Why Arrays Matter in Apps Script

Arrays become essential the moment you work with Google Sheets. For example, reading the clicker’s twelve months of demand from a column:

let sheet = SpreadsheetApp.getActiveSheet()
let range = sheet.getRange("B2:B13")
let values = range.getValues()

getValues() returns an array of arrays — an array of rows, where each row is itself an array. You’ll explore this in the next section, but the key idea is this: arrays are how Apps Script represents spreadsheet data. If you want to process a demand column or a catalog, you’ll be working with arrays.

6.2.6 What You Can Do With Arrays

By the end of this chapter, arrays will feel like second nature. You’ll use them to read a demand column or catalog into memory, transform values before writing them back, build new lists from existing ones, and represent an entire table at once. Arrays are the gateway to handling real data at scale — and they set up the arrays of arrays that Google Sheets hands you.

6.3 Arrays and Google Sheets

Arrays become truly useful once you start working with data from a sheet. Almost everything you read — a column of demand, a row of item details, or a whole catalog — comes back as an array. And when you read more than one cell at a time, Apps Script gives you an array of arrays, where each inner array is a single row.

6.3.1 `getValue()` vs. `getValues()`

These two methods behave very differently. getValue() returns a single value; getValues() returns an array of arrays.

let sheet = SpreadsheetApp.getActiveSheet()

let oneMonth = sheet.getRange("B2").getValue()        // one value

let allMonths = sheet.getRange("B2:B13").getValues()  // array of arrays

If you log allMonths, you’ll see the demand column as rows:

[
  [480],
  [240],
  [180],
  [300]
  // ...and so on, one row per month
]

Each inner array represents one row from the range.

6.3.2 Thinking in Rows and Columns

When you read a range like "A2:C2", you get one row, represented as an array:

["classroom clicker", 120, 64800]

When you read "A2:C4", you get three rows, represented as an array of arrays:

[
  ["classroom clicker", 120, 64800],
  ["whiteboard marker", 480, 9000],
  ["USB drive", 75, 42000]
]

This structure mirrors the spreadsheet: the outer array is all the rows, the inner arrays are each row, and the items inside each inner array are the cells in that row. Once you see the pattern, it becomes intuitive.

6.3.3 Extracting Values

In the next chapter we’ll use a “loop” to walk through an array elegantly, but for now let’s see how to reach individual values in an array of arrays. Suppose the store’s catalog sits in a sheet like this:

	A	B	C
2	classroom clicker	120	64800
3	whiteboard marker	480	9000
4	USB drive	75	42000

If you run this statement:

let catalog = sheet.getRange("A2:C4").getValues()

you’ll have a variable catalog holding an array of arrays:

[
  ["classroom clicker", 120, 64800],
  ["whiteboard marker", 480, 9000],
  ["USB drive", 75, 42000]
]

Here’s one way to pull the first row into individual variables:

let catalog = sheet.getRange("A2:C4").getValues()

let firstRow = catalog[0]              // ["classroom clicker", 120, 64800]
let item = firstRow[0]                 // "classroom clicker"
let onHand = firstRow[1]               // 120
let annualDollarVolume = firstRow[2]   // 64800

We assign firstRow to the first element of catalog. That element is itself an array, holding the item, its on-hand quantity, and its annual dollar volume. We can reach the same values without the intermediate variable:

let catalog = sheet.getRange("A2:C4").getValues()

let item = catalog[0][0]                 // "classroom clicker"
let onHand = catalog[0][1]               // 120
let annualDollarVolume = catalog[0][2]   // 64800

Similarly, you can pull a single column — say, every item name — by reading the first element of each inner array:

let catalog = sheet.getRange("A2:C4").getValues()

let first = catalog[0][0]    // classroom clicker
let second = catalog[1][0]   // whiteboard marker
let third = catalog[2][0]    // USB drive

You’re simply navigating the structure — outer array first, then inner array.

6.3.4 Why This Matters

Arrays are the format Google Sheets uses to give you data. Once you can read and reach values inside arrays of arrays, you can work with an entire demand history or catalog at once, extract specific rows or columns, and prepare data for transformation. This sets the stage for everything that comes next — soon you’ll reshape these arrays and write them back into the sheet.

6.4 Writing Arrays Back to the Worksheet

Now that you’ve seen how Sheets gives you data as arrays, the next step is writing data back. This is where arrays become truly powerful: you can prepare everything in memory and write it to the spreadsheet in a single, efficient operation. Apps Script provides two methods: setValue() writes a single value, and setValues() writes an array of arrays. Since this chapter is about arrays, we’ll work with setValues().

6.4.1 Writing a Single Row (One Inner Array)

To write one row, you pass a single inner array. Using a catalog row:

function writeOneRow() {
  let sheet = SpreadsheetApp.getActiveSheet()
  let row = ["classroom clicker", 120, 64800]

  sheet.getRange("A2:C2").setValues([row])
}

Notice that setValues() expects an array of arrays, even for one row. That’s why we wrap row in another pair of brackets.

6.4.2 Writing Multiple Rows (An Array of Arrays)

This is also how you’d write the result of a calculation back to the sheet — for instance, a reorder status for each item:

function writeReorderStatus() {
  let sheet = SpreadsheetApp.getActiveSheet()

  let results = [
    ["classroom clicker", "Reorder"],
    ["whiteboard marker", "Healthy"],
    ["USB drive", "Reorder"]
  ]

  sheet.getRange("E2:F4").setValues(results)
}

Each inner array is a row, and the outer array holds all the rows. As long as the range size matches the array size (3 rows × 2 columns here), Apps Script writes everything in one step.

6.4.3 Why This Matters

Writing data with setValues() is fast (one operation instead of many), clean (your script stays simple and readable), and reliable (the array’s structure matches the sheet’s). It’s the preferred way to write any non-trivial amount of data back into Google Sheets.

6.4.4 A Helpful Pattern to Remember

Whenever you want to write data:

Build an array of arrays in memory
Select a range with the same dimensions
Call setValues()

This pattern is the foundation of efficient spreadsheet automation — and notice what’s still missing. We can now hold the whole demand history and the whole catalog in arrays, but we’ve only reached into them one element at a time, by hand. To actually compute a forecast from twelve months of demand, or to run the reorder check on every item in the catalog, you need a way to step through an array automatically, one element after another. That is what loops do, and it’s where the next chapter begins.

menu6. Arrays

Search Book

6 Arrays

6.1 Why Arrays Matter in Apps Script

6.1.1 Arrays Hold Multiple Values in a Single Variable

6.1.2 Arrays Are How Google Sheets Returns Ranges

6.1.3 Arrays Make Your Scripts Faster and Cleaner

6.1.4 Arrays Help You Think in Terms of Data, Not Cells

6.1.5 What You’ll Learn in This Chapter

6.2 Understanding Arrays

6.2.1 What an Array Looks Like

6.2.2 Accessing and Changing Array Values

6.2.3 Arrays Can Hold Any Type of Data

6.2.4 Common Mistakes to Watch For

6.2.5 Why Arrays Matter in Apps Script

6.2.6 What You Can Do With Arrays

6.3 Arrays and Google Sheets

6.3.1 `getValue()` vs. `getValues()`

6.3.2 Thinking in Rows and Columns

6.3.3 Extracting Values

6.3.4 Why This Matters

6.4 Writing Arrays Back to the Worksheet

6.4.1 Writing a Single Row (One Inner Array)

6.4.2 Writing Multiple Rows (An Array of Arrays)

6.4.3 Why This Matters

6.4.4 A Helpful Pattern to Remember

Search Book

6 Arrays

6.1 Why Arrays Matter in Apps Script

6.1.1 Arrays Hold Multiple Values in a Single Variable

6.1.2 Arrays Are How Google Sheets Returns Ranges

6.1.3 Arrays Make Your Scripts Faster and Cleaner

6.1.4 Arrays Help You Think in Terms of Data, Not Cells

6.1.5 What You’ll Learn in This Chapter

6.2 Understanding Arrays

6.2.1 What an Array Looks Like

6.2.2 Accessing and Changing Array Values

6.2.3 Arrays Can Hold Any Type of Data

6.2.4 Common Mistakes to Watch For

6.2.5 Why Arrays Matter in Apps Script

6.2.6 What You Can Do With Arrays

6.3 Arrays and Google Sheets

6.3.1 getValue() vs. getValues()

6.3.2 Thinking in Rows and Columns

6.3.3 Extracting Values

6.3.4 Why This Matters

6.4 Writing Arrays Back to the Worksheet

6.4.1 Writing a Single Row (One Inner Array)

6.4.2 Writing Multiple Rows (An Array of Arrays)

6.4.3 Why This Matters

6.4.4 A Helpful Pattern to Remember

6.3.1 `getValue()` vs. `getValues()`