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