数组比在Google脚本中访问Google表格单元快多少？ [英] How much faster are arrays than accessing Google Sheets cells within Google Scripts?

问题描述

以下是应用程序：

$ b在谷歌脚本中访问和操作数组的速度比访问和操作谷歌表中的数据要快多少？
$ b

我正在制定一个计划来为员工创建时间表。我的策略是为每天创建一个可用员工列表，随机化列表，然后根据特定参数逐个插入随机员工。然后重复每周的每一天，直到日程安排完成。

有时在给定随机列表的情况下参数不符合，我需要重新启动循环。平均来说，我会运行〜1100次迭代循环，直到时间表被填充。如果GoogleScript运行时间大于6分钟，则会导致该功能超时，并自动停止。

我的功能是混合访问GoogleSheets中的数据并直接访问数据来自函数内的数组。一般来说，重写我的函数是否会有明显的区别，这样所有的数据都可以直接在数组中存储和操作？

解决方案

影响程度取决于您正在进行多少阅读和写作。如果当前使用了大量增量数据传输，那么更改您的方法可以带来巨大的收益。

调用电子表格API通常需要0.01秒或更长时间才能完成。调用导入/导出数据或调用子函数将花费更长时间（例如 getDataRange ， getValue（s） ， setValue（s））。 Apps脚本非常智能，可以优化一些连续的调用，但是如果您是交替读取和写入，则Google的任何内容都无法为您提供帮助。

您可以在查看 - > Execution Transcript 菜单。

我的建议是将任何现有的基于单元格的验证公式转换为脚本函数，员工之间的对象映射和他们已经工作的转变，以及拟议的工作转移。然后，您可以使用1次呼叫导入员工日可用性列表，并在每个班次中为每个班次导入一个随机选择的可用员工，并根据他们在调度期间的上一次班次来验证。最后，你会把你的对象写回到工作表。

---

计时脚本谷歌由于连续读取）

  function writeRand_（rows，cols）
 {
 var datasheet = SpreadsheetApp .openById（SSID）.getSheetByName（ 'Sheet 1中'）; 
 datasheet.getDataRange（）。clearContent（）; 
 var rand = []; （var row = 0; row< rows; ++ row）
 {
 var data = []; （var col = 0; col< cols; ++ col）
 data.push（Math.random（））; 
 
 rand.push（data）; 
} 
 datasheet.getRange（1，1，rand.length，rand [0] .length）.setValues（rand）; 
返回数据表; 
} 
 
函数readAndTime（）
 {
 var rows = 50，cols = 8; 
 var datasheet = writeRand_（rows，cols）; 
 //一个一个地求和
 var sum1 = 0; 
 var startRangeSum = new Date（）。getTime（）;对于（var col = 1; col< = cols; ++ col）
 sum1 + = datasheet，
为（var row = 1; row< = rows; ++ row）
 .getRange（row，col）.getValue（） -  0; 
 var endRangeSum = new Date（）。getTime（）; 
 //读全部，然后求和。 
 var sum2 = 0; 
 var startArraySum = new Date（）。getTime（）; 
 var inputs = datasheet.getDataRange（）。getValues（）; （var var = 0; row< inputs.length; ++ row）
 for（var col = 0; col< inputs [0] .length; ++ col）
 sum2 + =输入[行] [col] -0; 
 var endArraySum = new Date（）。getTime（）; 
 Logger.log（Value count：+ rows * cols）; 
 Logger.log（Range sum：+（endRangeSum  -  startRangeSum）/ 1000 +sec。+ sum1）; 
 Logger.log（Array sum：+（endArraySum  -  startArraySum）/ 1000 +sec。+ sum2）; 
} 
  

以上给出了~8s的范围和0.2s的数组 - 这基本上都是由于调用 getDataRange（）来创建输入

所需的时间。

How much faster is accessing and manipulating an array in google scripts vs accessing and manipulating data from a google sheet?

Here's the application:

I am working on a program to create a schedule for employees. My strategy is to create a list of available employees for each day, randomize the list, and then insert a random employee into each open slot one by one based on specific parameters. Then repeat for each day of the week until the schedule is filled.

Sometimes the parameters aren't met given the randomized list, and I need to restart the loop. On average I'll run ~1100 iterations of the loop until the schedule is filled. If the GoogleScript runs >6 min then it results in a timeout of the function and it's stopped automatically.

My function is a mix of accessing data from GoogleSheets and accessing data directly from arrays within the function. In general, would there be a noticeable difference to re-write my function such that all the data is directly stored and manipulated within an array?

解决方案

The impact magnitude will depend on how much reading and writing you are doing. If a lot of incremental data transfer is currently used, then changing your approach can yield massive benefit.

Calls to the Spreadsheet API generally take 0.01 seconds or more to complete. Calls that import / export data, or call sub-functions, will take longer (e.g. getDataRange, getValue(s), setValue(s)). Apps Script is smart enough to optimize some successive calls, but if you are alternating reads and writes, then nothing on Google's end can help you.
You can view this timing data on the View -> Execution Transcript menu.

My suggestion is to move any existing cell-based validation formulas into script functions that operate on a passed employee name, an object mapping between employees and the shifts they have already "worked", and the proposed shift to work. Then you'd be able to use 1 call to import the employee - day availability list, and for each day, for each shift, validate that a randomly chosen available employee can work it, given their previous shifts in the scheduling period. Lastly, you'd write your object back to the sheet(s).

---

Timing Script (which gets internally optimized by google due to successive reads)

function writeRand_(rows, cols)
{
  var datasheet = SpreadsheetApp.openById(ssid).getSheetByName('Sheet1');
  datasheet.getDataRange().clearContent();
  var rand = [];
  for(var row = 0; row < rows; ++row)
  {
    var data = [];
    for(var col = 0; col < cols; ++col)
      data.push(Math.random());
    rand.push(data);
  }
  datasheet.getRange(1, 1, rand.length, rand[0].length).setValues(rand);
  return datasheet;
}

function readAndTime()
{
  var rows = 50, cols = 8;
  var datasheet = writeRand_(rows, cols);
  // sum one-by-one
  var sum1 = 0;
  var startRangeSum = new Date().getTime();
  for(var row = 1; row <= rows; ++row)
    for(var col = 1; col <= cols; ++col)
      sum1 += datasheet.getRange(row, col).getValue()-0;
  var endRangeSum = new Date().getTime();
  // Read all, then sum.
  var sum2 = 0;
  var startArraySum = new Date().getTime();
  var inputs = datasheet.getDataRange().getValues();
  for(var row = 0; row < inputs.length; ++row)
    for(var col = 0; col < inputs[0].length; ++col)
      sum2 += inputs[row][col]-0;
  var endArraySum = new Date().getTime();
  Logger.log("Value count: " + rows * cols);
  Logger.log("Range sum: " + (endRangeSum - startRangeSum)/1000 + " sec. " + sum1);
  Logger.log("Array sum: " + (endArraySum - startArraySum)/1000 + " sec. " + sum2);
}

The above gives me ~.8s for range and .2s for array - and that .2s is essentially all due to the time it takes to call getDataRange() to make inputs

这篇关于数组比在Google脚本中访问Google表格单元快多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！