Google Apps Script表单触发时报错Cannot read property 'values' of undefined求助

1. 核心报错TypeError: Cannot read property 'values' of undefined的原因 #

这个函数是Google表单提交事件的触发回调函数，参数e是表单提交时系统自动传入的事件对象，只有真实触发表单提交、或者通过触发器测试时才会有值：

• 如果直接在Apps Script编辑器点击「运行」按钮手动执行该函数，没有传入事件参数e，自然会读取不到e.values
• 如果已经配置了触发器还报错，检查触发器是否绑定正确：触发器类型选「来自表单」，事件类型选「表单提交」，关联到你绑定的Google表单

2. 代码里的其他语法/逻辑错误 #

你代码里还有几处明显错误会导致运行失败：

• 变量名引用错误：你定义的变量是NameOfTheCustomerProspect，后续两处都写成了NameOfTheCustomer/Prospect，斜杠是非法变量名字符，属于变量名写错
• file.makeCopy参数错误：makeCopy方法的第二个参数是要存入的目标文件夹对象，你写了未定义的CallReport变量，应该传入你之前获取的folder对象
• 文件名拼接逻辑错误：DateOfMeeting + ',' + NameOfTheCustomer/Prospect这里的斜杠会被识别为除法运算符，完全不符合你的命名需求

3. 修正后的完整代码 #

function autoFillGoogleDocFromForm(e) {
  // 先做参数校验，避免无参数运行报错
  if (!e || !e.values) {
    throw new Error('该函数只能通过表单提交事件触发，禁止手动直接运行');
  }

  //e.values is an array of form values
  var timestamp = e.values[0];
  var EmailAddress = e.values[1];
  var CallReportWriter = e.values[2];
  var DateOfMeeting = e.values[3];
  var NameOfTheCustomerProspect  = e.values[4];
  var ContactNameFromClient = e.values[5];
  var TitleandContacts = e.values[6];
  var PresentfromSesomo = e.values[7];
  var MeetingObjectives = e.values[8];
  var MeetingResult = e.values[9];
  var Background = e.values[10];  
  var Opportunities = e.values[11];  
  var Followuprequired = e.values[12];  
  var Responsible = e.values[13];  
  var Targetdates = e.values[14];  

  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('12zJTxLgy_Nmxk1FScyZ3vqHzYnKQ55ckdQ8RsYy-MdA'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('1SorfCjOGknFVt1ch39MJ7atBorLf_Sdr')
  // 修正文件名和传参错误
  var copy = file.makeCopy(DateOfMeeting + ',' + NameOfTheCustomerProspect + ' 通话报告', folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody(); 
  
  //Then we call all of our replaceText methods
  body.replaceText('{{MeetingDate}}', DateOfMeeting); 
  body.replaceText('{{NameOfCustomer}}', NameOfTheCustomerProspect);  
  body.replaceText('{{ReportWriterName}}', CallReportWriter); 
  body.replaceText('{{ContactNameFromClient}}', ContactNameFromClient); 
  body.replaceText('{{TitleAndContact}}', TitleandContacts); 
  body.replaceText('{{PresentFromSesomo}}', PresentfromSesomo); 
  body.replaceText('{{MeetingObjectives}}', MeetingObjectives); 
  body.replaceText('{{MeetingResults}}', MeetingResult); 
  body.replaceText('{{Background}}', Background); 
  body.replaceText('{{Opportunities}}', Opportunities); 
  body.replaceText('{{FollowUpRequired}}', Followuprequired); 
  body.replaceText('{{Responsible}}', Responsible); 
  body.replaceText('{{TargetDate}}', Targetdates); 
  
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); 
}

4. 后续操作步骤 #

• 保存修正后的代码
• 在Apps Script编辑器左侧「触发器」菜单，新增触发器：选择对应的函数，触发来源选「表单」，事件类型选「表单提交」，按照提示完成权限授权
• 不要手动在编辑器运行该函数，直接提交一次表单测试功能即可

内容的提问来源于stack exchange，提问作者Da Silva Mandeiro