[SOLVED] How to organise form responses in Google Spreadsheets

How to organise form responses in Google Spreadsheets

So I'm trying to set up an online invitation and I have it set up so that once the form is sent, the responses get stored in a Google spreadsheet.

Here's a basic version of the invitation: http://jsfiddle.net/ax1ncdmu/7/

Then at the bottom there's a send button which sends the info off to the spreadsheet.

My problem is that when its sent, it looks like this in the spreadsheet:

guest0 | guest1 | guest2 | mealChoice0 | mealChoice1 | mealChoice2
-------------------------------------------------------------------
Tom    | Dick   | Harry  | Meat        | Meat        | Fish

Which isn't ideal, as the number of guests per invitation can change and it's hard to match up the name with their own meal choice.

Idealy, the spreadsheet would be laid out like this:

Name   | Meal Choice
-------|-------------
Tom    | Meat
Dick   | Meat
Harry  | Fish

But I'm not sure if that's even possible? Or maybe beyond my coding knowledge... I was trying to think whether it would be best to somehow incorporate it into multiple forms or somehow have all the names already present in the spreadsheet to reference and add on the meal choices accordingly?

Anyway, I'm open to suggestion, even if it's that it's not even possible :)

Thanks

Edit: Also, this might be useful: http://pastebin.com/FD6QqpJQ This is the gs script that I found which handles the data. Maybe this is what has to change, but its kinda beyond my level of coding unfortunately :/

Solution

This formula works for your sample data - can you modify it to work with your actual sheet:

={"Name","Meal Choice";TRANSPOSE(FILTER(2:2,IFERROR(FIND("guest",1:1),0))),TRANSPOSE(FILTER(2:2,IFERROR(FIND("meal",1:1),0)))}

You can see it working in this sample sheet: https://docs.google.com/spreadsheets/d/1KnO9fGH4md-WV4NMzqfNxs-dpACK-DM4N-gDWJ6mqkM/edit?usp=sharing