in Tools for Working

Organising Data with Forms and Sheets

In my work supporting online learning, I use a lot of Google Forms to collect and curate information. One of the problems that can occur is that there are many sheets with data spread across the all. Here then are some of the steps that I have taken to streamline some of the steps and processes.

Multiple Sheets Imported Together

Summary sheets linked to forms can add up quickly. One way around this is to use the Import Range formula to collect a number of responses in one place. Along with formulas to translate text, generate sparklines and fetch financial data, IMPORTRANGE is a part of the Google collection:

=IMPORTRANGE(spreadsheetkey, rangestring)

The formula allows users to bring in a range of data from one spreadsheet to another. It needs to be noted that the first time it is used, the user will be prompted to grant permissions.

Conditioning Completed

Another issue with forms is that long lists of data can become unwieldy. One particular use is submitting responses and feedback. Often these tasks involve an action, however it can be difficult to manage these. One answer was to add an additional column and use this information as a trigger for conditional formatting to colour a whole row. The following custom formula that allows this is:

=$A1=”TEXT”

‘A’ is the column that includes the trigger, while ‘TEXT’ being the actual trigger.

Developing a Dashboard

Having all the data imported into separate tabs within the one spreadsheet is one step in organising information. The next step is representing this content in the form of a dashboard. My first iteration was to provide a summary of the responses across all the sheets. To capture this I counted the responses by focussing on emails, using the UNIQUE formula (thanks Martin Hawksey:

=COUNTA(UNIQUE(A:A))

The reason that I included ‘UNIQUE’ is because some people submitted multiple responses for various reasons. Although there are other means of avoiding this (submit once or adjust responses), these solutions sometimes create their own issues and confusions.
Once this summary table was complete, I used it to create a chart to visualise it. To share this particular information, I made it a separate tab and published it. This way I do not need to give access to the sheet and instead can give access to the summary. Although this is not technically ‘a dashboard’, I will most likely share the whole dashboard as I develop it further. For more information on designing a dashboard, I recommend this post from Ben Collins.


So these are some of the ways in which I have streamlined data and the way in which people are able to engage with it. The add to my previous tips and tricks associated with Google Sheets. What are some of the ways in which you use Forms and Sheets? As always, comments welcome.


If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

Aaron Davis

I am an Australian educator supporting the integration of technology and innovation. I have an interest in how collectively we can work to creating a better tomorrow.

Latest posts by Aaron Davis (see all)

Organising Data with Forms and Sheets by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

Continue the conversation ...

Comment

  1. Hi Aaron, fascinating post. Lots of stuff to explore. The dashboard link looks like it might be a goldmine. I don’t use sheets much (I’ve a few copies of Martin Hawksy’s tags set up) and I need. To learn more. The way sheets can pull in and parse info from the web is pretty exciting as is the way the spreadsheet can be made accessible online to other web things. One think I am playing with that might be of interest is to display ‘some’ of the submitted data, sorted, beside/below a form. http://teachmeet.scot/events/event/tmslf2017/ I hope that the form and associated spread sheet can be templates to repeat this process.

    • Thanks John for the comment. I have tinkered blindly with a few of Martin’s project. Provides some great examples of what is possible. Another who does some interesting stuff is Tom Woodward. I kind of get the feeling that your use of a sheet to generate data on a page goes into his territory. I am finding that the more I push the application, the further it goes.