Rebuilding e.namedValues in Index Order, Pushing to Document in Google Apps Script

Question

I have a Google Form that has many different branches; as a result, out of ~100 questions in total, any given response only includes about 20 answers. I'm trying to find a way to remove questions and answers to which the answer is "" (blank) and then push the questions and answers to a Google Document.

I'm using e.namedValues triggered on FormSubmit. But, of course, e.namedValues doesn't actually return the responses in the order that the questions were asked. I need a way to:

1. Build an array of questions and answers where only those questions and answers are shown where the answer has a response

2. Sort that array so that the questions and answers are in the order that they were asked

3. Push that array into a Google Document (ideally, a table, but I'm unable to do that with the arrays that I'm building).

Here's my current code:

function formResponsetoGoogleDoc(e) {
  var formID = {{ Insert FormID Here }};
  var response = e.namedValues;
  var document = DocumentApp.create('Test').getBody();
  var questions = FormApp.openById(formID).getItems();
  var questionarray = [];
  for (Key in response) {
    var label = Key;
    var data = response[Key];
    if (data != "") {
      for (q in questions) {
        var title = questions[q].getTitle();
        if (title == label) {
          var number = questions[q].getIndex();
        }
      }
      questionarray.push(number + " - " + label + ": " + data);
      document.appendListItem(number + " - " + label + ": " + data)
    }
  }
  questionarray.sort();
  Logger.log(questionarray);
}

This code doesn't work. Currently, questionarray gets sorted in the order of questions (e.g.) 0, 0, 15, 16, 17, 18, 18, 19, 2, 20, 21, 23, 24). Also, any time I try to use this as an appendtable, I get "Exception: The parameters (number[]) don't match the method signature for DocumentApp.Body.appendTable."

Would really appreciate any help that you can give. Willing to consider using e.values as well, but I've never figured out how to cull both questions and (non-)answers from the two arrays needed. I have too many questions to justify hardcoding the replacetext options either, so I'm trying to avoid that.

Answer 1

Answer:

You can use filter to remove empty responses while storing the index of the empty value.

Code Example:

Let e.values be:

e.values = ["Response 0", "Response 1", "", "Response 3", "Response 4", "", "", "", "Response 9"];

We can filter out the empty responses and use another array to store the indices which were removed:

function filterValues(e) {
  var indices = [];
  var filtered = e.values.filter(function (response, i) {
    if (response == "") indices.push(i);
    return response != "";
  });
  
  console.log(filtered);
  console.log(indices);
}

Will display the following in the console:

Jan 11, 2021, 10:50:33 PM   Debug   [ 'Response 0',
  'Response 1',
  'Response 3',
  'Response 4',
  'Response 8' ]
Jan 11, 2021, 10:50:33 PM   Debug   [ 2, 5, 6, 7 ]

References:

• Array.prototype.filter() - JavaScript | MDN

Answer 2

You probably need to battle with this problem a while longer because you don't seem to be able to articulate a very specific question. But you seem to have interest in determining which answers have been answered and in what order and also you seem to want the the index of the answers that don't. I believe this data set should provide you some insight as to how you can obtain that information. The information for each form submission is appended to a spreadsheet along with the headers.

function onMySubmit(e) {
  const ss=SpreadsheetApp.getActive();
  const osh=ss.getSheetByName('Sheet1');
  let arr1=[['Header','Value','Index','Column']];
  let sh=e.range.getSheet();
  let hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  let col={};
  let hdr={};
  hA.forEach((h,i)=>{col[i]=i+1;hdr[i]=h;});
  e.values.map((v,i)=>{arr1.push([hdr[i],v,i,col[i]]);});
  osh.getRange(osh.getLastRow()+1,1,arr1.length,arr1[0].length).setValues(arr1);  
}