Script to autofill google doc from google form using checkboxes

Question

I have the following issue. I am trying to create a script that will autofill a template google document using the submission of a google form. I am able to get the script to work for questions that are input with text but am struggling on getting the data from questions in the form that are checkboxes (or multiple choice) to work and fill the google document. Any assistance would be great. For example the variable identified as "offense" is from a question with checkboxes that has about 30 different options, I would like each option that is checked on the form to replace text within my google doc. Thanks.

 function autoFillGoogleDocFromForm(e) {
  //e.values is an array of form values
  var timestamp = e.values[4];
  var studentName = e.values[3];
  var oe = e.values[16];
  var gradelevel = e.values[14];
  var program = e.values[15];
  var offense = e.values[6];
  var action = e.values[18];
  var serve = e.values[31];
  var makeUp = e.values[32];
  var comments = e.values[29];
  
  
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('1nPWC0IKc1zUJXYxbGahJsSW4uNWwhxnLM8shcD8kEE4'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('1FlpHRKqYrEHttA-3ozU3oUVJlgiqqa-F')
  var copy = file.makeCopy(studentName + ', ' + timestamp, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody(); 
  
  //Then we call all of our replaceText methods
  body.replaceText('<<Student Name>>', studentName); 
  body.replaceText('<<Incident Date>>', timestamp);  
  body.replaceText('<<Student Grade>>', gradelevel); 
  body.replaceText('<<Open enrolled?>>', oe);
  body.replaceText('<<IEP/504?>>', program);
  body.replaceText('<<Reason for Referral (Handbook)>>', offense);
  body.replaceText('<<Administrative Action>>', action);
  body.replaceText('<<Date(s) to be Served>>', serve);
  body.replaceText('<<Make up Date(s)>>', makeUp);
  body.replaceText('<<Comments>>', comments);

  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); 
}

Answer 1

You need to use the labels assigned to the checkboxes to determine if they have been checked. Same for multiple coice.

You can't use ListItems because you can't set the glyph to a check box so I simply insert text with a checkbox character.

I created a form

I then created an onFormSubmit(e) installed trigger in the spreadsheet to get the form response and put it in the Doc. Here I've simply used an active doc to perform my tests. You will need to adjust the script to handle your template doc.

function onFormSubmit() {
   //  test data
let e = {"authMode":"FULL","namedValues":{"Timestamp":["8/16/2022 14:40:26"],"Student Grade":["Junior"],"Reason for Referrel":["Bad grades, Disruptive in class, Other"],"Student Name":["Joe Smith"],"Open Enrollment":["Yes"]},"range":{"columnEnd":5,"columnStart":1,"rowEnd":2,"rowStart":2},"source":{},"triggerUid":"12151926","values":["8/16/2022 14:40:26","Joe Smith","Junior","Yes","Bad grades, Disruptive in class, Other"]};
  
  try {
    let doc = DocumentApp.getActiveDocument();
    let body = doc.getBody();

    let referrels = ["Bad grades","Unexcused absence","Disruptive in class","Fighting","Other"];

    body.replaceText("<<Student Name>>",e.namedValues["Student Name"]);
    body.replaceText("<<Student Grade>>",e.namedValues["Student Grade"]);
    body.replaceText("<<Open Enrollment>>",e.namedValues["Open Enrollment"]);
    // Notice the regex expression below because findText doesn't seem to handle parenthesis well
    let text = body.findText("<<Reason for Referral.*>>");
    body.replaceText("<<Reason for Referral.*>>","");
    if( text ) {
      let index = body.getChildIndex(text.getElement().getParent())+1;
      referrels.forEach( item => {
          let checked = e.namedValues["Reason for Referrel"][0].indexOf(item);
          if( checked >= 0 ) {
            let listItem = body.insertListItem(index,item);
            index = body.getChildIndex(listItem)+1;
          }
        }
      );
    }
  }
  catch(err) {
    Logger.log(err);
  }
 }