Text

|
The power of query in sorting out data in Sheets

There are many challenges to sharing specific data in Google Sheets, some of these can be overcome using the QUERY formula.

I attended a day recently continuing the look into ongoing reporting. One of the focuses involved reflecting on various points pf data. Something that stood out was the willingness of teacher to share data. Whether it be with students, teachers or parents, for some providing access can still be a challenge.

I discussed this with a principal attending the day and one suggestion made is that it can be hard to share particular data, without sharing everyones data. This is especially the case when talking with parents or conferencing a student. An answer is using the QUERY formula.

With Google the chosen platform, a lot of teachers store their data in Google Sheets.

QUERY combines a whole lot of functionality into the one formula. As David Krevitt explains:

QUERY combines all of the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function like FILTER.

It is a language developed by Google, using the principles of Structured Query Language (SQL).

For example, you may wish to share the results of just one students:

To do this, you select the columns you want to copy from the MASTER tab and which value you wish to filter by:

=QUERY(MASTER!A:F, "SELECT C,D,E,F WHERE A = 'Donna'", -1)

If you wished to quickly create a tab for each student, Alice Keeler has created a script for generating tabs from a list. This personalised information can then be shared with students (see Jake Miller’s explanantion). Another thing to consider is to protect formulas by adjusting permissions at a celular level, as well as prevent others from copying the file, therefore getting the information that way.

The other option is to create a dynamic selector involving either a name:

Or even a whole form:

These options might be used when working with colleagues or talking with parents. You are able to bring up just the information required. Depending on the data, you can also create dynamic charts. One other benefit to using the QUERY formula is that it allows you to quickly and easily reorder the representation of data. So lets say ‘F’ is associated with literacy testing and you would like that at the start. Rather than writing SELECT C,D,E,F you would write SELECT F,C,D,E.

This is only the tip of the iceberg of what the QUERY formula can do. For more information, see posts from Ben Collins and David Krevitt. Collins also ran two webinars, which you can go back and watch. One on the basics, while the other getting a bit more complicated.

One thing to note when using Google Sheets to store data is what sort of information you are collecting. In some districts and regions there are issues raised about storing ‘sensitive data’ in platforms like Google.

Reflecting on Class Dojo, Ben Williamson explains that ‘sensitive’ can be the consequence of collecting data:

The ‘sensitive information’ contained in ClassDojo is the behavioural record built up from teachers tapping reward points into the app.

This same concern needs to be considered in regards to Sheets, especially with the changes being brought about by GDPR.

As always, comments welcome. Webmentions too.

Text

|
The power of query in sorting out data in Sheets

My first iteration using Query and Sheets to automate a solution for turning a collection of data into a regular newsletter.

This year I decided to create a monthly newsletter collecting together the updates and resources that I came upon during the previous month. As I have reflected elsewhere, I realised that there was an opportunity to automate some of this process. Although some spoke about Pinboard or generating a post via email, I was interested in sticking to GSuite. I therefore began with the aim of generating a solution within Sheets. So here is my first iteration of an automated solution for turning a collection of links into a summary.

Organising the Data

I remember being in a session with Jay Atwood a few years ago talk about the importance of considering the way you collect your data before anything else. As I looked at my databases, one for updates and the other for resources, I realised that the first thing that I needed to do was reorganise the way that I was storing information. This included restricting the options associated with type and application, as well as separating the link and title and then smashing them together using the HYPERLINK formula. Inspired by Ben Collins’ post on working with text, I also created a column summarising the information in each row into Markdown summary.


Reorganised Data w/ Formulas by mrkrndvs is licensed under CC BY-SA

From Lookups to Queries

Once I had my data organised, I then started explored sorting and shaping the data. I began with a VLOOKUP with a dynamic selector. This allowed me to filter it in different ways. However, I quickly realised that this was limited. I turned to QUERY.

I remember David Krevitt talking about QUERY, describing it as the, ” big kahuna of Sheets functions.” I think this initially put me off. This time I opened up a number of guides from Krevitt, Collins and Anand Varma and dived in. This lead me to rewrite my VLOOKUP as a QUERY.

Bit by bit I stretched the solution. I began with a dynamic selector to represent variables and explored the ability to define queries by date. I then created a prototype with a query for each application across the sheet.


1st Iteration by mrkrndvs is licensed under CC BY-SA

Once I had that working, I create a vertical set of queries. To allow for the variable of the unknown number of posts each month, I left 30 blank rows between each formula.

Filtering Results

To get rid of the spaces and the data headings, I used a FILTER formula and removed the spaces and column headings produced by the QUERY formulas. This left me with a choice, copy the MarkDown data and paste it without formatting, therefore removing the table/sheet that it was in, or using the add-on Sheets to Docs to copy the text to a Google Doc.

So that is the first step in my solution using Sheets to generate the text for a newsletter. My next challenge is transferring this to a Google Script. If you have any thoughts and advice about this, I would greatly appreciate it. Otherwise, as always feel free to leave a comment.

Text

|
A part of the focus on deep learning is the realisation that reporting needs to be ongoing.

It can be easy to look at an application and provide one answer, the problem with this is that it does not cover all contexts. Here is a collection of ideas associated with GSuite and ongoing reporting and assessment.

I recently attended a professional learning day investigating ongoing reporting. As opposed to mandated biannual reporting, the interest was the different ways in which students engage with their learning. During the initial discussions, the following ideas were identified when developing any sort of solution:

  • Consistency
  • Timeliness
  • Clarity
  • Logistics
  • Stakeholders
  • Customisation

During a conversation during a break, I was asked about some ways which GSuite can be used to support ideas. Teachers may know about the different applications, however it is not always clear how these may support ongoing learning. Here then are some thoughts:

Docs

One of the benefits to Google Docs is the ability to work collaboratively within a digital environment. This can incorporate a range of formats, whether it be texts, tables, hyperlinks, images, charts, drawings and gifs. In terms of ongoing learning, Docs allows for feedback at any point, whether in the form of a suggestion or as a comment. Add-ons, such as CheckMark or JoeZoo further extend these possibilities by providing additional functionality, while there are also various options for inserting voice comments.

Slides

Similar to Docs, Google Slides offers a number of ways to collect, collaborate and communicate. Where it differs is the ability to engage with desktop publishing. At a simple level, you can add video, texts and images, as well as use the Explore Tool to automagically organise this content. Alternatively, it is possible to build upon a preexisting template, such as Jennifer Scott’s Slides Yearbook. Matt Miller and Alice Keeler have also created an add-on that allows users to produce a presentation from a collection of images in Slides.

Sheets

For some all solutions begin with Google Sheets. With the ability to protect access, hide cells and sheets, as well as link to a particular cell, Sheets provides a number of ways to organise data and information. One idea is to use Sheets as a central space for writing comments, linking to work and recording reflections. This could include sharing results with students from a mastersheet via IMPORTRANGE or providing an open space for students to support each other as Bianca Hewes’ has done with her work on medals and missions. Another approach to using Sheets is using scripts to automate some of the process. For example, Alice Keeler has created a template for making and communicating rubrics to students.

Forms

Building on the potential of Sheets, Google Forms provides a number of ways to collect and co-ordinate ongoing learning. One way is through the use of pre and post tests to drive differentiated instruction. Although in the past you had to use Flubaroo to automate this, with the addition of quizzes you are now able to do a lot more without the support of add-ons. Another use of Forms is as a way to efficiently record data. For example, you maybe conducting a reading conference, a Form can automate this process and send a summary to the student. Going a step further, it is also possible to create a unique link with pre-filled in content, such as name and class. This could even include attaching evidence using the ‘Upload a File’ function. This might be a short video or some work that has been annotated. This workflow is particularly useful when saving work on a mobile device.

Classroom

One of the challenges with ongoing reporting can be coordinating everything. Google Classroom allows you to create and communicate various resources and templates. This can include sending out individual files or sharing a collaborative document. In addition to giving feedback, Classroom provides a space for teachers to coordinate an ongoing conversation using private comments. Those using the mobile application are also able to annotate submitted artefacts. Classroom provides a way of communicating with parents. This involves sending regular summaries of missing work, upcoming dates and class acivities, such as questions, announcements and assignments. Although this could be done using Gmail, which would in fact allow dialogue, the benefit of Classroom is that it automates the process and allows parents to moderate how the communication works.

Google Drive

Although Google has added the ability to insert video from Drive into a presentation, it is possible to take this a step further and embed content from Drive in other spaces. This might include audio files, PDF documents or images. The benefit of embedding with Drive is that you are able to manage who has access to various content, whether it be only people within an instance or even just particular users. This can be useful when developing something like a closed portfolio. Another use of Drive is to capture and organise learning. As discussed, Forms now provides the ability to upload files. These items are then placed in one folder associated with the responses.

Keep

An alternative to using Drive and Classroom to collect content with Keep. There are a number of ways to organise and annotate evidence within Keep. For example, it can be useful when working with photos on moboile devices, as it allows you to avoid adding images to the camera roll. Notes can also be organised using labels and collaborated upon. This content can then be curated in Docs and Slides via the ‘Keep Notepad’.

Sites

A common application used to share and publish ongoing learning is Google Sites. The new Sites allows users to quickly and easily collect and collate work. One of the challenges though when sharing using Sites is that the setting associated with the various files allow access. If creating a public showcase it can be useful to add all the files into a folder with the desired sharing settings, which then overwrites the original settings. Another option is to use Alice Keeler’s AnyoneCanView Add-on, which changes the default settings associated with the document. For those wanting to embed more than just documents and images, Martin Hawksey has demonstrated how to embed any iFrame application using via Google Apps Scripts.

Many of these aspects cross-over to posts that I have written before involving portfolios and documentation, however where this differs is the attempt to capture many of the parts and how they might interconnect. As always, I am interested in your views. Is there something I have missed or maybe something you disagree with? Comments welcome for this is all ongoing learning, right?

Text

|
Tom Woodward on APIs and automation

Building on the APIs provided by Google Sheets and Google Calendar, I demonstrate how to automate the addition and change of multiple events.

I have been spending some time developing resources to support schools with timetables and reports. One of the things that occurred to me is the challenge of remembering to complete a number of key steps throughout the year. I therefore thought of creating a digital calendar that could be shared with schools that would help remind them.

In my search for a tool that would help with this process, I discovered From Sheets to Calendar, a Google Sheets add-on, that would allow me to create a series of events from a spreadsheet. As I explored this, it occurred to me that this might have ramifications for other groups in my organisation, especially those coordinating professional development. I have seen many plan things out visually in a spreadsheet. However, this means then creating these events again in a calendar. Here then is a guide to managing events with GSuite and sharing them with others.

Setup Calendars

Before setting up the various events, you need to make sure that you have created the various calendars. For example, you might have one for ‘meetings’, ‘professional development’ and ‘events’.

First Start

With everything set up in Google Calendar, install the add-on in Google Sheets. Once this is done, go to Add-ons menu and run ‘FirstStart’ to populate the template to work with.

Add in Events

With all the headings provided, enter the various information, such as title, time, location and description. Also, make sure ‘Add’ is listed against each of the events in the Action/Status column.

Import to Calendar

Once the events have been added, go to the Add-ons menu and run the ‘Import to Calendar’ to create events. Once created, there is an option to update and delete by changing the request in the Action/Status column.

Share the Calendars with Others

Although it is possible to send invites via the sheet, the other option is to share the particular calendars. For schools using Microsoft Outlook, Google Calendars can be shared as an ‘internet calendar’. The other option for users without a Google Account is to download and share a copy of the iCal file. The problem with this is that recipients will not be able to receive updates if there are any updates.

One of the challenges with a solution like this is that it is dependent on someone else’s scripts and support. In addition to this, to create more than 20 events, you need to pay a subscription of a $1 US a year. For those wanting an enterprise option, Zapier provides the connections, but it comes at a cost of $25 per month. There are also a range of scripts to build upon in Github. Another option is to manually import a CSV spreadsheet. So what about you? Are there any processes that you use when automating the creation of calendar events? As always, comments welcome.

Postscript

The Add-on, From Sheets to Calendar, has been removed from the Google Workspace Marketplace, however there are a number of other options. For me it highlights the benefits of solutions like Zapier which manage the connects and any changes seamlessly in the background.

Text

|
Spreadsheets

A plan for an automated monthly newsletter produced from Google Sheets. The intention is to develop data in a way that it can be used in a number of ways.

I recently wrote a post reflecting on the Digital Technologies curriculum. One of things that I realised through the process is that I often wait to the end to discuss my projects. Although this can be useful in providing an overview of learning and achievements, it does not necessarily allow others a means to provide feedback early on. I usually ask questions online, but this often lacks context. So this post is an attempt to plan out a new project, with the hope that others might be able to provide advice and guidance.

This year I started a monthly newsletter associated with Google. With GSuite the chosen learning and teaching platform in my organisation, I thought it would be useful to summarise the various resources for others. I started with a Google Doc, organising the various links under headings associated with the featured application, as well as a section documenting the overall updates.

This has ebbed and evolved as the year has gone on, with a clear order of applications to correspond with a range of modules. However, the question that has arisen is whether there is a better way of recording the various links and updates so that they are easily searchable.

Currently, you can go back through the various posts and look for resources, but this is both cumbersome and tedious. It therefore had me think about storing the links in a Google Sheet and possibly generating the monthly summary/newsletter from that.

I know that I could probably do this with a social bookmarking platform or even a blog, but I feel that putting the information into a spreadsheet provides more operability. It would mean that the data would be in a format with which I could present it a number of ways. It also means the links could be recorded using something as simple as Google Forms.

I am therefore thinking of creating a script in Sheets that collates all the links for the month in a Google Doc. To be honest, Google Apps Script is all still new to me, but I am wondering about the possibility of creating a template with merge fields. I remember Autocrat doing something similar. I could then use this to post in WordPress.

I am left with a number of questions, such as how should I action the script? Would it need some sort of selector or could it be done automatically? How customisable are templates? Could I generate a markdown version for the purpose of posting?

Maybe you have an idea or a post that you would recommend checking out before beginning or just a tip of where to start. As always, comments welcome.

Text

|
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.

Text

|
Productivity
“Productivity” by mrkrndvs is licensed under CC BY-SA

I was in a session recently unpacking GSuite. The discussion was around the Explore Tool, something Google added last year. Basically, it provides a range of suggestions based on the information on the page. During the conversation, someone remarked that they wished that the Research Tool was still there. For those who may have forgotten, the Research Tool was a small window added to the side of the screen which provided a number of ways to find content and information. It offered several types of results to sort by, including, images, quotes, scholar, quotes and dictionary. All of these aspects are available in a new tab via the Google Search Page or via Google Scholar.

The real problem as I see it is that Explore is not the Research Tool. Where the Research Tool was the same no matter what applications you go to, the Explore is dymanic. It provides different responses for each application it is attached too (only Sheets, Docs and Slides at this stage), each time and all automated. As Google explain,

Explore uses Google smarts to help you create amazing presentations, spreadsheets and documents in a fraction of the time they used to take… so you can get on with what’s most important in your life. It’s like having a researcher, analyst and designer by your side.

It is Google using machine learning to help people be more productive.

Google have a long history of killing off particular services. Some because of their niche use, while others because they no longer fit with the company’s goals and vision. I would argue that the reason that the Research Tool was removed was that it did not fit with Google’s focus on automated productivity. For some this is a reminder that Google’s prime focus is not learning, but I think that it is a reminder of who is in control of our platforms. That for me is one of the biggest differences between a platform like Blogger as opposed to an open sourced solution like WordPress. We are often dependent on others for infrastructure, applications and subsequently our ways of working.

With little sway over the design of applications such as Docs and Slides (other than sending in suggestions via the help menu), what I do have control over is appreciating how the various parts, such as Add-ons and the Explore tool, work. This is a particular challenge with the Explore Tool.  Whereas it was obvious Research Tool did, the Explore Tool is not so clear, that is until you open the hood. As I was looking through Kin Lane’s extensive investigation into Google’s application programming interface. I noticed a correlation between the options offered by the APIs and what was showing up in the Explore Tool. The Explore Tool could therefore be described as Google exploring what machine learning can provide when combined with APIs. This offers a useful insight into the possibilities of little bits of the web working together. 

To me this is what is at the heart of the current digital technologies push. Fine, students may use apps to learn how to code or schools might set up their own makerspaces to foster creativity and play, but more than this what is needed is a deeper understanding of the world that they are a part of, the algorithms with live by and computational thinking involved. Productivity is not always productive when it takes away the understanding and leaves us with a tool instead. This is the risk we face when coding becomes too complex. What we can appreciate are the parts and and how they might work together.

So what about you? What have your experiences been? As always, comments welcome.

Text

|
Portfolio
“Portfolio” by mrkrndvs is licensed under CC BY-SA

I was recently talking to a colleague about potential portfolio platforms. I have written about reporting packages in the past, but this is different. I therefore got thinking about the different possibilities. There are many things to consider, including accessibility for parents, students and teachers, ease of use, ability to incorporate different content, compatibility with different devices, the potential to transfer ownership and the level of security and protection. So here then is a start to a list of possibilities and some aspects to consider:

  • Global2/Edublogs: Built on WordPress, Global2 is a Edublogs Campus provided by the Victorian State Government. Student blogs are managed via a central teacher blog. Although Edublogs allows users to add a range of media and personalise the blog in a number of ways, including the addition of a password on posts. Although Edublogs have done a lot to streamline the experience, WordPress can still be challenging, especially for early years students. Read more here, while for an example of portfolios, check out the Geelong College examples.
  • Old Google Sites: Sites offers versatility and potential to integrate with GSuite. It is possible to make a template and produce a copy for every student, while then allowing students to make further comments. Similar to Global2, the complexity associated with editing can be a challenge for students, especially in the early years. Another concern are the limitations associated with connecting and communicating through the platform. Associated with this, is the problem where. unless you create GSuite accounts for adults, sharing directly with a wider audience can be challenging. For more information, read Anthony Speranza’s reflection.
  • New Google Sites: A rebuild from the ground on up, it is easy to drop and drag content around the page. One of the concerns with the new Sites is that much of the functionality associated with the old sites is missing, such as the ability to adjust permissions for different pages or embedding HTML code. There is also no means of providing feedback, unless you add a Google Form. Like Google Classroom, it will be developed further. However, there is no guarantee what and when. For more information, check out Eric Curts’ walkthrough.
  • Blogger: One of the benefits to Blogger is the ease of use and integration with GSuite. With simple themes and the ability to add video and images, for some it is a more convenient alternative to WordPress/Edublogs. Similar to Global2, there are means of moderating comments, while protection is provided by adding different users to the blog. Another benefit is that blogger can also be managed through Hapara. When students leave, ownership can be transferred as students move on. From recent conversations it would seem that Google maybe looking to give it a facelift. For more information on Blogger, go here, while Bill Ferriter has shared some of his experiences with Blogger too.
  • SeeSaw: A relatively new addition, SeeSaw allows users to document a wide range of learning artefacts. Associated with this, it provides the means to comment, annotate and attach text. Teachers are able to develop classes and add students, while users can sign in with their GSuite accounts. It is easy to share between parents, students and students and is available as an app or in browser. It is free to sign up, however the paid version allows for more control, especially around archiving content. One of the real pluses is that it does not necessarily require a 1:1 environment, as I have heard of cases where a teacher uses a tablet to capture work and link it to the specific student. Go here for more information.
  • Slides (and GSuite): Although not the most sophisticated method, another options is using Slides. It provides the ability to create a template and push it out via Google Classroom (or Hapara), while files can easily be shared between teachers, parents and students. There is the ability to engage through comments, even allowing for spoken feedback through extensions, such as Talk & Comment. Another added benefit is the ability to add video from Drive, therefore avoiding the need to publish to YouTube. For more on Slides, go here.

This is a start. Other options that I have not really explored in regards to portfolios include, Weebly, Book Creator, Kidsblog, Schoolbox and Onenote. The reality is, each context will have its own set of concerns and considerations. I hope that it offers a starting point for a deeper conversation.

So what about you? As always, comments welcome.

Update: in an original version of this post, I incorrectly suggested that there was a connection between Google Sites and Wix, which there is not. Thank you to the anonymous comment which highlighted this error.

 

Text

|

Future
“Future” by mrkrndvs is licensed under CC BY-SA

I have been saying to quite a few people that there seems to be change afoot in regards to Google Drawings. I base this hunch on a few signs. The majority of the information in the support site has been stripped back, there is no mention in the GSuite Learning Centre and there is a move to remove Web Clipboard. Google also have history of buying other products and integrating them within their core suite. Google have purchased a number of image applications, such as Nik (rebranded as Snapseed). This got me thinking about what I would actually want in a revised Google Drawings:

SCREEN CAPTURE

In my job, I often use Google Drawings to annotate images on the web. Whether it be taking a screenshot or using an application to overlay, capturing the web often involves third party applications. In the past, I used SnagIt until it was removed it from the play store. What you realise when you work with Google Chrome is that extensions are built on APIs provided by Google. Therefore I wonder whether Drawings could incorporate some of the features that SnagIt used and in the Insert Image option provide the means to capture the browser.

EXTENDED EDITING FEATURES

One frustration with Drawings is the lack of options in regards to editing. Fine you can adjust the transperancy or apply some basic filters, there is little options for editing elements or applying personalised effects. In addition to this, it would be good to have some way of visualising the order of objects. Although this might be pushing it. It would be good to have.

MOBILE DRAWING

It has been interesting to watch the use of Google Keep grow and develop. One of the functionalities offered is the ability to capture and create on mobile. This includes drawing sketches and rough drawings. Drawings is not available on mobile and does not necessarily allow any means for freehand drawings. It would great to be able to make and create via mobile, especially iOS. Although Slides offers many of the same functionalities, the iOS does not really make creating images and diagrams easy. The closest thing available is AutoDraw, however that seems to be designed to gather data, rather than fill a gap.

IMPROVED QUALITY

Google Drawings offers the means of integrating images within other applications such as Docs and Slides. However, one of the limitations is that as soon as an image is added, the quality is stripped. This means that if you are serious about incorporating images then they need to be complete before being inserted into the document.

Maybe the change will be different? Maybe instead of recreating Drawings, the answer will be incorporating Android applications into the web? Maybe I should just stick to using the alternatives. It will be interesting to see.

So what about you? What would you add to Google Drawings? What would make it better for you? As always, comments welcome.

Text

|

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.