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.

Bulk uploading items to a Moodle Glossary

The Moodle Glossary is one of the simplest activities to use within Moodle. It’s primary purpose is to set up a glossary that the students populate or at least add to, rather than the tutor populating it – however there are occasions when it is useful for the tutor to populate the glossary. This could be if the tutor wants to provide the students with a ‘correct’ list of technical terms and their definitions, or if the tutor wants to create a crossword using the Moodle Game Activity plugin – which pulls the data out of a glossary activity.

If the tutor is populating the glossary, they could enter the data manually item by item, but this is very time consuming, especially if they already have the data to hand in a spreadsheet or similar. Luckily there is a way to bulk upload these items. It is a little complex, but once you have done a few not too bad, and certainly a lot easier than manually typing in lots of items.

Firstly – we need to create the glossary, the following video from Moodle, goes through this step:

Then the clever part is importing the list of terms from an external source. This is covered in this video:

The XML converter that is used, can be located here:

https://moodle.org/mod/forum/discuss.php?d=91224#p489666 with the direct link to the actual file itself being:

https://moodle.org/pluginfile.php/159/mod_forum/attachment/489666/glossaryXMLconverter_html4.zip

You only need to download and unzip this once – as long as you can remember where you have saved it to. Although it may seem a little convoluted at first, this technique will save serious amounts of time compared to manually entering lots of data.

The only other thing to note, is the default settings of the glossary may allow the students to add their own items to this glossary, if you don’t want this to happen, you can stop this by using either of the following options:

  1. In the glossary settings – make it so the ‘Approved by default’ option is set to ‘no’.
  2. Go into the permissions for that glossary and next to where is says ‘Create new entries’ delete students from the list of roles that can do this.

 

 

How to display the sheet name in a cell in an Excel spreadsheet

I use Excel a lot, not just for crunching numbers, but for creating teaching resources, lesson planning, managing my accounts and invoices and various other uses. One feature that I often use, is the ability to have the sheet name appearing inside a cell in the spreadsheet – so for example with my invoices – I rename the sheet name with the invoice number, this then updates the invoice within the sheet.

To do this I use the following formula below.

This may seem a complex formula, and it doesn’t matter if you don’t fully understand it (I don’t), you just need to copy and paste this into a cell in the spreadsheet, and the sheet name will appear. If you change the sheet name, the cell will change accordingly.

The only caveat is, that the workbook has to have been saved at some point for this to work – so if you do this with a new workbook, it won’t work until it has been saved.

This technique is unique to Microsoft Excel, it doesn’t work with other spreadsheet tools such as Open office, Google sheets or Apple’s Numbers.

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.

Choosing different pins when creating Google Maps

I am not sure quite how (as I am sure it worked when I originally wrote this post) but the method that I describe below doesn’t work completely as described. I am trying to work out how to get round this, as I think this would be a really useful technique. If I can solve this, then I will re-post to this blog and will update this post accordingly.

Sorry for any confusion caused.

I have in the past blogged a couple of times about Google Maps – for example https://davefoord.wordpress.com/2009/09/07/quickly-creating-a-google-map-from-a-spreadsheet-of-data/ gives instructions on how to create a Google Map from a Spreadsheet of data, and https://davefoord.wordpress.com/2010/05/21/creating-a-distance-from-google-map/ explains how you can create a radius diagram from a pin of either road distances or travel times.

With the first of the above techniques – one problem with this is that you often end up with the pins that are plotted being invisible at first – meaning that you have to then edit each one in turn to choose the desired colour pin, but it is possible to add data to the original spreadsheet to automatically plot pins of your choosing.

To do this add a column and title it something like ‘image’ or ‘icon’. Then you need to add against each entry the URL of an image that you want to act as the marker for the point – you could use anything (e.g. your own company logo) as long as it is small in size. Or you can use the pins that Google provide – all we need to do is find out the URL of each of the pins, that you may want, which I have done for you here so

Red

Red

has a URL of: http://www.google.com/intl/en_us/mapfiles/ms/icons/red.png

Red-dot

red-dot

http://www.google.com/intl/en_us/mapfiles/ms/icons/red-dot.png

Red pushpin

Red pushpin

http://www.google.com/intl/en_us/mapfiles/ms/icons/red-pushpin.png

(so all you need to do is copy the URL and change the ending to either red.png, red-dot.png or red-pushpin.png to get the desired shape.

You can then replace the colour ‘red’ with yellow, green, purple, pink, or blue to get different colour pins

We can also have letter markers such as

MarkerA

MarkerA

which has the code of http://www.google.com/mapfiles/markerA.png (and you can change the letter A for any other letter of the alphabet)

If we are creating our starting list in the spreadsheet it is possible to automatically create a different letter for each entry – by using a simple formula in the spreadsheet

And whilst researching for this post, I discovered http://sites.google.com/site/gmapicons/home which has a few other icon URLS in its list.

So having created our spreadsheet of data, and added an image URL against each URL – if we then go to http://www.batchgeo.com/ the site mentioned in my first blog post) – When you get to Step 2 – there is a button for ‘Advanced Options’ – if you choose this, then against ‘Image URL’ choose whatever column header you had in your spreadsheet.

With some clever understanding of Excel and the use of formulas, this could be a very powerful technique for creating maps.

Quickly creating a Google Map from a Spreadsheet of data

I think that Google Maps are a really useful tool, and everytime that I need to visit somewhere new, the first thing I do is put the postcode into Google Maps, and save the location. This way if I visit that town or city again, but to a different location I can see where the new location is in relation to the previous one, which helps me with my navigating. I recognise that many will use their in car sat nav systems, but because I mainly use the train I still use paper maps for these purposes.

One thing though that I have found hard in the past, is uploading lots of points to a map – which done manually takes an age, so I was relieved when I found a technique that does this for me.

We start of by using http://www.batchgeocode.com/ which looks a bit scary on first inspection, but is OK once you have used it a few times. Here are the steps to follow

  1. Create a spreadsheet with your table of information in – it can have whatever you want in there, as long as the postcode is in its own column, and the top row are column headings
  2. Copy the contents of your table (highlight and Ctrl-C) – including the column headings – Go back to http://www.batchgeocode.com/ and paste into the ‘table’ in step 2
  3. Validate the code by clicking the button
  4. In step 4 you tell it, what information to include and where – so for example next to ‘PostCode’  choose the post code column from your table – and for ‘Title’ Choose whatever you want the point on the map to be called. It may take a few attempts to get these settings right, but after a few goes makes sense.
  5. Run the geocoder by pressing the button – this will create a table similar to above, but with 2 extra columns giving longitudinal and latitudinal GPS co-ordinates
  6. At the bottom of Step 6, you should see your map, and below it a button to ‘Download to Google Earth (KML) file’ – click on this
  7. Save the KML file somewhere (e.g. desktop)
  8. Go to Google Maps
  9. if you don’t have a Google Account you may need to create 1, if you already have one, then go to My Maps
  10. Create a New Map
  11. Choose the mport option at the top left of the screen
  12. Browse to find the KML file that you found earlier
  13. And as if by magic, your map appears.

This I find very useful.