Search code examples
google-apps-scriptgoogle-sheetsgoogle-forms

How can I account for Google Forms questions that are not answered?

I am trying to iterate through a Google form and its responses to print them to a PDF in a way that works regardless of what the questions (items) are and which ones are answered or not.

The problem is that some types of items - e.g., file upload and linear scale - return nothing at all if they are left empty. (Other items will return null or "".) So how can I tell if that question has been answered or not?

For example, let's say I have a form of 4 scale items. If one of them is not answered, the item array will have 4 entries and the response array will have only 3. How would it be possible to know which item was left unanswered when you're looping through the items and responses? I'm stumped.

Here's the code I've got so far, which loops through form items and responses:

  var form = FormApp.getActiveForm();
  var title = form.getTitle();
  var items = form.getItems();
  var responseSet = form.getResponses();
  var lastResponse = responseSet.length - 1;
  var itemResponse = responseSet[lastResponse].getItemResponses(); //this is the set of responses for the most recent submission

  var table = [];

  title = title + '\n';
  var docTitle = noteBody.appendParagraph(title);
  docTitle.setAttributes(titleStyle);
    
  var j = 0;
  for (i=0; i<items.length; i++){
    var itemType = items[i].getType();
    var itemTitle = items[i].getTitle();
    var thisResponse = "";

    //question items
    if (itemType != 'IMAGE' &&
        itemType != 'PAGE_BREAK' &&
        itemType != 'SECTION_HEADER' &&
        itemType != 'VIDEO'         )
      {

        //additional step needed for multiple answer options - turn array into string        
        if (itemType == 'CHECKBOX_GRID' || itemType == 'GRID'){
          
          table.push([itemTitle.toUpperCase()]); //add grid item title
          
          if (itemType == 'CHECKBOX_GRID') {var cgItem = items[i].asCheckboxGridItem()};
          if (itemType == 'GRID'){var cgItem = items[i].asGridItem();}

          var answerRow = [];
          var row = cgItem.getRows();
          var numRows = cgItem.getRows().length;

          //when question is unanswered
          if (itemResponse[j] == null){
            var tableRow = ["     [no response]"];
            table.push(tableRow);
          }
          else { //there is some response
            var cgResponse = itemResponse[j].getResponse();     
            for(n=0; n<numRows; n++){
              var rowTitle = "     " + row[n].toString();
              if (cgResponse[n] == null) {cgResponse[n] = "[no response]";} //when one row of a grid is left blank
              var answers = cgResponse[n].toString();
              answerRow = [rowTitle, answers];
              table.push(answerRow)
            }
          }
        }      

        else {
          
          //itemResponse[j] is the response to question #j (from the most recent answer set) 
          if (itemResponse[j] == null || itemResponse[j].getResponse() == ""){thisResponse = "[no response]";}
          else {thisResponse = itemResponse[j].getResponse();}
         
          var tableRow = [itemTitle, thisResponse]; 
            
          //turning response from array into string?
          var responseString = "";  
          for (m=1; m<tableRow.length; m++){
            responseString = responseString + tableRow[m];
            if (m<tableRow.length-1) {responseString = responseString + ", ";} //if it's the last one don't add this
          }
          tableRow = [itemTitle, responseString];
          table.push(tableRow);
        }
        
        j++;
      }
Solution

  • You want to analyse the Responses for a Google Form and are concerned about how to deal with multiple linear scale questions where the respondent fails to answer one or more the question.

    You want to do the analysis "from the form itself".

    The following answer is adapted by combining the answers from two other questions:

    // based on so_69536568: Storing all forms answers in one single file
// https://stackoverflow.com/a/69537071/1330560
// by Tanaike
// h/t: Substitute value in array;
// h/t: https://stackoverflow.com/a/47575480/1330560
// by Matheus208

// function to be bound to the Google Form
function surveyAnalysis() {
  var form = FormApp.getActiveForm()
  var ss = SpreadsheetApp.openById('1yryiSltp8zv6GJCkwFRl-qrftbK0dH64NtcDTbRadoU')
  var sheet = ss.getSheetByName("Sheet1")

  // Gets the title and description of the form and write to spreadsheet
  var formTitle = form.getTitle()
  var formDesc = form.getDescription()
  sheet.getRange("B1").setValue(formTitle).setFontWeight("bold")
  sheet.getRange("B2").setValue(formDesc).setFontWeight("bold")

  // get the question titles for Headers
  var headers = form.getItems().map(e => e.getTitle())
  // get the form Responses
  var formResponses = form.getResponses()

  // create an array of the responses for each question
  var values = formResponses.map(f => f.getItemResponses().reduce((o, i) => {
    var r = i.getResponse();
    return Object.assign(o, {[i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r})
  }, {})).map(o => headers.map(t => o[t] || ""))
  // convert blanks answers to a meaningful text value
  var unansweredQns = "N/A"
  var adjustedValues = values.map(line => line.map(el => el.length === 0 ? unansweredQns : el))

  // paste the question headers to the spreadsheet
  sheet.getRange(3,1,1,headers.length).setValues([headers]).setFontWeight("bold").setBorder(true, true, true, true, true, true)

  // paste the adjusted question values to the spreadsheet
  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(adjustedValues).setBorder(true, true, true, true, true, true)
}

    SAMPLE

    snapshot