Easily Managing Time-Driven Triggers Using Google Apps Script
Abstract
Google Apps Script can be executed by time-driven triggers. This is one of the very important points for taking cloud computing. But, the scenarios using time-driven triggers are different for each user, and there are a lot of situations for using time-driven triggers. But, when a script for implementing time-driven triggers is developed, each script is different and complicated. In this report, I would like to introduce easily managing time-driven triggers using a Google Apps Script library.
Introduction
In this report, I would like to introduce the method for easily managing time-driven triggers using Google Apps Script.
Google Apps Script can be executed with not only manual operation but also several triggers. The time-driven trigger is one of them. When the time-driven trigger is used, Google Apps Script can be executed at the time you set. There are various situations for using time-driven triggers. And, I thought that when I created each script for each situation, the script might tend to be complicated. Ref and Ref Actually, when I prepared the scripts for each situation in my work, I thought that each script was largely different. I have wished a logic for integrating time-driven triggers for Google Apps Script were existing. So, I created a Google Apps Script library for achieving this. Ref When this library is used, a script for various situations using time-driven triggers can be simply created. I would like to introduce the sample scenarios for sample situations and the sample scripts for achieving each scenario here.
Install Google Apps Script library: TriggerApp
All sample scripts in this report use TriggerApp of a Google Apps Script library.
In order to easily manage time-driven triggers, I created a Google Apps Script library (TriggerApp). In this post, this library is used. So, please install this library.
You can see how to install it here. And also, you can see detailed information about the specification of this library there.
The project key for installing the library is 1LihDPPHWBCcadYVBI3oZ4vOt7XqlowoHyBLdaDgRIx_5OpRBREA7Z1QB
Algorithm
The algorithm of this library is straightforward. An important point of this algorithm is to use an object for managing various trigger tasks. In order to explain this, the trigger process shown in the following figure is used.
This figure shows the trigger process. For example, when “00:00” is “00:00” of “2024–01–01”, “workFunction1”, “workFunction2”, and “workFunction3” are run as follows.
- “workFunction1”: “2024–01–01T00:00:00”, “2024–01–01T06:00:00”
- “workFunction2”: “2024–01–01T00:30:00”,,, “2024–01–01T02:30:00”, and “2024–01–01T03:30:00”,,, “2024–01–01T05:30:00”, and “2024–01–01T06:30:00”,,, “2024–01–01T08:30:00”.
- “workFunction3”: “2024–01–01T03:00:00”, “2024–01–01T09:00:00”
When this process is run, I thought that if only the next trigger time for the current time is set, the script might be able to be used. When the above image is used,
- When the current date time is “2023–12–31T23:00:00”, the following trigger time is “2024–01–01T00:00:00” with “workFunction1”.
- When the current date time is “2024–01–01T00:00:00”, the following trigger time is “2024–01–01T00:30:00” with “workFunction2”.
- This is repeated in the last task.
- When the current date time is “2024–01–01T09:00:00”, no next trigger is installed.
In the above flow, this process can be achieved even with only 2 time-driven triggers. Actually, I tested this logic many times, and I could obtain that this can be used. This algorithm can achieve various triggers by installing only 2 time-driven triggers.
I think that when the work function is only one, even with only one time-driven trigger, this process can be achieved. But, in my actual situation, I am required to use multiple work functions. So, in this case, I have given the specification for requiring 2 time-driven triggers.
Limitations
- The time-driven trigger follows “Current limitations of Quotas for Google Services”. Ref
- When “toDay” in the object for TriggerApp is not used, the trigger cycle is repeated to infinity. But., when an error occurs on the internal Google side for some reason, the trigger is stopped. At that time, please run the main function, again. By this, the trigger process is restarted.
- In this library, I set the minimum interval between triggers as 60 seconds. Because, in the current stage, when the minimum interval time is less than 60 seconds, the time-driven trigger cannot be installed by the function executed by the time-driven trigger. This situation might be resolved in the future update.
- When I tested the time-driven triggers many times using this library, for example, even when the function is trying to execute at “00:00:00”, at least, the script is run after “00:00:00” like “00:00:10”. It seems that the function was not executed before “00:00:00”. But, there were cases where the function is executed at “00:01:30”. I guessed that the reason for this issue is due to the Google side. This might be also a limitation.
- I’m not sure whether this has to be included in the “Limitations” section. When you want to execute functions using the “everyYear” property, I’m worried that when it occurs no execution of the script for a long time in the Google Apps Script project, it might be required to reauthorize the scopes. But, in the current stage, I have no experience with this issue. If you got it, please tell me. I believe that it will be useful for other users.
- I believe that this library will be able to adapt to a lot of scenarios for using time-driven triggers. However, on the other hand, I think that this library cannot be used in all scenarios. Please be careful about this.
Scenarios
In this section, I would like to introduce sample scenarios for using time-driven triggers. I have actually used these scenarios in my work.
As a sample current date, it supposes that now is January 1st, 2024.
1. Execute 2 functions at specific dates and times
Trigger dates
Trigger times
- Execute “sampleFunction1” at “2024–01–15T09:00:00”.
- Execute “sampleFunction2” at “2024–02–25T10:00:00”.
Script
function sample(e) {
const obj = [
{
ownFunctionName: "sample",
functionName: "sampleFunction1",
everyMonth: [15],
atTimes: ["09:00:00"],
fromDay: "2024-01-15T00:00:00.000",
toDay: "2024-01-16T00:00:00.000",
},
{
ownFunctionName: "sample",
functionName: "sampleFunction2",
everyMonth: [25],
atTimes: ["10:00:00"],
fromDay: "2024-02-25T00:00:00.000",
toDay: "2024-02-26T00:00:00.000",
}
];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}Please set the function name of the function for executing TriggerApp to ownFunctionName, and set the event object e to setEventobject.
For example, when const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log) is replaced with const res = TriggerApp.setEventObject(e).simulateTriggers(obj), this process can be simulated. When the above obj is used, the following result is returned.
[
{ triggerTime: '2024-01-15T09:00:00', executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-02-25T10:00:00', executeFunction: 'sampleFunction2' }
]From this result, it is found that the value of obj is a valid value for using installTriggers and the expected process can be achieved. After you confirm the process of triggers by this simulation, you can run obj with installTriggers.
2. Execute a function with a specific cycle between specific times between specific dates
Trigger dates
Trigger times
- Execute “sampleFunction1” from “09:00” to “17:00 “ every 10 minutes every day from “2024–02–01T00:00:00” to “2024–08–01T00:00:00”.
Script
function sample(e) {
const obj = [{
ownFunctionName: "sample",
functionName: "sampleFunction1",
everyDay: true,
interval: 600,
fromTime: "09:00",
toTime: "17:00",
fromDay: "2024-02-01T00:00:00.000",
toDay: "2024-08-01T00:00:00.000",
}];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}In this script, when toDay property is removed from obj[0], this cycle is continued to be run without finishing. When you don’t want to set the last day, it is not required to include toDay property.
3. Execute 3 functions with a specific cycle between specific times on weekdays
Trigger dates
Trigger times
These trigger tasks are run on Monday, Tuesday, Wednesday, Thursday, and Friday every week. The trigger times in one day are as follows.
1. Execute “sampleFunction1” at “09:00:00”.
2. Execute “sampleFunction2” from “09:10:00” to “12:00:00” every 10 minutes.
3. Execute “sampleFunction2” from “13:00:00” to “16:50:00” every 10 minutes.
4. Execute “sampleFunction3” at “17:00:00”.
Script
function sample(e) {
const obj = [
{
ownFunctionName: "sample",
functionName: "sampleFunction1",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
atTimes: ["09:00:00"],
},
{
ownFunctionName: "sample",
functionName: "sampleFunction2",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
interval: 600,
fromTime: "09:10",
toTime: "12:00",
},
{
ownFunctionName: "sample",
functionName: "sampleFunction2",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
interval: 600,
fromTime: "13:00",
toTime: "16:50",
},
{
ownFunctionName: "sample",
functionName: "sampleFunction3",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
atTimes: ["17:00:00"],
},
];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}
In this script, toDay property is not included. By this, this cycle is continued to be run without the last day.
4. Send an email on a birthday every year
As a sample, it supposes that the birthday is October 1st.
Trigger dates
Trigger times
- Execute “sampleFunction1” at “09:00:00” on October 1st every year. The 1st execution is “2024–10–01T09:00:00”.
Script
function sample(e) {
const obj = [{
ownFunctionName: "sample",
functionName: "sampleFunction1",
everyYear: ["2024-10-01"],
atTimes: ["09:00:00"],
}];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}In this script, the function “sampleFunction1” is run on October 1st every year.
5. Execute specific functions on specific weekdays in a week
Trigger dates
In this case, only January 2024 is used.
Trigger times
- Execute “sampleFunction1” with 10-minute intervals from “09:00:00” to “12:00:00” on Monday, Wednesday, and Friday.
- Execute “sampleFunction2” with 10-minute intervals from “13:00:00” to “17:00:00” on Tuesday, Thursday, and Saturday.
Script
function sample(e) {
const obj = [
{
ownFunctionName: "sample",
functionName: "sampleFunction1",
everyWeek: ["Monday", "Wednesday", "Friday"],
interval: 600,
fromTime: "09:00",
toTime: "12:00",
fromDay: "2024-01-01T00:00:00.000",
toDay: "2024-02-01T00:00:00.000"
},
{
ownFunctionName: "sample",
functionName: "sampleFunction2",
everyWeek: ["Tuesday", "Thursday", "Saturday"],
interval: 600,
fromTime: "13:00",
toTime: "17:00",
fromDay: "2024-01-01T00:00:00.000",
toDay: "2024-02-01T00:00:00.000"
},
];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}In this script, toDay property is included. By this, this cycle is run to February 1st, 2024 the last day. When toDay property is removed, this cycle is continued to be run without finishing.
6. Execute 6 different functions every 10 minutes from “09:00:00” to “11:50:00” in order
Trigger dates
In this case, the current time is “2024–01–01T00:00:00.000” and toDay property is set as “2024–01–04T00:00:00.000”. From this condition, “2024–01–01” to “2024–01–03” are selected.
Trigger times
In this case, the following flow is run.
- Execute 6 functions of “sampleFunction1” to “sampleFunction6” with 10-minute intervals at 09:00:00" to “11:50:00” on “2024–01–01” to “2024–01–03” in order.
Script
function sample(e) {
const n = 18;
let start = new Date();
start.setHours(9, 0, 0, 0);
const ar1 = Array(n).fill(null);
const obj = [...Array(Math.ceil(ar1.length / 6))].flatMap((_, i) =>
ar1.splice(0, 6).map((_, j) => {
const temp = {
ownFunctionName: "sample",
functionName: `sampleFunction${j + 1}`,
everyDay: true,
atTimes: [Utilities.formatDate(start, Session.getScriptTimeZone(), "HH:mm:ss")],
toDay: "2024-01-04T00:00:00.000"
}
start = new Date(start.getTime() + 10 * 60 * 1000);
return temp;
})
);
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}For example, this obj is used with “simulateTriggers” method, and the following result is obtained.
[
{ triggerTime: '2024-01-01T09:00:00', executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-01T09:10:00', executeFunction: 'sampleFunction2' },
{ triggerTime: '2024-01-01T09:20:00', executeFunction: 'sampleFunction3' },
{ triggerTime: '2024-01-01T09:30:00', executeFunction: 'sampleFunction4' },
{ triggerTime: '2024-01-01T09:40:00', executeFunction: 'sampleFunction5' },
{ triggerTime: '2024-01-01T09:50:00', executeFunction: 'sampleFunction6' },
{ triggerTime: '2024-01-01T10:00:00', executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-01T10:10:00', executeFunction: 'sampleFunction2' },
{ triggerTime: '2024-01-01T10:20:00', executeFunction: 'sampleFunction3' },
{ triggerTime: '2024-01-01T10:30:00', executeFunction: 'sampleFunction4' },
{ triggerTime: '2024-01-01T10:40:00', executeFunction: 'sampleFunction5' },
{ triggerTime: '2024-01-01T10:50:00', executeFunction: 'sampleFunction6' },
{ triggerTime: '2024-01-01T11:00:00', executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-01T11:10:00', executeFunction: 'sampleFunction2' },
{ triggerTime: '2024-01-01T11:20:00', executeFunction: 'sampleFunction3' },
{ triggerTime: '2024-01-01T11:30:00', executeFunction: 'sampleFunction4' },
{ triggerTime: '2024-01-01T11:40:00', executeFunction: 'sampleFunction5' },
{ triggerTime: '2024-01-01T11:50:00', executeFunction: 'sampleFunction6' },
{ triggerTime: '2024-01-02T09:00:00', executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-02T09:10:00', executeFunction: 'sampleFunction2' },
,
,
,
]
It is found that in this library, the detailed process of the trigger tasks can be confirmed by using “simulateTriggers” method.
7. Opening hours of Google Form from 09:00 to 17:00 on Weekdays
Trigger dates
Trigger times
- Google Form is opened at 09:00 on the weekday.
- Google Form is closed at 17:00 on the weekday.
Script
In this case, please copy and paste the following script to the script editor of Google Form.
const openForm = _ => FormApp.getActiveForm().setAcceptingResponses(true);
const closeForm = _ => FormApp.getActiveForm().setAcceptingResponses(false).setCustomClosedFormMessage("off duty");
function sample(e) {
const obj = [
{
ownFunctionName: "sample",
functionName: "openForm",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
atTimes: ["9:00:00"],
},
{
ownFunctionName: "sample",
functionName: "closeForm",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
atTimes: ["17:00:00"],
},
];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}8. Opening Hours of Google Spreadsheet from 09:00 to 17:00 on Weekdays
Trigger dates
Trigger times
- Google Spreadsheet is opened at 09:00 on the weekday.
- Google Spreadsheet is closed at 17:00 on the weekday.
Script
In this case, please copy and paste the following script to the script editor of a standalone script. And, please set your Spreadsheet ID to the following script. And, please enable Drive API at Advanced Google services.
const openSpreadsheet = _ => Drive.Files.patch({ contentRestrictions: [{ readOnly: false }] }, "###spreadsheetId###");
const closeSpreadsheet = _ => Drive.Files.patch({ contentRestrictions: [{ readOnly: true, reason: "off duty" }] }, "###spreadsheetId###");
function sample(e) {
const obj = [
{
ownFunctionName: "sample",
functionName: "openSpreadsheet",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
atTimes: ["9:00:00"],
},
{
ownFunctionName: "sample",
functionName: "closeSpreadsheet",
everyWeek: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
atTimes: ["17:00:00"],
},
];
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}When “closeSpreadsheet” is run the Spreadsheet is locked. By this, the users including the owner of the Spreadsheet cannot edit it while they can see it. When “openSpreadsheet” is used, the Spreadsheet is unlocked.
9. Execute specific functions at 09:00 on Monday to Friday at the beginning of specific months
Trigger dates
Trigger times
In this sample, January, March, and May are used.
- “sampleFunction1” is executed at 09:00 on 1 to 5 in January.
- “sampleFunction1” is executed at 09:00 on 4 to 8 in March.
- “sampleFunction1” is executed at 09:00 on 6 to 10 in May.
Script
function sample(e) {
const executeMonth = ["2024-01-01", "2024-03-01", "2024-05-01"];
const obj = executeMonth.map(e => {
const date = new Date(e);
const topMonday = [...Array(7)].map((_, i) => {
const now = new Date(date.getTime());
now.setDate(i + 1);
return now;
}).find(d => d.getDay() == 1 ? true : false);
const dates = [...Array(5)].map((_, i) => {
const temp = new Date(topMonday.getTime());
temp.setDate(temp.getDate() + i);
return temp.getDate();
});
date.setMonth(date.getMonth() + 1)
const toDay = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
return {
ownFunctionName: "sample",
functionName: "sampleFunction1",
everyMonth: dates,
atTimes: ["09:00:00"],
fromDay: e + "T00:00:00",
toDay: toDay + "T00:00:00",
};
});
const res = TriggerApp.setEventObject(e).installTriggers(obj, console.log);
console.log(res);
}
For example, this obj is used with “simulateTriggers” method, and the following result is obtained. By the way, in this sample, it supposes that now is “2024–01–01T00:00:00”. Please be careful about this.
[
{ triggerTime: '2024-01-01T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-02T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-03T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-04T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-01-05T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-03-04T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-03-05T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-03-06T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-03-07T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-03-08T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-05-06T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-05-07T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-05-08T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-05-09T09:00:00',
executeFunction: 'sampleFunction1' },
{ triggerTime: '2024-05-10T09:00:00',
executeFunction: 'sampleFunction1' }
]Note
The above sample scenarios are several of a lot of scenarios. I created the library “TriggerApp” for using various trigger tasks in my work. I believe that this library can be used for many more various scenarios. If this was useful for your situation, I’m glad.
References
- Time-driven triggers
- GitHub repository of TriggerApp is here.
