While we can efficiently read and update multiple ranges in Google Sheets with the Google Sheets API Advanced Service in Google Apps Script, there is often occasions where I want to run multiple batch updates on different Google Sheet files.
As you have probably discovered, updating multiple sheets consecutively can be time-consuming and frustrating. I wanted a way to read and update my Google Sheets asynchronously.
Why?
Let’s say I have a bunch of Google Sheets, one each for my users. Perhaps on a daily cadence, I want to gather aggregate data for their manager. Perhaps my manager wants me to add some new spreadsheet features to the current Google Sheets tools. I need an efficient way of updating and reading these sheets.
So was I successful?
Well, mostly.
I discovered that I could run batch updates on up to around 60 Google Sheets somewhat asynchronously.
How did I do this?
I discovered that while “UrlFetchApp.fetchAll()” does not claim to be asynchronous, benchmark research by Kanshi TANAIKE suggests that “fetchAll()” behaves asynchronously.
In conjunction with the Google Sheets API’s “spreadsheets.values”batchGet and batchUpdate requests, we can do a pretty good job updating up to around 60 Google Sheets very quickly.
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.
