G Suite – Paid editions, Gmail, Google Sheets, Google Docs, Google Slides, Google Drive
Recently, I had updated all the course materials in a learning management system (Not Google Classroom. Sorry Google) to only use G Suite files like Docs, Sheets and Slides for students to access.
For me, this was a pretty logical step. It allowed course creators to update their files live when they had to correct errors or make minor changes quarter-to-quarter or year-to-year. Administratively, it meant that files did not have to be accounted for, deleted and updated every time a change was made.
While time-consuming, the changeover went well as we changed all of the student resources into Google files. We set all the files to be accessible to anyone with the link can view (The organization does not have student accounts on the same domain as the staff).
However, in the back of my mind, there was a problem I knew I needed to address. The dreaded request for access to edit. With over 3,500 students on the program and hundreds of files for them to access, it would be a huge pain if some of these students clicked that view and requested edit access.
There is no way to prevent users from not being able to do this within the document’s share options. Likewise, my organisation may want to allow requests inside the domain (for example firstname.lastname@example.org is okay, but email@example.com is not).
Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet
Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile them later.
The following tutorial is a simple tick box-based Google Sheet that the user ticks when they have finished writing their quarterly report. I use something like this in my day to day to keep track of a number of reporting processes.
This is very much a follow-along guide for Google Sheet Beginners to help develop some basics skills and think about how to design Sheets for the workplace. Feel free to read what you need or skip to the end for a link to the google sheet for you to make a copy of.
Here is what we will complete by the end of the tutorial.
If the user’s tick box had not been ticked in Column ‘C’, Column ‘D’ will automatically report Overdue in red and Column ‘E’ will report the number of days overdue.
There is a space set aside for administration to note the reminders that they have sent to the user and the last date the reminder was sent. Admin can easily copy the emails and send a message to those staff who have not completed their report by the deadline.
Let me walk you through the workflow for creating this: