These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.
TLDR: This post will walk through how you can create Forms to then interact with your Google Sheets. This post is only going to focus on Forms, including how you create and configure them for your specific requirements. The complete Form in this post can be accessed from the following link:
https://docs.google.com/forms/d/1jct39zeEk9Ss-no6MZGGAzzm9qUNl1Eqv-B46e2NQkI/template/preview
In this article we are going to introduce working with Forms when using Google Sheets. Forms are a powerful and easy way to allow you to collect data and deliver it directly into your sheet. Forms are really easy to use and in this post we will look at creating a simple form to allow you to track the runs you are doing every week.
In following posts, we will use the data gathered by our Form to creating a basic dashboard showing the weekly milage and activity. Although Forms are listed as a seperate application in the suite of Google apps, I feel it works perfectly with sheets and can make things so simple.
In this post we will be using Forms, which as long as you have a Google account, you will be able to have access to Forms. There is sample data, you can also use to speed up your own work. The formulas we are using will be pretty basic and we will walk you though them as we go.
We will start by setting up our form to enter our data into Google Sheets. We will be collecting information like Date and Time of our run, the distance, duration of the run, the running shoes we are wearing, the title of the run, and a brief description.
Alternative ways to create your form
You can also create a new form directly from the spreadsheet you are working from, so your new form will feed directly into the sheet you are working on. From the sheet you are working on, select “Create a new form” from the Tools menu. To work with an existing form, you can instead select “Manage form” from the Tools menu.
When you bring up a new Form, you will see a page similar to the one below, which looks pretty basic but can let us do a lot of different things.
We have added numbers to all the different parts of the Forms interface, to give you all the highlights and guide you through how it works.
Once you create the new form, you will be presented with a basic form and only one question ready for you to edit.
Continue setting up the form, in this section we will set up the time of run, the title, distance and duration.
We can now set up the final questions of our form. As you can see in the image below, we have three more questions, Description, Running Shoes and Perceived Exertion.
Sometimes, we want to make sure we are getting the exact data we need, and we can set up a validation rule for our final question to make sure we only get numbers back as a response.
Once you are happy with the way your Form looks and the questions you are asking, you will need to Publish the Form before you can start to gather responses.
Even though we will be working with Google Sheets to view our responses, you may have noticed already that Forms provides you with a way to view responses directly from the Forms page. As you can see in the image below, we have selected the Responses option from the top three links at the top of the page. Forms does a really nice job of presenting the results we have in visual format, and will determine the best way to present the data.
In the image below, we can see some of the later questions being displayed as both pie charts and column graphs.
If we now want to either view our responses from an existing Google Sheets spreadsheet or a new spreadsheet, you can do this from the Responses tab in your Forms page. Up the top of the Responses, you should see a “View in sheets” option. When you click on it, you will be given a similar option as the one below:
Weather you create a new sheet or use an existing one, your data will be opened up in a spreadsheet similar to the one below where you can start working with the data as you normally would in Google Sheets.
Even though we’ve done little work with Google Sheets, I am sure by now you can see how Forms are a great way to gather the data you need. We did a great job in this post introducing Google Forms, how to use the interface and add different questions and configure question types. We also showed you how to set up response validation, and how to publish your Forms to be used to collect data.
We then showed you how to view responses in both Forms, as well as opening up responses in Google Sheets. In our next post, we are going to start using our data to create some nice dashboards using our responses we’ve collected in our Forms.
Some of our earlier posts include:
The post is written by Vincent Sesto, a Aussie Software Engineer, living and working in Auckland, New Zealand. If you are interested in my authors page on Amazon, feel free to checked it out at the following link:
https://us.amazon.com/stores/author/B073R3VW2G
