Gists

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.

• Execute a function every 10 minutes only in 09:00 - 12:00 and 15:00 - 18:00 for the weekday.

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.

Gists

This is a sample Google Apps Script for running the specific function when the specific sheet is edited.

Sample script

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
}

• In this sample script, when the cells of “Sheet1” and “Sheet2” are edited, functionForSheet1() and functionForSheet2() are run, respectively. When other sheets are edited, no functions are run.
• In this sample script, 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.

Note

• This method can be also used for other event triggers like OnChange, OnSelectionChange and so son.

References

• Simple Triggers
• Installable Triggers
• Event Objects

Gists

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.

Demo

Sample script

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");
}

References

• Simple Triggers
• onSelectionChange(e)

Gists

Abstract

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.

Gists

Abstract

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.

Demo

Experiment

Sample script

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.

Gists

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.

Demo

Usage

1. Please copy and paste the following script to the container-bound script of Google Spreadsheet, and save the script.
2. Please reopen the Google Spreadsheet.
   • By this, onOpen is run and the current sheet is put to PropertiesService.
   • Unfortunately, in the current stage, it seems that the event object of onSelectionChange has no information about the change of tab. So in order to detect the change of tab, I used the PropertiesService.
3. Then, please select a cell and cells on sheet.
   • By this, onSelectionChange is run by the onSelectionChange event trigger, and put the A1Notation to the cell.
   • When the active tab is moved, the sample script detects this, and the information of the changed tab is put to the cell.

Sample script

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);
}

References

• onSelectionChange(e)
• Properties Service

GetEditType was updated to v1.0.1.

• v1.0.1 (October 25, 2019)

  1. Updated: This answer was reflected.

You can see the detail information here https://github.com/tanaikech/GetEditType

Overview

GetEditType is a GAS library for retrieving the edit types of the OnEdit event trigger of Spreadsheet using Google Apps Script (GAS).

Description

In the case that the OnEdit event trigger (simple and installable triggers) is used at Spreadsheet, when users manually edited the cell of Spreadsheet, the trigger is fired. At this time, there is the case that I want to know the edit type. For example, I would like to know about the following edit types.

Gists

Overview

This is a report about the possibility of asynchronous process using event triggers. This is for Google Apps Script (GAS).

Description

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.