Dynamic data block
const lastRow = sheet.getLastRow(); sheet.getRange(2, 1, lastRow - 1, 6).getValues() skips the header when lastRow is accurate.
What is getRange in Google Sheets Apps Script?
getRange returns a Range object pointing at one or more cells on a sheet. You call it on a Sheet to read with getValue or getValues and write with setValue or setValues. Correct range targeting is the foundation of almost every Sheets script.
When to use it
Use getRange whenever your script touches specific cells: read a settings block, format a header row, update a status column, or grab the active table before sorting.
When to skip it
Skip hard-coded A1 addresses when a named range or dynamic getLastRow sizing will survive column inserts. Fragile literals like D:D break when someone adds a column left of D.
How it works
-
1
On a sheet, call getRange('B2:F100') with A1 notation for a fixed block.
-
2
Or getRange(row, column, numRows, numColumns) with 1-based indexes for dynamic sizing.
-
3
getRange('A:A') selects a full column. getDataRange() selects the current data rectangle.
-
4
Named ranges use spreadsheet.getRangeByName('InvoiceTotal') for stable references.
-
5
Chain methods like getRange(2, 1, lastRow - 1, 5) after getLastRow for data-only areas.
-
6
Active selection uses SpreadsheetApp.getActiveRange() when the user highlighted cells.
Examples in Google Sheets
Named range sort
spreadsheet.getRangeByName('OrdersTable').sort({column: 4, ascending: false}) keeps sort logic stable when the name is maintained.
Format header
sheet.getRange(1, 1, 1, sheet.getLastColumn()).setFontWeight('bold') formats only the header row width.
Build this without starting from a blank cell
Use a Better Sheets tool for getRange, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Off-by-one in getRange(row, col, numRows, numCols) by passing end row instead of row count.
- getRange on the spreadsheet object vs sheet object confusion; most calls need a Sheet first.
- Assuming getLastRow includes empty rows in the middle of data; it only helps for contiguous blocks from row 1.
- Hard-coding Sheet1 when the tab was renamed, causing null reference errors.
- getRange larger than the sheet limits without splitting writes.
Frequently asked questions
- What is A1 notation vs numeric getRange?
- A1 uses letters and numbers like C5:E10. Numeric form uses start row, start column, number of rows, and number of columns, all 1-based.
- How do I get the selected range?
- SpreadsheetApp.getActiveRange() returns what the user selected. It can be null if nothing is selected.
- Can getRange span multiple sheets?
- No. Each range belongs to one sheet. Read each tab separately and combine in code.
- What is getRangeList?
- getRangeList accepts multiple A1 strings on one sheet for batch format operations on non-contiguous areas.
- How do named ranges work in scripts?
- Define names in the Sheets UI or script, then getRangeByName on the spreadsheet. Renaming in the UI updates the script reference.
- Why does getRange return null?
- getSheetByName failed because the tab name changed, or the range string is invalid.
- Can I getRange a whole row?
- Yes. getRange('5:5') selects row 5. Full column is getRange('C:C').
- Is getRange the same as getActiveCell?
- getActiveCell is one cell. getActiveRange can be multiple cells the user highlighted.
Related Tutorials
Watch how getRange works
Deep Inside Dark Habits Google Script
Add Tasks to Google Tasks From Google Sheets
How do I reference a different spreadsheet in Apps Script?
Embed a Number in a Website from a Google Sheet
Automate Google Sheets With Zero Experience
Scrape Google Maps
Related blog posts
Guides that explain getRange in more depth.
How to reference a different Google Spreadsheet in Apps Script?
Transferring Data Between Cells in Different Spreadsheets
Read post →Extract URLs from Google Sheets
Extract url from hyperlink google sheets.Skip the Command K. This Google Sheet tutorial will make it easier for you to extract URLS from Google Sheets.
Read post →Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users
If you know how to code, you'll learn in this step-by-step tutorial how to code in Google Sheets.
Read post →Related glossary terms
Script Editor
The Script Editor is the Apps Script workspace inside your spreadsheet where you write JavaScript that talks to Sheets, Gmail, and other Google services. You open it from Extensions > Apps Script. Every function you save can be run manually, bound to a trigger, or linked from a custom menu.
Read guide →Apps Script loops
Loops in Apps Script let you repeat code over rows, columns, sheets, or API pages. You read ranges into arrays with getValues(), process each item in a for or forEach loop, then write results back in one setValues() call when possible. Batch reads and writes beat cell-by-cell updates for speed and quota limits.
Read guide →Google Sheets automation
Automation means work happens without repeating the same clicks every day: imports update, emails send, rows move, and dashboards refresh. In Sheets, automation usually stacks built-in features, Apps Script, and sometimes the Sheets API or add-ons. Start with the lightest tool that still solves the job.
Read guide →Google Sheets workflows
A workflow is the path work takes through your sheet: intake, review, approval, done. Good workflows use consistent columns, statuses everyone understands, and just enough automation to move tasks forward without hiding steps from the team.
Read guide →Done reading about getRange?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.