Summary on open
onOpen uses SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard') and sets A1 to today's date.
What is SpreadsheetApp in Google Sheets?
SpreadsheetApp is the main Apps Script service for talking to Google Sheets. It lets your script open the active spreadsheet, grab sheets by name, read cells, write values, and change formatting. Almost every Sheets automation starts with SpreadsheetApp.getActiveSpreadsheet().
When to use it
Use SpreadsheetApp whenever your script reads or writes grid data: custom menus, triggers, web apps, mail merges, or batch updates that formulas cannot handle cleanly.
When to skip it
Skip SpreadsheetApp when you only need to send email, fetch a URL, or store a setting with no sheet access. Other services like MailApp or PropertiesService handle those jobs.
How it works
-
1
Call SpreadsheetApp.getActiveSpreadsheet() in a bound script tied to the open file.
-
2
Use getSheetByName('TabName') or getSheets() to target the right tab.
-
3
Get a range with getRange('A1:D10') or getActiveRange(), then read or write values.
-
4
Open another file by ID with openById when the script is authorized for that spreadsheet.
-
5
Flush changes with SpreadsheetApp.flush() when you need pending writes visible before the next step.
-
6
Check Executions and use Logger.log while testing so errors in range names are obvious.
Examples in Google Sheets
Copy rows to archive
Read all values from the Data tab, filter rows where status is Closed, and append them to an Archive sheet in the same file.
Open a master workbook
SpreadsheetApp.openById('abc123') opens a shared master sheet and reads a lookup table into an array for validation.
Build this without starting from a blank cell
Use a Better Sheets tool for SpreadsheetApp, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Calling getActiveSpreadsheet() in a standalone script with no active sheet context.
- Using getSheetByName without checking for null when the tab was renamed.
- Reading the entire sheet with getDataRange() on huge tabs, slowing every run.
- Hard-coding spreadsheet IDs in many places instead of one Settings tab or PropertiesService.
- Forgetting that openById needs the script to have access to that file.
Frequently asked questions
- What is the difference between getActiveSpreadsheet and openById?
- getActiveSpreadsheet targets the file bound to the script or currently open. openById opens any spreadsheet the script can access by its ID from the URL.
- Can SpreadsheetApp create a new sheet?
- Yes. Call insertSheet on the spreadsheet object to add a tab, then write headers and data.
- Does SpreadsheetApp work in web apps?
- Yes. Server functions in a web app use SpreadsheetApp the same way, subject to deployment permissions.
- How do I get the spreadsheet ID?
- Copy it from the sheet URL between /d/ and /edit. Store it in Script Properties for standalone projects.
- Can I delete rows with SpreadsheetApp?
- Yes. Use deleteRow, deleteRows, or clearContent on ranges. Test on a copy first because there is no script Undo.
- Why does my script say Cannot find spreadsheet?
- The ID may be wrong, the file was deleted, or the account running the script lacks permission to open it.
- Is SpreadsheetApp the same as the Sheets API?
- No. SpreadsheetApp is built into Apps Script. The Sheets API is a separate HTTP API often used from outside Google.
- Can macros use SpreadsheetApp?
- Recorded macros generate SpreadsheetApp calls. You can edit and extend that code in the Script Editor.
Related Tutorials
Watch how SpreadsheetApp works
From Sheet to Script And Back Again - Learn to Code In Google Sheets Part 3
The First Thing I Do When Starting a Google Sheet
Embed a Number in a Website from a Google Sheet
MAKE Your Google Sheets Add-on WORK
Spreadsheet Automation 101 Lesson 1: GetValue - Introduction to SpreadsheetApp
Related blog posts
Guides that explain SpreadsheetApp in more depth.
What's Better Than Google Sheets?
Nothing is better than Google Sheets. You just need to learn more about the unlimited power of Google Sheets. Functions, Formulas, Apps Script and more.
Read post →How to Use Google Sheets as a Website Database
Did you know you can use Google Apps Script to turn a Google Sheet into a free, auto-syncing database for your website? This guide shows you exactly how to set it up. You’ll prepare your spreadsheet, write a few lines of code and publish your site. Don’t worry if you’re not a developer, we’ll walk you through it. Why Use Google Sheets as a Database? For small projects, Google Sheets offers many advantages over traditional databases: * Free hosting and storage * Easy to edit data without...
Read post →Getting Started in Google Sheets Spreadsheet Management
Users can also customize the appearance of their spreadsheets with different fonts, colors, and themes.
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 API
The Google Sheets API lets other programs read and update spreadsheets over HTTP using JSON. It is how dashboards, backends, and mobile apps sync data without opening the Google Sheets UI. Apps Script inside a file is simpler for sheet owners; the API fits systems built in Python, Node, or other stacks.
Read guide →Done reading about SpreadsheetApp?
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.