create pdf form sheet doc and send email

I have a Google Sheet with Col A as First Name, Col B as Last Name, Col C as Class, Col D as quiz link, and Col E as email Address I have one Google Doc Template Letter with invitation text. I want to use this Doc as a template to send student invitations. Make a copy I Doc template to generate a PDF letter by replacing {{First}} with First Name from Col A, {{Last}} with Last Name of Col B, {{Classroom}} with Class from Col C, {{Quiz Link}} with quiz link on column D and {{Today}} with today's date. Name the PDF file as "Quiz Online" I want to generate one PDF letter for each row of the Sheet that contains data and then email that PDF to the respective email on Col E. Generate Google Apps Script Code for this.

*****************

Sure, here is the Google Apps Script code for that:

function generateAndEmailLetters() {
  // Set the ID of the Google Doc Template
  var docTemplateId = "PUT_YOUR_DOC_TEMPLATE_ID_HERE";
  
  // Set the ID of the Google Sheet containing student data
  var sheetId = "PUT_YOUR_GOOGLE_SHEET_ID_HERE";
  
  var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  // Start from the second row to skip header
  for (var i = 1; i < data.length; i++) {
    var firstName = data[i][0];
    var lastName = data[i][1];
    var classroom = data[i][2];
    var quizLink = data[i][3];
    var emailAddress = data[i][4];
    
    // Make a copy of the template document
    var docCopy = DriveApp.getFileById(docTemplateId).makeCopy();
    var docCopyId = docCopy.getId();
    var doc = DocumentApp.openById(docCopyId);
    var body = doc.getBody();
    
    // Replace placeholders in the document with data from the sheet
    body.replaceText('{{First}}', firstName);
    body.replaceText('{{Last}}', lastName);
    body.replaceText('{{Classroom}}', classroom);
    body.replaceText('{{Quiz Link}}', quizLink);
    body.replaceText('{{Today}}', Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"));
    
    // Save and close the document
    doc.saveAndClose();
    
    // Generate PDF from the document
    var pdfFile = DriveApp.getFileById(docCopyId).getAs("application/pdf");
    
    // Rename the PDF file as "Quiz Online"
    pdfFile.setName("Quiz Online");
    
    // Send email with the PDF attachment
    MailApp.sendEmail({
      to: emailAddress,
      subject: "Quiz Invitation",
      body: "Dear " + firstName + ",\n\nPlease find attached the quiz invitation letter.\n\nBest regards,\nYour Name",
      attachments: [pdfFile]
    });
    
    // Delete the temporary document copy
    DriveApp.getFileById(docCopyId).setTrashed(true);
  }
}
  // Run the function.
generateAndEmailPdfLetters();

****************** 

Before using the script, make sure to follow these steps:

1. Open your Google Doc template and copy its ID from the URL (the long alphanumeric string after "/d/").
2. Open your Google Sheet containing student data and copy its ID from the URL (the long alphanumeric string between "/spreadsheets/d/" and "/edit").
3. Replace "PUT_YOUR_DOC_TEMPLATE_ID_HERE" with the Doc Template ID you copied in Step 1.
4. Replace "PUT_YOUR_GOOGLE_SHEET_ID_HERE" with the Sheet ID you copied in Step 2.

Now, save the script and run the generateAndEmailLetters function. It will generate one PDF letter for each row of data in the sheet that contains data and then email that PDF to the respective email address in Col E.

Please note that you need to have the necessary permissions to access the Google Sheet, Google Doc Template, and the ability to send emails from your Gmail account to use this script.

 

 

 

*******************

 

Sure, here is the Google Apps Script code to generate PDF letters and email them to students:

function generateAndEmailPdfLetters() { // Get the spreadsheet with the student data. var sheet = SpreadsheetApp.openById("YOUR_SPREADSHEET_ID");

// Get the template document. var templateDoc = DocumentApp.openById("YOUR_TEMPLATE_DOCUMENT_ID");

// Create a new PDF file for each row of data in the spreadsheet. for (var i = 1; i <= sheet.getLastRow(); i++) { // Get the student data from the spreadsheet. var firstName = sheet.getRange("A" + i).getValue(); var lastName = sheet.getRange("B" + i).getValue(); var classroom = sheet.getRange("C" + i).getValue(); var quizLink = sheet.getRange("D" + i).getValue(); var today = Utilities.formatDate(new Date(), "MM/dd/yyyy");

// Replace the placeholders in the template document with the student data.
templateDoc.replaceText("{{First}}", firstName);
templateDoc.replaceText("{{Last}}", lastName);
templateDoc.replaceText("{{Classroom}}", classroom);
templateDoc.replaceText("{{Quiz Link}}", quizLink);
templateDoc.replaceText("{{Today}}", today);

// Save the PDF file.
var pdfFile = templateDoc.saveAsPdf("Quiz Online" + i + ".pdf");

// Send the PDF file to the student's email address.
MailApp.sendEmail(sheet.getRange("E" + i).getValue(), "Quiz Invitation", "Please find your quiz invitation attached.", {
  attachment: pdfFile
});

} }