How to write Google Apps Script logs into Google Sheets
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.
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.
// Mimic an Enum for Severity Levels
const Severity = {
INFO: 'INFO',
WARNING: 'WARNING',
ERROR: 'ERROR',
DEBUG: 'DEBUG'
};
/**
* Class representing a logger with functionality to log messages to a Google Spreadsheet.
*/
class LocalLogger {
/**
* Creates a logger instance.
* @param {string} emailAddress - The email address to send notifications to. (Leave blank to disable notifications)
* @param {boolean} logAtTop - Determines if logs should be placed at the top of the spreadsheet.
* @param {GoogleAppsScript.Spreadsheet.Sheet} [logSheet] - Optional. The sheet to log messages to.
*/
constructor(emailAddress = null, logAtTop = false, notifyLevel = Severity.ERROR, logSheet = null) {
this.spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
this.logSheet = logSheet || this.spreadsheet.getSheetByName('Logs');
this.logAtTop = logAtTop;
this.notifyLevel = notifyLevel; // Notify when an ERROR or more severe log is added
this.emailAddress = emailAddress; // Email address for sending notifications
}
/**
* Initializes the logger by creating a Logs sheet if it does not exist.
*/
init() {
if (!this.logSheet) {
this.logSheet = this.spreadsheet.insertSheet('Logs');
this.logSheet.appendRow(['Timestamp', 'Severity', 'Message', 'User/Session']);
this.logSheet.getRange('1:1').setFontWeight('bold');
}
}
/**
* Logs a message to the spreadsheet.
* @param {string} message - The message to log.
* @param {string} [severity=Severity.INFO] - The severity of the log message.
*/
log(message, severity = Severity.INFO) {
const userEmail = Session.getActiveUser().getEmail(); // May return empty in some contexts due to privacy
const sessionID = Session.getTemporaryActiveUserKey(); // Alternative session identifier
const userInfo = userEmail || sessionID;
const timestamp = new Date();
const formattedTimestamp = Utilities.formatDate(timestamp, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
const logEntry = [formattedTimestamp, severity, message, userInfo];
try {
this.writeLogEntry(logEntry);
// Trigger notification if severity is high
if (this.shouldNotify(severity)) {
this.notify(message, severity);
}
} catch (e) {
// Handle the error, e.g., log to a different place or send an email
Logger.log('Failed to log message: ' + e.toString());
}
}
/**
* Writes a single log entry to the spreadsheet.
* @param {Array} entry - The log entry to write.
*/
writeLogEntry(entry) {
let range;
if (this.logAtTop) {
// Insert a new row after the headers for the new log entry
this.logSheet.insertRowAfter(1);
range = this.logSheet.getRange(2, 1, 1, 4); // Now the new entry will be on the second row
} else {
// Append at the bottom of the log sheet
const lastRow = this.logSheet.getLastRow();
range = this.logSheet.getRange(lastRow + 1, 1, 1, 4);
}
range.setValues([entry]);
range.setFontWeight('normal');
this.applyLogColor(range, entry[1]); // Apply color based on severity
}
/**
* Sends a notification for a log entry.
* @param {string} message - The log message.
* @param {string} severity - The severity of the log message.
*/
notify(message, severity) {
if(!this.emailAddress) {
Logger.log('No email address specified for notifications');
return;
}
try {
const subject = `New ${severity} log entry`;
const body = `A new log entry with severity ${severity} was added: \n ${message}`;
MailApp.sendEmail(this.emailAddress, subject, body); // Use the stored email address
} catch (e) {
Logger.log('Failed to send notification: ' + e.toString());
}
}
/**
* Determines if a log entry should trigger a notification based on its severity.
* @param {string} severity - The severity of the log entry.
* @return {boolean} True if notification should be sent, false otherwise.
*/
shouldNotify(severity) {
const severityOrder = [Severity.DEBUG, Severity.INFO, Severity.WARNING, Severity.ERROR];
return severityOrder.indexOf(severity) >= severityOrder.indexOf(this.notifyLevel);
}
/**
* Applies background color to a log entry based on its severity.
* @param {GoogleAppsScript.Spreadsheet.Range} range - The range to apply the background color to.
* @param {string} severity - The severity of the log entry.
*/
applyLogColor(range, severity) {
let color = "#FFFFFF"; // Default white background
switch (severity) {
case Severity.INFO:
color = "#D9EAD3"; // Light green
break;
case Severity.WARNING:
color = "#FFE599"; // Light yellow
break;
case Severity.ERROR:
color = "#F4CCCC"; // Light red
break;
case Severity.DEBUG:
color = "#CFE2F3"; // Light blue
break;
}
range.setBackground(color);
}
}Setting Up LocalLogger
In order to test LocalLogger yourself:
- Create a new Google Sheet
- Go to the Apps Script Editor (Extensions –> Google Apps Script Editor.
- Create a new file (called LocalLogger for example) and paste the LocalLogger Implementation
- 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.
const notificationEmailAddress = "apptiva.projects@gmail.com";
const showNewestMessageFirst = true;
const logger = new LocalLogger(notificationEmailAddress, showNewestMessageFirst);
function onOpen() {
logger.init();
let ui = SpreadsheetApp.getUi();
ui.createMenu('Logging')
.addItem("Generate Debug Message", 'generateDebugMessage')
.addItem("Generate Info Message", 'generateInfoMessage')
.addItem("Generate Warning Message", 'generateWarningMessage')
.addItem("Generate Error Message", 'generateErrorMessage')
.addToUi();
}
function generateDebugMessage(){
logger.log('This is a DEBUG Message', Severity.DEBUG);
}
function generateInfoMessage(){
logger.log('This is an INFO Message', Severity.INFO);
}
function generateErrorMessage(){
logger.log('This is an ERROR Message', Severity.ERROR);
}
function generateWarningMessage(){
logger.log('This is a WARNING Message', Severity.WARNING);
}
function generateWarningMessage(){
logger.log('This is a WARNING Message', Severity.WARNING);
}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.
