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.
- 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 usedonEdit()
.
- Custom functions cannot use
func1()
imports a formula that you want to measure the execution time by the script launched by the trigger.- 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.
- By measuring the cost per one call for
- The result of measurement can be seen at Stackdriver as milliseconds.
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);
}
- 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.