Basic · B4

Reading and writing Sheets

Now JavaScript meets the spreadsheet. You will find a sheet, point at cells, read them into a 2D array and write results back. This is the core of nearly every Apps Script project.

Make the practice sheet

Every example in this chapter uses the same small sheet. Make it first:

Create a new Google Sheet called Class Marks.
Rename the first tab (double-click "Sheet1") to Marks.
Type this table starting in A1:

	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

Open Extensions → Apps Script and name the project Marks scripts.

If you break the sheet while experimenting, just retype the table. Press Ctrl + Z in the Sheet to undo what a script wrote.

Next: the hierarchy →

Spreadsheet → Sheet → Range

Apps Script mirrors what you see on screen:

Spreadsheet

The whole file. SpreadsheetApp.getActiveSpreadsheet()

Sheet

One tab. ss.getSheetByName('Marks') or ss.getActiveSheet()

Range

One cell or a block. sheet.getRange('A1:C4')

Code.gs

function hierarchy() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();   // the whole file
  const sheet = ss.getSheetByName('Marks');            // one tab
  const range = sheet.getRange('A1:C4');               // a block of cells

  console.log(ss.getName());
  console.log(sheet.getName());
  console.log(range.getA1Notation());
  console.log(ss.getSheets().map((s) => s.getName()));
}

Execution log

Class Marks
Marks
A1:C4
[ 'Marks' ]

getActiveSheet or getSheetByName?

getActiveSheet() means "whichever tab is open right now", which changes when someone clicks another tab. For anything that matters, use getSheetByName('Marks'). It returns null if there is no such tab, which is how you check that a tab exists.

Next: ranges →

Four ways to point at cells

Code.gs

function fourWaysToGetARange() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');

  const a = sheet.getRange('B2');          // A1 notation, one cell
  const b = sheet.getRange('A2:C4');       // A1 notation, a block
  const c = sheet.getRange(2, 3);          // row 2, column 3 → C2
  const d = sheet.getRange(2, 1, 3, 3);    // start row 2, col 1, 3 rows, 3 cols → A2:C4

  console.log(a.getValue());               // M.5/3
  console.log(c.getValue());               // 72
  console.log(d.getA1Notation());          // A2:C4
  console.log(b.getNumRows(), b.getNumColumns());  // 3 3
}

Execution log

M.5/3
72
A2:C4
3 3

Form	Means	Best for
`getRange('B2')`	A1 notation	Fixed cells you can see
`getRange(row, col)`	One cell by numbers	Inside loops
`getRange(row, col, numRows)`	A column strip	One column, many rows
`getRange(row, col, numRows, numCols)`	A block	Writing a 2D array back
`getDataRange()`	Everything from A1 to the last cell with data	Reading the whole table

The third and fourth numbers are how many rows and columns, not where the range ends. getRange(2, 1, 3, 3) starts at A2 and is 3 × 3, so it ends at C4.

Next: reading →

Reading values

getValue() returns the top-left cell's value: a string, number, boolean or Date.
getValues() returns a 2D array, always, even for one row or one column.
getDisplayValue(s) returns the text as shown, with number formatting (e.g. "72.0%").
getLastRow() / getLastColumn() give the last row and column that contain data.

Code.gs

function readAll() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const data = sheet.getDataRange().getValues();   // everything that has data

  console.log(data);
  console.log(`Rows: ${sheet.getLastRow()}, columns: ${sheet.getLastColumn()}`);

  const [header, ...rows] = data;
  for (const [name, room, mark] of rows) {
    console.log(`${name} → ${mark >= 50 ? 'pass' : 'fail'}`);
  }
}

Execution log

[ [ 'Name', 'Room', 'Mark' ],
  [ 'Ploy', 'M.5/3', 72 ],
  [ 'Ben', 'M.5/1', 88 ],
  [ 'Mint', 'M.5/3', 45 ] ]
Rows: 4, columns: 3
Ploy → pass
Ben → pass
Mint → fail

const [header, ...rows] = data takes the first row into header and the rest into rows. Inside the loop, [name, room, mark] unpacks each row into named variables, so you don't have to remember that the mark is row[2].

Next: writing →

Writing values

Code.gs

function writeExamples() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');

  // One cell.
  sheet.getRange('G1').setValue('Updated');

  // A block: the 2D array must be the same shape as the range.
  const table = [
    ['Grade', 'Pass?'],
    ['C', true],
    ['A', true],
    ['F', false],
  ];
  sheet.getRange(1, 4, table.length, table[0].length).setValues(table);

  // A new row under the last one.
  sheet.appendRow(['Tee', 'M.5/2', 90, 'A', true]);

  // A formula, written as text that starts with =.
  sheet.getRange('C7').setFormula('=AVERAGE(C2:C5)');
}

	A	B	C	D	E	G
1	Name	Room	Mark	Grade	Pass?	Updated
2	Ploy	M.5/3	72	C	TRUE
3	Ben	M.5/1	88	A	TRUE
4	Mint	M.5/3	45	F	FALSE
5	Tee	M.5/2	90	A	TRUE
6
7			73.75

The Marks tab after the run. Highlighted cells were written by the script. (The grades in this example are typed in by hand; the next example works them out.)

Read, compute, write

Put it together: read the Mark column, work out a grade for each row in memory, and write the whole Grade column at once.

Code.gs

function addGrades() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const lastRow = sheet.getLastRow();
  // Read only the Mark column, rows 2..lastRow.
  const marks = sheet.getRange(2, 3, lastRow - 1, 1).getValues();  // [[72], [88], [45]]

  const grades = marks.map(([m]) => {
    if (m >= 80) return ['A'];
    if (m >= 70) return ['B'];
    if (m >= 60) return ['C'];
    if (m >= 50) return ['D'];
    return ['F'];
  });

  sheet.getRange(1, 4).setValue('Grade');
  sheet.getRange(2, 4, grades.length, 1).setValues(grades);  // one write for all rows
}

Column D after the run (start from the original 4-row sheet)

D1 Grade
D2 B
D3 A
D4 F

setValues needs [[…]], even for one column

A column of grades must be [['B'], ['A'], ['F']], one array per row. Writing ['B', 'A', 'F'] gives a "parameters don't match" error. That is why the map returns ['A'], not 'A'.

Next: fast and slow →

Batch your reads and writes

Every getValue/setValue goes over the network to Google's servers and back. Both functions below do the same job. On 1,000 rows the slow one can take minutes; the fast one takes about a second.

Code.gs

// SLOW: one call to Google per cell. 500 rows ≈ 500 round trips.
function slowWay() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let r = 2; r <= sheet.getLastRow(); r++) {
    const mark = sheet.getRange(r, 3).getValue();
    sheet.getRange(r, 4).setValue(mark * 2);
  }
}

// FAST: one read, work in memory, one write.
function fastWay() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const n = sheet.getLastRow() - 1;
  const marks = sheet.getRange(2, 3, n, 1).getValues();
  const doubled = marks.map(([m]) => [m * 2]);
  sheet.getRange(2, 4, n, 1).setValues(doubled);
}

Rule of thumb

Never call a get… or set… method on a Range inside a loop over rows. Read before the loop, change the array, and write after the loop. Scripts also stop after 6 minutes, so slow code doesn't just waste time, it fails halfway.

Next: practice →

Practice

1. Times table Easy

On an empty tab, fill A1:L12 with a 12 × 12 times table. Build the 2D array with two loops, then write it with one setValues.

Show a solution

Code.gs

function timesTable() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const table = [];
  for (let r = 1; r <= 12; r++) {
    const row = [];
    for (let c = 1; c <= 12; c++) row.push(r * c);
    table.push(row);
  }
  sheet.getRange(1, 1, 12, 12).setValues(table);
}

2. Visit log Easy

Each run adds a row with the date and your email to a tab called Log. Create the tab and a header row if they don't exist yet.

Show a solution

Code.gs

function logVisit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Log') || ss.insertSheet('Log');
  if (sheet.getLastRow() === 0) sheet.appendRow(['When', 'Who']);
  sheet.appendRow([new Date(), Session.getActiveUser().getEmail()]);
}

3. Top student Medium

Find the student with the highest mark and write "Top student" in G1 and their name in H1.

Show a solution

Code.gs

function topStudent() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const rows = sheet.getDataRange().getValues().slice(1);
  let best = rows[0];
  for (const row of rows) {
    if (row[2] > best[2]) best = row;
  }
  sheet.getRange('G1:H1').setValues([['Top student', best[0]]]);
}

4. Copy the passes Challenge

Copy the header and every row with a mark of 50 or more into a tab called Passed. Running it twice must not double the rows.

Show a solution

Code.gs

function copyPassed() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName('Marks').getDataRange().getValues();
  const [header, ...rows] = data;
  const passed = rows.filter((r) => r[2] >= 50);

  const out = ss.getSheetByName('Passed') || ss.insertSheet('Passed');
  out.clearContents();
  const table = [header, ...passed];
  out.getRange(1, 1, table.length, header.length).setValues(table);
}

Next: quiz →

Check yourself

1. What does sheet.getRange(3, 2) refer to?

2. getValues() on A1:C1 returns…

3. setValues fails with "The number of rows in the data does not match the number of rows in the range". What is wrong?

4. Which method adds a row after the last row with data?

5. An empty cell read with getValue() gives…

6. Why is reading the whole range once faster than reading cell by cell in a loop?