Detecting Quickly Checked Checkboxes on Google Spreadsheet using Google Apps Script

27 May 2020, 13:44

report / GasTips

Google Apps Script / Spreadsheet / event / trigger

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.

Demo

The left and right images are the typical situation and the situation modified with this report, respectively.

Experimental procedure

In this report, as the experimental procedure, I used the following 4 patterns. And each pattern is run by the OnEdit event trigger and the OnSelectionChange event trigger. By this, the total patterns are 8. In order to reduce the process cost, the minimum necessary script is used.

Confirm each edited checkbox using the OnEdit event trigger and the OnSelectionChange event trigger. In this case, the exclusive processing of the script is not reflected.
- The main script is as follows.
```
const pattern1 = (e) => {
  const range = e.range;
  range.offset(0, 1).setValue(range.isChecked() ? "checked" : "unchecked");
};
```
- pattern1 is called by the following script. When onEdit is used, onSelectionChange is removed, and vice versa.
```
const onEdit = (e) => pattern1(e);

const onSelectionChange = (e) => pattern1(e);
```
Confirm each edited checkbox using the OnEdit event trigger and the OnSelectionChange event trigger. In this case, the exclusive processing of the script is reflected using the lock service.
- The main script is as follows. In this case, the script of pattern1() is wrapped by the lock service.
```
const pattern2 = (e) => {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      pattern1(e);
    } catch (e) {
      throw new Error(e);
    } finally {
      lock.releaseLock();
    }
  }
};
```
- pattern2 is called by the following script. When onEdit is used, onSelectionChange is removed, and vice versa.
```
const onEdit = (e) => pattern2(e);

const onSelectionChange = (e) => pattern2(e);
```

Confirm each edited checkbox and all checkboxes using the OnEdit event trigger and the OnSelectionChange event trigger. In this case, the exclusive processing of the script is not reflected.

The main script is as follows.

const pattern3 = (e) => {
  const checkBoxRange = "A1:A20";
  const range = e.range;
  const sheet = range.getSheet();
  const values = sheet
    .getRange(checkBoxRange)
    .offset(0, 0, range.getRow())
    .getValues()
    .map(([a], i) => [a === true ? "checked" : "unchecked"]);
  sheet
    .getRange(checkBoxRange)
    .offset(0, 1, values.length, values[0].length)
    .setValues(values);
};

pattern3 is called by the following script. When onEdit is used, onSelectionChange is removed, and vice versa.
```
const onEdit = (e) => pattern3(e);

const onSelectionChange = (e) => pattern3(e);
```

Confirm each edited checkbox and all checkboxes using the OnEdit event trigger and the OnSelectionChange event trigger. In this case, the exclusive processing of the script is reflected using the lock service.
- The main script is as follows. In this case, the script of pattern3() is wrapped by the lock service.
```
const pattern4 = (e) => {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      pattern3(e);
    } catch (e) {
      throw new Error(e);
    } finally {
      lock.releaseLock();
    }
  }
};
```
- pattern4 is called by the following script. When onEdit is used, onSelectionChange is removed, and vice versa.
```
const onEdit = (e) => pattern4(e);

const onSelectionChange = (e) => pattern4(e);
```

Results

The following table is the results for 4 patterns for OnEdit event trigger and OnSelectionChange event trigger.

Pattern	OnEdit event trigger	OnSelectionChange event trigger
Pattern 1
Pattern 2
Pattern 3
Pattern 4

Discussions

When the results of pattern 1 of OnEdit event trigger are seen, checked values are sparsely put to the cells. At OnSelectionChange event trigger, checked values are put to only the cells of “A1” and “A20”. From this situation, it is considered that the response of “OnEdit” is higher than that of “OnSelectionChange”. From this, it is found that “OnSelectionChange” is not suitable for the short time selection. From the results of pattern 1 and pattern 2, it is considered that in this case, the exclusive processing by the lock service doesn’t affect to the results.

When the results of pattern 3 of OnEdit event trigger are seen, checked values are put to the cells “A1:A20”. At OnSelectionChange event trigger, checked values are put to only the cells of “A1” and “A20”, and then, checked values are put to the cells “A1:A20”. It is considered that these are due to that all checkboxes are confirmed every run. But from the response speed of “OnSelectionChange”, it is found that in this case, “OnEdit” is more suitable than “OnSelectionChange”. From the results of pattern 1 and pattern 2, it is considered that in this case, also, the exclusive processing by the lock service doesn’t affect to the results.

By the way, as additional information, the OnChange event trigger has no range in the event object. When the above patterns are measured using getActiveCell(), it was found that the response of OnChange event trigger is almost the same with that of the OnEdit event trigger.

Summary

In this report, the response of event trigger has been investigated. The following results were obtained.

It was found that the response of “OnEdit” is higher than that of “OnSelectionChange”.
For the situation for checking multiple checkboxes using as the trigger for executing the script, it was found that “OnEdit” is more suitable than “OnSelectionChange”.
It was found that in this case, the exclusive processing by the lock service doesn’t affect to the results.