Organizing presentations, reports and other tasks with Google Sheets and Google Calendar

When I have student presentations in class I usually create a Google sheet in which students sign up for topics and a date. I like to have the date both in a spreadsheet (so that I can copy or import (using the importrange() function) the data into my gradebook and in my Google calendar so that I know who are the presenters for a given class without having to check a separate sheet. This can be done by using a CSV file for export/import.

Of course it would also be possible to share a Google calendar with the students, however using a sheet has a couple of advantages. Cases where you might want to use sheets instead of Calendar are:

• you already have a list of dates which you don’t want to enter manually into a calendar
• you have a large set of data, which are easier to enter in a spreadsheet
• anonymous collaboration (when not all of your students have a Google account)
• when data validation comes in handy (e.g. only certain dates possible)
• you already have a list of dates which you don’t want to enter manually into a calendar
• when you have recurring tasks or events (e.g. students taking turns to do tasks like keeping the classroom clean)

So, here is what I do to collect the dates:

1. Create and share a sheet with the list of topics for my students to sign up for
2. Use data validation to create a dropdown list with my students’ names
3. Use data validation to limit the possible dates and avoid mistakes
4. Create a sheet which matches the data to my class list
5. Copy the data to an export sheet for Google Calendar

Sounds complicated? Most of these steps are actually optional and only meant to make the signup process more user-friendly to my students. All you need is one sheet with specific headings for Google  Calendar. In it’s most basic form these are “Subject” and “Start Date”. Of course you can also use “End Date”, “Location” and some others in case you need them.

As far as the calendar display is concerned, you can pack everything you want to show up in your calendar into the “Subject” column. You can do this by concatenating the relevant information, e.g. = student &”: “&”presentation topic. In my sample sheet I copy the information from a separate sheet: = students!B2& ": "&students!C2

To import the dates into your calendar (or class calendar which you share with your students) you have to save the sheet as a CSV file and import into Google Calendar (go to “other calendars, drop down menu > import calendar, then choose the CSV file and specify which calendar you would like to import the data to in case you have more than one calendar).

Note: This workflow is not very convenient if you keep updating the sheet. In that case a script automating the export/import process would be ideal. Unfortunately I cannot recommend any such script as all the scripts I have found so far didn’t work properly or had some kind of a flaw. I would be greatful if someone could point out a great script in the comments.