Learn how to automate repetitive tasks and complex workflows using Google Apps Script triggers. This chapter discusses different types of triggers, such as time-driven and event-based triggers, and shows you how to set them up to automate tasks like sending emails, updating sheets, and processing data at scheduled intervals.
Automating workflows with script triggers can save you time and effort by performing repetitive tasks automatically. Google Apps Script provides various triggers that can execute your scripts based on specific events, such as opening a document, editing a spreadsheet, or at predetermined time intervals. In this chapter, we will explore different types of triggers and how to use them to streamline your workflows.
Simple On-Open Trigger
Automatically execute a function when the spreadsheet is opened.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘My Custom Menu’)
.addItem(‘Show Alert’, ‘showAlert’)
.addToUi();
}
function showAlert() {
SpreadsheetApp.getUi().alert(‘Spreadsheet opened!’);
}
Time-Driven Trigger to Send Daily Emails
Set up a trigger to send an email every day at a specific time.
function createDailyTrigger() {
ScriptApp.newTrigger(‘sendDailyEmail’)
.timeBased()
.everyDays(1)
.atHour(8) // 8 AM
.create();
}
function sendDailyEmail() {
MailApp.sendEmail(‘recipient@example.com’, ‘Daily Update’, ‘This is your daily update.’);
}
On-Edit Trigger to Log Changes
Automatically log changes made to a spreadsheet.
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var newValue = e.value;
var oldValue = e.oldValue;
var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘EditLog’);
if (!logSheet) {
logSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘EditLog’);
logSheet.appendRow([‘Timestamp’, ‘Sheet’, ‘Cell’, ‘Old Value’, ‘New Value’]);
}
logSheet.appendRow([new Date(), sheet.getName(), range.getA1Notation(), oldValue, newValue]);
}
Installable Trigger for Weekly Report Generation
Generate and email a weekly report.
function createWeeklyReportTrigger() {
ScriptApp.newTrigger(‘generateWeeklyReport’)
.timeBased()
.onWeekDay(ScriptApp.WeekDay.MONDAY)
.atHour(9) // 9 AM
.create();
}
function generateWeeklyReport() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var report = ‘Weekly Report\n\n’;
data.forEach(function(row) {
report += row.join(‘, ‘) + ‘\n’;
});
MailApp.sendEmail(‘recipient@example.com’, ‘Weekly Report’, report);
}
Form Submission Trigger
Automatically process data when a form is submitted.
function onFormSubmit(e) {
var responses = e.values;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Responses’);
sheet.appendRow(responses);
MailApp.sendEmail(‘admin@example.com’, ‘New Form Submission’, ‘A new form has been submitted.’);
}
Custom Time-Driven Trigger to Clean Up Old Data
Delete rows older than 30 days every day.
function createCleanupTrigger() {
ScriptApp.newTrigger(‘cleanupOldData’)
.timeBased()
.everyDays(1)
.atHour(2) // 2 AM
.create();
}
function cleanupOldData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var today = new Date();
for (var i = data.length – 1; i >= 1; i–) {
var rowDate = new Date(data[i][0]);
if ((today – rowDate) / (1000 * 60 * 60 * 24) > 30) {
sheet.deleteRow(i + 1);
}
}
}
On-Change Trigger to Update Cell Values
Automatically update cell values when changes are made.
function onChange(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() === ‘Data’) {
var range = sheet.getRange(‘A1’);
range.setValue(‘Last updated: ‘ + new Date());
}
}
Custom Menu to Manage Triggers
Create a custom menu to easily manage triggers.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Trigger Manager’)
.addItem(‘Create Daily Email Trigger’, ‘createDailyTrigger’)
.addItem(‘Delete All Triggers’, ‘deleteAllTriggers’)
.addToUi();
}
function deleteAllTriggers() {
var allTriggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < allTriggers.length; i++) {
ScriptApp.deleteTrigger(allTriggers[i]);
}
SpreadsheetApp.getUi().alert(‘All triggers have been deleted.’);
}
Using ClockApp for Precise Time Triggers
Schedule a task to run every hour.
function createHourlyTrigger() {
ScriptApp.newTrigger(‘hourlyTask’)
.timeBased()
.everyHours(1)
.create();
}
function hourlyTask() {
Logger.log(‘Hourly task executed at ‘ + new Date());
}
On-Install Trigger to Set Up Initial Configuration
Automatically set up triggers when the script is installed.
function onInstall(e) {
onOpen();
createDailyTrigger();
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Setup’)
.addItem(‘Create Daily Trigger’, ‘createDailyTrigger’)
.addToUi();
}
function createDailyTrigger() {
ScriptApp.newTrigger(‘sendDailyEmail’)
.timeBased()
.everyDays(1)
.atHour(8) // 8 AM
.create();
}
function sendDailyEmail() {
MailApp.sendEmail(‘recipient@example.com’, ‘Daily Update’, ‘This is your daily update.’);
}
These examples demonstrate how to use Google Apps Script triggers to automate various tasks and workflows. By leveraging these triggers, you can enhance productivity, ensure timely actions, and reduce manual intervention in repetitive processes.