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

Answer 2

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 3

One can make a Named Function (just to have it easily reusable) to measure formula execution using only formulas, no scripts needed.

BENCHMARK()

BENCHMARK() (returns execution time in seconds):

=N(
  REDUCE(
    0,
    {0; 1; 0},
    LAMBDA(
      acc,
      cur,
      IF(
        cur = 0,
          LAMBDA(x, x)(NOW()) - acc,
          acc + 0 * ROWS(func())
      )
    )
  ) * 24 * 60 * 60
)

Its single parameter is func - a function to run and time its execution. You just get a working formula, enclose it in LAMBDA() and pass to BENCHMARK(). Examples are at the bottom.

BENCHMARKN()

BENCHMARKN():

=N(
  REDUCE(
    0,
    {0; SEQUENCE(number); 0},
    LAMBDA(
      acc,
      cur,
      IF(
        cur = 0,
          LAMBDA(x, x)(NOW()) - acc,
          acc + 0 * ROWS(func())
      )
    )
  ) * 24 * 60 * 60 / number
)

There's another parameter number: func will be executed in a loop number times. Then total execution time will be divided by number to get an average execution time.

UPD: enclosing NOW() in LAMBDA(x, x)(NOW()) freezes the value and prevents recalculation caused by any change in the sheet. Got it from here.

Examples

Say, we want to sum all squares of natural numbers from 1 to 1000000. So we have our formula:

=SUM(ARRAYFORMULA(SEQUENCE(1000000)))

We time it like so:

=BENCHMARK(LAMBDA(SUM(ARRAYFORMULA(SEQUENCE(1000000)^2))))

And in a 10-step loop like so:

=BENCHMARKN(LAMBDA(SUM(ARRAYFORMULA(SEQUENCE(1000000)^2))), 10)

Answer 4

Here's my option to caclulate the difference between ArrayFormulas:

1. Create a template with sample data
2. Set the script: change array with formulas, template Spreadsheet Id, cell address to set formulas, cell address to iterate values.

This function will test 2 or more formulas:

1. Copy sample file to isolate the influence of previous iterations.
2. Set formula and calculate the time to calculate it the first time
3. Edit source data in a loop and calculate time needed for the formula to recalculate.

Sample output, plotted in Sheets:

↑ formulas for the tests are taken from this question.

The y-axis is the processing time, and bars are the iterations of the script.

• the low bars are the the times to insert the formula,
• and higher bars are the times to re-calculate the formula when the value changed.

The Code:

function test_formulas_speed() {

  /**  Options ↓ ********************************************************************************************* */
  // https://docs.google.com/spreadsheets/d/1vQu7hVr7FwH8H5N8JOlOGfvjlJgKtpfoM2DPPjgUaLo/template/preview
  var testSpreadsheetId = '1vQu7hVr7FwH8H5N8JOlOGfvjlJgKtpfoM2DPPjgUaLo';
  var formulas = [
    '=INDEX(if(A2:A="",,LAMBDA(srt, SORT(SCAN(0,MAP(SEQUENCE(ROWS(A2:A)),LAMBDA(v,if(v=1,0,if(INDEX(srt,v,1)<>INDEX(srt,v-1,1),1,0)))),LAMBDA(ini,v,IF(v=1,1,ini+1))),index(srt,,2),1) ) (SORT({A2:A,SEQUENCE(ROWS(A2:A))}))))',

    '=LAMBDA(a,INDEX(if(a="",,COUNTIFS(a,a,row(a),"<="&row(a)))))(A2:A)'

  ];
  // value from the first cell of arrayformula ↓
  var returnValue = 1;
  // range to insert the formula
  var rA1 = 'B2';
  var sheetName = 'Sheet1';
  var iterations = 5;

  var testChangeRaangeA1 = 'A5';
  var sub_iterations = 5; // the number of times to change the value
  /**  Options ↑ ********************************************************************************************* */

  
  var results = [];
  var file = DriveApp.getFileById(testSpreadsheetId);
  var results = []
  var testOne_ = function(formula, indx) {
    // prepare
    var copy = file.makeCopy();
    var id = copy.getId();
    var ss = SpreadsheetApp.openById(id);
    var s = ss.getSheetByName(sheetName);
    var r = s.getRange(rA1);
    var rCh = s.getRange(testChangeRaangeA1);
    var addToResult = function(t) {
      var result = new Date() - t;
      if (results[indx]) {
        results[indx].push(result);
      } else {
        results[indx] = [result];
      }
    }

    // measure time
    var t = new Date();
    r.setFormula(formula);
    SpreadsheetApp.flush();
    // loop until expected value is returned
    while (r.getDisplayValue() != returnValue) {}
    addToResult(t);
    for (var i = 0; i < sub_iterations; i++) {
      t = new Date();
      // clear the cells, because of the internal cache
      rCh.clearContent();
      rCh.setValue(i);
      SpreadsheetApp.flush();
      addToResult(t);
    }

    // clean up
    copy.setTrashed(true);

    return 0;
  }

  for (var i = 0; i < iterations; i++) {
    formulas.forEach(testOne_);
  }
  
  console.log(results);

}

Ref:

This benchmark by @Tanaikech

Answer 5

To add another option you can do:

function myFunction() {
  console.time('someFunction');

  // do something

  console.timeEnd('someFunction');
}

And then look at the Stackdriver logs for the Function Execution.

Example output would be:

Jul 3, 2020, 1:03:00 AM Debug someFunction: 80ms

Answer 6

Using functions without LAMBDA or apps script, it is possible to calculate function execution times.

Why not lambda?

• LAMBDA functions itself is slow
• Sample size for tests cannot be large, because of artificial limitations: See What factors determine the memory used in lambda functions?

Methodology:

The idea is to create two cells:

• Start test - A checkbox(say B1)
• B3 will have the formula to evaluate(say, =SEQUENCE(10000)).
• Output cell[End test]- A final output of the formula will be filled in this cell.(say, the last cell in column B: B10000)

Then,

C1(Start time):

=IF(B1, NOW())

C2(End time):

=IF(AND(B1, B10000<>""), NOW())

The difference between these gives the calculation time.

B2:

=C2-C1

To start the test, click the checkbox.

Sample:

Here's a sample benchmark spreadsheet[For a copy, click here] based on this question