Google Sheets are incredibly powerful for collaboration and it's great that they can be worked on simultaneously. Sometimes though, you want to add a bit more protection to stop people editing things that they shouldn't.

You can protect cells and ranges from being edited quite easily in Google Sheets, but up until now, that had to be done manually. This was particularly complicated if you had a sign up sheet for example and you didn't want people overwriting or removing people accidentally (or maliciously!).

Once you activate the editLock add-on, as soon as someone edits a cell, it gets locked down and can't be edited by anyone else. See the 2 minute video below for a quick walkthrough.

I hope you find it useful! Get the add-on here.

We are delighted to announce the beta launch of a new free tool called calendarMap.

This web app allows you to select any of your Google Calendars and place all events that have a location on a Google Map. You can even set custom date boundaries!

The idea came from a school leader who runs an outreach program where his staff visit other schools and he wanted to a better visual representation of where they were. As you use the tool, we'd love to hear your use cases - please let us know in the comments!

If you see anything that doesn't look quite right, please let us know - feedback is really helpful!

Click here to access the web app (NOTE: one time authorisation required for the app to read your calendar information).

Google Trends is a great free tool that allows you to analyse search trends over time. It can help you spot seasonal patterns (e.g. 'Christmas'), but also compare results.

Here is a comparison for Facebook vs. YouTube:


You might want to get students to hypothesise what a graph might look like then test it out. It allows you to see searches from specific countries which might help get a grasp of world news or current events. For example, the trend of searches for 'ISIS' shows the speed in which the organisation has grasped media and public attention:


Even if you don't find any direct pedagogical applications for your subject, it's still pretty interesting!

Click here to go to Google Trends.

Permanent Clipboard is a great free extension for Google Chrome that allows you to save a bank of comments to quickly use anywhere on the web (i.e. Google Docs).

Once you've added the extension it's really easy to create your own entries (animation below). You can then right click anywhere you can type and choose to 'Insert from permanent clipboard' and your comment text will automatically appear!









As you can see, this is a real time saver particularly when you find yourself saying very similar things to students when you give feedback!

TOP TIP: Add links to YouTube videos to your comments to make them even more effective!

Click here to install Permanent Clipboard on Google Chrome.

We've developed another Google Sheets Add-on that allows you to get detailed information about Google Docs all on one handy sheet.

• singe files,
• Drive folders,
• Google Classroom assignments.

Get Essay Metrics for free here!

You can get the app from here: tinyurl.com/rowlink (you can bookmark it for later).

NOTE: You'll need to set the spreadsheet sharing settings to 'Anyone with the link can view'.

A recent staff room conversation had us questioning why it seemed that our persistent late students seemed to live so close to school. The area around our school is quite diverse and we thought it would be interesting to try and identify student trends based on where they live. It turns out that this is surprisingly easy to do, and within 5 minutes we had mapped 1000 students and categorised by a variety of data from conduct points to lateness. I'll explain the 3 steps it took to achieve this:



1) Export student data from your MIS

As a Maths teacher, I sometimes don't get to fully use the incredible features of Google Docs to their full potential. That's why I love helping out teachers from other subjects to improve their workflows and support student progress.

If you regularly get students to complete writing assignments, and use marking rubrics to help grade and assess work, this is the solution for you!

Here's a simple breakdown of what happens:

1. Set an assignment in Google Classroom
2. Use Doctopus to get all the assignments onto one spreadsheet for easy grading
3. Use Goobric to fill out marking rubrics from the same window
4. Marking grids stamped on student doc and values logged on spreadsheet

I was just asked in a recent scripting session that I delivered, how to get all of the events from a calendar. It can be a little tricky with Javascript dates, so I've quickly thrown together a spreadsheet template that does that.

All you need to do is get the Calendar ID which you can do by going into the calendar settings and copying the link (see image below). Choose two dates and press the button to run! All events between those two dates will appear on the 'Output' sheet.



To get the sheet, you will need to click 'File' and 'Make a copy' of this doc.

It will then be stored in your drive.

Very useful for auditing purposes!

Let me know your thoughts and feedback!

Intro
The addition of Add-ons in Google Forms this week reminded me of how powerful Forms can be for signing up to limited capacity activities. There are some powerful tools released to limit form responses, but to make the most of these you need to be fairly proficient in Sheets and formulae.

Problem
Every 6 weeks at our school we have around 1000 students choose from a list of around 50 'enrichment' activities. Our amazing librarian sends round paper copies to each form group asking for students top 4 choices, has to do a lot of data crunching, then has to deal with the fallout of a lot of students not getting what they want and asking to change.

Solution
I've designed a Sheet/Form combo that allows her to do the following:

1) Add a list of activities to a sheet and have them populate as choices on a form

2) Set the capacity for each activity - when it fills, the item is removed from the form preventing future submissions to choose it (see pic below)

3) A list of students is automatically populated for each activity (a roster/register)

4) A sheet allowing her to easily see which students have and haven't made selections

Sharing
If you'd like to use the sheet yourself - you can make a copy here.

There are a few simple instructions on the sheet which you can translate into any language you would like!

I'd love to hear if you use it how you get on and what kind of things you've used it for.

Some possible thoughts:
- Parent/Teacher workshops (set activities as a list of times and set capacity to 1)
- Extra-curricular activities
- Lunch choices
- Art project supplies
- IT room hire
- and many more...

A really nice way for students to show learning, collate a project, or demonstrate progress is through a Google Site. Creating site templates is a really nice way to give students a pro-forma of what you want included. However, in practice it can be quite a logistical task getting hundreds of students to find the template site that you have created, make a copy of it, and share it with you.

I've created a Google Site generator that solves this issue. By filling in a few details and pressing the button on the template, you will automatically create a new site for every student on the spreadsheet. What's best is that each site will now appear in the student's 'Sites' page. This is a much easier way of getting students up and running and they can get straight to the editing part! You can then access all students sites directly from the spreadsheet.

You can make a copy of the template here.

All you have to do is add in your names and emails in the appropriate column, edit the information in the yellow cells, then press the button! You'll have to authorise the script first.

Enjoy!

(this will only work if you are using a Google Apps for Education account)

I've recently been helping out Kimberly Pollishuke with a great idea to send parents emails if a student is missing a piece of homework. She wanted to do it using Google Forms and use the autocrat script to send an email out to parents immediately.

Obviously, she didn't want to inputting a parent email on every form submission, so we've used VLOOKUP and ARRAYFORMULA to let Google Sheets do all the hard work for us!

Here is a video tutorial on how to set this up if you are using the new version of Google Sheets. (I believe that Kim is setting up a screencast for the old version shortly)

The two formulae that you need can be found below.



In cell A1 of 'Array Values' tab: =ARRAYFORMULA('Form Responses 1'!A:E)

In cell F2 of 'Array Values' tab: =if(isblank(C2),"", VLOOKUP(C2,'Parent Emails'!$A$1:$B$55,2,FALSE))
Don't forget to drag this one down!

The new version of autocrat doesn't have an automatic trigger on form submit yet, so you will have to run the merge manually (although I imagine that this feature will soon be available!)

You can have a look at the spreadsheet I used in the video here.

You can find the Merge Template Doc here.


OPTIONAL FOR ADVANCED USERS:
I designed a similar script quickly that will do a similar job, which you can then trigger the onformsubmit option. To do this, follow the instructions as before, but do not install autocrat.

Instead go to Tools > Script Editor and paste the code below:

function myFunction() {
var check = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mail Merge');
Logger.log(check.getLastRow());
for (var i=2; check.getRange(i, 1).getValue() != "";)
if (check.getRange(i, 7).getValue() == "Sent")
{
i++
}
else{
var teacher = check.getRange(i, 2).getValue();
var student = check.getRange(i, 3).getValue();
var subject = check.getRange(i, 4).getValue();
var reason = check.getRange(i, 5).getValue();
var parentemail = check.getRange(i, 6).getValue();
var blob = Utilities.newBlob('Dear Parent/Carer,<br><br>I am writing to let you know that '+student+' failed to hand in '
+subject+' homework. The reason given was: '+reason+'<br><br>Kind regards,<br><br>'+teacher , 'text/html', 'Missing_Homework.html');
MailApp.sendEmail(parentemail, student+" Missing Homework", 'Please see the attached missing homework document.',
{attachments: [blob.getAs(MimeType.PDF)]});
check.getRange(i, 7).setValue("Sent");
}
}

You can then click Resources > Current project's triggers. You can then set up the function to run whenever you submit a form, so no need to manually run the merge. You need to select the following:

Run 'myFunction' 'From spreadsheet' 'On form submit'

That's it! Test it out. If you feel up to it you could play around with the wording in the code to personalise the email to your needs.