Google Forms to Sheets in Google Sheets - Dashboard Guide

Dashboard Mode - Google Forms to Sheets

Dashboard Goal

Track and analyze responses collected from a Google Form automatically recorded in Google Sheets. See total responses, average ratings, and response details in one place.

Sample Data

Timestamp	Name	Rating (1-5)	Feedback
2024-06-01 09:15:00	Alice	4	Good service
2024-06-01 10:30:00	Bob	5	Excellent experience
2024-06-01 11:45:00	Charlie	3	Average, room for improvement
2024-06-01 13:00:00	Diana	4	Very helpful staff
2024-06-01 14:15:00	Edward	2	Slow response time

Dashboard Components

• Total Responses
  Formula: =COUNTA(B2:B6)
  Counts how many people submitted the form (counts names).
• Average Rating
  Formula: =AVERAGE(C2:C6)
  Calculates the average rating from all responses.
• Response Details Table
  Shows all form responses with timestamp, name, rating, and feedback.
  Uses the raw data range A1:D6.

Dashboard Layout

+-------------------+-------------------+
| Total Responses   | Average Rating    |
|       [5]         |       [3.6]       |
+-------------------+-------------------+
|                                   |
|       Response Details Table       |
|       (Timestamp, Name, Rating,   |
|        Feedback)                  |
+-----------------------------------+

Interactivity

Add a filter by Rating to show only responses with a certain rating or higher. When you select a rating filter (e.g., 4), the Total Responses and Average Rating update to reflect only those filtered responses. The Response Details Table also updates to show only matching rows.

Self Check

If you add a filter to show only responses with Rating >= 4, which components update and what are their new values?

• Total Responses: Updates to 3 (Alice, Bob, Diana)
• Average Rating: Updates to 4.33 ((4+5+4)/3)
• Response Details Table: Shows only rows for Alice, Bob, and Diana

Key Result

Dashboard showing total responses, average rating, and detailed feedback from Google Form submissions.