由于 getValue 和单元格插入可能导致处理时间过长 [英] Long processing time likely due to getValue and cell inserts

问题描述

我刚刚编写了我的第一个谷歌应用程序脚本，从 VBA 移植而来，它格式化了一列客户订单信息(感谢您的指导).

说明:

代码通过 - 前缀标识州代码，然后将以下名字与姓氏组合在一起(如果存在).然后在姓氏所在的位置写下订单完成".最后，如果订单之间没有间隙，它会插入一个必要的空白单元格(见下图).

问题:

问题在于处理时间.它无法处理更长的数据列.我被警告

<块引用>

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

现有优化:

根据对

格式更新:

有关使用字体或背景颜色格式化输出的指南，请查看此后续问题此处.希望您能从这些专业人士给我的建议中受益:)

解决方案

使用诸如 .getValue() 和 .moveTo() 之类的方法在执行时间上可能非常昂贵.另一种方法是使用 批量操作，您可以获得所有列值并在一次调用中写入工作表之前根据需要遍历数据整形.当您运行脚本时，您可能已经注意到以下警告:

<块引用>

脚本使用了一种被认为开销很大的方法.每个调用生成对远程服务器的耗时调用.那可能对脚本的执行时间有重要影响，尤其是大数据.如果性能是脚本的问题，您应该考虑使用另一种方法，例如Range.getValues().

使用 .getValues() 和 .setValues() 你的脚本可以改写为:

函数格式() {var ss = SpreadsheetApp.getActiveSpreadsheet();var s = ss.getActiveSheet();var lastRow = s.getLastRow();//更有效的获取最后一行的方法无功排；var data = s.getRange("A:A").getValues();//获取列中所有值的 [][]var 输出 = [];//我们将构建一个 [][] 来输出结果//遍历A列的所有单元格for (row = 0; row < lastRow; row++) {var cellValue = 数据[行][0];var dash = false;if (typeof cellValue === 'string') {破折号 = cellValue.substring(0, 1);} else {//如果一个数字复制到我们的输出数组output.push([cellValue]);}//如果是破折号如果(破折号 ===-"){var name = (data[(row+1)][0]+" "+data[(row+2)][0]).trim();//构建名称output.push([cellValue]);//添加行状态output.push([名称]);//添加行名output.push(["订单完成"]);//行顺序完成output.push([""]);//添加空行行++；//多跳一行以加快速度}}s.clear();//清除工作表上的所有现有数据//如果您需要工作表中的其他数据，则可以//s.deleteColumn(1);//s.insertColumns(1);//设置我们在输出 [][] 数组中创建的值s.getRange(1, 1, output.length).setValues(output);}

用 20 行数据测试你的脚本发现执行需要 4.415 秒，上面的代码在 0.019 秒内完成

I've just written my first google apps scripts, ported from VBA, which formats a column of customer order information (thanks to you all of your direction).

Description:

The code identifies state codes by their - prefix, then combines the following first name with a last name (if it exists). It then writes "Order complete" where the last name would have been. Finally, it inserts a necessary blank cell if there is no gap between the orders (see image below).

Problem:

The issue is processing time. It cannot handle longer columns of data. I am warned that

Method Range.getValue is heavily used by the script.

Existing Optimizations:

Per the responses to this question, I've tried to keep as many variables outside the loop as possible, and also improved my if statements. @MuhammadGelbana suggests calling the Range.getValue method just once and moving around with its value...but I don't understand how this would/could work.

Code:

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

Formatting Update:

For guidance on formatting the output with font or background colors, check this follow-up question here. Hopefully you can benefit from the advice these pros gave me :)

解决方案

Using methods like .getValue() and .moveTo() can be very expensive on execution time. An alternative approach is to use a batch operation where you get all the column values and iterate across the data reshaping as required before writing to the sheet in one call. When you run your script you may have noticed the following warning:

The script uses a method which is considered expensive. Each invocation generates a time consuming call to a remote server. That may have critical impact on the execution time of the script, especially on large data. If performance is an issue for the script, you should consider using another method, e.g. Range.getValues().

Using .getValues() and .setValues() your script can be rewritten as:

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

Testing your script with 20 rows of data revealed it took 4.415 seconds to execute, the above code completes in 0.019 seconds

这篇关于由于 getValue 和单元格插入可能导致处理时间过长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！