Post number 10 of “The Practical Google Sheets Guide”

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: When we start collecting data, its time we start to work through the results and start extracting and collating information, so we can start presenting it. You can find the complete spreadsheet for this post at the following link:
https://docs.google.com/spreadsheets/d/16drGn-dGo4K-D-JKBMViYyDbF9MgpYUau7tfWOPGRNI/copy

Introduction

In our previous post, we started working with Forms to allow us to quickly gather information for us. In this post, we are going to start looking through our data and extracting and preparing our information so that we can start to present our data as some nice looking dashboards to our users.

What You Need To Know To Get This Done?

In our last post we started working with our Forms, and if you did not get to that post and want to jump in quickly, don’t worry, you can start with the sample data we have set up and collected ready to be worked on.

If you want to checkout Part 1 of this series, head over to the following link first:
https://hive.blog/hive-163521/@strava2hive/getting-started-with-forms-and-dashboards-part-1

As we mentioned before, we are doing to start extracting and preparing our data from the responses we have received from our Forms. In the following, it will give us a chance to look through the data we have collected and work out the important information we want to present to our user. Although, we are using simple data collected on someones run training, this can be done with anything and is probably the first logical step after you have received responses, you would then want to see what makes sense, and see what data can then be presented as dashboards and graphs.

A lot of the work we are doing in this post is going to be carried out by functions. In this post we will be using the following functions as part of our formulas:

• QUERY
• COUNT
• SUM
• INDIRECT

To find a reference and explanation of the functions used as part of this post, checkout our previous post here:
https://hive.blog/hive-163521/@strava2hive/my-google-sheets-function-reference

Focus Our Data

We could create a dashboard on all the data we’ve collect. This could be something we would do later on to provide an overall view. Instead, we are doing to focus our data to only display one week of data. As we saw in our previous post, Forms adds all our data into a new spreadsheet ready for us to use. We can start working with this sheet, but for our purposes, we are going to create a new sheet and extract one weeks worth of data from our responses.

• Just like in the example below, our data from our Forms has been placed into a sheet called “Form Response 1”. We will leave the responses here, but create a new sheet to allow us to work with a subset of the data. Create the new sheet by clicking on the (+) at the bottom left of the screen.
• By default the new sheet will be called “Sheet 2”, click on that sheet tab to move into it now.
• Move to the top of the screen, specifically cell A1, where we will add a data to show the start of our week for the data we are working with.
• In our instance, the start of our week will be “5/5/2025”, so add this to the cell.
• Move down one cell to A2 and add the following QUERY function to extract one week of data from our responses:
  =QUERY('Form Responses 1'!A2:I33," where B >= date '"&TEXT(A1-7,"yyyy-mm-dd")&"' and B <= date '"&TEXT(A1,"yyyy-mm-dd")&"' ")
  This is one of the more complex formulas we have used, so a breakdown of what is being done is below:
  -- First, we specify the Sheet we are working with and the range of data, in our case “Form Responses 1”, with the cell range A2:I33, which is all the data in our responses.
  -- We then use column B as our date, extracting dates in between the value in A1 - 7 days, and the value in A1…So the past week.

So hopefully you should see something similar to the image below.

Gather Some Key Values And Data

Now we have some more focused data that we can now start to work with, let’s get three key values from our data. For me personally, the kind of information I want to see from a weeks worth of training data are the total number of runs done, the total time spent running, and the total distance run.

• By using the COUNT function, we can easily get a total number of the runs we have in our data. Each row represents a run, so all we need to do is select a column, using the count function. Start by adding a title to our data in cell K1, called “Total Runs:”
• Now add the count function in the L1 cell as follows:
  =COUNT(B:B)
• We will actually use the value from this count in the next two functions to extract the total time and distance. Start again by adding “Time Running:” into the K2 cell.
• Normally we would use a SUM function, which is what we will be doing, but instead of specifying a range of data, we will create the range with the value in our previous step. In cell L2, add in the following function:
  =SUM(INDIRECT(CONCATENATE("F2:F", 2 + L1)))
  This function is exactly the same as doing =SUM(F4:F14), but we are:
  -- Using the CONCATENATE function to build our range, meaning we won’t capture any unnecessary data, or leaving any data out by simply guessing how far the range needs to be.
  -- Using the INDIRECT function to allow SUM to use the vales from our CONCATENATE function. Without using INDIRECT, we would get an error.
• We can do almost the exact same thing to work out the Total Distance for our data. Start with adding the words “Total Distance:” into the K3 cell.
• This time we are using the Distance column in column E, and add in the new SUM function into cell L3:
  =SUM(INDIRECT(CONCATENATE("E2:E", 2 + $L$1)))

Using Data To Create More Data, And Your First Visualisation

Using the data we have in our responses, we can summarise and collate extra totals. Specifically, we can use the selection of running shoes to summarise and calculate the number of times each week, we use each pair of shoes.

• Move down to cell K4, where we have been adding in our extra data values. For calculate the shoes being used for the week, add in the following formula into that cell:
  =QUERY(H1:H20, "select H, count(H) where H <> '' group by H label count(H) 'Shoe Count'", 0)
  Another big QUERY, this one is a little different
  -- First we are specifying the range in Column H, which is the running shoe used for each run.
  -- Then we count the values in H, except for any blank values
  -- Finally specifying the label of “Shoe Count” that will be displayed in L4
• Now we can create a visualisation of the Shoe data. Start by selecting the cells for the “Shoe Count” data we just created, with our data, select the range K4:L7.
• From the Insert menu, select Chart
• Google Sheets will do a good job in selecting the best options for you, but incase, yours looks a little different from the one below, you can amend it with the following options:
  -- From Setup select “Pie chart”
  -- From the Customise tab, select Chart style and select Background colour as Black
  -- From Chart and axis title, give the title of “Shoes Being Used”

Although just sitting in the middle in our screen below, the chart can be adjusted and moved around the screen to be fit where ever it is needed.

Summing Up

We haven’t worked for a long time and been able to extract some good values from our data. I always use this type of method to extract key values before I start setting up my visualisations. We did this by extracting a subset of our data based on the date, we calculated the time, distance and time being run. Then we created a nice function to count the number of times I have been wearing each of my running shoes. We then used this data to create a Pie Chart representing these values.

In our next post, we are going to take this a little further and make what will hopefully be a nice looking dashboard representing a number of different items from the data we have collected.

About The Author

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