可能由于getValue和单元格插入，导致处理时间过长。

Question

我刚写了我的第一个Google Apps脚本，从VBA移植而来，它可以对客户订单信息的一列进行格式化（感谢您所有的指导）。

描述：

该代码通过其前缀“-”识别州/省代码，然后将下一个名字与姓氏（如果存在）组合在一起。最后，在姓氏应该出现的地方写入“订单完成”。如果订单之间没有空隙，则最后插入必要的空单元格（请参见下面的图像）。

问题：

问题在于处理时间。它无法处理更长的数据列。我收到了以下警告：

方法Range.getValue在脚本中被大量使用。

现有优化：

根据此问题的回复，我尽可能将许多变量放在循环外，并改进了我的if语句。@MuhammadGelbana建议仅调用一次Range.getValue方法并移动其值...但我不明白这如何工作。

代码：

function format() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var lastRow = s.getRange("A:A").getLastRow();
var row, range1, cellValue, dash, offset1, offset2, offset3;

  //loop through all cells in column A
  for (row = 0; row < lastRow; row++) {
    range1 = s.getRange(row + 1, 1);

    //if cell substring is number, skip it
    //because substring cannot process numbers
    cellValue = range1.getValue();
    if (typeof cellValue === 'number') {continue;};
    dash = cellValue.substring(0, 1);

    offset1 = range1.offset(1, 0).getValue();
    offset2 = range1.offset(2, 0).getValue();
    offset3 = range1.offset(3, 0).getValue();

    //if -, then merge offset cells 1 and 2
    //and enter "Order complete" in offset cell 2.
    if (dash === "-") {
       range1.offset(1, 0).setValue(offset1 + " " + offset2);
       //Translate
       range1.offset(2, 0).setValue("Order complete");
     };

    //The real slow part...
    //if - and offset 3 is not blank, then INSERT CELL
    if (dash === "-" && offset3) {
       //select from three rows down to last
       //move selection one more row down (down 4 rows total)
       s.getRange(row + 1, 1, lastRow).offset(3, 0).moveTo(range1.offset(4, 0));
     };    
  };
}

格式更新：

如果您需要了解如何使用字体或背景颜色来格式化输出，请查看此后续问题。希望您能从这些专业人士的建议中受益 :)

- MinneapolisCoder9

---

@Rubén 如果您正在寻找其他答案，我可以问一下吗？如果我误解了您的情况，我很抱歉。 - Tanaike

@Tanaike，当前答案侧重于OP的特定代码，但是有些概念可以简要解释，以便服务更广泛的观众，如刚开始使用Google Apps Script处理数组的人。这是因为最近出现了一个问题，其中OP表示他们无法使用有关错误消息的问题的答案。 - Rubén

@Rubén 谢谢您的回复。我认为答案的脚本正在使用批处理操作。因此，例如，您希望使用其他批处理操作来检索相同的结果作为其他方法之一。我的理解正确吗？ - Tanaike

@Tanaike 不用担心，感谢您的时间和问题。 - Rubén

@Rubén 举个例子，这份报告对你的情况有用吗？https://gist.github.com/tanaikech/d102c9600ba12a162c667287d2f20fe4 - Tanaike

Answer 1

问题：

• 在循环中使用.getValue()和.setValue()会导致处理时间增加。

文档摘录：

• 减少服务调用：

尽可能在Google Apps Script本身完成的任何任务都比需要从Google服务器或外部服务器获取数据的调用快得多，例如对电子表格、文档、站点、翻译、UrlFetch等的请求。

• 前瞻性缓存：

Google Apps Script已经内置了一些优化措施，例如使用前瞻性缓存来检索脚本可能获取的内容，并使用写入缓存来保存可能设置的内容。

• 最小化“读/写”数量：

您可以编写脚本以充分利用内置缓存，通过最小化读取和写入次数。

• 避免交替读/写：

交替执行读取和写入命令会变慢。

• 使用数组：

为了加速脚本，可以使用一个命令将所有数据读入数组，对数组中的数据执行任何操作，然后使用一个命令将数据写出。

慢脚本示例：

/** 
 * Really Slow script example
 * Get values from A1:D2
 * Set values to A3:D4
 */

function slowScriptLikeVBA(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  //get A1:D2 and set it 2 rows down
  for(var row = 1; row <= 2; row++){
    for(var col = 1; col <= 4; col++){
      var sourceCellRange = sh.getRange(row, col, 1, 1);
      var targetCellRange = sh.getRange(row + 2, col, 1, 1);
      var sourceCellValue = sourceCellRange.getValue();//1 read call per loop
      targetCellRange.setValue(sourceCellValue);//1 write call per loop
    }
  }
}

• 注意每个循环中都进行了两次调用（排除Spreadsheet ss、Sheet sh和range的调用。只包括昂贵的get/set值调用）。在这个例子中有两个循环；进行了8次读取调用和8次写入调用，以完成一个简单的2x4数组复制粘贴。
• 此外，注意到读取和写入调用交替进行，使得“向前看”缓存变得无效。
• 服务总调用次数：16
• 所需时间：约5秒+

快速脚本示例：

/** 
 * Fast script example
 * Get values from A1:D2
 * Set values to A3:D4
 */

function fastScript(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  //get A1:D2 and set it 2 rows down
  var sourceRange = sh.getRange("A1:D2");
  var targetRange = sh.getRange("A3:D4");
  var sourceValues = sourceRange.getValues();//1 read call in total
  //modify `sourceValues` if needed
  //sourceValues looks like this two dimensional array:
  //[//outer array containing rows array
  // ["A1","B1","C1",D1], //row1(inner) array containing column element values
  // ["A2","B2","C2",D2],
  //]
  //@see https://dev59.com/jb3pa4cB1Zd3GeqPXBE3
  targetRange.setValues(sourceValues);//1 write call in total
}

• 服务调用总数：2
• 耗时：约0.2秒

参考资料：

• 最佳实践
• range方法的getValues()返回什么？setValues()接受什么？

- TheMaster

Answer 2

使用像 .getValue() 和 .moveTo() 这样的方法可能会非常耗时。另一种替代方法是使用批量操作，在一次调用中获取所有列值，并在重塑数据后写入表格。当您运行脚本时，您可能已经注意到以下警告：

该脚本使用了一个被认为是昂贵的方法。每个调用都会生成一个耗时的远程服务器调用。这可能对脚本的执行时间产生重大影响，特别是在处理大量数据时。如果性能是脚本的问题，您应该考虑使用另一种方法，例如 Range.getValues()。

使用.getValues() 和.setValues() 重写您的脚本即可：

function format() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var lastRow = s.getLastRow(); // more efficient way to get last row
  var row;

  var data = s.getRange("A:A").getValues(); // gets a [][] of all values in the column
  var output = []; // we are going to build a [][] to output result

  //loop through all cells in column A
  for (row = 0; row < lastRow; row++) {
    var cellValue = data[row][0];
    var dash = false;
    if (typeof cellValue === 'string') {
      dash = cellValue.substring(0, 1); 
    } else { // if a number copy to our output array
      output.push([cellValue]); 
    }
    // if a dash  
    if (dash === "-") {
      var name = (data[(row+1)][0]+" "+data[(row+2)][0]).trim(); // build name
      output.push([cellValue]); // add row -state
      output.push([name]); // add row name 
      output.push(["Order complete"]); // row order complete
      output.push([""]); // add blank row
      row++; // jump an extra row to speed things up
    } 
  }
  s.clear(); // clear all existing data on sheet
  // if you need other data in sheet then could
  // s.deleteColumn(1);
  // s.insertColumns(1);

  // set the values we've made in our output [][] array
  s.getRange(1, 1, output.length).setValues(output);
}

测试你的脚本使用20行数据，发现它需要4.415秒才能执行，上述代码只需0.019秒即可完成

- mhawksey

---

作为一种完成技巧，当使用这个[]推送技术时，是否可以调用一个函数？例如：var rowPlus1 = (data[(row+1)[0]]).getA1Notation() -> ="Order in cell range " + rowPlus1 + " is complete!" -> "Order in cell range A5 is complete!" - MinneapolisCoder9

getValues()返回一个基本的数组对象，因此无法访问getA1Notation()。但是，你可以使用当前的output.length()来计算行，例如：rowPlus1 = "A单元格范围中的订单"+(output.length()+1)+"已完成！";（未经测试，你可能需要将+1调整为+2） - mhawksey

我无法弄清楚那个问题，但我能够简单地使用matrix.push（['订单' + name + '已处理！']）; 谢谢你，问题解决了！ - MinneapolisCoder9