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).

goFormative.com has been my favourite tool to use in the classroom this year. It enables me to give every student a voice, see misconceptions and thought processes in real time, and feedback and guide students the moment they need it.

Anyone can set up a free account and there is account integration with Google which is great. You can also easily add assignments to Google Classroom for quick student access. There is such a variety of question types, and there is an option to upload and transform pdf documents to help you go paperless!

I recently gave a workshop at the AppsEvents Sweden Summit to a group of teachers, you can get those slides here.

This video shows how to use the tool in under 5 minutes:


If you wanted more information you can find it here:

• Tutorial videos
• User guide
• Assignment sharing! (use quizzes designed by others)

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'.

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)