Replace a null value for one of the questions in a Google Form with AppsScript

Question

I have a requirement where I need to get the answer for 2 form questions in a Google Form and send the details in email.

Question 1 - is a mandatory field and it is a text field so there is no problem.

Question 2 - is an optional field (not mandatory) where they can upload documents like picture,pdf,word doc or excel. There is no text.

When answering the form, if I upload a document then I get the email with the document uploaded as a link to the drive. But if I don't upload any document my below code is failing with the below error: when no document uploaded how do I replace that doc with some message like "no document uploaded"

TypeError: Cannot read properties of undefined (reading 'length')

Also, I want to know when there is no document uploaded how can I replace the link in the html email message with something like "No answer"?

let htmlMsg = thisAnswer + "https://drive.google.com/file/d/" + thisAnswer2

Here is my current code:

function EmailForm() {
 var allQuestions,
 i, 
 itemType, 
 L, 
thisAnswer, 
Question1, 
Question2, 
itemType2, 
thisAnswer2, 
number_of_submissions;
 

number_of_submissions = FormApp.getActiveForm().getResponses().length;

allQuestions = FormApp.getActiveForm().getResponses()[number_of_submissions - 1].getItemResponses();

L = allQuestions.length;
thisSubmissionsAnswers = [];
  
  Question1 = allQuestions[0];
  
  itemType = Question1.getItem().getType();

  if (itemType === FormApp.ItemType.PAGE_BREAK) {

  };

  thisAnswer = Question1.getResponse().toString();//Get the answer
  Logger.log(thisAnswer);


  Question2 = allQuestions[2];//Get this question

  if (Question2 === "") {

    Question2 = "None"
  }
  else {
    itemType2 = Question2.getItem().getType();

    if (itemType2 === FormApp.ItemType.PAGE_BREAK) {

    };

    thisAnswer2 = Question2.getResponse().toString();//Get the answer
    Logger.log(thisAnswer2);

    
    let htmlMsg = thisAnswer + "https://drive.google.com/file/d/" + thisAnswer2

    if (thisAnswer === 'Yes') {
      GmailApp.sendEmail('[email protected]', "Incident Reported", "", { htmlBody: htmlMsg });
    }

  };`

I am quite new to AppScript so please help.

Answer 1

You have a Google Form and you want to send an email on submission of a form.

• the Form includes a non-mandator "File Upload" question.
• if the "File Upload" question is NOT answered, you want to "flag" this in your email.

There are two types of onFormSubmit trigger.

• a script bound to the Google Form
• a script bound to the linked Google Sheet
• each trigger has different Event Objects.

There are circumstances where one type is preferred to the other. In this case, where specific Form values are required, a script bound to the Sheet is a better option because of the consistency of results:

• values are returned for ALL questions including those where the question is not answered.
• each value is returned as a string regardless of question-type.
• the value of an unanswered question will be an empty string.

The OP's script was written as as a script "bound to the Form".

The following answer is written as a script bound to the linked Google Sheet, and triggered by onFormSubmit.

---

function  sendEmail(e) {

  Logger.log(JSON.stringify(e))

  var values = e.values
  Logger.log(values)
  Logger.log("number of questions = "+values.length)
  var timeStamp = values[0]
  var q1 = values[1]
  var q2 = values[2]
  Logger.log("timestamp = "+timeStamp+", question#1 = "+q1+", question#2 = "+q2)

  if (q2 === ""){
    // the question wasn't answered, so set q2 = "Not Answered"
    q2 = "Not Answered"
    Logger.log("q2 wasn't answered. Value = "+q2)
  }
  else{
    // q2 was answered
    Logger.log("q2 was answered; the value = "+q2)
  }

}

---

SAMPLE - Form Responses

---

SAMPLE - Executions Log