Why is submitting the google form with response in "Others" option in checkbox not reflecting in individual response?

Question

I am trying to submit the responses in a google sheet to google form. I found the script and made some changes to it.

There are some checkbox fields with "Other" as an option. In the google sheets, there are some responses with values for the "Other" field in some of the questions.

I can see the "Other" responses in the summary. But I cannot see those answers in individual responses. I've used the method showOtherOption as well, but I couldn't get the solution.

Here is the script I am using:

function readSpreadsheet() {
  var sheet = SpreadsheetApp.openById("123456");
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var values = range.getValues();
  var form = FormApp.getActiveForm();
  var items = form.getItems();
  for (var i = 2; i < numRows; i++) {
    var value = values[i];
    var formResponse = form.createResponse();
    var k = 1;
    for (var j = 0; j < items.length; j++) {
      var item;
      switch (items[j].getType()) {
        case FormApp.ItemType.LIST:
          item = items[j].asListItem();
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item = items[j].asMultipleChoiceItem();
          var aa = value[k++];
          formResponse.withItemResponse(item.createResponse(aa));
          break; 
        case FormApp.ItemType.PARAGRAPH_TEXT:
          item = items[j].asParagraphTextItem();
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.TEXT:
          item = items[j].asTextItem();
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;
        case FormApp.ItemType.CHECKBOX:
          item = items[j].asCheckboxItem();
          var checkboxValue = value[k++];
          if (typeof checkboxValue !== 'string') {
              checkboxValue = checkboxValue.join(',');
          }
          var checkboxValueArr = checkboxValue.toString().split(/ *, */);
          var choices = item.getChoices();
          var foundElem = [];
          var notFoundElem = [];
          checkboxValueArr.forEach((elem) => {
            if(choices.map((choice) => choice.getValue()).indexOf(elem) != -1) {
              foundElem.push(elem);
            } else {
              notFoundElem.push(elem);
            }
          });
          if(notFoundElem.length > 0) {
            foundElem.push(notFoundElem.join(","));
          }
          item.showOtherOption(true);
          formResponse.withItemResponse(item.createResponse(foundElem));
          break;                 
        default:
          Logger.log("#" + (i + 1) + ":Do nothing for item " + j + " of type " + items[j].getType());
          continue;
      } 
      if(j==0){
        Logger.log("This item is the FORM NUMBER");        
      };
    }
    formResponse.submit();
    break;
  }
}

Is there any other way I can get those options to be visible in individual responses ?

Edit: Here is the example format of the data.

Timestamp,Your name,Email ID,How old are you ?,Do you have any sources of income ?,How do you manage the money you earn ?,"Whom do you consult to manage your money apart from necessary expenses like rent, bills, etc ?",How well do you know about the ways you can save taxes ?,Can you share the reasons behind no knowledge or basic knowledge about taxes ?,Whom do you consult to invest your money ?,"In case you invest, what are challenges you've faced while trying learn and do it ?","In case you do not invest, what are the reasons you not to invest your money ?",Would you like to learn managing personal finances ?,Would you like to share your feedback on managing your own personal finances ?

7/3/2021 0:17:20,Name1,Email1,23-30 Years old,Yes,Pay the bills and rent and keep remaining in savings accounts,Nobody. I don't manage the money I earn,"I have basic knowledge. Example, 80C","Difficulty to understand the rules, I don't have enough time to learn about it, I don't know where to learn about saving taxes",Nobody. I don't invest money,Not applicable for me,"Lack of knowledge, I find the concepts difficult to understand, I find it risky, I don't know where to start",Yes,I wish to learn about financial literacy
7/3/2021 0:17:22,Name2,Email2,23-30 Years old,Yes,Pay the bills and rent and keep remaining in savings accounts,Friends/family,"I have basic knowledge. Example, 80C","Difficulty to understand the rules, Less reliable sources of information,lack of awareness",Friends/Family,"Lack of relavant and structured material to study, I did not have anyone to discuss with about investments, I found too many options for investment confusing, Lack of reliable source of information,scams make someone lose interest in investing","Lack of knowledge, I/My friends/relatives lost their money made me doubtful about investing, I find the concepts difficult to understand, I find it risky, I don't know where to start",Yes,
7/3/2021 0:17:24,Name3,Email3,23-30 Years old,Yes,"Pay the bills and rent and keep remaining in savings accounts, Invest in fixed deposit, Invest in mutual funds, Invest in stock market",Myself,Very well,,I manage it on my own,Lack of relavant and structured material to study,Not applicable for me,No,
7/3/2021 0:17:26,Name4,Email4,23-30 Years old,Yes,"Invest in fixed deposit, Invest in mutual funds, Invest in stock market, Invest in Tax saving schemes",Myself,"I have basic knowledge. Example, 80C",I think I know the question there is no such option,I manage it on my own,A lot of time required to study a particular stock.,Not applicable for me,Yes,no

Can you share a sample of your sheet? – Jason E. Jul 05 '21 at 14:04 — Jason E., Jul 05 '21 at 14:04
@JasonE.: added the sample of 4 rows in the question – Prateek Jul 20 '21 at 17:18 — Prateek, Jul 20 '21 at 17:18

idfurw · Answer 1 · 2021-07-27T03:45:41.313

It seems this is an issue as answered by Iamblichus.

There are 2 workarounds:

Method A: fetch the response with prefilled url

entry.XXX=__other_option__&entry.XXX.other_option_response=answer

Method B: add a question for the 'other' answer through Google Apps Script

Insert a section and a text question (i.e. itemOther) with blank title for 'other' answer right after your original question (i.e. item)
Jump the section created in 1. to hide it from UI submission Show form questions based on answers
You may like to skip the (a) page break item and (b) 'other' question in your items loop

(a) add FormApp.ItemType.PAGE_BREAK to you switch argument

        case FormApp.ItemType.PAGE_BREAK:
          break;

(b) add a if statement before the switch argument

      if (items[j].getTitle === '') continue;

Modify the code to handle 'other' answer

let included = false;
for (const choice of item.getChoices()) {
  if (value[k++] === choice.getValue()) {
    included = true;
    break;
  }
}
if (included) {
  formResponse.withItemResponse(item.createResponse(value[k++]));
}
else {
  formResponse.withItemResponse(item.createResponse('__other_option__'));
  formResponse.withItemResponse(itemOther.createResponse(value[k++]));
}

showOtherOption(true) is used to enable 'other' option when you add or edit the question

Why is submitting the google form with response in "Others" option in checkbox not reflecting in individual response?

1 Answers1