Replace empty field with string on Google forms

Question

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

e.values[13]=null should be e.values[13]==null (double equals). Also you should check the value with if( isset( e.values[13]) ) — , Sep 08 '14 at 23:34
changing it to == still doesn't work. It sends me the PDF but the order of the fields are incorrect. — Arsene de Conde, Sep 09 '14 at 13:46
var_dump(e); as the first line of the function. Are all the values there and in the correct order? If not then the process that calls your function needs to be looked at as to why it isn't filling in the correct data. — , Sep 09 '14 at 14:19
Yes, all values are in correct order ONLY when no fields are left blank/empty when submitting the form. All questions are "required" questions except values 13 and 14, which are the ones I am having trouble with. Where should var_dump(e) be placed in the code? — Arsene de Conde, Sep 09 '14 at 14:24

score 0 · Accepted Answer · edited May 23 '17 at 10:26

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

See documentation here.

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.

Example :

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 :

(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.)

Below is a full example that uses namedValues instead of indexed values.

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"]}
*/

so I could use this function for the fields that could be potentially left blank or does it have to be for all questions? so for example, for item 13, it would look like this: e.namedValues {'Preferred background':['Not Specified']} or does one e.namedValues function include ALL questions from form? — Arsene de Conde, Sep 11 '14 at 14:06
for each question : e.namedValues['Preferred background'] will return the response or null if empty. — Serge insas, Sep 11 '14 at 14:10

score 0 · Answer 2 · answered Feb 21 '20 at 18:42

Had a similar problem. Should be possible to replace lastResponses with 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']

Replace empty field with string on Google forms

2 Answers2

EDIT 2 :

Linked