SpreadsheetApp: sheets, ranges, getValues/setValues

Concept

The hierarchy

SpreadsheetApp            ← the service
 └─ Spreadsheet (file)    ← getActiveSpreadsheet() / openById()
     └─ Sheet (tab)       ← getSheetByName('Sales')
         └─ Range (cells) ← getRange('A1:C10')

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Sales');
const range = sheet.getRange('A1:C10');

Prefer getSheetByName over getActiveSheet() — the "active" tab is whatever the user happens to be looking at, and triggers have no active tab.

Ways to select a range

sheet.getRange('B2');            // one cell, A1 notation
sheet.getRange('A2:D20');        // a block
sheet.getRange(2, 1);            // row 2, column 1 → A2
sheet.getRange(2, 1, 19, 4);     // start row, start col, number of rows, number of cols → A2:D20
sheet.getDataRange();            // everything from A1 to the last used cell

The 4-number form is the one you'll use most in code, because you can calculate the numbers:

const lastRow = sheet.getLastRow();          // last row that has data
const data = sheet.getRange(2, 1, lastRow - 1, 4).getValues(); // skip header

Reading values

const one = sheet.getRange('B2').getValue();          // a single value
const many = sheet.getRange('A2:D20').getValues();    // a 2D array [[...],[...]]
const shown = sheet.getRange('C2').getDisplayValue(); // text exactly as shown, e.g. '₹1,200.00'
const formula = sheet.getRange('E2').getFormula();    // e.g. '=C2*D2'

getValues() always returns a 2D array — even for a single column ([['a'],['b'],['c']]).

Writing values

sheet.getRange('F1').setValue('Total');

sheet.getRange('F2:F4').setValues([
  [100],
  [200],
  [300]
]);

sheet.getRange('E2').setFormula('=C2*D2');

Golden rule: the array you pass to setValues must be exactly the same size as the range. 3 rows × 1 column needs [[x],[y],[z]].

Appending a row

function addSale() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
  sheet.appendRow([new Date(), 'Gold Ring', 1, 48500]);
}

appendRow adds after the last row with data. It's perfect for logs and form-style entries.

Clearing

sheet.getRange('A2:D100').clearContent(); // values only, keeps formatting
sheet.getRange('A2:D100').clear();        // values + formatting

Worked example: calculate line totals

Sheet Sales has headers in row 1: Date | Item | Qty | Rate. Add Amount in column E.

function fillAmounts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return; // no data

  const rows = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
  const amounts = rows.map(r => [r[2] * r[3]]); // each output row is an array with one value

  sheet.getRange('E1').setValue('Amount');
  sheet.getRange(2, 5, amounts.length, 1).setValues(amounts);
}

Key points

Spreadsheet → Sheet → Range. Get tabs by name.
getRange(row, col, numRows, numCols) is the coding-friendly form.
getValues / setValues work with 2D arrays of matching size.

What you'll learn