将 Google 表单上的空字段替换为字符串

这段代码效果很好！ 提交表单后，它会自动填充我创建的模板，然后将其发送到我的电子邮件。 除非用户在表单上留下空白字段，否则这非常有效。 如果是这样的话，自动填充的顺序是错误的...... 如果用户决定将字段留空，是否可以自动包含文本（例如“未指定”）？ 如果用户输入文本，那就太好了！如果没有，那么我希望它显示“未指定”。

请帮忙！

 // Requisition Form
// Get template from Google Docs and name it
var docTemplate = "the tamplate I created";
var docName = "Requisition Form";

    // When Form Gets submitted
    function onFormSubmit(e) {
      //Get information from form and set as variables
      var email_address = "myemail";
      var business_entity = e.values[2];
      var rotation = e.values[3];
      var category = e.values[4];
      var city = e.values[5];
      var state = e.values[6];
      var environment = e.values[7];
      var date = e.values[8];
      var recurring = e.values[9];
      var supervisor = e.values[10];
      var phone = e.values[11];
      var email = e.values[12];
      var background = e.values[13];
      if (e.values[13]=null) {
          e.values[13]='not specified';}  //this does not work!
      var skills = e.values[14];
        if (e.values[14]=null) {
          e.values[14]='not specified';}  //this does not work!
      var development = e.values[15];
      var knowledge = e.values[16];
      var responsibilities = e.values[17];

      // Get document template, copy it as a new temp doc, and save the Doc’s id
      var copyId = DocsList.getFileById(docTemplate)
      .makeCopy(docName+' for '+supervisor)
      .getId();

      // Open the temporary document
      var copyDoc = DocumentApp.openById(copyId);

      // Get the document’s body section
      var copyBody = copyDoc.getActiveSection();

      // Replace place holder keys, in our google doc template
      copyBody.replaceText('keyBU', business_entity);
      copyBody.replaceText('keyRotation', rotation);
      copyBody.replaceText('keyCategory', category);
      copyBody.replaceText('keyCity', city);
      copyBody.replaceText('keyState', state);
      copyBody.replaceText('keyEnvironment', environment);
      copyBody.replaceText('keyDate', date);
      copyBody.replaceText('keyRecurring', recurring);
      copyBody.replaceText('keySupervisor', supervisor);
      copyBody.replaceText('keyPhone', phone);
      copyBody.replaceText('keyEmail', email);
      copyBody.replaceText('keyBackground', background);
      copyBody.replaceText('keySkills', skills);
      copyBody.replaceText('keyDevelopment', development);
      copyBody.replaceText('keyKnowledge', knowledge);
      copyBody.replaceText('keyResponsibilities', responsibilities);

      // Save and close the temporary document
      copyDoc.saveAndClose();

      // Convert temporary document to PDF
      var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

      // Attach PDF and send the email
      var subject = "Requisition Form";
      var body = "Here is a Requisition Form from " + supervisor + "";
      MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

      // Delete temp file
      DocsList.getFileById(copyId).setTrashed(true);
    }

使用命名值而不是索引获取响应...即使某些字段留空，也不会出现任何可能的混乱。

See 文档在这里.

您可以使用以下命令检索项目名称FormApp 方法 getTitle()或者直接在脚本中输入名称，因为您似乎确切地知道问题标题。

例子 ：

e.namedValues   {'First Name': ['Jane'], 'Timestamp': ['6/7/2015 20:54:13'], 'Last Name': ['Doe']}  An object containing the question names and values from the form submission

EDIT 2 :

（第一次编辑非常相似，但我注意到空字段检测写得不好。 我在下面的代码中使用了紧凑的 IF 形式以使代码更简单。）

下面是一个完整的例子，使用namedValues代替indexed values.

顺便说一句，我添加了一个测试函数，用于模拟具有 2 个空字段的表单提交。（最初的想法借自这篇文章（Mogsdad）：如何测试 GAS 中的触发功能？)

function onFormSubmit(e) {
  //Get information from form and set as variables
  var email_address = "myemail";
  var business_entity = e.namedValues['Please select your business entity'];
  var rotation = e.namedValues['Rotation Name'];
  var category = e.namedValues["Rotation Category"];
  var city = e.namedValues["City"];
  var state = e.namedValues["State"];
  var environment = e.namedValues["Work Environment"];
  var date = e.namedValues["Date Available"];
  var recurring = e.namedValues["Is this a recurring ALLEX rotation or a one time only request?"];
  var supervisor = e.namedValues["Supervisor name"];
  var phone = e.namedValues["Supervisor phone number"];
  var email = e.namedValues["Supervisor email"];
  var background = e.namedValues["Preferred background"]!=''? e.namedValues["Preferred  background"] : 'not specified'; 
  // compact IF form : if not empty then value else 'not specified'
  var skills = e.namedValues["Specific skills required"] !=''? e.namedValues["Specific skills required"] : 'not specified';
  var development = e.namedValues["Skills to be developed in this rotation"];
  var knowledge = e.namedValues["Knowledge to be gained in the rotation"];
  var responsibilities = e.namedValues["Project and/or ALLEX Responsibilities"];
  var docName = 'test doc';
  // Get document template, copy it as a new temp doc, and save the Doc’s id
  var copyId = DocsList.getFileById("18-j5MOX3MkVsydXzFTjH5DZaEVF-5ZhYZQuDcLkEDCI")
  .makeCopy(docName+' for '+supervisor)
  .getId();
  // Open the temporary document
  var copyDoc = DocumentApp.openById(copyId);
  // Get the document’s body section
  var copyBody = copyDoc.getActiveSection();
  // Replace place holder keys, in our google doc template
  copyBody.replaceText('keyBU', business_entity);
  copyBody.replaceText('keyRotation', rotation);
  copyBody.replaceText('keyCategory', category);
  copyBody.replaceText('keyCity', city);
  copyBody.replaceText('keyState', state);
  copyBody.replaceText('keyEnvironment', environment);
  copyBody.replaceText('keyDate', date);
  copyBody.replaceText('keyRecurring', recurring);
  copyBody.replaceText('keySupervisor', supervisor);
  copyBody.replaceText('keyPhone', phone);
  copyBody.replaceText('keyEmail', email);
  copyBody.replaceText('keyBackground', background);
  copyBody.replaceText('keySkills', skills);
  copyBody.replaceText('keyDevelopment', development);
  copyBody.replaceText('keyKnowledge', knowledge);
  copyBody.replaceText('keyResponsibilities', responsibilities);
  // Save and close the temporary document
  copyDoc.saveAndClose();
  // Convert temporary document to PDF
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf");
  // Attach PDF and send the email
  var subject = "Requisition Form";
  var body = "Here is a Requisition Form from " + supervisor + "";
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(), subject, body, {htmlBody: body, attachments: pdf});  
  // Delete temp file
  DocsList.getFileById(copyId).setTrashed(true);
}

function test(){
  var sh = SpreadsheetApp.getActive().getSheetByName('FormResponses');
  var headers = getTitlesFromSs(sh);
  var data = sh.getRange(2,1,1,sh.getLastColumn()).getValues().join(',').split(',');
  data[13]="";// empty field background
  data[14]="";// empty field skills
  var e = buildFakeForm(headers,data);
  onFormSubmit(e);
}

function getTitlesFromSs(sh) {
  var headers = sh.getDataRange().getValues().shift();
  for(var n in headers){
//    Logger.log('title '+n+' = '+headers[n]);
  }
  return headers;
}

function buildFakeForm(headers,data){
  var response = {};
  var namedValues = {};
  var values = {};

  for(var n in headers){
    namedValues[headers[n]]= [data[n]];
  }
  response.namedValues = namedValues;
  response.values = data;
  Logger.log(JSON.stringify(response));
  return response; // this object can simulate a form reception. Usage : (for example) onFormSubmit(response); // will do the same as submiting a form...
}

/*  result

 {"namedValues":{"Timestamp":"Fri Sep 12 2014 22:06:29 GMT+0200 (CEST)","Username":"serge","Please select your business entity":"testCol20","Rotation Name":"testCol21","Rotation Category":"testCol4","City":"testCol5","State":"testCol6",
 "Work Environment":"testCol7","Date Available":"testCol8","Is this a recurring ALLEX rotation or a one time only request?":"testCol9","Supervisor name":"testCol10","Supervisor phone number":"testCol11",
 "Supervisor email":"testCol12","Preferred background":"testCol13","Specific skills required":"testCol14","Skills to be developed in this rotation":"testCol15","Knowledge to be gained in the rotation":"testCol16",
 "Project and/or ALLEX Responsibilities":"testCol17","What is the purpose of this ALLEX Requisition Form submission?":"testCol18"},"values":["Fri Sep 12 2014 22:06:29 GMT+0200 (CEST)","serge","testCol2","testCol3",
 "testCol4","testCol5","testCol6","testCol7","testCol8","testCol9","testCol10","testCol11","testCol12","testCol13","testCol14","testCol15","testCol16","testCol17","testCol18","testCol19","testCol20","testCol21"]}
*/