Automatically pull an entire sheet of data between google spreadsheets

In November 2011, I wrote a blog post titled ‘How to automatically pull data between different Google Spreadsheets‘ – which was based on a feature called ‘ImportRange’. Although nearly 5 years old, this particular blog post is one of my most frequented and certainly the most commented on post that I have ever written.

I have recently been introduced to a new add on feature called ‘Import Sheet’ – which can be found at – and this does exactly what it says on the tin. It allows you to easily import (or export) a sheet from one file to another. So for example, I use google sheets to log the work that I do for each of my clients, with each client having a separate workbook (this means that I can share that workbook with them, without them seeing other contracts that I work on). I can then have a master dashboard, which imports copies of these sheets into a single location, and using simple functions such as the = function, I can then pull key data out of each of the sheets into my dashboard. Using Importsheet rather than the ImportRange feature is much easier, quicker and less likely to have problems.

The add on is a commercial tool, that does have a pricing plan, however the free version does what most people will want. Obviously I have no idea how the pricing plan may change in the future – they may choose to get rid, or reduce the functionality of the free version, so I wouldn’t suggest that people invest lots of time creating high stakes activity with this add on (unless they are prepared to pay in the future) – but certainly for the moment, this looks rather neat.

Creating a YouTube based discussion activity in Moodle

I run a lot of training on effective uses of a VLE (usually Moodle) and one of the easiest activities that I show, is finding a video on YouTube, and then embedding this into a forum activity within the VLE.

The reasons for doing this are:

  1. By embedding the video (rather than simply linking to it) – we remove all the distractions, adverts, etc. that appear on YouTube around the edges.
  2. By adding this as a discussion activity, we ask the students a question – this will focus their attention whilst watching the video, rather than just passively  ‘absorbing’ it.

It doesn’t matter if students don’t actually post their answers to the forum (although useful if they do), as they will still benefit from watching the video with the question in their mind.

The following video goes through the steps of how to embed the video, and the basic settings within a Moodle forum activity.

And if you want to only show a portion of the video you can always identify the exact start and end points that you want to play, by following these instructions:

Trimming and Embedding a YouTube Video into PowerPoint

I have blogged many times in the past about things to do with PowerPoint, including how to embed a YouTube video, or how to use TubeChop to embed a YouTube video.

In more recent versions of PowerPoint (2013 and 2016), the ability to embed a YouTube video has been made easier, and the following video will take you through the steps:

Although easier to do than in the past, this technique has been unreliable for some people in some organisations, so I always recommend to people to paste the video’s URL onto the slide somewhere as a live link, so if this doesn’t work, you have the fall back of simply accessing the video via the YouTube website.

This technique is showing how to embed the video – this means you still need access to the Internet when viewing the presentation, and it won’t work if the organisation blocks YouTube.

This technique replaces the older method of using the shockwave flash object, or using TubeChop to trim the video.

How to trim a YouTube video and embed it into WordPress

Regular followers of my blog, will know that I have previously blogged about different ways of trimming or cropping YouTube videos to use in different situations, e.g:

Trimming videos can be really valuable, as often (in education) there may be a key message or element in a video, that we want to draw attention to, without having to show the entire video, so carefully selecting sections of the video can drastically improve the impact of using that video as a resource.

Up until recently, something that I couldn’t do was to get this to work in WordPress, I could only embed the whole video – however today I found out that I can do trim a video, if you follow these instructions exactly:

  1. Locate the video you want on YouTube.
  2. Under the video choose ‘Share’ and then ‘Embed’.
  3. Underneath the preview, there will be some tick boxes – make sure the one called ‘Show suggested videos when the video finishes‘ is unticked (this is really important).
  4. Copy the embed code that is above the preview.
  5. Go into your WordPress post, and into the HTML editor.
  6. Paste the copied embed code in the correct position.
  7. Preview your post.
  8. Go back to edit your post, and again into the HTML editor.
  9. The code that you pasted in, will have been changed, towards the end of it, locate the text rel=0.
  10. Immediately after rel=0 add &start=xxx&end=yyy (where xxx is the number of seconds you want the video to start at and yyy is the number of seconds you want the video to end at).
  11. Preview your post – if it works then publish.

So – if I have a video that I want to start at 2:35 and end at 3:15 – I convert these into seconds (2:35 = 155, 3:15 = 195) – and the end of the code will change from:




Below is an example of one of my YouTube videos, trimmed to start at 155 seconds and end at 195 seconds.

Using my phone to record audio for Moodle

This blog post is about how a mobile phone and a free internet service called iPadio, helped me to recover an awkward situation by recording audio from my phone – then adding this to a Moodle course.

I was running Moodle training for a client in Worcester. The day that was arranged I had access to my car so planned to drive – however due to the floods we had to postpone the training to a different day when I didn’t have my car, so had to rely on the train network. I was a little nervous as had to catch 3 separate trains, with not much time for changes – so if any of the 3 trains were delayed I risked turning up late. The client accepted this risk – so we went ahead.

As it happened my nervousness was justified as my first train was significantly late, meaning that I knew I would be late for the training. Many people in this situation (including me a few years ago) would at best find this distressing and at worst enter a mild panic – but I was able to execute a plan:

Robin Hood (1922) - Allan Dwan

Because the first part of the Moodle training involved attendees accessing an ice breaker activity – this could be started without me – all I needed to do was introduce myself and session to the delegates, and luckily I own a phone that gave me all the tools that I needed.

I used a service called iPadio (I have blogged about this in the past on numerous occasions) – this is a free service which I had previously subscribed to – and it allowed me to record an audio file simply by dialling a London phone number (therefore free to me as part of my minutes allocation) and talk to an answerphone to record my welcome message.

As soon as I had finished recording, I went to the iPadio website (via my phone) located the recording, and copied the URL for that recording. I then went into the Moodle course that I was using during the training and added this at the top of the course as a link. Ideally I would have downloaded the audio file, and then uploaded it as an MP3 file to the Moodle course,  but I couldn’t quite do this from my iPhone – so had to settle for linking to it instead

Then the final part of the plan was for me to phone my contact at the centre – explain the situation, and ask them if they could start the session for me – by simply finding the Moodle course, and playing the audio message I had left for them.

All of this I completed in less than 10 minutes from my phone whilst stood on a cold and windy platform at Leicester Railway Station, and I hadn’t even had breakfast or a cup of coffee at this point (and those that know me well, know that I don’t function until after my second cup of coffee)

Although not an ideal way to start a days training – it did show the attendees one of the powers of Moodle – to be used in situations where the teacher isn’t present (either planned or not planned) but where the teacher is still able to influence the class.

Usually when I do audio recordings, I am sat in my quiet office, with a headset on and using either Audacity (PC) or Garageband (Apple) – which gives me good quality audio recordings, however there are often situations when I want to record an audio recording when I don’t have this set up, and for this using iPadio is great – as all I need is my phone, the final file will be an MP3 which is the best for most purposes (if I used the built in sound recorder in my phone, it saves it in a proprietary format that can only be accessed by people with the same make of phone), and I can do various things including downloading, linking to, or embedding which covers all possible bases.

Printing a YouTube video and it’s uses in education

This may sound like a daft title for a blog post, but it is possible to easily get an export of still images taken at regular intervals from any YouTube video, to create a storyboard of that video. These can either be printed or easily saved as a PDF, and then used electronically.

I don’t want to claim any credit for discovering or developing the technique – that needs to go to Amit Agarwal, who explains the technique on his blogpost ‘Do you want to print a YouTube video?

I am not sure what the legal issues are regarding copyright and this technique, but assuming that this is OK legally, then this simple technique could have a few really smart educational uses.

  1. As someone with a sports background, my first instinct was to use this for movement analysis, but the gap between the frames is too great for this to be realistic – but this could be used in a sports setting for crude notational analysis. E.g. if watching a game of say netball – you could count how many frames is team A in possession, how many frames is team B in possession, then work out the ratio, and you have a rough gauge of possession – compare this to the final score line and see if there is a correlation?
  2. In a subject like marketing, you could look at the output from a companies advert, and analyse how much of the 30 seconds is spent doing different things e.g. showing the product, showing the prize, repeating a key message etc.
  3. In teacher training, if a trainee teacher is filmed completing a microteach – this can be uploaded to YouTube as a private video (so no-one else sees it) you can then create the storyboard of printed images, and then analyse how their time is spent – e.g. how many frames are they writing on the board (with their back turned), how many frames are the students doing something etc. This could be a very effective tool – as counting frames on a piece of paper is much quicker and easier than trying to do the same with complex timings and starting and stopping clocks etc.
  4. In media studies – you can analyse the different types of shot (close up, mid shot, scene setting etc.) that are used in a sequence, and what effect this has on the message being conveyed.

Although in general I often like to move away from paper, and converting a media rich resource such as a video into a less rich image, seems to be a backward step, I think the ease of this technique and the power that it brings to carry out a basic analysis of the video is superb, and would be an excellent teaching activity.

Why educators should use creative commons images?

I have regularly blogged about different tools to locate creative commons images, however I thought it would be useful for me to go into more information about ‘why’ we should use these – and not just from the logical response of staying on the right side of copyright law.

To start with, we need to understand what Creative Commons is. The following YouTube video explains this quite clearly. This is a New Zealand based video but the principle is the same in the UK and elsewhere.

Now onto the question of  why? I will be talking about this in an event next week, for which I have created an audio and an associated transcript (pdf) below.

Why it is important that educators use CC images (audio)

Why it is important that educators use CC images (transcript)

So – as well as the legal reason for using Creative Commons images, there is also the moral and educational reason – in that we need to prepare our current learners not just for their current studies, but also for the workplace beyond.

I was running some training at a primary school, where I introduced them to the idea of the Xpert attribution tool, and how this attaches the reference to the image, and if we get primary school children used to seeing correct referencing of images, when they get to secondary and FE/HE levels, they will be more familiar with this notion.