谷歌脚本/减少执行时间低于执行时间限制-Java 学习之路

编辑＃2：

我决定尝试批量在所有电子邮件被触发后运行的复制粘贴操作 . 在之前发布的代码中，在每个循环的最后一行中，我将每个采购订单（“电子邮件”表）中的项目复制并粘贴到另一个（“AsignRec”） . 现在，我想要做的是将每个循环中“电子邮件”表中的项目存储到Javascript数组中，并将所有项目粘贴到最后的“AsignRec”中，只需一次 .

但是，我坚持最后的粘贴/ setValues（） . 我相信阵列是正确形成的，因为它的长度为49，这是发送给供应商的唯一SKU的数量 . 仍然，在setValues（[OCitems]）第185行，我得到错误 "Incorrect range height, was 1 but should be 49 （第185行，文件"TestArrayMultiple4"）“ .

我认为这意味着目标/输出范围与数组/输入（称为OCitems）的大小不同 . 我不明白为什么，因为我使用OCitems.length定义了输出范围的长度 . 我错过了什么，不知道是什么 .

This is the important bit of the code, and full code below. Same GDocs link as before, script file TestArrayMultiple4, lines 160-185. https://docs.google.com/spreadsheets/d/1yzvMTh0VYhRhiexNzQPIjTwz1FCMq4XnbpGvCF1FYu8/edit#gid=436022027

/// Get Range we want to change to creating Javascript array and paste at end only

var OcNoHeader = sheet.getRange("B9:J" + MaxTableRow).getValues(); // get items to send to supplier from "Email Sheet"

// if supplier number is 1, create array "OCitems" by storing OcNoHeader. If not supplier #1, then append to existing array "OCitems"

  if(y == 1){
    var OCitems = OcNoHeader}
    else
      {for(j=0;j<OcNoHeader.length;j++){
      OCitems.push(OcNoHeader[j]);
  }}


  Logger.log(OCitems);
  Logger.log("OCitems length = " + OCitems.length)

  debugger;

i++; // after firing email, y+1 to go to next supplier
Logger.log("i++ IF =" + 1);

Logger.log("new i ELSE =" + 1)

debugger;

} // only do while x = max number of suppliers reached
  while (i < x);  

  sheet3.getRange(3, 3, OCitems.length, 9).setValues([OCitems]); // paste operation, NumRows set equal to length of array

=========================

编辑＃1：使用几个单元格的getValues改进性能，而不是多次执行单独的getValue（） . Unfortunately, this hasn't reduced the execution time in a stable or noticeable manner (sometimes it finished before 6min, sometimes not).

在下面发布代码（您可以在 new Sheet shared below 中的脚本文件“ TestArrayMultiple2 ”中访问它）：

使用Execution transcript，我看到虽然以前花费很长时间的getValue（）行的执行时间基本上已经减少到零，但是其他代码行现在花费更多的时间并且杀死从批处理其他getValue获得的增益（） .

在特定细胞上仍然有4-5个“个体”getValue（）（比以前少得多），但我不明白为什么它们需要这么长时间 . 因此，即使我删除剩余的“个人”getValue（），如果只剩下一个，也需要更长时间 .

在我看来它与缓存有关（我完全理解这个概念），原因如下：1）它总是循环中的第一个getValue（），它花费的时间最长 . 2）我尝试通过更改发送所有电子邮件之后发生的复制/粘贴操作的代码（在"TestArrayMultiple2"脚本文件中的第150行）进入不同的路径 . 我基本上尝试创建一个数组，在每个循环（append / push方法）中获取更多数据但是没有正确（第二个脚本文件是最后一个， "TestArrayMultiple3" ），但我可以看到电子邮件被解雇更快 .

再次，您的帮助将不胜感激 .

> // version with 1 getDataRange array which stores for supplier ID, name, email, MaxTableRow for PO email, email subject all from Dashboard sheet

function TestArrayMultiple2() {

  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var sheet = ss.getSheetByName("Email"); 
  var sheet1 = ss.getSheetByName("Pedido email");
  var sheet2 = ss.getSheetByName("Dashboard");
  var sheet3 = ss.getSheetByName("AsignRec");
  var sheet4 = ss.getSheetByName("ListadoProductos");
  var sheet5 = ss.getSheetByName("Registro-Consolid");
  var sheet6 = ss.getSheetByName("Registro-Unico");

  var x = ss.getSheetByName("Dashboard").getRange("C4").getValue();
  Logger.log("x = " + x)

  var offsetV = 4; // number of rows of offset for email status to be inserted in Dashboard sheet 

  var OffSetColProv = 1; // column in Dashboard sheet with supplier name
  var OffsetColMaxPOrows = 3; // Number of unique SKUs or rows in PO. Replace MaxTableRow formula in Email Sheet
  var OffSetColPzas = 4; // column in Dashboard sheet with number of items in supplier purchase order. 
  var OffSetColEmail = 5; // column in Dashboard sheet with supplier email
  var OffSetColCC = 13; // column in Dashboard sheet with supplier email CC
  var OffSetColSubject = 14; // column in Dashboard sheet with supplier email Subject

  var colStatus = 11; // column in Dashboard sheet where send status of email inserted -----> LEAVE AS IS FOR NOW, not an offset, is fixed, col. K = 11
  var OffsetEmailRows = 8 // number of rows in Email sheet before the items in PO are shown

  var ProvNumEmail = sheet.getRange(1,2); // Supplier number in email sheet used to refresh products in purchase order email via FILTER formula

  var StatusRange = sheet2.getRange("K5:K100");
  Logger.log("StatusRange = " + StatusRange)

  var currentTime =  new Date();
  var timestamp = Utilities.formatDate(currentTime,'GMT-0600','dd/MM/yyyy HH:mm:ss');
  Logger.log("timestamp = " + timestamp);

  var ProvArray = sheet2.getRange("E5:S100");
  var DashValues = ProvArray.getValues();

  i = 0;

  do {


  var y = DashValues[i][0];
  Logger.log("y = " + y)

  ProvNumEmail.setValue(y);   // set value of next supplier in Email sheet to load next purchase order products


    // emails var here in order to update email value in IF email = ERROR condition and skip to else

  var Prov = DashValues[i][OffSetColProv];
  Logger.log("Prov = " + Prov);

  var EmailSubject = DashValues[i][OffSetColSubject];
  Logger.log("EmailSubject = " + EmailSubject)

  var MaxTableRow = DashValues[i][OffsetColMaxPOrows] + OffsetEmailRows;
  Logger.log("MaxTableRow = " + MaxTableRow)

  var EmailTo = DashValues[i][OffSetColEmail];
  Logger.log("EmailTo = " + EmailTo)

  var EmailCC = DashValues[i][OffSetColCC];
  Logger.log("EmailCC = " + EmailCC)

  var Piezas = DashValues[i][OffSetColPzas];
  Logger.log("Piezas = " + Piezas)

  SpreadsheetApp.flush();

  var name = "Petsy Compras - Juan Carlos León";
  var ReplyToEmail = "compras@petsy.mx";
  var email = EmailTo;
  var subject = EmailSubject;
  var name = name;
  var replyTo = ReplyToEmail;
  var Emailcc = EmailCC;
  var schedRange = sheet.getRange("B7:J"+MaxTableRow);
  var body = '<div>';            
    body += "Estimados," +'<br>' + '<br>';
    body += "Envío la orden de compra, por un total de " + '<b>' + Piezas + " piezas." + '</b>' +'<br>' + '<br>';
    body += "Favor de confirmar las existencias lo más rápidamente posible, dentro del mismo correo y"+ '<b><a style="color:#FF0000">'+ " enviar factura a: "+ '</a></b>' + "facturasproveedores@petsy.mx." +'<br>' + '<br>';
    body += "Al dar " +'<b><a style="color:#FF0000"> '+ "RESPONDER A TODOS" + '</a></b>' +" la tabla con los productos pedidos se hace editable: favor de marcar por cada item si será faltante." +'<br>' + '<br>';
    body += "Cualquier duda avísenme por favor." +'<br>' + '<br>';
    body += "Un saludo" +'<br>' + '<br>';
    body += '<b>'     + "Juan Carlos León" + '<b>' + '<br>';
    body += "Petsy Compras"+'<br>';
    body += "Mapa aquí: "+'<br>';
    body += "Fijo directo 1: (55) 68 12 07 97 / Fijo directo 2: (55) 68 12 07 99 / Cel y Whatsapp: 55 32 23 57 17"+'<br>' + '<br>';
    body += "" +'<br>' + '<br>';
    body += getHtmlTable(schedRange);
    body += '</div>';


  // variables for error email

  var emailERR = 'oscialom@petsy.mx'
  var subjectERR = 'ERROR ENVIO OC' + ' // ' + Prov + ' ' + timestamp


  if(email == 'ERROR' || MaxTableRow == 0) // skip condition to go begin loop with y+1

{

// if above skip condition is true, y+1 to move to next purchase order

  Logger.log("y = " + y);
  Logger.log("IF");
  sheet2.getRange(y + offsetV,colStatus).setValue('NOT_SENT'); // set email send status next to supplier in Dashboard sheet
  {
     GmailApp.sendEmail(emailERR, subjectERR, "Requires HTML", 
                { 
                    'name':name, 
                    'replyTo':replyTo,
                    'htmlBody':'', 
                    'cc':''});
        }


  i++;

  Logger.log("new i IF =" + 1);
  continue

}
else
{

  // if skip condition is false, fire current supplier purchase order email email

  Logger.log("i = " + i);
  Logger.log("y = " + y);
  Logger.log("ELSE");

  GmailApp.sendEmail(email, subject, "Requires HTML", 
                { 
                    'name':name, 
                    'replyTo':replyTo,
                    'htmlBody':body, 
                    'cc':EmailCC});
        }        

sheet2.getRange(y + offsetV,colStatus).setValue('OK'); // set email send status next to supplier in Dashboard sheet



// START copy-paste Asign-Rec

var MaxTableRowASIGN = sheet3.getRange("A1").getValue();
Logger.log("MaxTableRowASIGN = " + MaxTableRowASIGN)


/// Get Range we want to change to creating Javascript array and paste at end only

  debugger; // stop debugger at this point !! REMOVE OR PLACE AT CORRECT LINE IF USING DEBUGGER

var OcNoHeader = sheet.getRange("B9:J" + MaxTableRow);
var ConsolAsignRec = sheet3.getRange("B3:K" + MaxTableRow);
var ProvOC = sheet.getRange("B2").getValue();
Logger.log("ProvOC = " + ProvOC)

var MaxRowB = sheet3.getRange("C1").getValue() + 1;
Logger.log("MaxRowB = " + MaxRowB);

var NextRowB = MaxRowB + 1;
Logger.log("NextRowB = " + NextRowB);

OcNoHeader.copyTo(sheet3.getRange(MaxTableRowASIGN + 1,3),{contentsOnly:true});
var NumRowsProv = OcNoHeader.getNumRows();

var ProvOCcolumn = sheet3.getRange(MaxRowB, 2, NumRowsProv)
Logger.log("ProvOCcolumn = " + ProvOCcolumn);

ProvOCcolumn.setValue(ProvOC);

// END copy-paste Asign-Rec

i++; // after firing email, y+1 to go to next supplier
Logger.log("i++ IF =" + 1);



Logger.log("new i ELSE =" + 1)



} // only do while x = max number of suppliers reached
  while (y<x);  

  // set y = 1 to reset value again after finishing loop 

  sheet.getRange(1,2).setValue(1); // reset ProvNumber = 1 to start again next time script is fired.

  var EmailsSent = sheet2.getRange("C10").getValue(); // set values
  Logger.log("EmailsSent = " + EmailsSent)

  var EmailErrors = sheet2.getRange("C11").getValue();
  Logger.log("EmailErrors = " + EmailErrors)

  var MaxTableRowEND = sheet2.getRange("C9").getValue();
  var schedRange = sheet2.getRange("E4:K" + MaxTableRowEND);
  var emailEND = "oscialom@petsy.mx";
  var subjectEND = 'OCs Inbound enviadas' + ' ' + timestamp + " (errores " +  EmailErrors + " / enviados " + EmailsSent + ")";
  var EmailCCEND = "";
  var bodyEND = getHtmlTable(schedRange);

   GmailApp.sendEmail(emailEND, subjectEND, "Requires HTML", 
                { 
                    'name':name, 
                    'replyTo':replyTo,
                    'htmlBody':bodyEND, 
                    'cc':EmailCCEND});

StatusRange.clearContent();


/// START RecordTimestamp code

  var Avals = sheet4.getRange("A1:A").getValues();
  var lastrow1 = Avals.filter(String).length;
  Logger.log('lastrow1 =' + lastrow1)

  var Avals2 = sheet5.getRange("A1:A").getValues();
  var lastrow2 = Avals2.filter(String).length;
  Logger.log('lastrow2 =' + lastrow2)

    sheet4.getRange("B2:B" + lastrow1).copyTo(sheet5.getRange(lastrow2 + 1, 1)) // copy order-items to Registro sheet, after last filled row

    sheet4.getRange("K2:K" + lastrow1).copyTo(sheet5.getRange(lastrow2 + 1, 2)) // copy Prov1 to Registro sheet, after last filled row


  var Avals3 = sheet5.getRange("C1:C").getValues();
  var lastrow2c = Avals3.filter(String).length;
  Logger.log('lastrow2c =' + lastrow2c);


  if(lastrow2 == 1)
  { sheet5.getRange(lastrow2c + 1, 3, lastrow1 - 1).setValue(timestamp)
    Logger.log('IF')

    }

    else
    {
    sheet5.getRange(lastrow2c + 1, 3, lastrow1 - 1).setValue(timestamp)
    Logger.log('ELSE')
    }

var MaxTableRowEMAIL = sheet6.getRange("G5").getValue()

var subject = "Items pedidos en OC automatizada " + timestamp
var email = "oscialom@petsy.mx";
var EmailCC = "";
var EmailBCC;
var name = "Petsy Compras";
var ReplyToEmail = "compras@petsy.mx"
var schedRange = sheet6.getRange("A1:C" + MaxTableRowEMAIL);
var body = getHtmlTable(schedRange);  


{
     GmailApp.sendEmail(email, subject, "Requires HTML", 
                { 
                    'name':name, 
                    'replyTo':ReplyToEmail,
                    'htmlBody':body, 
                    'cc':''});
        }

/// END RecordTimestamp code


Logger.log("MaxTableRowASIGN " + MaxTableRowASIGN);

var endtime = new Date();
Logger.log("timestamp end " + timestamp);
Logger.log("endtime " + Utilities.formatDate(endtime,'GMT-0600','dd/MM/yyyy HH:mm:ss'));

var scripttime = (endtime - currentTime);
Logger.log("scripttime original" + scripttime);

// strip the ms
scripttime /= 1000;
Logger.log("scripttime / 1000" + scripttime);

// get seconds (Original had 'round' which incorrectly counts 0:28, 0:29, 1:30 ... 1:59, 1:0)
var seconds = Math.round(scripttime % 60);
Logger.log("scripttime % 60" + scripttime);

// remove seconds from the date
scripttime = Math.floor(scripttime / 60);
Logger.log("scripttime / 60" + scripttime);

// Browser.msgBox("Script completado en " + seconds + " segundos",Browser.Buttons.OK_CANCEL); // removed MsgBox to measure real execution time
Logger.log("seconds " + seconds)

}

=====================

原始邮政

我编写了一个Google脚本，以便将采购订单流程自动化到多个供应商 . 该过程采用产品列表（来自工作表ListaProductos），将产品信息格式化为电子邮件格式（工作表"Email"），发送电子邮件，并将一些复制/粘贴到同一电子表格的其他工作表中 . However, I always run into the execution time at around 75% of the script . 我知道下一步该尝试什么 .

1 回答

1
我在这部分代码中看到了问题：
```
do { 
  // read info from the sheet
  range.getValue();
  // more code here...

} // only do while x = max number of suppliers reached
  while (y<x)
```
操作 getValue 需要很长时间才能运行 . 最佳做法是使用整个范围：
```
var data = sheet.getDataRange().getValuses();
```
然后使用数据作为进一步计算的来源 .

在这里查看更多信息：

https://developers.google.com/apps-script/best_practices
回复于 2024-05-15T21:57:42+08:00

谷歌脚本/减少执行时间低于执行时间限制

1 回答

相关问题