Merge multiple checkbox responses in google forms response sheet

Question

I have a google Form that for various reasons has multiple checkbox questions. I eventually want a known quantity of these to be merged.

Once a form submitted, I figured I could have an app script take the submitted data, copy it to another sheet and then merge the aforementioned question responses into a single cell csv.

so take the following example:

name: 'name'
question1: ['a','b','e']
question2: []
question3: ['dog','cat']
question4: ['notMerged']

and copy it into another page that looks like:

name: 'name'
questions: ['a','b','e','dog','cat']
question4: ['notMerged']

I've been reading on about setting up the trigger for onFormSubmit and getting the data but am not sure about things like merging the results, placing it in the same row # etc. The column index of the fields to merge are static however no there will always be some amount of them that are empty so I will need to do a null check on each before joining.

I am working my way through the 'Quickstart: Managing Responses for Google Forms' code to see whats going on but I dont really know JavaScript.

Not looking for someone to write this out for me but help with a few pointers to get me goin in the correct direction

You can use JavaScript concat() Method to concatenate arrays. Reference Info Link — Alan Wells

Ed Nelson Ed Nelson · Accepted Answer · 2017-01-14T02:47:40

If you don't mind a slightly different format on your second sheet, you can do this without script. On the second sheet, enter your headers ('Questions' and 'Question 4' in A1 and B1. Then in A2 enter:

= IFERROR(JOIN( "," , FILTER('Form Responses 1'!B2:D2, NOT('Form Responses 1'!B2:D2 = "") )))

In B2 enter:

=iferror('Form Responses 1'!E2)

Copy the formulas down. Rows with no data (yet) will be blank until form data is added.

I figured out what is happening. When the form is submitted, it inserts a line and that causes the formulas to change. Use this code and it will set the formulas on the correct row. (Remove the old formulas.) Let me know if you have any problrms.

//you need to set an installable onFormSubmit trigger
function test(){
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  Utilities.sleep(4000)
  var s=ss.getSheets()[1]
  var s1=ss.getSheets()[0]
  var lr=s.getLastRow()+1
  var lr1=s1.getLastRow()
  var r="A"+lr1
  var q="B"+lr1
  var t="D"+lr1
  var u="E"+lr1
   s.getRange(lr, 1, 1, 1).setFormula("=iferror('Form Responses 1'!"+ r +")")
   s.getRange(lr, 2, 1, 1).setFormula("=  IFERROR(JOIN(\",\" , FILTER('Form   Responses 1'!"+ q +":"+ t +", NOT('Form Responses 1'!"+ q +":"+ t +" =\"\")   )))")
   s.getRange(lr, 3, 1, 1).setFormula("iferror('Form Responses 1'!"+ u +")")
}

Merge multiple checkbox responses in google forms response sheet

1 Answers