Google spreadsheet loop for send email with PDF not working

Question

I have google spreadsheet of invoice, where i have used vlookup formula to take data from other (data) sheet. Only I need to enter row number in invoice sheet. So I created loop script where auto row number from data sheet will enter in invoice sheet and send email with PDF attachment. When run that script row Number of each row enter correctly but the email will not go.

function SendInvoiceNew4() {
  var sheet = SpreadsheetApp.getActiveSheet();

// Loop from CELL Number Value to CELL Number Value EQUAL
  for(i=sheet.getRange("H11").getValue();i<=sheet.getRange("I11").getValue();i++) // *************** Enter Start Invoice Serial No Cell Reference & Last Serial No Cell is Auto
  sheet.getRange("H11").setValue(i); //Auto Enter Next Loop Serail Number

  var InvDate = Utilities.formatDate(new sheet.getRange("H13").getValue(), "GMT+1", "MMM-yyyy") //Set invoice Date Format = MONTH & YEAR
  var emailTo = sheet.getRange("B12").getValue(); //Get Email Address from Data
  var message = 'Dear' + "\n\n" + 'See attached your attached invoice in PDF format.' + "\n\n" + 'Thanking you' + "\n" + 'www.xyz.in' + "\n" + '[DO NOT REPLY to this Email.]'; //Enter Custom Messagen ************************************************** Message Body
  var subject = 'Invoice for Month ' + InvDate;  // ************* Enter Cell Reference for Date of Invoice for Subject

// Convert Invoice Sheet to PDF
  var originalSpreadsheet = SpreadsheetApp.getActive(); // Set original invoice sheet
  var pdf = DriveApp.getFileById(originalSpreadsheet.getId()).getAs('application/pdf').getBytes(); // Convert PDF file
  var attach = {fileName:'Invoice',content:pdf, mimeType:'application/pdf'}; //Set File Name

// Send Email with attached PDF file   
  //MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
  MailApp.sendEmail(emailTo, subject, message);
  SpreadsheetApp.flush(); // Make sure the cell is updated right away in case the script is interrupted

}

Do you have any error in the Apps Script Dashboard when running the script? — Alessandro
And still no email is sent from your account? Did you check your gmail sent folder? — Alessandro
only last record goes email. Means only one email goes with attachment. But Cell H13 change as per every record. — Nitu Kumari
I can see that you don't send emails in the loop. Maybe you are forgetting to wrap your code in the for loop? Can you please explain which part of your code is not working as you wanted? — Alessandro

Alessandro Alessandro · Accepted Answer · 2020-04-17T15:01:52

Solution

You should wrap your for loop in {} brackets otherwise only the very next operation (sheet.getRange("H11").setValue(i);) will be executed in the loop.

Considerations

You are overwriting the starting value for the loop inside your loop. Keep in mind that when you will try again to send the emails the starting value will be equals to the ending value -> the loop will only execute once. I suggest taking the loop values from another sheet where you can keep track of invoices serial numbers.

Code:

function SendInvoiceNew4() {
  var sheet = SpreadsheetApp.getActiveSheet();

// Loop from CELL Number Value to CELL Number Value EQUAL
  for(i=sheet.getRange("H11").getValue();i<=sheet.getRange("I11").getValue();i++) {// *************** Enter Start Invoice Serial No Cell Reference & Last Serial No Cell is Auto
    sheet.getRange("H11").setValue(i); //Auto Enter Next Loop Serail Number

    var InvDate = Utilities.formatDate(new sheet.getRange("H13").getValue(), "GMT+1", "MMM-yyyy") //Set invoice Date Format = MONTH & YEAR
    var emailTo = sheet.getRange("B12").getValue(); //Get Email Address from Data
    var message = 'Dear' + "\n\n" + 'See attached your attached invoice in PDF format.' + "\n\n" + 'Thanking you' + "\n" + 'www.xyz.in' + "\n" + '[DO NOT REPLY to this Email.]'; //Enter Custom Messagen ************************************************** Message Body
    var subject = 'Invoice for Month ' + InvDate;  // ************* Enter Cell Reference for Date of Invoice for Subject

    // Convert Invoice Sheet to PDF
    var originalSpreadsheet = SpreadsheetApp.getActive(); // Set original invoice sheet
    var pdf = DriveApp.getFileById(originalSpreadsheet.getId()).getAs('application/pdf').getBytes(); // Convert PDF file
    var attach = {fileName:'Invoice',content:pdf, mimeType:'application/pdf'}; //Set File Name

    // Send Email with attached PDF file   
    MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
    //MailApp.sendEmail(emailTo, subject, message);
    SpreadsheetApp.flush(); // Make sure the cell is updated right away in case the script is interrupted
  }
}

Google spreadsheet loop for send email with PDF not working

1 Answers

Solution

Considerations

Code: