Google script that checks if a Google form checkbox was marked

Question

I have a Google Script that merges from a Google Form Spreadsheet to PDF. Is it possible in Google Apps script to check if a checkbox was marked, replicate the form.

Question
Choice 1
Choice 2
Choice 3

Basically, here it works: There is a Google Doc template that has keys for anything that a user fills in the Google Form. The script references the spreadsheet and template.

Here is the working code I have:

//Get template and name it
var docTemplate = "id";
var docName = "name";


//Reference Spreadsheet
function getColIndexByName(colName) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange(1, 1, 1, numColumns).getValues();
  for (i in row[0]) {
    var name = row[0][i];
    if (name == colName) {
      return parseInt(i) + 1;
    }
  }
  return -1;
}


//Form submittal
function onFormSubmit() {
//spreadsheet values
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var email = sheet.getRange(row, getColIndexByName("E-mail:")).getValue();
  var title = sheet.getRange(row, getColIndexByName("Title of Proposed Request (problem      / opportunity):")).getValue();
  var school = sheet.getRange(row, getColIndexByName("School")).getValue();
  var office = sheet.getRange(row, getColIndexByName("Office")).getValue();
  var facproblems =sheet.getRange(row, getColIndexByName("Which of the following best  describes the facilities-related problem (or opportunity) your School/Office has identified?")).getValue();
  var uses = sheet.getRange(row, getColIndexByName("Please list what other uses /     functions need to be adjacent to your program/activity.")).getValue();
  var describeProblem = sheet.getRange(row, getColIndexByName("a) Describe the identified problem or opportunity.")).getValue();
  var impacts = sheet.getRange(row, getColIndexByName("b) Part I: Provide justification and evidence in support of the identified problem/opportunity. Impacts - select one or more from the below list.")).getValue();
  var impactsExplain = sheet.getRange(row, getColIndexByName("b) Part II: Provide justification and evidence in support of the identified problem/opportunity. Explain how the proposed project will achieve the impacts you indicated above.")).getValue();
  var advance = sheet.getRange(row, getColIndexByName("c) Explain how the requested project will advance the College’s Strategic Plan and/or the requesting school or office’s academic plan and / or business plan.")).getValue();
  var estimate = sheet.getRange(row, getColIndexByName("d) Part I: What is your estimate of the potential cost (in rough order of magnitude) of addressing the problem or opportunity described above?  ")).getValue();
  var estimateExplain = sheet.getRange(row, getColIndexByName("d) Part II: Please explain how your developed this estimate.")).getValue();
  var requestor = sheet.getRange(row, getColIndexByName("Name of Requestor/Proposer")).getValue();
  var date = sheet.getRange(row, getColIndexByName("Date")).getValue();
  var title = sheet.getRange(row, getColIndexByName("Title of Proposed Request (problem / opportunity):")).getValue();
  var phone = sheet.getRange(row, getColIndexByName("Phone No.")).getValue();
  var sponsor = sheet.getRange(row, getColIndexByName("Name of Project Sponsor")).getValue();


  //copy and combine doc name and name form
  var copyId = DocsList.getFileById(docTemplate)
  .makeCopy(docName+' for '+requestor)
  .getId();

//open temp doc and copy body
 var copyDoc = DocumentApp.openById(copyId);
 var copyBody = copyDoc.getActiveSection();

  //replace placeholderkeys from template
  copyBody.replaceText('keyProposedTitle', title);
  copyBody.replaceText('keySchool', school);
  copyBody.replaceText('keyOffice', office);
  copyBody.replaceText('keyFacProblem', facproblems);
  copyBody.replaceText('keyUses', uses);
  copyBody.replaceText('keyDescribeProblem', describeProblem);
  copyBody.replaceText('keyImpacts', impacts);
  copyBody.replaceText('keyImpactsExplain', impactsExplain);
  copyBody.replaceText('keyAdvance', advance);
  copyBody.replaceText('keyEstimate', estimate);
  copyBody.replaceText('keyEstimateExplain', estimateExplain);
  copyBody.replaceText('keyRequestor', requestor);
  copyBody.replaceText('keyDate', date);
  copyBody.replaceText('keyPhone', phone);
  copyBody.replaceText('keyEmail', email);
  copyBody.replaceText('keySponsor', sponsor);

 //save and close doc
  copyDoc.saveAndClose();

//create PDF of temp doc
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

//email recipient
  var subject = "Manhattan College Space Planning Form Submittal";
  var body = "Thanks for submitting your request. \n\nPlease find an attachment with the details given."
  MailApp.sendEmail(email, subject, body, {htmlBody: body, attachments: pdf});

//trash temp doc
  DocsList.getFileById(copyId).setTrashed(true); 


}

Thanks for any help.

Welcome to SO! Can you clarify what you are trying to do? Namely, what is the label and significance of the checkbox, and why are you trying to replicate the form? Based on the email you are setting up towards the end of your code block, I'm guessing that you are trying to copy the entire form's contents into the email to the user? — Derek
Thanks for responding. The checkbox doesn't have a label since it was created using Google Forms no App Script to create it. There are a bunch of questions that have checkboxes so that users can simply mark which ones apply to them. The uses gets emailed a PDF attachment on their responses. — sigkg

sigkg sigkg · Accepted Answer · 2013-11-07T16:26:42

I figured it out.

On the Google Form Spreadsheet did the following:

Separated the options into different columns in the spreadsheet.
Used the formula = ArrayFormula(IFERROR(SIGN(FIND(F$1;$E2:$E))))
F$1 is used in this case since the choice is in Column F Row 1
Column E is the original column where the question was asked and the responses start in Row 2, so $E2:$E is used.
It places a "1" in any response that matches the column.

On the code I added more variables. Below is the entire code:

//Get template and name it
var docTemplate = "1n9ytnC2bNrkPZ62pNiyi3Xy6GcDRvGPLZRMfJxsLY1g";
var docName = "ManhattanCollegeSpacePlanning";


//Reference Spreadsheet
function getColIndexByName(colName) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange(1, 1, 1, numColumns).getValues();
  for (i in row[0]) {
    var name = row[0][i];
    if (name == colName) {
      return parseInt(i) + 1;
    }
  }
  return -1;
}


//Form submittal
function onFormSubmit() {
//spreadsheet values
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var email = sheet.getRange(row, getColIndexByName("E-mail:")).getValue();
  var title = sheet.getRange(row, getColIndexByName("Title of Proposed Request (problem / opportunity):")).getValue();
  var school = sheet.getRange(row, getColIndexByName("School")).getValue();
  var office = sheet.getRange(row, getColIndexByName("Office")).getValue();
  var addSpaceExisting = sheet.getRange(row, getColIndexByName("Additional space is needed for existing projects/programs/activities.")).getValue();
  var addSpaceNew = sheet.getRange(row, getColIndexByName("Additional space is needed for new projects/programs/activities.")).getValue();
  var currentSpace = sheet.getRange(row, getColIndexByName("Current space is substandard or inappropriate")).getValue();
  var relocation = sheet.getRange(row, getColIndexByName("Relocation required for better adjacencies (list what other uses / functions need to be adjacent to your program/activity)")).getValue();
  var displacement = sheet.getRange(row, getColIndexByName("Displacement from existing space – relocation required.")).getValue();
  var other = sheet.getRange(row, getColIndexByName("Other (not described in other categories above).")).getValue();
  var uses = sheet.getRange(row, getColIndexByName("Please list what other uses / functions need to be adjacent to your program/activity.")).getValue();
  var describeProblem = sheet.getRange(row, getColIndexByName("a) Describe the identified problem or opportunity.")).getValue();
  var impacts = sheet.getRange(row, getColIndexByName("b) Part I: Provide justification and evidence in support of the identified problem/opportunity. Impacts - select one or more from the below list.")).getValue();
  var impactsExplain = sheet.getRange(row, getColIndexByName("b) Part II: Provide justification and evidence in support of the identified problem/opportunity. Explain how the proposed project will achieve the impacts you indicated above.")).getValue();
  var advance = sheet.getRange(row, getColIndexByName("c) Explain how the requested project will advance the College’s Strategic Plan and/or the requesting school or office’s academic plan and / or business plan.")).getValue();
  var estimate = sheet.getRange(row, getColIndexByName("d) Part I: What is your estimate of the potential cost (in rough order of magnitude) of addressing the problem or opportunity described above?  ")).getValue();
  var estimateExplain = sheet.getRange(row, getColIndexByName("d) Part II: Please explain how your developed this estimate.")).getValue();
  var requestor = sheet.getRange(row, getColIndexByName("Name of Requestor/Proposer")).getValue();
  var date = sheet.getRange(row, getColIndexByName("Date")).getValue();
  var title = sheet.getRange(row, getColIndexByName("Title of Proposed Request (problem / opportunity):")).getValue();
  var phone = sheet.getRange(row, getColIndexByName("Phone No.")).getValue();
  var sponsor = sheet.getRange(row, getColIndexByName("Name of Project Sponsor")).getValue();


  //copy and combine doc name and name form
  var copyId = DocsList.getFileById(docTemplate)
  .makeCopy(docName+' for '+requestor)
   .getId();

//open temp doc and copy body
 var copyDoc = DocumentApp.openById(copyId);
 var copyBody = copyDoc.getActiveSection();

  //replace placeholderkeys from template
  copyBody.replaceText('keyProposedTitle', title);
  copyBody.replaceText('keySchool', school);
  copyBody.replaceText('keyOffice', office);
  copyBody.replaceText('keyAddSpaceExisting', addSpaceExisting);
  copyBody.replaceText('keyAddSpaceNew', addSpaceNew);
  copyBody.replaceText('keyCurrentSpace', currentSpace);
  copyBody.replaceText('keyRelocation', relocation);
  copyBody.replaceText('keyDisplacement', displacement);
  copyBody.replaceText('keyOther', other);
  copyBody.replaceText('keyUses', uses);
  copyBody.replaceText('keyDescribeProblem', describeProblem);
  copyBody.replaceText('keyImpacts', impacts);
  copyBody.replaceText('keyImpactsExplain', impactsExplain);
  copyBody.replaceText('keyAdvance', advance);
  copyBody.replaceText('keyEstimate', estimate);
  copyBody.replaceText('keyEstimateExplain', estimateExplain);
  copyBody.replaceText('keyRequestor', requestor);
  copyBody.replaceText('keyDate', date);
  copyBody.replaceText('keyPhone', phone);
  copyBody.replaceText('keyEmail', email);
  copyBody.replaceText('keySponsor', sponsor);

 //save and close doc
 copyDoc.saveAndClose();

//create PDF of temp doc
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

//email recipient
  var subject = "Manhattan College Space Planning Form Submittal";
  var body = "Thanks for submitting your request. \n\nPlease find an attachment with the details given."
  MailApp.sendEmail(email, subject, body, {htmlBody: body, attachments: pdf});

//trash temp doc
 DocsList.getFileById(copyId).setTrashed(true); 


}

Google script that checks if a Google form checkbox was marked

1 Answers