Search code examples
google-apps-scriptgoogle-sheetsgmail-apimailmerge

Mail Merge on Google Sheets and Apps Script

I currently have a script that will take a particular sheet and send it to the email address on Cell C8 as a PDF, this cell is currently populated by selecting a name in a dropdown, as we are using it to send receipts, is there a way to loop through the script and send automatic emails with their respective copy? I've tried already with the for statement but no luck so far. Below my code (which is working perfectly but for a single email only) and a spreadsheet sample. Any ideas on how to achieve this?

function emailSpreadsheetAsPDF() {

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PDF").getRange("C8");

    var email = emailRange.getValues();

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName("PDF"); // Enter the name of the sheet here

    var subject = "Test Receipt ";

    var body = "\n Please see attached your current test receipt: ";

    // Base URL
    var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

    /* Specify PDF export parameters
    From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
     */

    var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
         + '&size=letter' // paper size legal / letter / A4
         + '&portrait=true' // orientation, false for landscape
         + '&fitw=true&source=labnol' // fit to page width, false for actual size
         + '&sheetnames=false&printtitle=false' // hide optional headers and footers
         + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
         + '&fzr=false' // do not repeat row headers (frozen rows) on each page
         + '&gid='; // the sheet's Id

    var token = ScriptApp.getOAuthToken();

    var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
            headers : {
                'Authorization' : 'Bearer ' + token
            }
        }).getBlob().setName(sheet.getName() + ".pdf");

    // Uncomment the line below to save the PDF to the root of your drive. 
    //  var newFile = DriveApp.createFile(response).setName(sheet.getName() + ".pdf")

    if (MailApp.getRemainingDailyQuota() > 0)
        GmailApp.sendEmail(email, subject, body, {
            htmlBody : body,
            attachments : [response]
        });
        }

https://docs.google.com/spreadsheets/d/1p4p9A9z8-EqlFGnlJ1o360gA8lcp-L29iuIPNylC4e8/edit?usp=sharing

Solution

  • I believe your goal as follows.

    • You want to send the emails by changing the name of cell "C6" in the sheet PDF.
    • You want to achieve this using Google Apps Script.

    Modification points:

    • At first, about MATCH, the default value of the 3rd argument is 1. So in your case, 0 is required to be set. So please modify the formulas of "C8", "E17", "E19" and "E21" as follows.

      • C8: =INDEX(Sheet2!B3:B,MATCH(C6,Sheet2!A3:A,0))
      • E17: =INDEX(Sheet2!C3:C,MATCH(C6,Sheet2!A3:A,0))
      • E19: =INDEX(Sheet2!D3:D,MATCH(C6,Sheet2!A3:A,0))
      • E21: =INDEX(Sheet2!E3:E,MATCH(C6,Sheet2!A3:A,0))

    • In your case, I think that there are several approaches. So in this answer, I would like to propose the following flow.

      1. Retrieve values from "Sheet2".
      2. Create sheets for converting to PDF data.
        • This part is run in the loop.
          1. Copy the sheet PDF as the template.
          2. Set the name at the cell "C6".
          3. Export the template sheet as the PDF data.
          4. Send an email using the email corresponding to the name of "C6".
          5. Delete the template sheet.

    Modified script:

    function myFunction() {
  // 1. Retrieve values from "Sheet2".
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  var values = sheet.getRange("A3:A" + sheet.getLastRow()).getValues();
  var template =  ss.getSheetByName("PDF");
  var ssId = ss.getId();
  var token = ScriptApp.getOAuthToken();
  var filenameOfPdf = template.getSheetName();
  
  // 2. Create sheets for converting to PDF data.
  values.forEach(([v], i) => {
    // 1. Copy the sheet `PDF` as the template.
    var temp = template.copyTo(ss).setName("temp" + (i + 1));
    
    // 2. Set the name at the cell "C6".
    temp.getRange("C6").setValue(v);
    SpreadsheetApp.flush();
    var email = temp.getRange("C8").getValue();
    
    // 3. Export the template sheet as the PDF data.
    var subject = "Test Receipt ";
    var body = "\n Please see attached your current test receipt: ";
    var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ssId);
    var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
    + '&size=letter' // paper size legal / letter / A4
    + '&portrait=true' // orientation, false for landscape
    + '&fitw=true&source=labnol' // fit to page width, false for actual size
    + '&sheetnames=false&printtitle=false' // hide optional headers and footers
    + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
    + '&fzr=false' // do not repeat row headers (frozen rows) on each page
    + '&gid='; // the sheet's Id
    var response = UrlFetchApp.fetch(url + url_ext + temp.getSheetId(), {headers : {'Authorization' : 'Bearer ' + token}}).getBlob().setName(filenameOfPdf + ".pdf");
    
    // 4. Send an email using the email corresponding to the name of "C6".
    if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {htmlBody : body,attachments : [response]});
    
    // 5. Delete the template sheet.
    ss.deleteSheet(temp);
  });
}

    References: