Asynchronous Processing using Event Triggers

report / GasTips
Google Apps Script / event / trigger

Gists

Kanshi Tanaike

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.

As the result, it was found that the following simple triggers and installable triggers work as the asynchronous process.

Simple triggers Installable triggers
onOpen() On open
onEdit() On edit

For example, when the event of on edit is used as the asynchronous process, it uses the following flow.

  1. Create a project as the container-bound script in a Spreadsheet.
  2. onEdit() which is used as the simple trigger is put in the project.
  3. myFunction() which is used as the installable trigger is put in the project.
  4. Install myFunction() as the trigger of “On edit”.

By above setting, when the cell of spreadsheet is edited, both onEdit() and myFunction() are run as the asynchronous process.

Simple Triggers and Installable Triggers

At first, it investigated about the work of simple trigger and installable trigger. The sample script is as follows. Here, “onEdit” was used. myFunction() was installed as the trigger of “On edit”. At both onEdit() and myFunction(), the start and end time are output after Utilities.sleep(t) was run. When the event trigger of on edit was run, it judges whether both functions worked as the asynchronous process by confirming the start and end time. As the result, it was found that the start and end time from both functions were almost the same. By this, it is considered that the simple trigger and installable trigger work as the asynchronous process with the 2 workers which are ```onEdit()andmyFunction()``.

var t = 10000; // For task.

function onEdit() {
  var start = new Date();
  Utilities.sleep(t); // This is a sample task.
  var end = new Date();
  console.log("onEdit: " + start + ' - ' + end);
}

function myFunction() {
  var start = new Date();
  Utilities.sleep(t); // This is a sample task.
  var end = new Date();
  console.log("myFunction: " + start + ' - ' + end);
}

Applications

Sample situations by the asynchronous processing using event triggers are as follows.

Sample situations

A. 2 independent tasks by 2 workers

In this case, each task is completely independent. The sample script becomes like the script which was used at the section of “Simple Triggers and Installable Triggers”.

B. Share in a task by 2 workers

In this case, each task is summarized after each worker was finished. The sample script is as follows. When you use this script,

  • Please install the trigger to myFunction() as the “On edit” trigger.
  • This script works at the container-bound script of Spreadsheet.
  • When a cell is edited, the script is run.

The sample script is as follows.

var t = 10000; // For task.

function onEdit(e) {
  var label = "sample";
  console.time(label);
  Utilities.sleep(t / 2); // This is a sample task.
  var c = CacheService.getDocumentCache();
  var v;
  while (!v) {
    v = c.get("value");
  }
  var r = Utilities.formatString("onEdit: %s, myFunction: %s", "onEdit was done. value is " + e.value, v);
  e.range.setValue(r);
  c.remove("value");
  console.timeEnd(label);
}

function myFunction(e) {
  Utilities.sleep(t / 2); // This is a sample task.
  var c = CacheService.getDocumentCache();
  c.put("value", "myFunction was done. Value is " + e.value);
}

The flow of this script is as follows.

  1. Users put a value in a cell.
  2. onEdit() and myFunction() are run by the simple trigger and the installable trigger, respectively. These functions work as the asynchronous process as mentioned above.
  3. At myFunction(), after own task was done, it puts the result using CacheService.
  4. At onEdit(), after own task was done, it waits for retrieving the result from myFunction(). When the result from myFunction() was retrieved, each result is summarized and show it.
    • When I put sample to “A1”, onEdit: onEdit was done. value is sample, myFunction: myFunction was done. Value is sample is put to “A1”.
    • For this situation, I used CacheService like the channel of golang.

Using this sample script, the process time with increasing the task was measured.

Fig. 1. Task vs. process time. Task is the time for Utilities.sleep(t).

Figure 1 shows the result of task vs. process time. Blue and red dots mean 1 worker and 2 workers, respectively. For 1 worker, the sample script is here. For 2 workers, the sample script is mentioned above. From Fig. 1, it is found that the result of 1 worker coincides with the time of task. On the other hand, it was also found that for 2 workers, the result of it is half of the time of task. From this result, it is found that the script for 2 workers works as the asynchronous process, and also the task can be shared by the workers. This result indicates the asynchronous process can be achieved by using the event triggers and the simple script.

Note

  • This is a report about the possibility of asynchronous process using event triggers. So if you want to use more workers as the asynchronous process, please use RunAll of the library. This library can achieve the parallel processing using Google Apps Script.
  • Under the condition that the simple trigger and the installable trigger are used, when a custom function is used, only when the custom function is firstly put in a cell, 3 workers can be used. But when the custom function is recalculated, the triggers don’t work. So after the function was put, it becomes only 1 worker.

Appendix

Scripts

Script for measuring single task

var t = 10000; // For task.

function onEdit(e) {
  var label = "onEdit";
  console.time(label);
  Utilities.sleep(t); // t is task.
  var r = "onEdit was done. value is " + e.value;
  e.range.setValue(r);
  console.timeEnd(label);
}

 Share!