How do I create a graph that shows progression over time?

Question

Firstly, to help explain what I'm looking for. We're a charity delivering services each week to children and young people. At the end of our service, a volunteer will fill out a report form on each child. Some of that form is selecting a number between 1-5 to measure their development, such as 'physical wellbeing' and 'social wellbeing'.

So, I have inherited a large google sheet that contains the above information. Each child is listed multiple times (as people report weekly) and there are columns that display the numbers chosen by the volunteer (1-5) in physical and social wellbeing.

• Is it possible to select that child's info and plot their development over time on a graph?
• How do I do that, please? The sheet also contains the date of each service.

Answer 1

Choose Insert > Sheet and put this formula in cell B1 of the new sheet:

=query( 
  'Form Responses 1'!A1:N, 
  "select N, toDate(A), P, S 
   where N is 'Jane' ", 
  1 
)

...where A is the timestamp column, N is the name column, and P and S are the metrics columns you want to plot. Then select columns C:E and choose Insert > Chart.

To easily switch from child to another, select cell A1 and choose Insert > Dropdown. In the Data validation rules sidebar, choose Criteria > Dropdown (from a range) and put 'Form Responses 1'!N2:N in the range box. Then modify the where N is 'Jane' ", clause of the query() formula like this:

where N is '" & A1 & "' ",