Creating customized maps for school trips

When it comes to maps Google services are even more confusing than when you are undecided where to upload photos to (Drive, Picasa or Google+). There is  a wild variety of options:

1. classic My Maps for creating a Google Map
2. more recent Maps Engine (lite) to create maps on separate layers
3. geocoding locations in Fusion Tables
4. geocoding locations in Google Sheets using an add-on
5. recording a trip, route or tour with MyTracks  Android App and saving to Drive
6. placing photos on a map using Picasa (not available in G+ Photos yet)
7. creating a tour on Google Earth

In the past I used options 1 and 5 most frequently for creating maps for school trips and for presenting them with photos on a map on Open Days, etc. Google has made it clear, however, that it is favouring Maps Engine over other services (e.g. by changing the export option in MyTracks to MapsEngine from MyMaps and Fusion Tables) when it comes to custom maps. For this reason I have decided to give it a try in a real life situation. For my upcoming trip to England I needed to create two maps (and share them with my students).

A) Creating a map  of all host families where the students are accommodated using Google Sheets

We will be staying in Brighton for a week and I have created a map of Brighton containing three layers (maximum for the lite version): general information, restaurants and host families.

The information for the “host families” layer comes simply  from a Google sheet which I received from the tour operator and which I imported right after creating the layer. When importing you need to tell Maps Engine which column to geocode. The final step is to change the placemark icon. I have used houses for accommodation and knife and fork for restaurants.

B) Creating a map for a day trip to London including walking route and meeting points

We will go on a day trip to London as well. So I have created a map which shows the route we will be taking using the walking route tool (first click on the draw line tool and then choose the walking route). It works very intuitively: just drag and click to add places.

Then I added some general places of interest which the students will be able to visit on their own during free time on a separate “sights” layer. And finally I colored in the meeting points.

Creating these maps was fun and easy, despite my initial reluctance to use Maps Engine. I would have preferred Google MyMaps instead. Apart from having the disadvantage that you have two different apps both on the web and on mobile, the biggest drawback of Maps Engine is that you can’t save a map on a mobile device like in Google Map, which of course would be highly useful on a trip abroad.

Having said that, there are also a number of nice features to Maps Engine:

• It works like other Drive apps including sharing, publishing and embedding
• Layers are definitely a bonus as they make the map more legible
• Importing data from a Google sheet and automatic geocoding is a huge time saver
• The mobile app allows you to use the phones sat nav system
• You can export in kml format (e.g. to Drive just to keep a backup, or to Google Earth for a presentation)

Automated grading using a grading scale in Google Sheets

Automated grading with key or grading scale in Google Sheets is fairly easy to do. You need to create a table with a key and then specify the range where to “look up” the grades.  

The corresponding function is vlookup

=VLOOKUP(G2;$I$2:$J$7;2;TRUE)

The beauty of this setup is its customizability. If you need to change the key or grade values (e.g. numerical values instead of letters) you can do just just by typing over the values. In case you need more than six values in the scale you also need to adjust the range in the vlookup formula.

Moreover, the setup still works if you want to delete or add columns for more tasks or categories. Here is a template which you can copy into your own Google Drive and modify.

One final tip: you can highlight top grades and fail grades with conditional formatting.

How to create a student report with the new Google Sheets (includes template)

I have been keeping my gradebooks in Google spreadsheets for years now. It’s very convenient to access them from different devices such as my chromebook and my tablet. There are lots of advantages to keeping gradebooks in spreadsheets, the most obvious one being automatic calculation of grades, class average, etc. However, when it comes to looking  up information about a particular student having all the information in one row is rather inconvenient and cumbersome to show to students or parents.

The solution is to create a separate report sheet transposing the relevant information and possibly pulling in information from different sheets (e.g. different terms, additional note). The new Google Sheets has a variety of features that makes this an easy process. Here are the necessary steps

Create a new sheet in your gradebook and transpose the relevant information. You can do this either using copy transpose if you want to choose specific information or the transpose formula if you want to choose all information from the gradebook:

=TRANSPOSE('Term 1'!1:1)

In the B column next to the name field use data validation (menu data/validation) to allow only the students’ names and to create a dropdown list.

The final step is to look up the matching information from other sheets. This can be best achieved with the index and match functions:

=INDEX( 'Term 1'!$A$2:$Z$34,MATCH($B$2,'Term 1'!$B$2:$B$34,0),ROW())

Finally you can pull in information from other sheets simply by repeating the process for other sheets in the file.  

In case that feels a bit overwhelming you can simply use my template

Here is the link to the template. If you want to use it just create a copy in your own Google Drive. Of course you can change the variables and sheet names without losing functionality.

How to automatically send homework and other files from your Gmail inbox to Google Drive

When it comes to homework there are a couple of ways for your students to hand them in using Google tools. The most convenient way is probably when the school uses Google Apps and the students simply share their own folders, or a teacher sets up folders for their students with the gclassfolders script.

However, more often it is the case that not all students in your class use Gmail and Google Drive. In that case you could a web form like Dropzone where the students can upload their files to your Google Drive without signing in or even having to have a Google account.

The most common way of handing in homework is, however, via email. There are a number of drawbacks when it comes to managing homework in your email inbox. It would be much more convenient to have the files in your Drive and, of course, on your PCs hard drive if you use the desktop version of Google Drive. Google doesn’t provide the possibility to save files via Gmail, however there is a great third party script which fulfills this functionality.

Here is how to set it up.

1. Create a homework label in Gmail

If you collect homework via Gmail, you might already be working with labels anyway. If not, here is what you have to do. Create a label (there is a link under Inbox and all your other labels), say. “homework”. Then create a filter with “contains the word “homework”” (I also have a second one with “HW”) and choose apply label “homework”.

2. Set up and run the script

The scrip “Send to Google Drive” is a Google spreadsheet script which is really easy to set up. You create a copy of the script in your own Drive and change the settings according to your own wishes (Gmail label and Google Drive folder in which you want to save the attached files). Finally you have to authorize and run the script (see screencast at the bottom of the post). Here are the author’s instructions:

http://www.labnol.org/internet/send-gmail-to-google-drive/21236/

Apart from collecting homework you can also use the script for mobile uploads from your students (e.g. photos, podcasts or short videos). The script runs every five mintues, so you might have wait a while before you see uploads in your Drive.

If you do audio podcasts with students who use iPhones, it is the only way of collecting the files in your Drive directly. iOS doesn’t allow direct access to your files other than photos and videos. So, the students can’t upload them neither to their own Drives nor via a web form.

The script has another advantage: it also saves homework from all past emails and thus creates an archive of past email. I move the old files into a separate folder (homework archive). Of course, you can also delete them, if you don’t want to keep a copy of your students’ homework.

Google’s Christmas Marathon

Google has updated the majority of its services and mobile apps in the past couple of weeks - it almost seems Google wanted to make Christmas presents to all of its users. Even though not all services (like the music identification feature in Google Search) can be enjoyed everywhere in the world, there are plenty of new nice features for everyone. Here are my favourites:

Mobile apps:

• Calendar app: finally Google has come up with a calendar app people really want to use
• YouTube app: remote control addition for Google TV/PC
• Google Drive: much improved editing of spreadsheets
• Gmail app: swipe and pinch features adding more user friendliness
• Currents: the magazine app is now a real competitor for the like of Flipboard
• Google Earth: new 3d tours
• Google Play Books: read aloud, dictionary, translation and notes (finally a competitor to the likes of the Kindle app and iBooks)
• Google Now: lets you know how much exercise (walking, running, cycling) you have done (though this one probably still needs a lot of improvement it has a potential to decrease obesity)
• Android camera: addition of photospheres (360 panoramic images); here is a Christmassy example
• Google+ app: user interface improvement to an already excellent app; addition of communities and photospheres

The web and the cloud

• YouTube: create slideshows from uploaded/imported (Picasa, Google+) photos
• Chrome: Save to Drive from the web (images, texts)  addon for the Chrome Browser
• Google Drive: improved Google Presentations/Slides (as it is called on my Chromebook) with a very handy presentation mode; integration of third party webapps
• Gmail: attach files from Google Drive (up to 10GB - this one is a real killer feature)
• Google+: communities: even though you can create communities with circles, this one comes more naturally

To heighten the Christmas spirit, Google has furthermore created its own Santa Tracker, both as a web app and as a mobile app:
http://www.google.com/santatracker/
Google Play Link

To mind Google has more than deserved the title of most innovative tech company awarded by The Economist (both in 2011 and in 2012).

I would like to finish my Christmas post with a “Happy Christmas to everyone!”

Creating a collaborative vocabulary trainer

In this episode I show how to create a collaborative vocabulary trainer using a mobile app 

Creating a Google Spreadsheet & Search mashup

Google Spreadsheets has a great function which lets you insert data from Google Search and thus automate searches. The function works like this:

=GoogleLookup(entity, attribute)

Creating a (mobile friendly) multiple choice quiz with images or multimedia

Ingredients:

• Google Forms
• Google Spreadsheets
• Picasa (optional)
• Blogger

Google Forms is a great tool for creating short surveys, polls and of course quizzes. It’s easy to create a math or vocab quiz and view the results in an associated Google spreadsheet. There is even a script that automatically grades the answers.

One thing you cannot do in Google form is to use images or html code to embed images, audio files or videos. There is however, a very simple workaround. You create the quiz without images or other multimedia components copy the html code (in the input view right click and show source code which you can then copy starting with the <title> tag, ending just before the </body> tag).

How to create your own Google Maps data mashup

Ingredients:

• Google Search
• Google Fusion Tables
• Google Spreadsheets

Google Maps are among the most popular services for mashups. It is possible to put almost anything on a map: photos, diagrams, text, data, even video and audio files. There are many ready-made mashups, like city guides and and tracking a emperor penguin named “Happy Feet”. For some more creative examples check out: http://googlemapsmania.blogspot.com/

I have already posted about how to put photos on Google Maps and in this post I would like to show how easy it is to put data on a map using Google Fusion Tables. Fusion Tables is a lesser known Google Service and it is not quite as intuitive as other Google Docs. Still it is easier to create a mashup with Fusion Tables than using Yahoo Pipes or using the programming API.

As an example I will use technical colleges in Austria.

Creating your own mobile database in the cloud

Ingredients:

• Memento (or any other database app that allows you to snyc with Google Spreadsheets)
• Google Spreadsheets

Google Spreadsheets has quite a lot to offer for use as a cloud database: open API, multiple tables, import from web sources, and a query function. This makes it ideal for mobile apps as a database and in fact it is used as database storage quite frequently, e.g. by the My Tracks app. The advantages of using Google Spreadsheets are

• Syncing the data easily across multiple devices: PC, mobile phone, tablet
• Typing in data from your PC rather than on your mobile devices

With Memento for android, a database application similar to Bento for iOS,  you can create your own database and link it to a Google spreadsheet.

Syncing and storing (learning) content on your phone for offline use with Google apps

While google has no note-taking app that could compare with Evernote or Springpad google apps do have quite a lot to offer to sync your notes from the PC to your mobile phone or vice versa. So here are some apps that might come in quite handy for your studies:

1. Save feeds posts and webclips to Google reader

Read your news and blog sources on your mobile phone and mark them with a star for later reading on a PC (e.g. you might want to watch a flash movie which is not available on the phone or the article might be too long to read on the phone). Select a specific part of of an article on the web or on the phone and post it to reader as a note (webclipping).

2. Store links to websites on Google bookmarks

Have your bookmarks available anywhere. Similarly to delicious and diigo you can even create lists and share your bookmarks (social bookmarking)

Creating and sharing flashcards collaboratively with classmates using google spreadsheets

You would like to use your mobile phone as flashcard trainer for an upcoming vocab test and you would like to split up the work of typing in the words with your friends. Almost all flashcard/vocab trainers use .csv format (coma separated values) for import. You could use MS Excel and export the vocab as .csv and copy the file onto your phone. That would leave the problem of collecting the files of your friends and turning them into one .csv file. A much more elegant and easier solution is using google docs.

Vocabulary Trainer for GDocs - Android

Vocabulary Trainer for GDocs - Android

A flashcard trainer which uses a Google Spreadsheet as a source for your learning data. 
Easly upload your vocabulary-lists to Google Docs (or create them with it) and than import your spreadsheets with this app to your phone as a flash-card trainer.

Features: update from GDocs, Text-to-Speak, Powerlearning, Image trainers, Auto-Modus, and a lot more. Download from: