Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active November 6, 2018 15:12
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
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