I was asked if it was possible to add a form to a website for an event dinner, collect the responses and place them into a website to reduce the laborious activity of copying feedback in forms by hand. Other examples of where you might want to do this are with a census, or survey. In the past I have helped to set these up and created the associated form, the responses come back as emails. These then need to be processed by hand. 

Having given this some thought, there is another way you could do this to simplify the data collection and processing. The problem is getting the responses into a spreadsheet. Once it is in a spreadsheet, then it is relatively easy to write formulae to process it, or extract the data you need. 

There are several aspects to this, some may not be that obvious:

1). The form data can present the options, and the form can only respond with a single selected option. So for example we can say you have a menu choice: Rolled Pork, Rolled Brisquet, Vegetable Stack. You can only select one of them. What is selected and returned in the form is the precise choice of words. If it is precise, then it is unique. You can process it in a spreadsheet. 

2). While you can have forms that say Enter A,B,C,D, E, F where A is strongly agree, and F is Strongly disagree. What you get back is A, B, C, D, E, or F. But this is not easy to process. Whereas “Strongly Agree” is easy to process. So use the language that humans understand. You can abbreviate it later in a spreadsheet. This is of much greater importance if you are not a computer programmer, or software engineer. Those people can think in terms of variable names, and values. If you are not used to thinking that way, keep it simple. 

3). If the website uses Contact Form 7 (one of the most popular contact form plugins and one I have been using for several years now), you can build the response email up very precisely. So much so that you can create the top part of the email in a human readable format, and the bottom part in a computer readable format which you can use in a spreadsheet. 

4). Send a copy of the response back to the person that sent it to you. It is good manners really, and in the case of a Dinner Dance, you have a hard copy of what you ordered for dinner. 

Processing the Data

You will see examples later in this article, let’s assume that I am collecting feedback on dinner choices. I have created a form which I can collect information from people. In this case I have received the following:

Starter: Smoked Salmon with Celeriac and Prawn Salad

Main Course: Slow-cooked Rolled Brisquet with gravy

Dessert: Summer Pudding

I can format the responses in the email which comes back to the organisation like this for human consumption, and like this for adding to a spreadsheet:

Smoked Salmon with Celeriac and Prawn Salad,Slow-cooked Rolled Brisquet with gravy,Summer Pudding

You will hopefully agree that the first version is easy to interpret, but the second version on a single line you have to look more carefully at to interpret.  So we provide both. 

CSV

The second version is the data presented in a CSV format or Comma Separated Value format. If you look closely the three values are separated by commas. 

I can paste that second CSV formatted row into a spreadsheet row for later formatting. 

Wait while collecting form data

If I use a special email box for all of those responses, I can set up the form and forget about it until I need to do some processing. All of the data will go to a special email account. I just work my way down, open up each one and paste the CSV formatted line to a new row in the spreadsheet. 

Convert the CSV rows to columns

Next I choose Data in the spreadsheet, highlight the rows of responses and convert the rows of CSV data to columns. After this is done, all of my First Course, Main Course and Desserts are all in aligned columns. 

Plus I know that the content in the columns is predictable and finite, I am not going to find Jelly under Dessert because it was not an option to start with. 

Add in some formulae

Choose a location at n+1 rows down your sheet where n is the maximum number of responses you were expecting. 

If I choose a range for say the Dessert column on the spreadsheet and I carefully look at the available options I will find that there can only be four possibilities. In this case none, Summer Pudding, Chocolate Tart or Lemon Cheesecake. I can now set up a formula which will count the total number of instances of “none”, “Summer Pudding”, “Chocolate Tart”, “Lemon Cheesecake”.  In other words I have created an automatic way of counting the instances of a given response in a single column. 

Summary

If you have something moderately complex you wish to achieve, it is possible to build CSV based response and paste these into a spreadsheet, convert them to data in columns, and then process the columns. It can make the tedious steps of processing information a lot easier.  

I am looking at this right now as an example for someone, so I will come back and provide a downloadable guide here once I have progressed it further. (January 2020). 

Update Feb 12th 2020: This system has been implemented and is in use with a client. It is now very very easy to collate all of the responses and run some analysis on a spreadsheet to identify, total income, total number of attendees, total plates of each type for the kitchen, and also identify special dietary needs. If the user had to paste each value from every email it would have taken a considerable longer period of time. 

Contact me to discuss further if you want to do something like this.