CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nitu Kumari
Nitu Kumari

Reputation: 1

Google spreadsheet loop for send email with PDF not working

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

}

Upvotes: 0

Views: 246

Answers (1)

Alessandro
Alessandro

Reputation: 2998

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

Upvotes: 0

Related Questions