Switching to Google 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.

The #edublogsclub challenge this week relates to books. I am always reading. However, instead of writing another review, I thought that it might be more pertinent to review a review of my reviews I made with Awesome Tables.

Awesome Table allows you to take data recorded in a Sheet and use App Scripts to create dynamic visualisations. As the website explains:

Awesome Table lets you display the content of a Google Sheet into various types of views: From a simple table to people directories, Gantt chart views, Google Maps, card views… There are many possibilities to suit your personal and professional needs. With it, data in Sheets are shown in a more functional way and can be shared with viewers.

I had started exploring options for dynamic displays with the help and guidance of Tom Woodward, who shared a template for my blog roll with me. However, he also directed me to Awesome Tables as another possibility.

Even with tags and categories, one of the challenges of having a blog with hundreds of posts is how to make it easier to find what you might be looking for. One example is finding my reviews of books and podcasts. I am often asked for suggestions and short of giving people a long list, wondered if there was a way of refining this process. This led me to Awesome Tables.

 


For those interested in making your own Awesome Table, I have created a basic guide:

1. Go to Awesome-tables.com and sign in with your Google account. This will then link with Google Sheets.

2. Choose a template from those provided. Preview it and once happy, click Use Template to create a copy.

3. Open the Spreadsheet attached to the template. There are two tabs: data and template. To use the basic template as is, delete the dummy data and add your own.

4. If there are fields that you do not want built into the template, then you can change the headings in the Data sheet. However, make sure that you do the same within the Template sheet.

It is possible really customise an Awesome Table or even start from scratch. However, it can get rather fiddly and involve a lot of abstraction. For those wanting to go down this path, there is a support site with a range of documentation.


So what about you? What strategies do you use to track the books you read? Have you used Awesome Tables in any other ways? 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.

I have been doing a bit of work with Google Sheets lately, here . To be honest, Sheets has been one of those applications which I have wanted to go further with for a while, but never really found the time or purpose. With the help of Ben Collins, Alice Keeler, Chris Betcher, Jay Atwood, Chris Harte and Eric Curts I have explored everything from formatting to formulas. Here then are some of my lessons learned through it all:

Smashing Cells Together

I have lost the amount of times that I have had to create a spreadsheet with a range of different data, but each somewhat related. For example, one column has a list of usernames, which then needs to be turned into an email address. Obviously the simple answer is to write two lists. However, the shortcut is to use ‘&’ to smash the two cells together. If you want two names combined then you use ‘&” “&’. To remove the formula from the cell, download as a CSV. This turns everything into text. Then you can either continue using the spreadsheet in another program or re-upload the new sheet.

Validating More than Just Data

I had always been aware of data validation. However, I had never quite seen the potential. Jay Atwood talks about keeping a menu of items in a separate tab in your sheet which you can easily make into a validated columns when collecting raw data. Another interesting use that I came upon is using a data validation cell is a button to select a particular focus. I found this via a video from Ben Collins who documents how to use the VLOOKUP formula to make a dynamic table. (You can actually find one built into the gradebook template in Sheets.) I took this further and made a dynamic table based on a CSV download of a simple timetable. Collins has also written a guide to creating wildcard to search through a data set. While the team at CIFL have developed a thorough introduction to the possibilities of the VLOOKUP formula.

Formatting

One of the big differences between Sheets and Microsoft Excel that I discovered early on with my use of GAFE/GSuite was the absence of formatting. It did not necessarily concern me. However, I worked with some teachers who were frustrated by this. Over time, Google has improved the formatting within Sheets. Now you can merge cells, add alternate colours and change direction of text. Although Sheets does not have the variety of preset formatting options, the Explore Tool now makes its best attempt to provide useful recommendations.

Colourful Conditions

Although it is easy enough to apply a conditional formatting to a set of numbers, it is not as obvious about how to deal with categorical data. The answer is to combine multiple single colour rules. Another useful trick is selecting the ‘awesome’ box with the conditional format menu open. This will then show all the rules applied throughout the sheet. Also, as you select each one, the range being affected is highlighted in the sheet.

Life Made Easier with Formulas

Last year, I decided to analyse my blogroll in an attempt to appreciate the diversity or lack thereof. I started by downloading my OPML file from Feedly and opening this up in a sheet. I then progressively went through my 200+ rss feeds and replaced them with the website, as well as the various categories. I recently discovered that I could have imported some of this data using the IMPORTFEED formula. In part it was Tom Woodward who uncovered this possibility for me through his post on exploring WordPress. The further that I go, the more I realise that formulas afford so many more possibilities than what is offered in the menu. For example, sorting using either filters or dropdowns can be limiting and restrictive. The SORT formula does the same thing, but with more of the nuances.

In Scripts with Trust

Moving on from formulas, I have also being toying around with a few scripts and addons lately. This has included:

  • TAGS Explorer: Martin Hawksey’s Twitter visualisation tool.
  • TimelineJS: the tool from KnightLab to build a visual timeline from a spreadsheet for representing timelines.  
  • Epic Rubric: Alice Keeler’s script for creating, collating and sending out rubrics to students.

More than building formulas, I feel that scripts involve a bit more effort and patience. Sometimes things do not work, but that is part of the learning to work backwards working out where things may have gone wrong. Usually it involves me breaking code that I was not meant to touch. I must be honest, I am still yet to properly dive into scripts and APIs, but know that is probably one of my next moves.

Creative Sheets

One of the areas that has surprised me about exploring Sheets is the various creative activities that seem to rise. Whether it be Tom Woodward’s play on magnetic poetry, Alice Keeler’s idea for pixel art, Eric Curts random emoji writing prompt generator and Jay Atwood’s use of text rotations to create a shape poem. Activities like this always leave me rethinking the limits as to what an application like Sheets may have to offer.


So that is me, what about you? Have you had any experiences with 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.