Measurement of execution time of built-in function

Question

Are there methods to measure the execution time when built-in functions completed for Spreadsheet? When I use several built-in functions (For example, IMPORTHTML and IMPORTXML), if I know the average execution-time, it is easy for me to use and design data sheet.

I measure it of custom functions using this script.

function myFunction() {
  var start = new Date();

  // do something

  var end = new Date();
  var executiontime = end - start;
}

Thank you so much for your time and advices.

Answer 1

Google Sheets doesn't include a built-in tool to measure the recalculation time.

One alternative is to use the Chrome Developers Tools Timeline but bear in mind that functions like IMPORTHTML and IMPORTXML are not recalculated every time that the spreadsheet does (reference Set a spreadsheet’s location and calculation settings).

Related Q&A

SO

• Google spreadsheet constantly recalculates functions

Web Applications

• What's causing / how to get rid of long web calls in Google Spreadsheets
• Where does the calculation take place?

Answer 2

Unfortunately, there are not measurement tools for retrieving the execution time of built-in functions. This has already been commented by @Rubén. So I thought of about the workarounds. How about the following 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 ###'; // Please set the built-in function you want to measure the execution time.

  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.

If this was useful for you, I'm glad.