How to automatically pull data between different Google Spreadsheets

Edit 29/03/2017 – Please also look at another related post – https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-values-in-a-given-column/ 

Edit 26/10/2016 – Please also look at a more recent addition to this post  – https://davefoord.wordpress.com/2016/10/26/automatically-pull-an-entire-sheet-of-data-between-google-spreadsheets/


I have been using Google Docs for quite a few years now, and in particular Google spreadsheets.

One feature of Google spreadsheets is there is a function called ImportRange that allows you to pull data out of one sheet and into another. This can be really useful, if for example you have a spreadsheet that you are using to collaborate with others, and then somewhere along the line you want another person to be able to see some of the data in the sheet but not all of it. e.g. if you are using this to track student grades, you could have a master sheet that you and other tutors can see all of, you could then create a separate sheet for each student, and pull through only the data that refers to them (you then share that sheet with the student) and they have a live constantly updating record of what they have achieved etc.

Or you could have a mechanism where each subject tutor has their own spreadsheet to record class grades, then the course leader has another sheet which pulls all of this data into one place, so they can at any point in time see how the class is doing without having to ask tutors to email over their latest version of their standalone spreadsheet (which I see happening all to often).

Here is a video showing the basic principle of this idea.

Why I don’t like ‘pet names’ for VLEs

Most educational organisations have a Virtual Learning Environment or VLE. If an FE college most likely Moodle, if a University probably either Blackboard or Moodle. If a school, it could one of many possibilities including Moodle, Frog, Fronter, and the list goes on.

Some organisations call their VLE by their proper name, e.g. they call it Moodle, or Blackboard of Frog etc., but other organisations decide to give their VLE what I call a ‘Pet name’ – things like LearnSpace, Myzone, LearningStuff, TheZone, Rex, Ginger, (OK maybe not the last 2), but basically an alternate name for the VLE. Now I don’t like this (in fact it is a pet hate of mine) for a couple of reasons.

Firstly when you do have an alternative name, invariably there will be confusion within the organisation as some people will call it one thing and others will at times call it the other, and if someone works at more than one organisation they will definitely get confused (and confuse other people) as they forget which name to use.

Secondly (and more importantly), when I work with staff I try to encourage people to try to find answers to problems themselves – e.g. ask Google the question and see what comes back. The problem with giving your VLE a pet name, is people will include that pet name in such a search (rather than the VLEs proper name) – and not suprisingly they don’t get much sense back. This is something that I have witnessed on many occassions, and frustratingly replacing the pet name with the real VLE name in the search criteria, has yielded the information they wanted. Another situation that I witnessed that saddened me, was a student was accessing the VLE and got stuck with something, so they used the inbuilt help tools within the VLE. However when they started reading the information because it used the VLE’s proper name rather than the pet one, they didn’t think it was referring to what they wanted so closed it down, even though the help function would have helped their query.

So if we want teaching staff and students to start taking responsibility for their own support, then can we please stop this obsession with calling our VLE by an irrelevant pet name, and call it what it is. Otherwise we are denying our users (both staff and students) the wonderful support mechanism that is the web and its many contributors.

Unless anyone can present a better argument for having these pet names….?

How to quickly upload lots of images to PowerPoint and then compress them

A few months back I ran a training session for some primary school teachers, on how to use PowerPoint within primary education, and they were very impressed with most of the ideas and techniques that I worked through with them, but the one that captured the imagination of even the slightly IT wary teacher was showing how we can quickly bring lots of photos from a camera into a PowerPoint presentation.

This is a very simple technique, but one that many people haven’t discovered yet.

A video showing how to do this, and how to compress the images in one go can be found here