Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active November 6, 2018 15:12
Show Gist options
  • Save tanaikech/1e7df0d6991d4e39330cd2b353573e72 to your computer and use it in GitHub Desktop.
Save tanaikech/1e7df0d6991d4e39330cd2b353573e72 to your computer and use it in GitHub Desktop.
Measuring Execution Time of Built-In Functions for Google Spreadsheet

Measuring Execution Time of Built-In Functions for Google Spreadsheet

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment