Замените пустое поле строкой в ​​формах Google

Question

Этот код прекрасно работает! После отправки формы она автоматически заполняет созданный мной шаблон, а затем отправляет его на мою электронную почту. Это прекрасно работает, если пользователь не оставляет пустые поля в форме. Если это так, порядок автозаполнения неправильный... Есть ли способ автоматически включать текст (например, "не указано"), если пользователь решает оставить поле пустым? Если пользователь вводит текст, отлично! если нет, то я бы хотел, чтобы он показывал "не указано".

Пожалуйста помоги!

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

Answer 1

Получите ответы, используя именованные значения вместо индексов... не будет никакой путаницы, даже если некоторые поля оставить пустыми.

Смотрите документацию здесь.

Вы можете извлечь имена элементов с помощью метода 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

---

РЕДАКТИРОВАТЬ 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"]}
*/

Answer 2

Была аналогичная проблема. Должна быть возможность заменить lastResponses на e

// get the last response
var allResponses = FormApp.getActiveForm().getResponses();
var lastResponses = allResponses[allResponses.length-1].getItemResponses();

// convert values into dictionary
var response = {};
for (var i = 0; i < lastResponses.length; i++) {
  response[lastResponses[i].getItem().getTitle()] = lastResponses[i].getResponse();
}

// access values like this (directly in code)
var full name = response['first name'] + response['last name']