v1.0.3 (June 26, 2024)
You can see the detail information here https://github.com/tanaikech/TriggerApp
One day, you might have a situation where you are required to update a sheet using Google Apps Script when the cell values retrieved by IMPORTRANGE are changed. This report introduces a workaround for achieving this situation.
Google Apps Script can be executed by several triggers. Ref When a cell in a Google Spreadsheet is manually edited, a function of Google Apps Script can be executed by detecting this edit. In most cases, the OnEdit trigger trigger of the simple trigger or the installable trigger is used. When the OnEdit trigger is used, a function can be executed by manually editing a cell. When the function is executed, the function can be run by giving the event object including the information about the edited cell.
v1.0.1 (August 18, 2023)
toDay is not used, there was a case that the next trigger is not installed. This bug was removed.You can see the detail information here https://github.com/tanaikech/TriggerApp
This is a Google Apps Script library for efficiently managing the time-driven triggers for executing Google Apps Script using Google Apps Script.
Google Apps Script can execute with not only the manual operation but also several triggers. The time-driven trigger is one of them, and this is one of a lot of important functions. When the time-driven trigger is used, Google Apps Script can be automatically executed at the time you set without launching the user’s PC.
This is a sample script for opening and closing Google Forms on time using Google Apps Script.
In order to test this sample script, please do the following flow.
Please create a new Google Form and set your sample questions. And, please open the script editor of Google Form.
Please copy and paste the following script to the script editor of Google Form. And, please set the values of start and end times you want.
This is a sample script for achieving the pseudo OnEdit trigger for Google Document using Google Apps Script.
In the current stage, there is not OnEdit trigger for Google Document. Ref But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.
This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved.
When the above situation is shown as an image, it becomes as follows.
In the above sample situation, a function is run every 10 minutes in the green ranges of 09:00 - 12:00 and 15:00 - 18:00.
This is a sample Google Apps Script for running the specific function when the specific sheet is edited.
Please copy and paste the following script to the container-bound script of Spreadsheet and set sheets object.
// When the cells are edited, this function is run by the fire of event trigger.
function onEdit(e) {
// Please set the sheet name and function as follows.
const sheets = {
Sheet1: functionForSheet1, // Sheet1 is the sheet name. functionForSheet1 is the function name of function which is run when Sheet1 is edited.
Sheet2: functionForSheet2,
};
const sheetName = e.range.getSheet().getSheetName();
if (sheets[sheetName]) {
sheets[sheetName](e);
}
}
// In this sample, when Sheet1 is edited, this function is run.
function functionForSheet1(e) {
console.log("Sheet1 was edited.");
// do something
}
// In this sample, when Sheet2 is edited, this function is run.
function functionForSheet2(e) {
console.log("Sheet2 was edited.");
// do something
}
functionForSheet1() and functionForSheet2() are run, respectively. When other sheets are edited, no functions are run.onEdit of the simple trigger is used. When the functions you want to run include the methods which are required to authorize, please use the installable trigger.
This is a sample script for highlighting the row and column of the selected cell using Google Apps Script. For this, the OnSelectionChange event trigger is used.
Please copy and paste the following script to the script editor of Spreadsheet. And, please select a cell. By this, the script is run by the OnSelectionChange event trigger.
function onSelectionChange(e) {
const range = e.range;
const sheet = range.getSheet();
const maxRows = sheet.getMaxRows();
const maxColumns = sheet.getMaxColumns();
sheet.getRange(1, 1, maxRows, maxColumns).setBackground(null);
sheet.getRange(1, range.getColumn(), maxRows, 1).setBackground("yellow");
sheet.getRange(range.getRow(), 1, 1, maxColumns).setBackground("yellow");
}
This is a report for detecting quickly checked checkboxes on Google Spreadsheet using Google Apps Script. It supposes that when the checkbox is checked, a function of Google Apps Script is run by the event trigger. In this case, when the multiple checkboxes on Google Spreadsheet are checked quickly, the script cannot be run for all checked checkboxes, because of the response speed of the event trigger. It is considered that to understand the response of event trigger is useful for creating the application for Spreadsheet. In this report, the detection of quickly checked checkboxes on Google Spreadsheet using Google Apps Script has been investigated. From this result, it led to understanding the response of event trigger.
I have already reported about “Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script”. Ref It is considered that when the situation which uses the event trigger of onSelectionChange is thought, the response speed is important. So, here, I investigated the characteristics of response for the event trigger of onSelectionChange.
In order to investigate the response speed, I used the following sample script. The work of sample script can be seen at above demonstration movie. In this report, the script is important for discussing the result. So I pot this at this section instead of the appendix.
onSelectionChange has been released at April 22, 2020. But this couldn’t be used at the released day. But now, I could confirm that this got to be able to be used. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script.
onOpen is run and the current sheet is put to PropertiesService.onSelectionChange has no information about the change of tab. So in order to detect the change of tab, I used the PropertiesService.onSelectionChange is run by the onSelectionChange event trigger, and put the A1Notation to the cell.function onOpen(e) {
const prop = PropertiesService.getScriptProperties();
const sheetName = e.range.getSheet().getSheetName();
prop.setProperty("previousSheet", sheetName);
}
function onSelectionChange(e) {
const prop = PropertiesService.getScriptProperties();
const previousSheet = prop.getProperty("previousSheet");
const range = e.range;
const a1Notation = range.getA1Notation();
const sheetName = range.getSheet().getSheetName();
if (sheetName != previousSheet) {
range.setValue(`Changed tab from ${previousSheet} to ${sheetName}. ${a1Notation}`);
// When the tab is changed, this script is run.
} else {
range.setValue(a1Notation);
}
prop.setProperty("previousSheet", sheetName);
}
Kanshi Tanaike
This is a report about the possibility of asynchronous process using event triggers. This is for Google Apps Script (GAS).
onEdit() which is a simple trigger is often used as a trigger when the values are modified on Spreadsheet. When users want to use the script including some methods which are required to be authorized as the onEdit event, a installable trigger of onEdit is used. If the trigger is installed for the function of onEdit(), when the event trigger is run, onEdit() is run 2 times. In order to avoid this, the installable trigger is installed to the functions except for the functions of simple triggers. The functions of simple triggers which is the same events are not used in the project. When I thought about this situation, I thought that both onEdit() which is run by the simple trigger and the function which is run by the installable trigger might be able to be used, simultaneously. So I investigated about this situation.
This sample script sends an e-mail, when spreadsheet was edited from outside by Sheet API v4. When you use this sample, please create a container bound script with spreadsheet which is edited by Sheet API. And please input your e-mail and run firstly a method of createTrigger(). By this, a trigger is installed as onChange(). After this, edit spreadsheet from outside by Sheet API v4.
When when spreadsheet was edited from outside by Sheet API v4, I used sendEmail() as a sample, because script editor is closed.