用Google表单上的字符串替换空字段 [英] Replace empty field with string on Google forms

问题描述

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

This code works great! Once the form is submitted, it auto-populates a template I have created, then send it to my email. This works great unless the user leaves empty fields on the form. If that's the case, the order of the auto-populate is wrong... Is there anyway to automatically include a text (for example "not specified") if the user decides to leave a field blank? If the user enters a text, great! if not, then I would want it to show "not specified".

请帮助！

Please help!

 // 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);
    }

推荐答案

使用命名值而不是索引......即使某些字段留空也不会有任何混淆。

Get the responses using named values instead of indexes... there won't be any possible confusion even if some fields are left empty.

请参阅 here here。

See documentation here.

您可以检索项目名称与 FormApp方法getTitle（）或输入因为您似乎完全知道问题标题。

You can retrieve the item names with the FormApp method getTitle() or type the names directly in your script since you seem to know exactly the question titles.

示例：

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

---

编辑2：

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

---

EDIT 2 :

(first edit was very similar but I noticed that the empty field detection was not well written. I used a compact IF form in the code below to make code simpler.)

下面是一个使用 namedValues 代替索引值。

顺便说一句，我添加了一个测试函数，提交2个空字段。（从这篇文章中借用的原始想法（Mogsdad）：如何测试GAS中的触发器功能？）

Btw, I added a test function that simulates a form submission with 2 empty fields.(original idea borrowed from this post (Mogsdad):How can I test a trigger function in 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"]}
*/

这篇关于用Google表单上的字符串替换空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！