Measuring Execution Time of Built-In Functions for Google Spreadsheet

GasTips
Google Apps Script / spreadsheet

Gists

This sample script is for measuring the execution time of built-in functions for Google Spreadsheet. Unfortunately, there are not measurement tools for retrieving the execution time of built-in functions. So I thought of about a workaround.

Flow :

  1. Import a value to a cell. The value is anything good, because this is used as a trigger. Please do this by yourself.
    • Custom functions cannot use setValue(). So I used onEdit().
  2. func1() imports a formula that you want to measure the execution time by the script launched by the trigger.
  3. At func2(), after set the formula, the measurement is started. The confirmation when built-in function was completed is carried out using loop.
    • By measuring the cost per one call for getValue(), it was found that that was about 0.0003 s. So I thought that this can be used.
  4. The result of measurement can be seen at Stackdriver as milliseconds.

Sample script :

function func1(range, formula) {
  range.setFormula(formula);
}

function func2(range) {
  var d = range.getValue();
  while (r == d) {
    var r = range.getValue();
  }
}

function onEdit() {
  var formula = '### Built-in function ###'; // Set the built-in function you want to measure.

  var label = "Execution time for built-in functions.";
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var range = ss.getRange(cell.getRow(), cell.getColumn());
  func1(range, formula);
  console.time(label);
  func2(range);
  console.timeEnd(label);
}

Note :

  • When built-in functions with very long time is measured, an error may occur at getValue().
    • In my environment, the built-in function for 10 seconds worked fine.

 Share!