This tutorial is for Google Workspace Domain accounts.
Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets.
This can be incredibly handy for project managers to see when a user completes a task or, at a glance, who edited a row.
Of course, there are some simple ways of doing this out-of-the-box within Google Sheets.
A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
If you want a more detailed history of edits on your Google Sheet workbook then you can always select the version history button in the top right of your Sheet.
The ‘clock’ icon here opens the version history page.
The problem with these options is that it is not there on the screen for the user to quickly see who edited what line.
In this tutorial, we are going to use some Google Apps Script magic to automatically add the editor’s email to the row when they click that checkbox.
Not only are Google Workspaces, Google Sheets, Docs, Forms and Slide great to work in and look awesome, but the convenience of collaborating and sharing your Google Drive Files and Folders is also super useful. So much so that many course creators share their documents with their students all the time.
The problem that course creators have is that they are generally stuck with two options when sharing their Google Drive files and folders:
Set sharing to Anyone with Link can view. And hope other freeloading students don’t find and share their course material.
Manually share each student as they enrol. Which is time-consuming for the course creator and annoying for the student who needs to wait to be shared before they can get their hands on your awesome course content.
Both options are really terrible.
I reluctantly chose option one for my first Google Sheets Essentials Teachable Course and it really bothered me. I needed to find a way to share my Google Drive course content with only those students who signed up for my course.
In this tutorial, I will guide you through creating a Google Apps Script web app that receives a webhook notification when a student enrols onto one of my Teachable courses. If a student enrolled with a non-Gmail or non-Google Workspace domain email account, they will be sent an email with an attached form to add a Google-friendly email.
The fun thing is that you will experience how the whole process works, because…well…that’s how I am going to share the Google Sheets file with you when you enrol. Neat, hey?
As a part of your purchase you will also get a few other perks:
Set files or folders for ‘view’, ‘comment’ or ‘edit’ access.
Add existing students to your selected course Google Drive Files and Folders.
Get your full course list from your Teachable site right in your Sheet.
A choice to bulk set your files and folders to:
prevent downloads, copying and print.
Prevent sharing by any documents you have provided ‘edit’ permission to.
If you want to understand how it all works and build your own, read on, you can always throw a couple of coins at me and enrol to run the workflow just for fun.
If you have made it to this post you are probably a little frustrated about why your Google Apps Script time triggers are not running on or around the times that you have designated. Of course, you could simply just be a well-oiled machine and want to know how to update the time zone of your project for your client.
To cut to the chase, the likely cause of clock triggers not running in the time range that you have selected is that the time zone in your Google Apps Script project is out.
So, how do you check the time zone of your project?
If you need to do it programmatically you can use the Session class and the getScirptTimezone() method. Something like this will log your project’s timezone:
1
console.log(Session.getScriptTimeZone())
Alternatively, you can check out your project manifest file.
To do this in your Google Apps Script IDE, go to your sidebar and select the Project settings cog and then check the Show “appsscript.json” manifest file in editor checkbox under the general settings.
Click to Expand!
Go back to the editor and your “appscript.json” file will be visible. Select it and you will be able to see the current time zone for your project.
Click to Expand!
Changing the timezone
So, at the time of writing this, I found the information on the correct syntax for the time zone in the manifest in the docs either too simplified or when clicking the associated link, too convoluted for my poor goat brain to handle.
In short, you can add certain countries followed by their city. Like this:
“Australia/Sydney”
Or in your code, like this:
1
2
3
4
5
6
7
{
"timeZone":"Australia/Sydney",
"dependencies":{
},
"exceptionLogging":"STACKDRIVER",
"runtimeVersion":"V8"
}
Alternatively, you can use GMT time in this format:
“Etc/GMT-10” << For Sydney, Australia
The -10 can be changed to whatever GMT you need.
Resources
After a bit of testing and some searching, I came across these two resources to help you when entering in the correct time zone.
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
