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

问题描述

我刚刚写下了我的第一批谷歌应用程序脚本，从VBA移植过来，这些脚本格式化了一列客户订单信息（感谢您所有的方向）。

描述：

这些代码通过它们的前缀来标识状态代码，然后将以下名字与一个姓氏（如果存在的话） 。然后它会写出Order complete（订单完成），其中姓氏已经存在。最后，如果订单之间没有空白，它会插入一个必要的空白单元格。

问题：

问题是处理时间。它无法处理更长的数据列。我警告说，脚本中大量使用了方法Range.getValue。

现有优化：

根据对

格式更新：

有关使用字体或背景颜色格式化输出的指导， up问题此处。希望你可以从这些专业人士给我的建议中获益：） 使用 .getValue（ ）和 .moveTo（）在执行时间上可能非常昂贵。另一种方法是使用批处理操作，您可以全部获得在一次调用中写入表单之前，根据需要重新整理数据并重新整形。当您运行脚本时，您可能已经注意到以下警告：

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

使用 .getValues（）和。setValues（）你的脚本可以改写为：

  function format（）{
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var s = ss.getActiveSheet（）; 
 var lastRow = s.getLastRow（）; //更有效的方式获取最后一行
 var row; 
 
 var data = s.getRange（A：A）。getValues（）; //获得列
中所有值的[] [] var output = []; //我们要构建一个[] []来输出结果
 
 //循环遍历列A 
中的所有单元格（row = 0; row< lastRow; row ++）{ 
 var cellValue = data [row] [0]; 
 var dash = false; 
 if（typeof cellValue ==='string'）{
 dash = cellValue.substring（0，1）; 
} else {//如果一个数字拷贝到我们的输出数组
 output.push（[cellValue]）; 
} 
 //如果破折号
 if（破折号=== - ）{
 var name =（data [（row + 1）] [0] + +数据[（行+ 2）] [0]）修剪（）; //建立名称
 output.push（[cellValue]）; //添加行 - 状态
 output.push（[name]）; //添加行名
 output.push（[Order complete]）; // row order complete 
 output.push（[]）; //添加空白行
 row ++; //跳过额外的行来加快速度
} 
} 
 s.clear（）; //清除工作表
上的所有现有数据//如果您需要工作表中的其他数据，则可以使用
 // s.deleteColumn（1）; 
 // s.insertColumns（1）; 
 
 //设置我们在输出[] []数组
 s.getRange（1，1，output.length）.setValues（output）; 
 
 
 
 
 
 $ b 
使用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屋！