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

问题描述

在谷歌脚本中访问和操作数组比从谷歌表访问和操作数据快多少?

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

这是应用程序:

我正在制定一项计划，为员工制定时间表.我的策略是为每天创建一个可用员工列表，将列表随机化，然后根据特定参数将随机员工插入每​​个空位中.然后在一周中的每一天重复，直到日程安排满为止.

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.

有时给定随机列表不满足参数，我需要重新启动循环.平均而言，我将运行大约 1100 次循环迭代，直到计划被填满.如果 GoogleScript 运行超过 6 分钟，则会导致函数超时并自动停止.

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.

我的函数是从 GoogleSheets 访问数据和直接从函数内的数组访问数据的混合体.一般来说，重新编写我的函数以便将所有数据直接存储和操作在一个数组中会不会有明显的不同?

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.

对电子表格 API 的调用通常需要 0.01 秒或更长时间才能完成.导入/导出数据或调用子函数的调用将花费更长的时间(例如 getDataRange、getValue(s)、setValue(s)).Apps Script 足够智能，可以优化一些连续的调用，但如果您交替读取和写入，那么 Google 的任何方面都无法帮助您.
您可以在 View -> 上查看此计时数据.执行成绩单菜单.

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.

我的建议是将任何现有的基于单元格的验证公式移动到脚本函数中，这些函数对传递的员工姓名、员工与他们已经工作"的班次之间的对象映射以及建议的工作班次进行操作.然后，您就可以使用 1 次调用来导入员工日可用性列表，并且对于每一天，对于每个班次，验证随机选择的可用员工是否可以工作，前提是他们在计划期间的先前班次.最后，您需要将对象写回工作表.

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

上面给了我 ~.8s 的范围和 .2s 的数组 - 0.2s 基本上都是因为调用 getDataRange() 来制作 inputs

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 Scripts 中的 Google Sheets 单元格快多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！