How to write Google Apps Script logs into Google Sheets

Image Showing Google Sheets Logs, Google Sheets and Google Apps Script Logo

Introduction

In Google Apps Script, the ability to track and record actions, errors, and performance metrics is crucial for both developers and users. However, the built-in logging mechanisms often fall short regarding accessibility and ease of use. This is where Local Google Apps Script Logging comes into play, offering a streamlined and integrated approach to capturing script activities.

Screenshot showing local Google Apps Script Logs

Watch the Video Tutorial

For a comprehensive visual guide, watch our video tutorial. If you prefer, you can also watch it directly on YouTube.

Why Local Google Apps Script Logs Matters

Local logging ensures that logs are easily accessible within a Google Sheet, providing a persistent and organized record of script activity. This is especially beneficial for scripts that are frequently executed or shared across teams, as it allows for immediate access to logs without navigating away from the workspace.

 

Introducing LocalLogger for Enhanced Logging

 

Overview of the Localogger Class

As seen in the Settings class implementation covered in a previous blog post, I love creating components that can be reused across different projects. This is the exact approach we followed here as well. 
 
Localogger is a custom class that extends the basic logging functionality in Google Apps Script. It allows developers to log messages directly into a Google Sheet, categorizing them by severity levels for better clarity. It includes a notification system sending email alerts in case of errors or critical issues.
 

Key Features

  • Optional Custom Sheet for Logging
    • Ability to provide a specific Google Sheet for logging, or default to a ‘Logs’ sheet if none is provided.
    • Enhances flexibility and allows logs to be organized in a dedicated sheet, separate from other data.
  • Configurable Logging Levels
    • Define severity levels (INFO, WARNING, ERROR, DEBUG) to categorize log messages.
    • Set a notification threshold to control which logs trigger email alerts.
  • Customizable Email Notifications
    • Configure an email address to receive alerts for logs of a certain severity.
    • Automated email alerts for logs that meet or exceed a set severity level, such as ERROR.
  • Dynamic Log Placement
    • Choose to place new log entries at the top or bottom of the Google Sheet.
    • Ensures the most recent log entries are easily accessible and visible.
  • Severity-Based Color Coding
    • Log entries are color-coded based on severity for quick visual identification:
    • INFO: Light green
    • WARNING: Light yellow
    • ERROR: Light red
    • DEBUG: Light blue
  • User Information in Logs
    • Capture and include the email or session ID of the user executing the script.
    • Provides traceability and is useful for scripts run by multiple users. 
  • Automated Log Sheet Setup
    • If no custom sheet is provided, automatically create a ‘Logs’ sheet with headers.
    • Format headers in bold for clear separation and organization of log entries. 
 
 

Setting Up LocalLogger 

In order to test LocalLogger yourself:
 
  1. Create a new Google Sheet
  2. Go to the Apps Script Editor (Extensions –> Google Apps Script Editor. 
  3. Create a new file (called LocalLogger for example) and paste the LocalLogger Implementation 
  4. Now in your main Code file, paste the code snippet below. 
As you can see below, we have LocalLogger’s instantiation and initiation.

Instantiation
Upon instantiation, we define:
  • The email address the logs should be sent to. If null, no emails will be sent.
  • Whether the newest log message should be placed in the first or last row of the sheet
  • The minimum severity level that should also trigger an email notification
  • The spreadsheet where the logs should be stored in. Feel free to leave it blank if already working within a Google Sheet. 
Initialization
Within the onOpen() function of the Sheet, we initialize – init() function – our Logger. During initialization, the logger checks for an existing ‘Logs’ sheet and creates one if necessary, complete with headers for ‘Timestamp‘, ‘Severity‘, ‘Message‘, and ‘User/Session‘.
 
The rest of the snippet shows the different ways to create a log message entry with different severity levels. 
 
 

Local Google Apps Script Logs – Conclusion 

The enhanced LocalLogger class allows for sophisticated Google Apps Script logs within Google Sheets, complete with severity levels and notification capabilities. By leveraging these features, developers can maintain a high level of awareness and control over their scripts, leading to more reliable and efficient automation.