谷歌电子表格循环发送电子邮件与PDF不工作

Question

我有谷歌发票电子表格，我用vlookup公式从其他（数据）表中获取数据。只有我需要在发票表中输入行号。所以我创建了一个循环脚本，其中数据表中的自动行号将输入到发票表中，并发送带有PDF附件的电子邮件。当运行该脚本时，请正确输入每行的行号，但电子邮件不会发送。

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

}

Answer 1

您应该将for循环包装在{}括号中，否则只有下一个操作（sheet.getRange（"H11"）. setValue（i）；）将在循环中执行。

您正在覆盖循环中循环的起始值。请记住，当您再次尝试发送电子邮件时，起始值将等于结束值-

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