Automatically pull an entire sheet of data between google spreadsheets

Edit (22/6/2017) – please note that since writing this post, the company importsheet.com has been renamed as sheetgo.com

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 https://importsheet.com/ – 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.

Advertisements

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:

….?rel=0]

to

….?rel=0&start=155&end=195]

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

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.

Adding subtitles to YouTube videos using CaptionTube

YouTube is a wonderful resource, it works on just about all internet enabled devices, it hardly ever goes wrong, it is easy to use and although there is a lot of low quality rubbish on there (in my opinion), there is also huge amounts of really useful high quality videos that we can use in education to enhance our teaching and learning practices.

A feature of YouTube that many don’t know about, is the auto-captioning option – in other words YouTube creates a transcript of the video without you having to do anything. If you are watching a video on the YouTube page and you want to see the captions, then there is a button below the video (currently to the right of the where it says ‘add to’) which is the transcript button – this brings up the transcript as a timeline below the videos and automatically advances with the video. This can be great for learners that have a disability (e.g. are deaf), but can also be really useful to find a key point within a video.

For example I often use short sections of the excellent TED talk video of Ken Robinson talking about schools killing creativity. If I want to locate a certain section within that video, I use the automatic captions that appear below it to locate the section that I want.

Because the transcripts are computer generated, they do contain errors – and depending on the clarity of the voice and the background noise of the video will determine the accuracy of the transcript. For some reason my voice never does well with automated speech to text systems, including YouTube.

However if you do want to override the automatic captions that YouTube creates with your own ones, then this is very easy to do – and for this I use a service called CaptionTube This is a simple system where you sign in (using a Google Account) you locate the video you want to caption (which could be your own or someone elses) and then you play the video pausing it at intervals to add your captions. If the video is your own, then you can add the captions to it there and then, if it isn’t your video then you can send the transcript to them to see if they want to upload it.

The following video (by John Skidgel) introduces the basics of CaptionTube.

Here is a video of mine that I captioned using this method. This took me 12 minutes in total from opening the page to my captions appearing on the video on YouTube.

Adding Captions to a video is a simple way to increase the accessibility of a resource, as well as potentially increasing the number of people that see your video, as the contents of the captions will get picked up by search engines (if the video is set to being public and listed).

Cropping a YouTube video before adding to Moodle

in April 2011 I blogged about how it is possible to use a service called TubeChop to crop a YouTube video and then embed it into PowerPoint, and this has been one of my most viewed posts on my blog.

I used to use TubeChop as well when embedding a video into a VLE such as Moodle or Blackboard, but have found that it hasn’t always worked, and doesn’t work on iphones/ipads etc as it plays as flash only – so I have found another far more reliable way of cropping a YouTube video before adding it to Moodle, by simply editing the embed code for the video. This technique works really well when accessing videos via Moodle on a mobile device, which I think is going to become a massive feature in the next few years.

The video below will show how to do this (apologies for the poorer than usual sound quality)

In summary:

  1. When we select the embed code we untick all of the option boxes below it.
  2. We copy and paste the embed code into something where we can edit it (e.g. word, notepad)
  3. We find the text “rel=0” and after it add “;start=xxx;end=yyy” where xxx is the number of seconds at which you wish to start the video, and yyy is the number of seconds at which you wish to end the video.

There are lots of uses for this technique – often when watching a YouTube video, you don’t want to show all of it, so cropping the bit that you want makes the process more efficient. You can also use videos as part of Moodle Quizzes, either in the question, the answer choices (it using multiple choice) or in the feedback. So if you find a video of something relevant to your area, you can crop it to just play a few seconds – then ask a question based on what the student has just seen, or have cropped videos in each of the answer choices (e.g. question could be which of the following videos shows the correct technique for ….) then show 4 videos clips, with one correct and 3 incorrect.

I am also a big fan of when using video, we need to instruct the learners what they are doing with it – e.g. asking them to observe something in particular, or critique the video, or watch the video then answer these questions… By breaking a video up using the cropping idea, we can easily add these textual instructions between the clips, rather than just dumping a whole video on the VLE for them to watch without a clear purpose.

This technique works really well with anything that allows iframes to be embedded (e.g. Moodle). It therefore may not work with Blackboard (in which case TubeChop may be still required). I also haven’t too date worked out how to get this to work in PowerPoint – so again for that I am stilling using TubeChop.

 

Making use of statistics from YouTube

A few years ago YouTube was seen by many in education as a source of evil that had to be blocked. banned and banished at all costs -because of the nasty things that learners may see there. This was a shame as alongside some possibly undesirable content is some excellent content, and the YouTube’s streaming capability is better than any others as works on all devices, is quick to load and in short just works.

Luckily the number of organisations blocking YouTube has reduced, especially within FE and HE, and even many schools. This pleases me as I have posted many videos to YouTube – most of which can be used by others, as they give simple clear step by step instructions on various elements of learning technology.

Something that I also find interesting, is the statistics that I get back – I can see how many people have viewed each video, where they are from, what sort fo device they have used and how they have found the videos. This helps me to plan future videos to meet my audiences needs, and if I was using these videos to support teaching and learning, I could use the analytical information to quickly see how effectively my learners are accessing these videos.

When I upload videos on behalf of one of the organisations that I work for, I almost always have the settings of making it public but unlisted. This means that the videos cannot be found by someone else searching for it – they can only be found if someone knows the link to it. If I embed one of these videos into an area the VLE to support a particular session or topic, a week or so later I can see how many people have viewed the video – (and when). Although not an exact science, this gives me a useful insight into the user behaviour – especially if I compare this with the usage data from the VLE.

In one instance I found that lots of learners had visited the area on the VLE but hadn’t played the video – which made me realise that I had embedded the video too far down the page – so I changed it’s position. On another occasion the video had been played many more times than the VLE area had been accessed, which I assume meant that the learners had watched the video multiple times – which as the video was directly related to the assignment task, I assume means they were using it to aid their completion of the task (which was its intention).

I appreciate that most teaching staff won’t have the time or inclination to look at things this way – I was mainly just looking out of interest – but if people do have the time/interest then this could be very useful information to confirm that they are doing things right, or give them pointers as to where they need to change things slightly.

Whilst looking at my own videos, I discovered that my most viewed video of all time is the one about adding countdown timers to PowerPoint.

This has had over 45000 views in 2 years, and currently gets viewed over 3000 times per month. This single video accounts for more than 65% of all views of the 67 videos on my channel. This video shows to me the power of YouTube – the fact that this attracts so many views means that it must be doing something right, and what a shame that there are still many educational organisations that are depriving their learners of this resource.