Writing scripts for Google Sheets with Apps Script can be super rewarding — it lets you automate tasks, clean data, and even send emails. But sometimes your script doesn’t work as expected. That’s where debugging tools like Logger.log() and the Execution Transcript come in. They help you figure out what your code is actually doing behind the scenes, step by step. If you’re new to scripting, this guide will help you debug with confidence and clarity.
What Is Debugging in Google Apps Script?
Debugging simply means finding and fixing errors in your code. In Google Apps Script, you can use built-in tools to check what values are being used, which lines are running, and where things might be going wrong. It’s like having a GPS for your code — so you’re never lost when things break.
Two Main Tools for Debugging
- Logger.log(): Shows specific values or messages from your code.
- Execution Transcript: A step-by-step log of what the script did when it ran.
Why Use Logger and Execution Transcript?
- Quickly spot where your code is failing.
- See what data your script is working with.
- Understand how your script flows step-by-step.
- Make debugging easier — especially when you’re just getting started.
Real-Life Example: Cleaning Up Task Statuses
Imagine you have a Google Sheet where your team logs tasks and updates their statuses. Sometimes someone accidentally types “pendng” instead of “Pending”. You write a script to clean up those errors, but it’s not working right. Using Logger.log() and the Execution Transcript can show exactly where it’s getting stuck.
Sample Task Sheet
| Task | Status |
|---|---|
| Design Logo | pendng |
| Update Website | Completed |
Basic Cleanup Script
function cleanUpStatuses() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getRange("A2:B" + sheet.getLastRow()).getValues();
for (var i = 0; i < data.length; i++) {
if (data[i][1].toLowerCase() === "pendng") {
Logger.log("Fixing row " + (i + 2)); // Debug line
sheet.getRange(i + 2, 2).setValue("Pending");
}
}
}
How to Use Logger.log()
- Open your Google Sheet.
- Click Extensions > Apps Script.
- Paste your script and add
Logger.log()inside your loops or conditions. - Click the Run button (▶).
- Go to View > Logs to see the log messages.
What You Might See in Logs
Fixing row 2
This shows you that your script recognized an issue in row 2 and fixed it — great confirmation!
How to Use the Execution Transcript
- Run your function in the Apps Script editor.
- Click Executions on the left sidebar.
- Select the most recent execution.
- Scroll through the Execution Log to see each action your script took.
Execution Transcript Sample
[10:01:23 AM] Starting function cleanUpStatuses
[10:01:23 AM] Getting data from range A2:B10
[10:01:23 AM] Found "pendng" at row 2 — correcting to "Pending"
[10:01:24 AM] Function completed
Pro Tips for Debugging Like a Pro
- Use
Logger.log()to print out variables and values you’re unsure about. - Run small sections of your code first to narrow down issues.
- If something seems to run but doesn’t update the sheet, check your range and row indexes.
- Look at the Execution Transcript for a play-by-play summary of your script’s actions.
Cheat Sheet: Debugging in Google Apps Script
| Tool | Purpose | Where to View |
|---|---|---|
Logger.log() |
Prints out messages or variable values | View > Logs |
| Execution Transcript | Shows step-by-step flow of script | Left menu > Executions |
| Break code into parts | Run individual chunks to isolate problems | Manually run smaller functions |
Debugging doesn’t have to be frustrating. With simple tools like Logger.log() and the Execution Transcript, you can see exactly what your script is doing and fix bugs faster. Whether you’re correcting typos, updating cells, or automating reports, these tools make your Apps Script journey way smoother.
So next time your code acts up, don’t panic — log it, track it, and fix it like a pro!