Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active June 28, 2023 06:59
Show Gist options
  • Save tanaikech/4892c97df7ac0504ffd715c2dd6cd546 to your computer and use it in GitHub Desktop.
Save tanaikech/4892c97df7ac0504ffd715c2dd6cd546 to your computer and use it in GitHub Desktop.
Benchmark: Event Objects for Google Apps Script

Benchmark: Event Objects for Google Apps Script

Introduction

There are event objects at Google Apps Script. Typically, users which use Spreadsheet often use onEdit(event). Here, I would like to introduce the process costs for the event objects using this onEdit(event).

When onEdit(event) is used for the spreadsheet, event of onEdit(event) has the following structure.

{
  "authMode": {},
  "range": {
    "columnStart": 1,
    "rowStart": 1,
    "rowEnd": 1,
    "columnEnd": 1
  },
  "source": {},
  "oldValue": "old sample text",
  "user": {
    "nickname": "sampleName",
    "email": "sample email"
  },
  "value": "sample text"
}

In this structure, for example, the range of active cell is "range": {"columnStart": 1, "rowStart": 1, "rowEnd": 1, "columnEnd": 1 }. Namely, it's "A1". Users can use the range of active cell using this event object. In this report, I have investigated the process cost for retrieving the range of active cell as a sample.

Experimental procedure

The sample script is as follows.

function onEdit(e) {
  var start = Date.now();
  for (var i = 0; i < 5000; i++) {
    var range = e.range.getA1Notation(); // A1
  }
  Logger.log("%s [s]", (Date.now() - start) / 1000);

  var start = Date.now();
  for (var i = 0; i < 5000; i++) {
    var range = e.source.getActiveCell().getA1Notation(); // A1
  }
  Logger.log("%s [s]", (Date.now() - start) / 1000);

  var start = Date.now();
  for (var i = 0; i < 5000; i++) {
    var range = SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation(); // A1
  }
  Logger.log("%s [s]", (Date.now() - start) / 1000);
}

In this sample script, the range of active cell is retrieved for 5000 times, and the time is measured. In this experiment, the average values of 10 times were used as the result.

Results

method Time in 5000 times [s] Time in 1 times [ms]
e.range.getA1Notation() 0.784 0.157
e.source.getActiveCell().getA1Notation() 4.048 0.810
SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation() 7.370 1.474

From above table, it was found that the process cost of e.range.getA1Notation() was 20 % and 10 % for those of e.source.getActiveCell().getA1Notation() and SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation(), respectively. It is considered that these process costs depend on the number of use of APIs. As the result, it was found that the event objects can decrease the process cost in the scripts. Although this is the general result, also I think that it is important to quantitatively know this.

Reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment