谷歌脚本循环性能 [英] google script loop performance

问题描述

我是Google脚本的新手，我不确定为什么在简单循环中与Excel VBA相比，我会遇到如此糟糕的性能.

I am new to google scripts and I am not sure why I am experiencing such poor performances compared to Excel VBA for a simple loop.

我附上了下面的代码，该代码在约1200行上是一个循环，并且每秒删除约2-3行！

I attached the code below which is a loop on ~1200 rows and it is deleting about 2-3 rows per second!

我编写的脚本效率很低吗?我还不熟悉Javascript，但是对我来说似乎并不那么低效.

Is the script I wrote very inefficient? I am not familiar with Javascript yet, but it does not look so inefficient to me.

正常吗?服务器现在很慢，但是通常会好很多吗吗?

Is it normal? Is it the server very slow now, but usually much better?

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");  
  var drng = sheet.getDataRange();
  var rng = sheet.getRange(2,1, drng.getLastRow()-1,26);

  var rangeformula = sheet.getRange(2,26, drng.getLastRow()-1);
  rangeformula.setFormula('=SUM(K2:V2)'); var rngA = rng.getValues();


var len = rngA.length;
for(var i = len; i>=1; i--) {

  if(rngA[i-2][25] == 0){

    sheet.deleteRow(i);}}

推荐答案

好吧，对于初学者来说，删除行是如此昂贵的过程(与VBA相比)并不明显.这是我过去避免避免在循环内删除行的一种变通方法.

Ok, it is not obvious for a beginner that deleting rows is such an expensive process (compared to VBA). Here is a work around I used to avoid deleting rows inside a loop.

PS:我对此并不陌生，因此这不是最优雅的方法，但可能会对其他人有所帮助.

PS: I am new to this, so it is not the most elegant way, but could be helpful for others.

function removeEmpty() {


var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");  
  var drng = sheet.getDataRange();
  var rng = sheet.getRange(2,1, drng.getLastRow()-1,26);
  var rangeformula = sheet.getRange(2,26, drng.getLastRow()-1);
  rangeformula.setFormula('=SUM(K2:V2)');
  var rngA = rng.getValues();
  var newRangeVals = [];
  var len = rngA.length+1;
  for(var i = len; i >=2; i--){
    if(rngA[i-2][25] != 0){

  newRangeVals.push(rngA[i-2]);
};
  };




rng.clearContent();

  var newRange = sheet.getRange(2,1,newRangeVals.length, newRangeVals[0].length);
  newRange.setValues(newRangeVals);

}

这篇关于谷歌脚本循环性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！