连接代码运行非常缓慢的谷歌脚本

Question

我的连接函数运行速度极慢，仅 28 行数据就需要将近 4 分钟才能运行，而且我还有其他代码需要运行，所以我在 Google 表格中达到了最大执行时间

如果我在 Excel 中运行类似的过程，这可能需要 15-30 秒

一切都在内存中，我看不出（由于我对 javascript 的了解有限）为什么我的代码这么慢

谢谢

包含数据的 Google 表格

//Sheet Name

//Values in Range to be overwritten by Header names

//List of Header names

//New Header Name 1

//New Header Name 2

function AsAboveSoBelow_Offers_Asks() {

   AsAboveSoBelow('Elements',

                  ["I can offer", "I have a ask"],

                  ["Header1","Header2","Header3","Header4","Header5","Header6",

                   "Header7","Header8","Header9","Header10","Header11",

                   "Header12","Header13","Header14"],

                   "Offers", 

                   "Asks");

}                  

function AsAboveSoBelow(shtName, arrPBV, arrHeaders, newHeader1, newHeader2) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s  = ss.getSheetByName(shtName);

var LC = s.getLastColumn();

var r  = s.getDataRange();

var v  = r.getValues();

var start = new Date(); 

var temp ="";

//Dim A

var A = [];

for (var i = 0; i < v.length; i++) {

     A[i] = [];

   for (var j = 0; j <= arrPBV.length - 1; j++) {

        A[i][j] = '';

   }

}

for(var e = 0; e <= arrPBV.length - 1; e++) {

    var search_Term  = arrPBV[e];

for(var row = 0; row < v.length; row++) {

  for(var col = 0; col <= arrHeaders.length - 1; col++) {

      var col2 = HTN(shtName,arrHeaders[col])

      var replace_Term = arrHeaders[col];

       if(v[row][col2-1].toString().indexOf(search_Term) > -1) {

          temp = temp + replace_Term + "|"

       }

     }

    //remove trailing pipe

     A[row][e] = temp.replace(/\|(?=\s*$)/, '')

       temp = ""

   }

 }

 A[0][0]= newHeader1

 A[0][1]= newHeader2

 s.getRange(1, v[0].length +1, v.length,A[0].length).setValues(A);

var end = new Date();

var executiontime = end - start;

Logger.log(executiontime);

};

//Helper function

function HTN(shtName,cheader){

var headers = 

SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shtName).getDataRange().getValues().shift();

var colindex = headers.indexOf(cheader);

return colindex+1;

}

Answer 1

您想降低脚本的处理成本。如果我的理解是正确的，这个答案怎么样？请认为这只是几个可能的答案之一。修改点：在您的脚本中，HTN()用作 for 循环。并且功能包括SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shtName).getDataRange().getValues().shift();。在您的脚本中，电子表格并sheetName没有改变。所以headers总是一样的。我认为这可能是主要的修改点。修改后的脚本：请按如下方式修改您的脚本。function AsAboveSoBelow_Offers_Asks() {&nbsp; AsAboveSoBelow(&nbsp; &nbsp; 'Elements',&nbsp; &nbsp; ["I can offer", "I have a ask"],&nbsp; &nbsp; ["Header1","Header2","Header3","Header4","Header5","Header6","Header7","Header8","Header9","Header10","Header11","Header12","Header13","Header14"],&nbsp; &nbsp; "Offers",&nbsp;&nbsp; &nbsp; "Asks"&nbsp; );}function AsAboveSoBelow(shtName, arrPBV, arrHeaders, newHeader1, newHeader2) {&nbsp; var ss = SpreadsheetApp.getActiveSpreadsheet();&nbsp; var s&nbsp; = ss.getSheetByName(shtName);//&nbsp; var LC = s.getLastColumn(); // It seems that this is not used.&nbsp; var r&nbsp; = s.getDataRange();&nbsp; var v&nbsp; = r.getValues();&nbsp; var start = new Date();&nbsp;&nbsp; var temp ="";&nbsp; //Dim A&nbsp; var A = [];&nbsp; for (var i = 0; i < v.length; i++) {&nbsp; &nbsp; A[i] = [];&nbsp; &nbsp; for (var j = 0; j <= arrPBV.length - 1; j++) {&nbsp; &nbsp; &nbsp; A[i][j] = '';&nbsp; &nbsp; }&nbsp; }&nbsp; var h = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0]; // Added&nbsp; for(var e = 0; e <= arrPBV.length - 1; e++) {&nbsp; &nbsp; var search_Term&nbsp; = arrPBV[e];&nbsp; &nbsp; for(var row = 0; row < v.length; row++) {&nbsp; &nbsp; &nbsp; for(var col = 0; col <= arrHeaders.length - 1; col++) {&nbsp; &nbsp; &nbsp; &nbsp; var col2 = h.indexOf(arrHeaders[col]) + 1; // Modified&nbsp; &nbsp; &nbsp; &nbsp; var replace_Term = arrHeaders[col];&nbsp; &nbsp; &nbsp; &nbsp; if(v[row][col2-1].toString().indexOf(search_Term) > -1) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; temp = temp + replace_Term + "|"&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; //remove trailing pipe&nbsp; &nbsp; &nbsp; A[row][e] = temp.replace(/\|(?=\s*$)/, '')&nbsp; &nbsp; &nbsp; temp = ""&nbsp; &nbsp; }&nbsp; }&nbsp; A[0][0]= newHeader1&nbsp; A[0][1]= newHeader2&nbsp; s.getRange(1, v[0].length +1, v.length,A[0].length).setValues(A);&nbsp; var end = new Date();&nbsp; var executiontime = end - start;&nbsp; Logger.log(executiontime);};笔记：在我的环境中，修改脚本的处理时间约为 5 秒。如果这不是直接的解决方案，我深表歉意。