Reading and writing sheets Updated November 2026

getActiveSheet vs getSheetByName

getActiveSheet() returns whichever tab the user has open, which is fine for menu buttons but wrong for triggers and scheduled scripts; getSheetByName("Data") returns a specific tab by its name (or null if it does not exist). In anything automated, use getSheetByName.

Syntax

SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name) | SpreadsheetApp.getActiveSheet()

Part	What it is
`getActiveSpreadsheet()`	The spreadsheet the script is bound to (or the user has open)
`getActiveSheet()`	The currently selected tab; null in standalone scripts with no open sheet
`getSheetByName(name)`	Tab with that exact name, case-sensitive; null when missing
`getSheets()`	Array of all tabs in order

Examples

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Orders");
Logger.log(sheet.getLastRow());

Result: Last row of the Orders tab regardless of which tab is open

const sheet = ss.getSheetByName("Archive") || ss.insertSheet("Archive");

Result: Gets Archive, creating it if it is missing

ss.getSheets().forEach(s => Logger.log(s.getName() + ": " + s.getLastRow() + " rows"));

Result: Orders: 120 rows, Archive: 0 rows, Settings: 8 rows

function onEdit(e) {
  if (e.range.getSheet().getName() !== "Orders") return;
  // handle edits on Orders only
}

Result: The trigger ignores edits on other tabs

Common mistakes

Cannot read properties of null (reading getRange): getSheetByName returned null because the tab name differs (trailing space, different case), so copy the name exactly from the tab.
A time-driven trigger writes into a random tab: getActiveSheet has no user when run by a trigger, so switch to getSheetByName.
Standalone script throws on getActiveSpreadsheet: there is no active spreadsheet; use SpreadsheetApp.openById(id).
Script breaks after someone renames a tab: store the tab name in one constant at the top, or find the sheet by its id with getSheetId.

Practice getActiveSheet vs getSheetByName

These drills check your answer on live data, free, no account needed.

Pick some keysReturn a new object with only the listed `keys` of `obj`. Skip keys that `obj` does not have, but keep keys it has even when the value is `0`, `false`, `null`, `""` or `undefined`. Do not change `obj`.

Questions

How do I get the sheet by index?

ss.getSheets()[0] is the first tab in the visible order. Index changes when tabs are moved, so prefer names for anything permanent.

How do I make a sheet active?

ss.setActiveSheet(sheet) switches the user's view; it is only needed for UI, not for reading or writing.

getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.
Copy data between sheetsCopy data between sheets or spreadsheets with Apps Script: range.copyTo for formats and formulas, getValues and setValues across files, copy a whole tab.
onEdit triggerUse the onEdit trigger in Google Apps Script: the event object with range, value and oldValue, limiting it to one sheet or column, timestamps, installable.
Time-driven triggersSchedule Google Apps Script with time-driven triggers: create them in the Triggers panel or with ScriptApp.newTrigger, run daily, hourly or weekly, delete.