Google Docs从Spreadsheet自动填充数据失败问题排查求助

问题描述 #

使用以下Google Apps Script实现从Spreadsheet自动填充Google Docs模板时，仅部分功能生效：可正常基于模板生成新文档、正确命名并将文档链接写入原Spreadsheet，但无法填充任何指定数据（模板占位符完全保留）。已核对表头与列号，未发现问题。

代码片段 #

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();
}
function createNewGoogleDocs() {

  const googleDocTemplate = DriveApp.getFileById('1ONJpemIqAwfTuEnRhkeoVnqVnfnWVexG_RnmzsP96FQ');
  const destinationFolder = DriveApp.getFolderById('19OwE5z9AnJ0nzEvU-lJ0tNTw3yJM2BHH')
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form responses 1')
  const rows = sheet.getDataRange().getValues();

  rows.forEach(function(row, index){
    if (index === 0) return;
    if (row[6]) return;
    const copy = googleDocTemplate.makeCopy(`${row[0]} Student Details` , destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[3]).toLocaleDateString(); // 错误根源

    body.replaceText('{{What is your full name?}}', row[0]);
    body.replaceText('{{What are your personal pronouns?}}', row[1]);
    body.replaceText('{{What is your personal email address?}}', row[2]);
    body.replaceText('{{What is your school student email?}}', row[3]);
    body.replaceText('{{What is your mobile phone number?}}', row[4]);
    body.replaceText('{{Do you have anything that you would like us or your tutor to know?}}', row[5]);

    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index + 1, 7).setValue(url)
  })
}

Docs模板内容 #

Student # details:
Student name: {{What is your full name?}} ({{What are your personal pronouns?}})
Student email 1: {{What is your personal email address?}}
Student email 2: {{What is your school student email?}}
Student phone number: {{What is your mobile phone number?}}
Subject: FILL
Comments/requests from your student: {{Do you have anything that you would like us or your tutor to know?}}

Spreadsheet列标题（A-G） #

• A: What is your full name?
• B: What are your personal pronouns?
• C: What is your personal email address?
• D: What is your school student email?
• E: What is your mobile phone number?
• F: Do you have anything that you would like us or your tutor to know?
• G: Document Link

问题根源与解决方法 #

1. 错误的日期格式处理（核心问题） #

代码中const friendlyDate = new Date(row[3]).toLocaleDateString();这一行完全错误：

• row[3]对应D列的学校学生邮箱，并非日期数据，将邮箱字符串转为日期会生成无效日期，直接触发脚本异常，中断当前行的后续执行（包括所有replaceText操作）。
• 解决：直接删除这一行无用且错误的代码。

2. 额外验证项（可选排查） #

• 确认模板中的占位符与代码中的字符串完全精确匹配：包括空格、标点、大小写，避免全角/半角符号差异。
• 检查Spreadsheet对应行的数据是否为空：若字段为空，replaceText会将占位符替换为空字符串，但不会保留原占位符；若占位符完全未变，优先排查上述核心问题。

内容的提问来源于stack exchange，提问作者Pedro Mello