Work with JavaScript arrays

Select customers without changing their rows

Count and list active customers from a two-dimensional array held in memory. You need the Apps Script editor and log from Run your first Sheets script and the helper functions from Make decisions with functions and loops. The customerRows literal matches the four-row array logged in the first Sheets script, in the same row-by-column shape that getValues() returns.

Instead of writing a loop yourself, you pass a helper function to an array method, and the method calls the helper for each row. The script finds the required columns by header name, turns each row into an object, keeps the active customers, and joins their names.

Install and run the complete project

Install both functions in Code.gs and run summarizeActiveCustomers. That function supplies the input array and calls isActiveCustomer.

function summarizeActiveCustomers() {
  const customerRows = [
    ['Name', 'Email', 'Status'],
    ['Avery Quinn', 'avery@example.com', 'Active'],
    ['Morgan Lee', 'morgan@example.com', 'Paused'],
    ['Riley Park', 'riley@example.com', 'Active']
  ];

  const headers = customerRows[0];
  const nameIndex = headers.indexOf('Name');
  const emailIndex = headers.indexOf('Email');
  const statusIndex = headers.indexOf('Status');
  if (statusIndex === -1) {
    throw new Error('Add a Status header before running this function.');
  }
  if (nameIndex === -1) {
    throw new Error('Add a Name header before running this function.');
  }
  if (emailIndex === -1) {
    throw new Error('Add an Email header before running this function.');
  }

  const dataRows = customerRows.slice(1);
  const customers = dataRows.map((row) => {
    return {
      Name: row[nameIndex],
      Email: row[emailIndex],
      Status: row[statusIndex]
    };
  });
  const activeCustomers = customers.filter(isActiveCustomer);
  const activeNames = activeCustomers.map((customer) => String(customer.Name));

  Logger.log('Active customers: ' + activeNames.length);
  Logger.log(activeNames.join(', '));
}

/**
 * @param {{Name: string, Email: string, Status: string}} customer
 * @returns {boolean}
 */
function isActiveCustomer(customer) {
  return customer.Status === 'Active';
}

The project manifest sets the V8 runtime and UTC time zone:

{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

1. At script.google.com, create a new standalone project named Customer arrays.

2. Replace the default Code.gs contents with the complete example file, including isActiveCustomer.

3. In Project Settings, select Show "appsscript.json" manifest file in editor. Return to Editor, replace appsscript.json with the included manifest, and save both files.

4. Select summarizeActiveCustomers, click Run, and open Execution log.

This manual execution runs as the signed-in user at the keyboard. It writes to the execution log through Logger.log() and does not require Google service consent. See Google's authorization guide.

Expect these two log messages:

Active customers: 2
Avery Quinn, Riley Park

Avery and Riley have status Active. Morgan has status Paused and is excluded from the summary.

Read both levels of the array

An array is an ordered collection of values written in square brackets. In customerRows, the outer array contains four inner row arrays: one header row followed by three customer rows.

Array indexes start at zero. customerRows[0] selects the first row, and a second pair of brackets selects a cell inside that row:

Because indexes start at 0, a four-row array has valid row indexes 0 through 3. Reading customerRows[4] returns undefined, and indexing a column on that result throws a TypeError. MDN's Array reference explains zero-based indexing and the .length property.

Spreadsheet row and column numbers start at 1, whereas JavaScript arrays start at 0. In the array that getValues() returns, [0][0] is always the top-left cell of the range. Range.getValues() returns cells as strings, numbers, booleans, or Date objects, with '' for blank cells.

Use the headers to find the columns

Reading row[0] as the name works only while the columns stay in that order. Searching the header row finds each column by name, so the script keeps working when someone moves a column.

headers.indexOf('Status') returns the first matching index (2), while the Name and Email lookups return 0 and 1. indexOf() returns -1 when a value is not found. row[-1] looks for a property named "-1" and returns undefined. It does not select the last element.

The script checks each index for -1 before reading any data rows. If a header is missing, throw new Error(...) stops the run with an error message before the script builds any records with undefined fields.

Header matching is exact: 'Status', 'status', and 'Status ' are distinct strings. If a header appears more than once, indexOf returns the first matching position.

Let a method call your function

A callback is a function passed as an argument to another function.

The helper isActiveCustomer takes one customer record and returns whether customer.Status === 'Active'. customers.filter(isActiveCustomer) passes the function itself, without parentheses, so filter can call it for each record. Writing isActiveCustomer() with parentheses would call the helper once, immediately, with no arguments, and pass its result to filter.

For short transformations, you can write an arrow function inline. In (customer) => String(customer.Name), the parameter list comes before =>, and the expression after => is returned automatically.

When an arrow function's body is wrapped in braces, it is a block of statements and needs an explicit return to return a value. In the dataRows.map(...) callback, the outer braces form the function body, and the inner braces after return form the object literal. See MDN's arrow function reference.

Turn row positions into named records

customerRows.slice(1) returns a new array starting at index 1, skipping the header row without modifying customerRows.

dataRows.map(...) calls its callback once per data row and collects the returned objects into a new array of the same length. Using the header indexes found earlier, the first row becomes:

{
  "Name": "Avery Quinn",
  "Email": "avery@example.com",
  "Status": "Active"
}

Once each row is an object keyed by header name, later code reads customer.Status without tracking column indexes. Reordering whole columns in customerRows still maps the right values. Renaming a required header stops the run at the -1 check.

Next, customers.filter(isActiveCustomer) returns a new array containing only the records for which isActiveCustomer returns true, preserving their relative order.

Finally, activeCustomers.map(...) extracts the Name strings, activeNames.length counts the active records, and join(', ') formats them into a comma-separated string.

Know what the new arrays share

slice and filter create shallow copies. dataRows is a new outer array, but its elements still reference the inner row arrays of customerRows. Likewise, activeCustomers shares the selected record objects with customers. See MDN's array copying explanation.

In dataRows.map(...), each callback returns a fresh object whose properties are primitive strings, so changing Status on a mapped record does not affect customerRows. An object cell value such as a Date would still be shared. In the same way, when rows come from getValues(), changing arrays in memory does not change the spreadsheet until you pass an array to a write method such as setValues().

Change a status, then restore it

1. Change Morgan's status in customerRows from 'Paused' to 'Active'.

2. Predict the result before running: map still builds three customer records, and filter now keeps all three in order.

3. Save and run summarizeActiveCustomers. Expect:

Active customers: 3
Avery Quinn, Morgan Lee, Riley Park

4. Restore Morgan's status to 'Paused', save, and rerun to confirm two active customers again.

To test the header check, change the header 'Status' to 'State'. Running summarizeActiveCustomers throws Add a Status header before running this function. before logging any summary. Restore 'Status' afterward.

To test column-order independence, swap the first two elements in every row, including the header row, so Email precedes Name. Because nameIndex updates to 1, the logged summary still lists customer names. Restore all four rows when finished.

Diagnose a surprising summary

When no row has status 'Active', filter returns [] and join(', ') returns '', so the script logs Active customers: 0 followed by an empty line. Check capitalization and trailing spaces first: isActiveCustomer requires the exact string 'Active'.

If running isActiveCustomer directly throws an error while reading customer.Status, the editor passed it no customer. Select summarizeActiveCustomers in the function menu instead, so filter passes each customer object to the helper.

Keep Customer arrays for the next chapter, where you compare it with your first Sheets project to understand the runtime and permissions.