Auto-generate PDF from spreadsheet data and email with HTML formatting after form submission

Other Questions Google Sheets
1

I’m currently on my third project using Google Apps Script, but I have encountered one last issue. My goal is to generate a PDF automatically with the data collected from a Google Form, then send this PDF via email.

So far, everything is set up correctly except for the fact that the PDF still contains placeholder text, like {staff}, instead of the actual data from the spreadsheet.

Here’s my existing code:

function generateReport(){
  const spreadsheetId = 'sheet-id-987654321';
  const workbook = SpreadsheetApp.openById(spreadsheetId);
  const worksheet = workbook.getSheetByName('Report-Info');
  const records = worksheet.getDataRange().getDisplayValues().slice(1);
  const mailTemplate = HtmlService.createTemplateFromFile('notification.html');
  const reportTemplate = HtmlService.createTemplateFromFile('reportLayout.html');

  records.forEach((record,index)=>{
    const employeeName = record[0];
    const reviewId = record[4];
    
    const recipient = {
      StaffName: employeeName,
      ReviewDate: record[2]
    };
    
    mailTemplate.recipient = recipient;
    mailTemplate.id = reviewId;

    reportTemplate['staff'] = record[0];
    reportTemplate['date'] = record[1];
    reportTemplate['period'] = record[2];
    reportTemplate['id'] = reviewId;
    reportTemplate['category'] = record[5];
    reportTemplate['score'] = record[6];
    reportTemplate['item1'] = record[7];
    reportTemplate['item2'] = record[8];
    reportTemplate['item3'] = record[10];

    const emailContent = mailTemplate.evaluate().getContent();
    const pdfContent = reportTemplate.evaluate().getContent();

    const pdfFile = Utilities.newBlob(pdfContent, MimeType.HTML).getAs(MimeType.PDF);
    pdfFile.setName(record[0] + '_' + record[1] + '.pdf');
    const recipientEmail = record[3];
    const emailSubject = 'Performance Report for ' + record[0];

    MailApp.sendEmail({
      to: recipientEmail,
      subject: emailSubject,
      htmlBody: emailContent,
      attachments: [pdfFile]
    });
  });
}

HTML template structure:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div style="font-family: Arial; font-size: 12px;">
    <table style="width: 100%;">
      <tr>
        <td style="text-align: center;">
          <h2>Performance Review</h2>
        </td>
        <td style="text-align: right;">
          <p>Employee: {staff}</p>
          <p>Review Date: {date}</p>
          <p>Period: {period}</p>
          <p>ID Number: {id}</p>
          <p>Category: {category}</p>
          <p>Final Score: {score}</p>
        </td>
      </tr>
    </table>
    <hr>
    <h4>Performance Items</h4>
    <p>Communication skills rating: {item1}</p>
    <p>Task completion assessment: {item2}</p>
    <p>Team collaboration score: {item3}</p>
  </div>
</body>
</html>

The placeholders like {staff} and {date} aren’t getting replaced with actual spreadsheet values. I’ve tried using Google Docs as templates and creating PDFs directly in code but same problem. Any ideas what I’m missing?

2

Your template syntax is the problem. Google Apps Script’s HtmlService doesn’t use curly braces - you need scriptlet tags instead. Replace all your {staff} placeholders with <?= staff ?> in your HTML template. I hit this exact issue on my first Apps Script project and wasted hours debugging before I figured out the syntax was different. Also double-check that your HTML file is actually saved as a template in the Apps Script editor, not just regular HTML. The variables you’re setting with reportTemplate['staff'] won’t work unless you use the proper scriptlet syntax.

3

Had the same issue last year with Apps Script templates. It’s definitely the placeholder syntax, but also check that your HTML file is saved with the .html extension in the Apps Script editor. Sometimes it won’t recognize it as a template otherwise. Change {staff} to <?= staff ?> and add some error handling around the template evaluation. I’ve seen evaluate() fail silently when there’s problems with template variables. Log the pdfContent variable before creating the blob to make sure the substitution actually worked.

4

you’re using the wrong syntax for HTML template variables. replace {staff} with <?= staff ?> in your HTML file. google apps script uses server-side scripting tags, not simple placeholders.