In Part 1, we learned the very first step of Google Apps Script (GAS)—using Logger.log() to display custom messages in the execution log.
Now, in Part 2, it’s time to take our first real step into interacting with Google Sheets using code!
Today, you’ll master SpreadsheetApp.getActiveSpreadsheet(). This core method loads your currently open spreadsheet file into your script so you can work with its data.
Using a practice project—a “Sapporo Soup Curry Tour List”—we’ll walk through everything from step-by-step setup to common beginner pitfalls and how to fix them!
1. What is SpreadsheetApp.getActiveSpreadsheet()?
At first glance, a long string of technical terms like this can feel intimidating. However, breaking it down into smaller parts makes its structure very easy to understand.
Deconstructing the Code Structure
In programming, we use a dot (.) to connect Who (Object) and What to do (Method):
SpreadsheetApp.getActiveSpreadsheet()
SpreadsheetApp
This is the main “parent class” managing all Google Sheets functionality. It announces to GAS: “Hey, we’re about to perform an action related to Google Sheets!”.getActiveSpreadsheet()
This is a method (command) sent toSpreadsheetApp. Just as the English name implies, it instructs GAS to “Get the currently open (active) spreadsheet file.”
In short, this line tells GAS: “Get the spreadsheet file currently open on my screen ready so I can work with it in my script!”
The “House, Room, and Furniture” Analogy
To visualize how a Google Sheet is structured, think of it like a single-family house:
| Sheet Concept | Real-World Analogy | Key GAS Command |
| Entire Spreadsheet File | House (Building overall) | SpreadsheetApp.getActiveSpreadsheet() |
| Worksheet (Tab) | Room (Living room, bedroom) | Spreadsheet.getActiveSheet() |
| Cell (Range) | Furniture / Drawer | Sheet.getRange() |
When you want to redecorate a room or grab something from a drawer, you first need to unlock the front door and step inside the house.
Executing SpreadsheetApp.getActiveSpreadsheet() is the exact equivalent of unlocking the front door of the house.
STEP 1: Set Up Your “Sapporo Soup Curry Tour List”
Let’s create a sample spreadsheet to practice with.
Instructions:
- Create a New Google Sheet
Open Google Drive in your browser, click + New, and select Google Sheets. - Rename the File
Click on “Untitled spreadsheet” in the top-left corner and rename it to:Sapporo Soup Curry Tour List - Add Sample Data (Optional)
Feel free to add headers like “Restaurant Name” in cell A1 and “Spice Level” in cell A2. - Open the Apps Script Editor
Click Extensions > Apps Script in the top menu bar.
Once the editor opens in a new tab, you’re ready to start coding!
STEP 2: Get and Log the Spreadsheet Name
Let’s write a script that recognizes the open spreadsheet and prints its file name to the execution log.
1. Paste the Code
Clear out any default code in the editor, then copy and paste the following script:
function getMyCurryList() {
// 1. Get the currently open spreadsheet file (the house)
var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// 2. Extract the file name of the spreadsheet (the house nameplate)
var fileName = mySpreadsheet.getName();
// 3. Output the extracted file name to the log
Logger.log("Fetched File Name: " + fileName);
}
2. Code Explanation
Here is what happens line by line:
var mySpreadsheet = ...varcreates a “container” (variable) to temporarily store data. Here, we create a variable namedmySpreadsheetand store the spreadsheet object returned bySpreadsheetApp.getActiveSpreadsheet().mySpreadsheet.getName()
We run.getName()on ourmySpreadsheetvariable to extract the file name string:"Sapporo Soup Curry Tour List".Logger.log("Fetched File Name: " + fileName)
Using the+operator, we concatenate the static text string with our variable’s content to display them together in the log.
STEP 3: Handle the First-Time Authorization Process
After pasting the code, click the Save icon (floppy disk) and then click Run.
When running a script for the first time, Google displays a security prompt:
Authorization Required
This project requires your permission to access your Google Account data.
Don’t panic! This is a standard Google security check. GAS is simply asking for permission so it doesn’t modify your spreadsheet without your consent.
Authorization Steps (3 Quick Steps):
- Click “Review Permissions” in the popup window.
- Select your Google Account.
- Grant Access:
- If you see the screen stating “Google hasn’t verified this app”, click Advanced in the bottom left.
- Click Go to Untitled project (unsafe) (or your project name).
- Click Allow on the final screen.
Once authorized, your script will execute automatically!
Verify the Result
Check the Execution log pane at the bottom of the editor. If you see the following output, you succeeded:
Info Fetched File Name: Sapporo Soup Curry Tour List
GAS successfully identified your open spreadsheet and retrieved its file name!
3 Common Traps for Beginners (and How to Fix Them)
When code doesn’t work as expected, it’s easy to feel stuck. Here are 3 common issues beginners face with getActiveSpreadsheet() and how to resolve them.
Trap 1: TypeError: Cannot read properties of null
If you get a red error stating Cannot read properties of null (reading 'getName'), the issue stems from how the script was created.
Google Apps Script supports two file types:
- Container-Bound Script (Recommended)
Created directly from a Google Sheet via Extensions > Apps Script. It is tied directly to that file. - Standalone Script
Created independently from Google Drive (+ New > More > Google Apps Script).
getActiveSpreadsheet() searches for the spreadsheet file paired with the script. If you run it from a Standalone Script, GAS finds no paired file (null) and throws an error.
- Solution
Always open the script editor directly from your active Google Sheet via Extensions > Apps Script.
Trap 2: Forgetting the Parentheses ()
A very common syntax mistake is leaving off the closing parentheses at the end of a method:
- ❌ Incorrect
var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet;(missing parentheses) - ✅ Correct
var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();(with parentheses)
In JavaScript (and GAS), trailing parentheses () act as the trigger to execute the command.
Without them, the method won’t run, resulting in odd log outputs like function getActiveSpreadsheet() { [native code] } or syntax errors.
- Solution
Make it a habit to always pair action commands with half-width parentheses().
Trap 3: Capitalization Errors (Typos)
JavaScript is strictly case-sensitive:
- ❌
getactivespreadsheet()(all lowercase) - ❌
getActiveSpreadSheet()(uppercase ‘S’ in Sheet) - ✅
getActiveSpreadsheet()(correct CamelCase)
GAS uses CamelCase notation, where the first letter of each concatenated word is capitalized (resembling camel humps).
- Solution
Avoid typing full commands manually. TypeSpreadsheetApp.get...and select the method from the autocomplete suggestion menu using the Tab or Enter key.
Bonus: Retrieve Additional Spreadsheet Details
Once you have grabbed the entire spreadsheet object (mySpreadsheet), you can pull various details beyond just the file name:
function getSpreadsheetDetails() {
var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// 1. Get the file name
var fileName = mySpreadsheet.getName();
// 2. Get the web URL of this spreadsheet
var fileUrl = mySpreadsheet.getUrl();
// 3. Get the total number of sheet tabs in the file
var sheetCount = mySpreadsheet.getNumSheets();
// Output everything to the log!
Logger.log("■ File Name: " + fileName);
Logger.log("■ URL: " + fileUrl);
Logger.log("■ Sheet Count: " + sheetCount + " sheet(s)");
}
Sample Output Log:
Info ■ File Name: Sapporo Soup Curry Tour List
Info ■ URL: https://docs.google.com/spreadsheets/d/1xxxxxx.../edit
Info ■ Sheet Count: 1 sheet(s)
Capturing the overall spreadsheet object unlocks possibilities like emailing file links to colleagues or counting tabs to automate batch processing!
Summary & What’s Next
Today, we covered SpreadsheetApp.getActiveSpreadsheet(), the foundation for manipulating Google Sheets in GAS.
Key Takeaways:
SpreadsheetApp.getActiveSpreadsheet()retrieves the open spreadsheet file (the house).- Always open the editor via Extensions > Apps Script inside your spreadsheet.
- Grant security permissions during your first run.
- Don’t forget half-width parentheses
()at the end of methods.
Now that we’ve unlocked the front door to our house, next time we’ll step inside to target a specific room!
Next Time: [GAS Beginner Series #3] Selecting the “Chuo Ward Area” Sheet Tab with Spreadsheet.getActiveSheet()
We’ll create multiple tabs like “Chuo Ward Area” and “Kita Ward Area” inside our curry tour list and learn how to switch between them seamlessly in code. Stay tuned!
