getRange / getValues / setValues — and why batch operations matter

1. Every sheet call is a network trip

Your script runs on Google's servers; the spreadsheet is a separate service. Each getValue() or setValue() is a round trip. 1,000 rows × 2 calls = 2,000 trips, which can take minutes. Apps Script also stops any run after 6 minutes.

2. The slow way (don't do this)

function slowGST() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  for (let r = 2; r <= sh.getLastRow(); r++) {
    const amt = sh.getRange(r, 3).getValue();     // read 1 cell
    sh.getRange(r, 4).setValue(amt * 0.18);       // write 1 cell
  }
}

3. getValues → 2D array

const values = sh.getRange('A2:C5').getValues();
// [ ['Laptop', 12, 55000],
//   ['Mobile',  3, 18000],
//   ['Chair',   0,  4500],
//   ['Desk',    8, 12000] ]

• It's an array of rows; each row is an array of cells.
• Indexes start at 0: values[0][2] = first row, third column = 55000.
• Empty cells come back as '' (empty string). Dates come back as JavaScript Date objects.

4. The fast way: read once, process, write once

function fastGST() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const lastRow = sh.getLastRow();
  if (lastRow < 2) return;

  const data = sh.getRange(2, 1, lastRow - 1, 3).getValues();   // A2:C(last)
  const out = data.map(row => {
    const qty = Number(row[1]) || 0;
    const rate = Number(row[2]) || 0;
    const amount = qty * rate;
    return [amount, amount * 0.18, amount * 1.18];               // D, E, F
  });

  sh.getRange(2, 4, out.length, 3).setValues(out);                // one write
  sh.getRange('D1:F1').setValues([['Amount', 'GST', 'Total']]);
}

getRange(row, column, numRows, numColumns) is the most useful form: start cell + size.

5. The size rule for setValues

The array must match the range exactly: same number of rows, and every row with the same number of columns. Otherwise: "The number of rows in the data does not match the number of rows in the range."

A single row is still 2D: [['Amount', 'GST', 'Total']] — note the double brackets.

6. Filtering rows in memory

Low-stock items into a "Reorder" sheet:

function buildReorderList() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName('Data').getDataRange().getValues();
  const header = data.shift();                         // remove and keep row 1
  const low = data.filter(row => Number(row[1]) < 5);

  const out = ss.getSheetByName('Reorder') || ss.insertSheet('Reorder');
  out.clearContents();
  out.getRange(1, 1, 1, header.length).setValues([header]);
  if (low.length) out.getRange(2, 1, low.length, header.length).setValues(low);
}

getDataRange() = everything from A1 to the last row/column with data.

7. Batch formatting too

Many range methods have plural versions that take arrays: setBackgrounds, setFontColors, setNumberFormats. For a single format on a whole block, one call on the range is enough: range.setNumberFormat('#,##0').

Common mistakes

Calling getValue/setValue inside a loop. Mismatched array size in setValues. Forgetting arrays are 0-indexed while sheet rows/columns start at 1. Treating '' (empty) as 0 without Number().