Basic · B3
Arrays, objects and loops
A sheet is a grid, and a grid in code is a list of lists. This chapter gives you the tools to hold many values at once and to go through them one by one. Master the 2D array tab and the next chapter is easy.
Arrays: ordered lists
An array holds values in order inside square brackets. Each value has an index, and the first index is 0.
function arrays() {
const names = ['Ploy', 'Ben', 'Mint'];
console.log(names[0]); // first item: index 0
console.log(names.length); // 3
names.push('Tee'); // add to the end
console.log(names);
console.log(names.indexOf('Mint')); // 2
console.log(names.includes('Nok')); // false
console.log(names.join(', ')); // one string
}Execution log
Ploy 3 [ 'Ploy', 'Ben', 'Mint', 'Tee' ] 2 false Ploy, Ben, Mint, Tee
| Method | Does |
|---|---|
push(x) / pop() | Add to / remove from the end |
length | How many items (not a method, so no brackets) |
indexOf(x) | Position of x, or -1 if it isn't there |
includes(x) | true or false |
slice(a, b) | A copy of part of the array |
join(', ') | Turn the array into one string |
sort() / reverse() | Reorder (they change the array itself) |
Objects: named fields
An array is good for "many of the same thing". An object is good for "one thing with several facts", like a student record. Each fact is a key: value pair.
function objects() {
const student = {
name: 'Ploy',
room: 'M.5/3',
marks: [72, 88, 91],
};
console.log(student.name); // dot notation
console.log(student['room']); // bracket notation
student.email = 'ploy@school.example'; // add a key
console.log(Object.keys(student));
console.log(student.marks[1]); // 88
}Execution log
Ploy M.5/3 [ 'name', 'room', 'marks', 'email' ] 88
Use dot notation (student.name) normally. Use brackets (student['First name']) when the
key has a space or is in a variable. Sheet column titles often have spaces, so you will need both.
Everything in Apps Script is an object
SpreadsheetApp, a sheet, a range and an email thread are all objects. Their methods are functions
stored as keys. That is why the code reads sheet.getRange('A1').
Three ways to repeat
function loops() {
const marks = [72, 88, 91, 45];
// 1. The classic for loop: you control the counter.
for (let i = 0; i < marks.length; i++) {
console.log(`Student ${i + 1}: ${marks[i]}`);
}
// 2. for...of: just the values, no counter.
let total = 0;
for (const m of marks) {
total += m;
}
console.log('Total', total);
// 3. forEach: a function runs for every item.
marks.forEach((m, i) => {
if (m < 50) console.log(`Student ${i + 1} needs help`);
});
}Execution log
Student 1: 72 Student 2: 88 Student 3: 91 Student 4: 45 Total 296 Student 4 needs help
| Loop | Choose it when |
|---|---|
for (let i = 0; i < n; i++) | You need the index, want to skip rows, or go backwards |
for (const x of list) | You just want each value, and may need break |
list.forEach((x, i) => …) | Short work on every item; gives value and index |
while (condition) | You don't know how many times, e.g. reading a Drive file iterator |
Off by one
i < marks.length, not <=. With 4 items the last index is 3. Going one too far gives
undefined, and in later chapters writes into an empty row.
Array methods that replace loops
Most loops over sheet data do one of four jobs. Each has its own method, which is shorter and harder to get wrong:
function mapFilterReduce() {
const marks = [72, 88, 91, 45, 60];
const plusFive = marks.map((m) => m + 5); // change every item
const passed = marks.filter((m) => m >= 60); // keep some items
const total = marks.reduce((sum, m) => sum + m, 0); // boil down to one
const top = marks.find((m) => m > 90); // first match
console.log(plusFive);
console.log(passed);
console.log(total, total / marks.length);
console.log(top);
}Execution log
[ 77, 93, 96, 50, 65 ] [ 72, 88, 91, 60 ] 356 71.2 91
map
Same number of items, each one changed. "Add 5 to every mark."
filter
Fewer items, unchanged. "Only the students who passed."
reduce
One value from many. "The total of all marks."
find
The first item that matches, or undefined. "The student called Ben."
The (m) => m + 5 part is an arrow function from B2. map and filter call it once per item and use
what it returns.
A sheet is a 2D array
When you read a block of cells in the next chapter, Apps Script gives you an array of rows, and each row is an array of cells. This sheet…
| A | B | C | |
|---|---|---|---|
| 1 | Name | Room | Mark |
| 2 | Ploy | M.5/3 | 72 |
| 3 | Ben | M.5/1 | 88 |
| 4 | Mint | M.5/3 | 45 |
…becomes this array. Notice that sheet row 3 is data[2], because code counts from 0 and sheets count from 1.
function twoD() {
// This is exactly how a sheet arrives in your code:
// an array of rows, and every row is an array of cells.
const data = [
['Name', 'Room', 'Mark'],
['Ploy', 'M.5/3', 72],
['Ben', 'M.5/1', 88],
['Mint', 'M.5/3', 45],
];
console.log(data[2][0]); // row 2, column 0 → Ben
console.log(data.length); // 4 rows (header included)
const header = data[0];
const rows = data.slice(1); // everything except the header
rows.forEach((row) => {
const [name, room, mark] = row; // unpack the row
console.log(`${name} (${room}) scored ${mark}`);
});
console.log(header.indexOf('Mark')); // 2: find a column by its title
}Execution log
Ben 4 Ploy (M.5/3) scored 72 Ben (M.5/1) scored 88 Mint (M.5/3) scored 45 2
The pattern you will use in almost every script
- Read the whole table into an array in one go.
- Split off the header row with
slice(1). - Loop, filter or map over the rows in JavaScript.
- Write the results back in one go.
Working in memory like this is hundreds of times faster than touching cells one at a time.
Practice
1. FizzBuzz Easy
Print 1 to 15, but "Fizz" for multiples of 3, "Buzz" for multiples of 5 and "FizzBuzz" for both.
Show a solution
function fizzBuzz() {
for (let i = 1; i <= 15; i++) {
if (i % 15 === 0) console.log('FizzBuzz');
else if (i % 3 === 0) console.log('Fizz');
else if (i % 5 === 0) console.log('Buzz');
else console.log(i);
}
}2. Longest name Easy
Given ['Ploy', 'Benjamin', 'Mint', 'Thanawat'], print the longest name.
Show a solution
function longestName() {
const names = ['Ploy', 'Benjamin', 'Mint', 'Thanawat'];
let longest = '';
for (const n of names) {
if (n.length > longest.length) longest = n;
}
console.log(longest); // Benjamin (the first of the two 8-letter names)
}3. Room average Medium
From a 2D array of Name, Room, Mark, print the average mark for M.5/3 only. Use filter, map and reduce.
Show a solution
function averageForRoom() {
const data = [
['Name', 'Room', 'Mark'],
['Ploy', 'M.5/3', 72],
['Ben', 'M.5/1', 88],
['Mint', 'M.5/3', 45],
['Tee', 'M.5/3', 90],
];
const marks = data.slice(1).filter((r) => r[1] === 'M.5/3').map((r) => r[2]);
const avg = marks.reduce((s, m) => s + m, 0) / marks.length;
console.log(`M.5/3 average: ${avg.toFixed(1)}`); // M.5/3 average: 69.0
}4. Rows to objects Challenge
Turn the 2D array (header + rows) into an array of objects whose keys are the header titles. This trick lets later code say s.Mark instead of row[2].
Show a solution
function rowsToObjects() {
const data = [
['Name', 'Room', 'Mark'],
['Ploy', 'M.5/3', 72],
['Ben', 'M.5/1', 88],
];
const [header, ...rows] = data;
const students = rows.map((row) => {
const obj = {};
header.forEach((title, i) => (obj[title] = row[i]));
return obj;
});
console.log(students);
// [ { Name: 'Ploy', Room: 'M.5/3', Mark: 72 },
// { Name: 'Ben', Room: 'M.5/1', Mark: 88 } ]
}