Work with JavaScript arrays

Select customers without changing their rows

Count and list active customers from an in-memory two-dimensional array. The customerRows literal matches the four-row array logged in Run your first Sheets script, using the same row-by-column shape that getValues() returns.

Instead of writing a manual loop as in Make decisions with functions and loops, you can pass a helper function to an array method that calls it for each row. The script locates required columns by header name, maps rows into objects, filters for active customers, and joins their names.

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: [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

Assuming every row's first element is a name works only while columns stay in that exact order. Searching the header row lets you locate columns by name instead.

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. In JavaScript, row[-1] looks for a property named "-1" rather than the last element, returning undefined.

The script checks each index for -1 before reading any data rows. throw new Error(...) raises an exception and stops the execution immediately so missing headers fail loudly instead of producing undefined fields.

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

Let a method call your function

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

The helper isActiveCustomer accepts one customer record and returns whether customer.Status === 'Active'. Calling customers.filter(isActiveCustomer) passes the function reference itself so filter can invoke it for each record. Writing isActiveCustomer() with parentheses would invoke the helper immediately with no arguments instead of passing it to filter.

For short transformations, you can write an arrow function inline. In (customer) => String(customer.Name), the parameter list precedes =>, and the expression after => is returned automatically. When an arrow function's body is enclosed in braces, it is a statement block and requires an explicit return statement 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.

Install and run the complete project

Install both functions in Code.gs and run summarizeActiveCustomers, which 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 and writes through Logger.log() without requiring 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.

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 a header-keyed record, downstream code can read customer.Status by name rather than tracking column indexes. Reordering whole columns in customerRows still maps the right values, whereas renaming a required header stops at the -1 guard.

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, and 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 would not affect customerRows (though an object cell value such as a Date would still share a reference). Likewise, when rows come from getValues(), transforming arrays in memory does not modify 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 verify the two-customer baseline.

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 '', logging 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, select summarizeActiveCustomers in the function menu instead so the helper receives each customer object from filter.

Keep Customer arrays for the next chapter, which uses it alongside your first Sheets project to understand the runtime and permissions.