测量电子表格内置函数的执行时间 [英] Measurement of execution time of built-in functions for Spreadsheet

问题描述

电子表格的内置函数完成后，有没有测量执行时间的方法?当我使用几个内置函数(例如，IMPORTHTML 和 IMPORTXML)时，如果我知道平均执行时间，我很容易使用和设计数据表.

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.

推荐答案

遗憾的是，没有用于检索内置函数执行时间的测量工具.@Rubén 已经对此发表了评论.所以我想到了解决方法.以下解决方法如何?

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?

1. 将值导入单元格.值是好的，因为它被用作触发器.请自行完成.
   • 自定义函数不能使用setValue().所以我使用了 onEdit().

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().

• 通过测量 getValue() 每次调用的成本，发现大约为 0.0003 秒.所以我觉得这个可以用.

• 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.

示例脚本:

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);
}

注意:

• 测量时间很长的内置函数时，getValue()可能会出现错误.
  • 在我的环境中，10 秒的内置函数运行良好.

  作为附加信息，我想再添加一个示例脚本，用于测量电子表格的内置函数完成时的执行时间以及使用该脚本的结果.

  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.

  这是一个简单的示例脚本，用于测量单元上函数的过程成本.一开始，为了确认这个脚本是否可以用于测量放入一个单元格的函数的过程成本，使用了一个自定义函数.因为在使用自定义函数时，通过Utilities.sleep(time)可以知道脚本的处理时间.

  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).

  当您测试此脚本时，请将以下脚本复制并粘贴到 Google 电子表格的容器绑定脚本中.运行main()的函数，可以得到=SAMPLE(5000)的过程开销.

  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();
  }
  

  • 在此示例中，当将 =SAMPLE(5000) 放入单元格时，5 秒后单元格中会显示 ok 的值.main() 测量这个的处理时间.
  • 此示例脚本检查函数的输出值.所以请设置returnValue.请注意这一点.
  • 在Spreadsheet的公式中，当公式放入单元格时，开始测量处理时间.因此，在此示例中，我将 setFormula 和 flush 包含在测量的流程成本中.
  • 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.

  作为一个实验，它显示了进程时间随着自定义函数睡眠时间的增加而发生的变化，如上图.该结果表明处理时间随着睡眠时间的增加而线性增加.发现处理时间至少比睡眠时间长，并且处理时间较大，以每个睡眠时间为偏移量在0.5 s左右.认为这个偏移量包括setFormula、flush、getDisplayValue和while循环的过程开销.但是，从这张图中，可以认为当使用上述脚本衡量各种公式的过程成本时，可以通过相对比较来比较它们.并且从这个结果可以认为，上面的脚本可以用来衡量函数在电子表格单元格中的执行时间.

  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.

  • 基准:使用 Google Apps 脚本测量 Google 电子表格中单元格公式的流程成本

  这篇关于测量电子表格内置函数的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！