Google Forms and Excel, part 1

Google Forms is one of the most amazing free tools. You can use its very easy interface to create web forms, and whatever responses you get will be inserted into a worksheet for further use. I have used Google Forms for many purposes, and last time I had a need for it, it was extremely useful.

I was in the Army in 1984-85 (Finland still has a national service), and because it was now 30 years since our Reserve Officer School course, we had a reunion. Of course, finding all 798 members was impossible, but we managed to locate 220 alumni via various channels, such as Facebook, LinkedIn and email. I created an enrollment form for the reunion, and we had a very successful party in early June with 108 people.

After the party, members wanted to create other events later on. I made a website using WordPress, and then it was necessary to create another form to collect information on what people want to have. So, all you need to do is to go to Google Forms, log in with your Gmail account, and off you go. If you have only a simple form to make, you don’t need to edit many of the options at all, such as “Go to page based on answer”, but you have that option too.

This is the first view of the form. The Form Settings need to be edited only if you have a multiple page form, or you want to let people to add only one answer, or if you want to shuffle the questions (if you are making a quiz, for example).

Google Forms first view
Google Forms first view

Set the name of the form by typing it on the top, where you see the words Untitled Form. Once you do that, it is set as the name also below. It is also a good idea to use the Form Description field to give the applicants some information on the form. Also, note how the form gets saved in Google Drive as soon as you pause typing. Note the buttons on the top: Edit questions, Change theme, View responses, and View live form. These are handy for doing just what the name implies.

Basic info filled in for the form
Basic info filled in for the form

Before we go into the fields, let’s click on the View live form button. This will save all changes once more and then show you the form as the users will see it (don’t worry about the blank look – there will be an option to change the look and feel of the form later):

FIrst view of live form
FIrst view of live form

Now, a word on the different fields of the form. You have Text, Paragraph text, Multiple choice, Checkboxes, Choose from a list, Scale, Grid, Date and Time available. Text is for short items such as name. Paragraph text is useful for collecting longer, free-form answers, while Multiple choice and Checkboxes are handy for selecting either just one value from a list with radio buttons, or zero or more values from a list using check boxes. Scale can be used for creating Likert-style questionnaires. Grid is a handy way to create large-scale questionnaires with many values and many questions handled on one group of options in the form. (Bear in mind that the resulting worksheet may be complex if you use complex forms.) Date and Time let you select a date and time, if you need that information in a set format.

What I need to collect from the alumni is this set of data: First name, Last name, Email, Company, Interests, Permission to publish email address, and Remove me from lists. Let’s create fields for all these now.

First name is text, as is last name and Email, so I will handle them all the same way:

First field created
First field created

Change the Question type to Text, add the question in the Question Title field, and add a help text if you think it necessary. The three buttons on the right are for editing questions, duplicating questions, and deleting questions. The Required question check box is vitally important if you want to force the users to submit a response to the question. The Advanced section, hidden by default, has options for checking the entered value against criteria, such as whether the entry has the “@” sign if it is an email field, and you can also have a proper response if there is a problem with the entry.

When the field is done, you can click on the Done button, then click on Add item to get the next one.

This is the live form with the First Name, Last Name, and Email fields in place. I have enabled the Advanced option to check for the existence of the “@” sign in the email.

Three text fields in place
Three text fields in place

So far so good. Now we need a multiple selection field with a set of values, but only one to be selected. This is one of the most misused types of user interface controls, by the way: if you have to select whether a value is true or not, you should use a check box, and if you need to pick just one, you should use a radio button. In this case, I have a list of companies in the ROTC, and every responder belongs to just one. Therefore I will create a Multiple selection with the company names as options. You could also create a dropdown list for this, as that control also lets you pick one value from a list, but I decided to go with radio buttons.

Starting the Company list
Starting the Company list

As soon as you click on the Option fields, Google Forms will create a new field for you to edit. Add the name of the option. When you have the full list of options done, you can reshuffle the options by dragging the grey dots to the left of the option’s name.

Also note that you can let users add their own option by clicking “Or Add Other”:

Finished multiple list
Finished multiple list

It is a good idea to view the form live every now and again:

Live form at this stage
Live form at this stage

If you wish, you can change the type of the question into “Choose from a List”:

Question type changed to "Choose from a list"
Question type changed to “Choose from a list”

The next field is used to collect data on what people want to do. I have thought of a set of things we might want to do together, and because ths time people may want to do more than one thing, it needs to be set up with check boxes. The event types I have thought of are as follows:

Event type list
Event type list

Note that I use the Help text field to remind people that they can pick more than one value.

Event type list
Event type list
Live view of event list
Live view of event list

The remaining three fields are inserted in a very similar fashion. The two yes/no questions are Multiple option types, and the additional information field is a Paragraph type.

Three remaining fields in place
Three remaining fields in place

And there you have it. The entire form looks like this:

Final form without style
Final form without style

To make it a little more interesting, I will add a theme to it, and edit the theme to suit this particular form. To access the form style, click Change Theme on the top of the form. This will open the style editor on the right side of the screen, from which you can pick any theme that suits your needs. I will use the style “Under the Stars”, and customize the header image, because I have a good image in mind. This is how the form looks as soon as you pick a theme:

Theme selected
Theme selected

To change the header image, click Customise. After that, you can change the styles of all elements in the form, be it header image, title style, question style, or anything else. I am happy with the rest of the form, but I want to change the header image. When I click on Change image, Forms lets me pick the image for the form. You need to note that any image you want to use must be of the proper size, ie. 500px × 125px. I have edited an image to this size, because I had a bicycle in the Army whose licese plate (yes, they have license plates on bikes in the Army) was 1985, the year of our course. I uploaded that to Forms and inserted it in the Header area:

Final form
Final form

And now I have the form ready to be sent out. When a recipient clicks on the link, they will be sent to the form, and when they click on Submit, the response will be sent to a Google Docs worksheet. I will show you the response now, and then I will cover the post-processing of the data in another blog entry soon. But first, this is the “thank you” page that the responders will see:

Thank you page
Thank you page

It goes without saying that you can set the “Thank you” page to contain whatever message you want it to have.

When you go to the answers, by clicking on the “View Responses” button, you will see the worksheet of collected data. It is very wide, so I have pasted the response parts on top of each other for clarity’s sake:

Worksheet of answers
Worksheet of answers

You can then export the worksheet to your computer, or, edit it right on Google Docs. I am more conversant with Excel, so I will save it locally and edit it in Excel, in Part 2 of this blog post.

I hope to have shown you that it is very easy to create a form for data collection on Google Forms, rather than going the HTML way and coding one on your own.

Loading

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.