Google Apps Script生成PDF时模板变量未替换问题求助

核心问题 #

你的PDF模板用了{de}这类大括号变量语法，但Google Apps Script的HtmlService.createTemplateFromFile()模板系统不识别这种写法，它要求使用GAS专属的脚本标签语法插入变量。

解决方案步骤 #

1. 修改PDF HTML模板的变量语法 #

把所有{变量名}替换为<?= 变量名 ?>（需要转义HTML内容时用）或<?!= 变量名 ?>（无需转义时用）。比如：

• {de} → <?= de ?>
• {qe} → <?= qe ?>

修改后的完整PDF模板：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div style="font-family: Arial, Helvetica, sans-serif; font-size: 12px;">
      <table style="width: 100%;">
        <tr>
          <td style="text-align: center;">
            <img src="https://1drv.ms/i/s!AqWDgdn-ZKI6jxvL-nKw7W6DDTyk?e=tVngMM" alt="Logo" style="width: 200px;">
          </td>
          <td style="text-align: right;">
            <h3>Quality Evaluation</h3>
            <p>Quality Evaluation for: <?= qe ?></p>
            <p>Date of Evaluation: <?= de ?></p>
            <p>Evaluation made by: <?= eb ?></p>
            <p>POM Number: <?= pn ?></p>
            <p>Call Type: <?= ct ?></p>
            <p>Evaluation Score: <?= es ?></p>
          </td>
        </tr>
      </table>
      <hr>
      <h4>Greeting</h4>
      <p>Did the agent use a professional tone of voice when greeting the Caller? <?= q1 ?></p>
      <p>Did the agent use proper greeting? <?= q2 ?></p>
      <hr>
      <h4>Call Etiquette</h4>
      <p>Did the agent listen actively and patiently to the caller's concerns? <?= q3 ?></p>
      <p>Did the agent avoid interrupting the caller during the call? <?= q4 ?></p>
      <p>Did the agent speak clearly and enunciate their words? <?= q5 ?></p>
      <p>Used courteous/professional language? <?= q6 ?></p>
      <p>Did the agent verify the Address, Customer name, POM#? <?= q7 ?></p>
      <hr>
      <h4>Process</h4>
      <p>Did the agent call the phone number in POM? <?= q8 ?></p>
      <p>Did the agent call 2nd number (if applicable)? <?= q9 ?></p>
      <p>Did the agent leave a voicemail? <?= q10 ?></p>
      <p>Did the agent email customer and SMS? <?= q11 ?></p>
      <p>Did the agent follow Special Instructions? <?= q12 ?></p>
      <hr>
      <h4>Email Quality</h4>
      <p>Was the email addressed to the correct recipient(s)? (HUB Manager EAM Team or Dispatch If applicable VP) <?= q13 ?></p>
      <p>Was the email clear and concise? <?= q14 ?></p>
      <hr>
      <h4>Pinnacle Note Quality</h4>
      <p>Did the agent include any special instructions or notes provided by the customer or Driver in the order documentation? <?= q15 ?></p>
      <p>Did the agent follow specific protocols or procedures for documenting orders in the system as outlined in the company's training materials or guidelines? <?= q16 ?></p>
      <hr>
      <h4>Comments</h4>
      <p>Comments or Observations <?= cm ?></p>
    </div>
  </body>
</html>

2. 调整代码中的模板实例创建逻辑 #

你之前在循环外创建pdfTemplate实例，重复赋值可能导致变量残留。建议每次循环内重新创建模板实例，确保每次都是全新的模板：

修改后的代码：

function createPDF(){
  const id = 'file ID 123456789';
  const ss = SpreadsheetApp.openById(id);
  const sheet = ss.getSheetByName('PDF-Data');
  const data = sheet.getDataRange().getDisplayValues().slice(1);
  const emailTemplate = HtmlService.createTemplateFromFile('qaemail.html');

  data.forEach((row,ind)=>{
    const name = row[0];
    const pom = row[4];
    
    const user = {
      AgentName: name,
      DateOfEvaluation: row[2]
    };
    
    emailTemplate.user = user;
    emailTemplate.id = pom;

    // 每次循环重新创建PDF模板实例
    const pdfTemplate = HtmlService.createTemplateFromFile('pdfTemplate.html');
    pdfTemplate['qe'] = row[0];
    pdfTemplate['de'] = row[1];
    pdfTemplate['eb'] = row[2];
    pdfTemplate['pn'] = pom;
    pdfTemplate['ct'] = row[5];
    pdfTemplate['es'] = row[6];
    pdfTemplate['q1'] = row[7];
    pdfTemplate['q2'] = row[8];
    pdfTemplate['q3'] = row[10];
    pdfTemplate['q4'] = row[11];
    pdfTemplate['q5'] = row[12];
    pdfTemplate['q6'] = row[13];
    pdfTemplate['q7'] = row[14];
    pdfTemplate['q8'] = row[15];
    pdfTemplate['q9'] = row[16];
    pdfTemplate['q10'] = row[17];
    pdfTemplate['q11'] = row[18];
    pdfTemplate['q12'] = row[19];
    pdfTemplate['q13'] = row[20];
    pdfTemplate['q14'] = row[21];
    pdfTemplate['q15'] = row[22];
    pdfTemplate['q16'] = row[23];
    pdfTemplate['cm'] = row[24];

    const htmlBody = emailTemplate.evaluate().getContent();
    const pdfBody = pdfTemplate.evaluate().getContent();

    const blob = Utilities.newBlob(pdfBody, MimeType.HTML).getAs(MimeType.PDF);
    blob.setName(row[0] + ' ' + row[1] + '.pdf');
    const email = row[3];
    const subject = 'Quality Evaluation PDF ' + row[0] + ' ' + row[4];

    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: htmlBody,
      attachments: [blob]
    });
  });
}

3. 验证测试 #

• 保存修改后的模板和代码
• 运行createPDF函数，测试单条数据生成PDF的效果
• 检查PDF中的变量是否已被正确替换

额外提示 #

• 如果模板要插入带格式的HTML内容，用<?!= 变量名 ?>语法，避免HTML被转义
• 确认Sheet列索引对应正确，比如代码中q3绑定的row[10]确实是对应的数据列

内容的提问来源于stack exchange，提问作者Metrpolitan Warehouse