google apps script - Measurement of execution time of built-in functions for Spreadsheet

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

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.

Updated at November 11, 2020:

As the additional information, I would like to add one more sample script for measuring the execution time of when built-in functions completed for Spreadsheet and the result using the script.

This is a simple sample script for measuring the process cost of functions on a cell. At first, in order to confirm whether this script can be used for measuring the process cost of the function put in a cell, a custom function was used. Because when the custom function is used, the process time of the script can be known by using Utilities.sleep(time).

Sample script:

When you test this script, please copy and paste the following script to the container-bound script of Google Spreadsheet. When you run the function of main(), the process cost of =SAMPLE(5000) can be obtained.

// This is a sample custom formula. This is used for testing.
function SAMPLE(time) {
  Utilities.sleep(time);
  return "ok";
}

// This is a script for measuring the process cost.
function main() {
  const obj = { formula: `=SAMPLE(5000)`, returnValue: "ok" }; // Set formula and response value.

  const label = "Execution time";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const range = sheet.getRange("A1");
  range.clear();

  // start --- measure
  console.time(label);
  range.setFormula(obj.formula);
  SpreadsheetApp.flush();
  while (range.getDisplayValue() != obj.returnValue) {}
  console.timeEnd(label);
  // end --- measure

  range.clear();
}

• In this sample, when =SAMPLE(5000) is put to a cell, the value of ok is shown in the cell after 5 seconds. main() measures the process time for this.
• This sample script checks the output value from the function. So please set returnValue. Please be careful this.
• At the formula of Spreadsheet, when the formula is put to the cell, the measurement of process time is started. So in this sample, I included setFormula and flush to the measured process cost.

Experimental result:

As an experiment, it shows the change of process time with increasing the sleep time of the custom function as above image. This result indicates that the process time is linearly increased with the increase in the sleep time. It was found that the process time was at least, more than the sleep time, and the process time was large in about 0.5 s for each sleep time as the offset. It is considered that this offset includes the process costs of setFormula, flush, getDisplayValue and the while loop. But, from this image, it is considered that when the process costs of various formulas are measured using above script, those can be compared by the relative comparison. And from this result, it is considered that above script can be used for measuring the execution time of the function in a cell of Spreadsheet.

Reference:

• Benchmark: Measuring Process Costs for Formulas in Cells on Google Spreadsheet using Google Apps Script