Compiling information in a for loop to email as confirmation-CodePudding

I have the following code to run a for loop and send all invoices that have adequate information. The code works fine, but I would like to send a confirmation email to myself after completed with the information compiled as the script runs. The code I need is in the *********** sections. Thanks in advance!

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,
          });
 }

CodePudding user response：

I believe your goal is as follows.

You want to create an email message like "I have sent invoices to *person1* for *amount1*, *person2* for *amount2*.... etc" in your for loop.

In this case, how about the following modification?

From:

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);}
}

To:

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.
}

In this case, message is `"I have sent invoices to person1 for amount1, person2 for amount2.... etc"``. So it can be used as the message body. It's as follows.

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

In your script, 3 scripts of const nextValue = values[values.indexOf(range.getValue()) 1] || values[0]; and range.setValue(nextValue) in the loop are the same. So I tihnk that you might be able to also modify it as follows.

  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.
  }

And also, the following modified script might be able to reduce the process cost. Please modify your for loop as follows.

  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);