將for回圈中的資訊編譯為電子郵件作為確認

我有以下代碼來運行 for 回圈并發送所有具有足夠資訊的發票。代碼作業正常，但我想在完成腳本運行時編譯的資訊后向自己發送一封確認電子郵件。我需要的代碼在 *********** 部分。提前致謝！

function testSendAll() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var invoice = ss.getSheetByName("Invoice");
  var range = invoice.getRange("B13");                                                               // Cell of validation
  const values = [...new Set(range.getDataValidation().getCriteriaValues()[0].getValues().flat())];   // Gets array of validation
  var first = values[0];                                                                              // 1st cell of validation
  var number = values.length - 1;                                                                     // Length of validation
  range.setValue(first);                // Sets value to first one
                                                                           
for(i = 0;i < number;i  ) {             // Loop number of names
    var test = invoice.getRange("B18").getValue();   
    var email =  invoice.getRange("B14").getValue();
    var person = invoice.getRange("B13").getValue();
    var amount = invoice.getRange("E3").getValue();
    if (test != "") {  
      if (email != "You have not added this contact") {

  //  emailSpreadsheetAsPDF();
  //  makeHistory();

//         *************************************************************                       
//******* Here I would like to compile person and amount for each loop ************
//         ***********************************************************

    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
    range.setValue(nextValue);
  } else {  
    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
    range.setValue(nextValue);
  }

}
else {
    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
range.setValue(nextValue);}
}
  const emailAdd = '[email protected]';
  
  // Subject of email message
  const subject = 'Your Sent Invoices';

  // Now the message
//    **************************************************************
//  ****** Here I would like to include all the gathered information from the for loop
//     I would like it to read "I have sent invoices to *person1* for *amount1*, 
//      *person2* for *amount2*.... etc
// ********************************************************************************

  const body = message;

  GmailApp.sendEmail(emailAdd, subject, body, {
      htmlBody: htmlMessage,
          });
 }

uj5u.com熱心網友回復：

我相信你的目標如下。

• 您想像"I have sent invoices to *person1* for *amount1*, *person2* for *amount2*.... etc"在 for 回圈中一樣創建電子郵件。

在這種情況下，如何進行以下修改？

從：

for(i = 0;i < number;i  ) {             // Loop number of names
    var test = invoice.getRange("B18").getValue();   
    var email =  invoice.getRange("B14").getValue();
    var person = invoice.getRange("B13").getValue();
    var amount = invoice.getRange("E3").getValue();
    if (test != "") {  
      if (email != "You have not added this contact") {

  //  emailSpreadsheetAsPDF();
  //  makeHistory();

//         *************************************************************                       
//******* Here I would like to compile person and amount for each loop ************
//         ***********************************************************

    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
    range.setValue(nextValue);
  } else {  
    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
    range.setValue(nextValue);
  }

}
else {
    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
range.setValue(nextValue);}
}

到：

var message = "I have sent invoices to ";
for (i = 0; i <= number; i  ) {
  var test = invoice.getRange("B18").getValue();
  var email = invoice.getRange("B14").getValue();
  var person = invoice.getRange("B13").getValue();
  var amount = invoice.getRange("E3").getValue();
  message  = `${person} for ${amount}`;
  if (i == number) continue;
  if (test != "") {
    if (email != "You have not added this contact") {
      const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
      range.setValue(nextValue);
    } else {
      const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
      range.setValue(nextValue);
    }
  } else {
    const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
    range.setValue(nextValue);
  }
  if (i < number) {
    message  = ", ";
  }
  // SpreadsheetApp.flush(); // This might be required to be used.
}

• 在這種情況下，message是`“我已經派人發票PERSON1為amount1，PERSON2為AMOUNT2 ....等”``。所以它可以用作訊息體。如下。

    const emailAdd = '[email protected]';
    const subject = 'Your Sent Invoices';
    const body = message;
    GmailApp.sendEmail(emailAdd, subject, body);
  

• 在您的腳本中，回圈中const nextValue = values[values.indexOf(range.getValue()) 1] || values[0];和 的3 個腳本range.setValue(nextValue)是相同的。所以我認為你也可以按如下方式修改它。

    var message = "I have sent invoices to ";
    for (i = 0; i <= number; i  ) {
      var test = invoice.getRange("B18").getValue();
      var email = invoice.getRange("B14").getValue();
      var person = invoice.getRange("B13").getValue();
      var amount = invoice.getRange("E3").getValue();
      message  = `${person} for ${amount}`;
      if (i == number) continue;
      if (test != "") {
        const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
        range.setValue(nextValue);
      }
      if (i < number) {
        message  = ", ";
      }
      // SpreadsheetApp.flush(); // This might be required to be used.
    }
  

• 而且，以下修改后的腳本可能能夠降低流程成本。請按如下方式修改您的 for 回圈。

    var message = "I have sent invoices to ";
    var [[person], [email],,,, [test]] = invoice.getRange("B13:B18").getValues();
    var amount = invoice.getRange("E3").getValue();
    for (i = 0; i <= number; i  ) {
      message  = `${person} for ${amount}`;
      if (i == number) continue;
      if (test != "") person = values[values.indexOf(  first)] || values[0];
      if (i < number) message  = ", ";
    }
    range.setValue(person);
  

標籤：数组 for循环 谷歌应用程序脚本

上一篇：Google應用腳本：檢查電子郵件列上的電子郵件重復，如果發現相同的電子郵件將呼叫另一個電子郵件正文（谷歌表）

下一篇：如何從比較不同電子表格中的兩個單元格的作業表A中獲取唯一訊息中的所有資料