Tips, Tricks and Sheets

Three Things


“Three Things” by mrkrndvs is licensed under CC BY-SA

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.

An Introduction to Google Classroom

Image via JustLego101

Image via JustLego101

Google Classroom is a platform for communicating and collaborating using Google Apps for Education. Unlike other platforms, Classroom focuses on three key areas: pose questions, make announcements and set assignments. Although this may seem somewhat limited, as Alice Keeler demonstrates, it provides a foundations for so many possibilities.

Some of the benefits include:

  • Sharing Resources: Whether it be as an assignment or through the announcements, you can easily share resources with students. In addition to this, Classroom creates a structured filing system in Google Drive.
  • Structured Organisation: Unlike spaces like Edmodo which can end up with a random student who mysteriously has three accounts, Classroom provides a central management system through Google Apps Admin meaning that you can in fact add students yourself. In addition to this, you are now able to have multiple teachers, something that was not possible at the beginning.
  • Extending Google Apps: Not to be confused with Learning Management Systems, Classroom works best when it is integrated with Google Apps. For example, through the creation of an assignment you can generate an individual copy of a Google Doc.
  • Multiple Device: Like Edmodo, Classroom is available in the browser, as well as on the iPad and iPhone.
  • Assessment and Rubrics: Although there are answers, such as Alice Keeler’s Sheets Add-on RubricTab or Andrew Stillman’s use of Goobric and Doctopus, the easiest way to create a rubric with Classroom is by creating a copy for each student via assignments. Associated with this, Classroom provides the ability to turn work in. This means that students can signal to you that although they may have shared a document with you, they have actually finished with it
  • One Less Logon: A part of Google Apps, using Classroom as opposed to other spaces means one less log on for students to remember.

So what about you? Have you used Google Classroom in your classroom? What have you seen as some of the benefits and challenges? As always, comments welcome.

Resources


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.

Making My Own Maps with GSuite


flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

I recently wrote about powering up blogs by adding video, audio and GIFs. Another form of content which you can add is an interactive map by embedding a Google My Map.

Google Maps has been a staple of Google’s applications for a long time. However, something that is often overlooked is the potential of creating your own maps. In the past, the place people often went was Google Earth, using features such as Tour Builder, while more recently Maps Engine Lite offered a way of customising traditional Google Maps.

One significant changes in rebadging Maps Engine Lite as My Maps has been to house the files within Google Drive. This has made it easier to create, collaborate and share various creations of space. You are able to easily make layers, add place marks, draw shapes and create directions. To take this to the next step, you can also import information via a spreadsheet. Uploading can be good if you have a long list of coordinates. While you can also export data from My Maps as a KML file. This can be useful if you want to add information into Google Earth.

In addition to this, you can add content to the place marks or shapes, whether this be a description, image and video. This then comes up when you click on the marker. In addition to this, you can also change the place marker icon and colour of the shape. Therefore giving you with another layer of meaning.

Some ideas for using Google My Maps include:

What is good about My Maps is that it provides a different way for communicating information and telling a story. So what about you, how could you use My Maps? As always, comments welcome.

Additional Resources


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.

Introduction to Blogging with Global2


flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

The term blog derives from ‘web log’ and was initially coined to describe “discrete entries (posts) typically displayed in reverse chronological order.” This though has changed over time. Now it incorporates a range of different methods for creating and communicating. Sometimes it is organised inside a bigger system, but more often than not it is standalone.  There are many different platforms out there, each having their benefits and negatives. What does not change is the focus presenting mixed media, including video, text, images and audio.

Global2 is a blogging community provided by the Victorian State Government to Catholic and State schools. It is the largest Edublogs campus in the world. Providing the functionality of WordPress, plus the added benefits of moderation, content filtering, class management and network administration.

Examples of the blogging activities in which you can use Global2:

  • Individual – Whether it be for personal learning reflections or as a portfolio for finished work, blogs offer students a great place to share a personal story with a wider audience. It also offers a means for providing visible comments and feedback.
  • Class – In addition to telling their own story, blogs provide a means for a whole class to actively engage in their learning by telling a shared story. Elaborating on the idea of documentation, sharing the load not only makes it more feasible (for you only need one device), but also provides for different perspectives.
  • Project – whether it is a passion project or particular topic being studied in class, a blog is a great place for an individual or a group to share everything. Whether it be resources, reflections or the final product, it can be a great way to keep track of everything.
  • Static Website – Often when we think of blogs we conjure up ideas of posts and the need for constant updates. However, a blog can also be more static and simply used to present information and resources.

At their heart, blogs enable students to showcase their learning to a wider more authentic audience. For some this can seem daunting, however it needs to be noted that there are means for managing this, whether it be making the site password protected or simply monitoring comments. What is important is that Global2 is easy and efficient. For although there are many walled gardens, like Edmodo, where we can present information, the need to complete three different steps before getting to the final destination can put many people off.

Further Reading

Global2 – A collection of resources associated with Global2. The first place to go.

Your Global2 Blog by John Pearce – A presentation unpacking everything from tags to widgets. A good run through of all the different things to consider.

The Edublogger – A community blog sharing everything associated with Edublogs, whether it be blogging with students or simply the latest updates and changes.

The Edublogs User Guide – As Global2 is a part of the Edublogs community, this user guide can be useful when trying to figure out some of the different intricacies.

Blogs in Plain English by Common Craft – A video for people who wonder why blogs are such a big deal.

10 Ways To Use Edublogs To Teach – A video unpack some more possibilities to consider when it comes to blogs.

3 Things You Should Know About Blogging by Steve Wheeler – A video exploring some crucial ideas around blogging.


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.

Making Sense of Creative Commons – A Collection of Resources


flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

In a previous post, I discussed the idea of Creative Commons and the importance of sharing. Although many give lip service to it all, one of the challenges is where to find appropriate content. The Creative Commons site provides a range of recommendations for finding appropriate content. I have unpacked these and more:

Images

Google Images: This is often the first place that anyone goes to. There are so many options available to find the right image, such as colour, size and type. Problem is, not many people realise that most of the images that come up are copyrighted and should not be used so freely, especially in public and online. In ‘Search Tools’ you can adjust the ‘Usage Rights’. However, as Alan Levine warns, Google assumes that if a page has a Creative Commons license attached to it that all the media within is included, which is not always the case.

Flickr: An alternative to Google Images, Flickr is an image repository. Designed with two aims in mind – to make images easily available and provide a means for organising them – Creative Commons is very much at the heart of Flickr. Like Google Images, you can adjust searches in ‘Advanced’ to look for different licenses. In addition to this, there are a range of site that you can use that help find the right image. Compfight and PhotoPin are popular ones, while Photos for Class not only filters out inappropriate content (a big problem at times with Flickr), but also applies a citation to the image.

Pixabay: Unlike Flickr which collects both copyrighted and Creative Commons images, all the images at Pixabay are not only free, but do not require attribution, even for commercial applications. Frustrated with finding images, the creators made a site to collect together a wide range of images published under Creative Commons Public Domain deed CC0. The aim is to provide images that are ready to use for any project, either amateur or professional.

Unsplash – A useful repository of images that are licensed under Upsplash’s own license, which is a derivative of Creative Commons Zero, meaning that they are free to use as you like. Originally it was somewhat random. However, more recently, a search option has been added to help find what you might be looking for.

Open Clip Art Library: Like Pixabay, Open Clip Art provides a wide range of images that have been published under Public Domain. The belief is that by removing as many restrictions as possible, the clip art is able to be shared as freely and openly as possible.

Pics 4 Learning: A repository of copyright-free images for school. Although you can search, images are also organised into different collections. Only issue seems to be quality and variety.

The Noun Project: A great place for icons, the Noun Project provides a mixture of options and licenses. Sites like Credly intergrate with The Noun Project to provide a library of open images to use when creating badges.

Pic4Carto: For something different, this site allows you to search based on location. It uses the geo location information provided by various sites to bring together recent photos taken in the area.

MediaChain: An open library, which connects media to its creator and subsequent information about it. Alan Levine has elaborated on it in a bit more detail here.

For some more ideas, I recommend John Spencer’s post for a collection of sites that do not need any attribution. However, the catch is that many of these collections are not curated.

Video

Vimeo: A little bit like Pixabay, Vimeo offers a range of quality videos. In addition to this, there is a wide range of content you can search and download based on licensing.

Youtube: It is often an overlooked feature, but you can find videos on Youtube which you are able to modify and remix. This material is accessed and edited via Youtube Video Editor. The only catch is that Creative Commons CC BY license is the only option, which has its limitations.

Sounds

Soundcloud: Arguably the world’s leading social sound platform where anyone can create sounds and share them everywhere. In some respect, Soundcloud is to sound what Flickr is to images. Created by the community, you can find anything and everything from podcasts to samples. Like Flickr, you can easily search through content based on licenses. There are some interesting collections here, such as a whole lot of tracks posted by Moby for the soundtracks of non-commerical films.

Jamendo: Whereas Soundcloud is simply about sharing sounds, Jamendo is about providing music for free. Organised around genres, instruments and moods, it provides an easy way to find the right sound for the situation. One of the catches to the site is that it can be difficult to sort through tracks based on licenses, therefore you are often left checking the bottom of the page.

ccMixter: This is a community music remixing site designed to foster sharing with Creative Commons. Not only does it provide a wide array of music, samples and sound effects, but many of the tracks come split up into parts, designed to be reused.

YouTube: Like video, you can find a collection of Creative Common sounds in Youtube via Youtube Audio Library. Similar to Jamendo, you can search by genre, mood, instruments and duration. In addition to pulling pieces into your own Youtube creations, there is also the option to download them and use them elsewhere.

Freesound: A collaborative database of various Creative Commons licensed sounds, Freesound allows you to browse through sounds via tags and track names. Although you can easily listen, the only catch is that you need to create an account to download tracks.

Sound Bible: SoundBible.com offers thousands of free sound effects, sound clips, and straight up sounds. There are a number of licenses used, including copyright free material.

General

Wikimedia Commons: A part of the Wikimedia family, its purpose is to make available public domain and freely-licensed media content to everyone.  All the content is either in the public domain or meets the definition of a free cultural work. Although the search options are not as easily definable as other sites, the wealth of content makes Wikimedia a gold mine. There is also an attribution tool you can use.

Europeana: A collection of cultural artefacts from all over Europe. Sourced from different galleries, libraries, archives and museums, items range from books and manuscripts, photos and paintings, television and film, sculpture and crafts, diaries and maps, sheet music and recordings. What is good is that you can easily refine searches to only include certain licenses and types of media.

Internet Archive: A non-profit library of millions of free books, movies, software and music. Its purposes include offering permanent access for researchers, historians, scholars, people with disabilities, and the general public to historical collections that exist in digital format. As its purpose is not primarily for feeding the remix culture, if you find Creative Commons material declared you may use the content according to the terms and conditions of the applicable license. However, it can be a little finicky trying to find applicable content.


In addition to a range of websites, there is a growing trend of applications which help sources appropriate Creative Commons images for you. Some examples are Haiku Deck, Adobe Voice and Adobe Slate (all of which are also available on the iPad).

I think that Alan Levine summed up this challenge in a recent post:

How do we expect people new to coming to an understanding to make sense of all the flavors of licenses? We want school kids to make these choices? And there are people out there who want even more kinds of licenses. CC-BY-MC-GD-ER-TZ-XD?

I am not trying to tell anyone else what to do here.

But I am going back to the simplest, and frankly, I’d rather focus my energy on the sharing rather than the licensing of the sharing.

If there are sites or steps you use to help focus on sharing and less on licensing, I would love to know. 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.

Also on: