How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course

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:

  1. Set sharing to Anyone with Link can view. And hope other freeloading students don’t find and share their course material.
  2. 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.

If you want a copy of the Google Sheet with the Apps Script attached, without coding it all yourself, plus written-visual tutorials on how to quickly set up your sheet head over to my teachable page now and purchase the sheet and instructions for just $2.95. Yeap not even the price of a cuppa.

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.

Instantly share ONLY Teach:able Students to selected Google Drive Files and Folders

 

If you are looking to build your own Teachable Course you can check out a how-to guide here:

How to launch an online course—and craft your email strategy

How it all works

We will be making use of Teachable’s handy webhooks to receive instances when a student enrols on one of our chosen courses.

We will need to set up a Google Sheet that contains two Sheet Tabs. One for our list of shared files and folders by course and one with our list of registered students.

We will also need to create a way to send a message to any student who has not registered to our Teachable school with a Gmail or Google Workspace domain email and ask them to provide a Google-friendly email. Once they reply we need to automatically share them with their new email.

Let’s Get started.

Creating your Google Sheet Manager

First, we need to create a Google sheet manager to make it easy for us to:

  • Add files and folders that we want to share with our selected courses.
  • Keep a record of our shared students by course.
  • Receive and manage form responses from students who need to provide a Google-friendly email.

Go ahead and create a new Google Sheet.

Setting up your Google Sheet

Name your Google Sheet workbook whatever you want. I’ve named mine:

Share Teachable Students to Google Drive Elements

Store File and  Folder Locations

Rename ‘Sheet1’ to ‘Shared Locations’.

In row 1 enter the following Headers:

  • Course Name
  • Course ID
  • File/Folder Name File
  • Folder URL

Google Sheets teachable connector tutorial Share Locations setup

Get the course ID for your Teachable Course

First, head over to your Teachable school. Log in and then select a course.

Navigate to your School Course page and select a course.

Google Sheets teachable connector Shared Locations select course

When the course loads, take a look at the URL, you will see the numerical code.

Google Sheets teachable connector Shared Locations manual selection url

Select and copy it.

Head back to your Google Sheet Manager and select the ‘Shared Locations’ Google Sheets tab.

Now paste it into Column B and then update the title in Column A.

Next, add a name for your File or Folder in Column C. You add what you want here or leave blank if you are feeling lazy. Sometimes it is better to rename the file or folder so that it makes better sense in the context of this list. If you do this, your original file or folder will not change.

Finally, copy and paste in your URL for your file and folder in Column D. Here, you can either navigate to your Google Drive file or folder and copy and paste in the URL. Alternatively, right-click on the file > select Get link > Copy > Done. Then paste in your URL.

Rinse and repeat.

Your ‘Shared Locations’ Google Sheets tab should look like this:

Google Sheets teachable connector tutorial Share Locations complete sheetThe most important stuff here is Column B and Column D. We will use this in our Google Apps Script.

Create a named range

Next, selected the range A2:B50 > right-click > View more cell actions > Define named range. Then name the range ‘ShareData’.

Google Sheets teachable connector tutorial Share Locations named range
Click to Expand!

We will also use this in our code later.

Store Enrolled students details and Tokens

Create a new Google Sheets tab and call it, ‘Students’.

Next, in cell D1 add the header: For non-Google Accounts.

On row two add from Column A2:

  • Timestamp
  • Email
  • Course
  • Non-Google Email
  • Access Token
  • Token Submit Date
Google Sheets teachable connector tutorial Share Locations Students Sheet tab
Click to expand!

You can see in the image above how a student’s details is added. This is all done via our Google Apps Script.

In the example above we have a student who did not register with a Google-friendly email so we send an email with our form and our Access Token for them to reply with a Google-friendly email. This is all updated automatically.

 

Now it’s time to crack on with the fun stuff, the code. Go to Extensions > Apps Script. You will load your Google Sheet connected Apps Script Editor.

Rename the project. Generally, I rename it to the same name as the Google Sheet.

Creating the Google Apps Script WebApp

Our first task is to receive a notification from our Teachable page via a webhook that a new student has enrolled in our course. We can do this with a Google Apps Script custom doPost() trigger function and building a webapp.

The doPost(e) function allows us to receive information from an external app like our Teachable webhook.

Here is the basic code that will allow us to make sure everything is running successfully:

When we receive our webhook information it will be packaged inside our 'e' or event parameter as a JSON object in the e.postData.contents property. Line 3

Finally, we need to build our frontend environment with the HtmlService class’s createHtmlOutput() method. We will leave its parameter blank here. Line 5

The first deployment of the webapp

We need to deploy our webapp now to get its URL.

In the top right select the Deploy button.

authorise scopes for Google Sheets teachable connector webapp deploy button

A dropdown will appear. Select New deployment.

authorise scopes for Google Sheets teachable connector webapp new deployment

A pop-up window will appear. You shouldn’t have to make any changes here. The Description is optional, I usually put in ‘First Release’ for the first deployment.

‘Execute As’ should be set to “Me(youremail@gmail.com)”.

‘Who has access’ needs to be “Anyone”, because your Teachable account needs to access your webapp.

Select Deploy.

authorise scopes for Google Sheets teachable connector webapp new deployment screenAnother pop-up screen will appear with your webapp URL. Copy the URL and select Done.

authorise scopes for Google Sheets teachable connector webapp new deployment screen url copy

Setting up the Teachable webhook

Head over to your Teachable account and log into your school.

in the sidebar of your Dashboard, select Settings.

Google Sheets teachable connector webhook settings

Then select Webhooks.

Google Sheets teachable connector webhook webhooks

Select New Webhook

Google Sheets teachable connector webhook new webhook

Paste in the Google Apps Script webapp that you created.

Then select the New Enrollment.

Finally, click Create Webhook.

Google Sheets teachable connector webhook selections

That’s it. You’re all done.

Your webhook will remain in a pending state until a user enrols into your course.

Google Sheets teachable connector webhook pending

Testing the webhook

Either create a new student or use an existing ‘test’ student account. Make sure that they are unenrolled from one of your courses.

Go to your Teachable school and select Users > Students.

  1. Search for your test student.
  2. Check the box.
  3. Select Bulk Actions.
  4. Select Enroll in Course.

Google Sheets teachable connector testing webhook add user dialogue already have

Wait a little bit, and then head back to your Google Apps Script editor and navigate to the Executions tab.

You should see that the doPost() function ran and the status was completed.

Google Sheets teachable connector tutorial doPost test

Note! If your dummy student account is already enrolled in your course you will not receive a webhook. Teachable will not send a webhook action for something that has already occurred. Simply unenroll your dummy student and enrol them again.

What does enrollment JSON look like?

When your test doPost() function ran it would have collected the following information:

Let’s go ahead and build the propper doGet() function.

Completing the doGet() function

Once we have collected our dataContents we want to first make sure that the type property exists. If it doesn’t, it is unlikely that it is not a webhook and not one sent from Teachable. I’ve used a JavaScript Ternary operator to determine if this property exists or not. We’ll store this in our webhookType variable. Line 13

Next, we check if the type name is "Enrollment.created". If it is, then we will run our main function addUserToDriveElement() taking the dataContents object as an argument. Lines 16-18

Adding users to the Drive files and folders

We will now set up our main run function to complete our task we will call it addUserToDriveElement(). It takes our dataContents as a parameter here.

Here’s the code:

Set the variables

Our first task is to extract the student’s email, their name, course ID and course name from the dataContent.object object. You can find the path to these properties in the JSON example I shared earlier. Lines 10-13

Get the Shared Locations data

Next, we grab the current spreadsheet with the SpreadsheetApp Google Apps Script class and the getActiveSpreadsheet() method.

Remember earlier when we created our Shared Locations sheet tab that we also created a named range called ‘ShareData’. We can conveniently use the getRangeByName() method to get this range. This method takes the named range as an argument and returns the range constructor. From there we can call the getValues() method to grab all the values in each cell. Lines 16-17

Using reduce to extract course URLs

From the values that are retrieved from our ‘ShareData’ named range, we need to extract all the Google Drive files and folder URLs related to the current course that the new student has enrolled in.

Here we use the JavaScript ‘reduce’ method. This method allows us to iterate through our 2d array of ShareData and store only the URLs for our course.

For us, this function will take an accumulator parameter to store our results. We will also use the current value argument here to look at each row of data on each iteration. We will also need to set our initial value to an array so we can add our URL as an array. Lines 19-24

Giving permission

Next, we will attempt to give permissions to our selected files and folders to our newly enrolled students.

Not every student will have enrolled with a Google-friendly email so we need to send those students an email with an attached form. They fill out the form with a Google-friendly email and send it back to us to automatically share them with the course docs.

Now we don’t want just anyone to be able to fill out the form or a ‘charitable student to fill the form out multiple times for their mates. So we need to set a unique token for them.

First, we set the token variable to null, for students who have a Google-friendly email. Line 27

Next, we will attempt to share the student with our course files and folders using our givePermission() function which takes the list of courseUrls and the student’s email. If the function cannot share the student it will return true. We will store this the nonGoogleEmail variable. Line 28

Now we check if nonGoogleEmail was set to true. If it was, then we need to set a form trigger on our Google Sheet and connected Google Form ( We will create this soon).

Then we will email the student to ask them to provide a Google-friendly email with our emailStudent() function, which will return a unique token for the student. This function takes the students name, email and course name. These will be added to the student’s email. Lines 30 – 34

Finally, we need to add our new student to our Google Sheet  addStudentToSheet(email, courseID, token). This will add the student to the ‘Students’ sheet tab along with a date-time stamp.

Now that we have the main function stored out, let’s look at the connected functions.

givePermission(urls, email)

This function is called from addUserToDriveElement(dataContents) and has two parameters:

  1. URLs – an array of Google Drive files and folder URLs connected to the course.
  2. Email – a string containing the student’s email.

The function will return true if the email is a non-Gmail or non-Google Workspace domain email.

Setup for non-Google Users

First, we set a nonGoogleUser variable to false. This will only change if the email could not be shared on the files and folders.

Google Drive API permissions payload

Next, set up the permission payload. This will be sent to the files and folders to give permissions.

We are using the Google Drive advance API here rather than the Google Apps Script DriveApp. Why? Because it allows us to share users without sending them an email confirmation to inform them that they are shared. We want the sharing to be seamless and under the hood. So this is the better approach.

First, we need to connect to the Google Drive advanced service. On the right side of your Google Apps Script editor IDE select Services.

A popup dialogue will appear. Scroll through until you find the Drive API. Select it and then click Add.

Google Sheets teachable connector DriveAPI service connect

Now let’s add our permissions:

Yes, weirdly ‘value’ will equal the email. The role of ‘reader’ equates to ‘view’ access here too. Lines 12 – 16

Iterate through the URLs

We now need to iterate through each of our Google Drive files and folders URLs. We will use the JavaScript forEach method to run our execution inside an arrow function.

Converting the URLs to an ID

Unfortunately, the Drive API does not take a URL as an argument so we will have to extract the file and folder IDs from our URLs. We do this on lines 20 – 25.

First, we set an id variable to an empty string.

Next, we grab the URL of the file or folder stored in our Google Drive.

We will use the JavaScript split() method along with some regular expressions. This methods allows us to separate our string into parts based on a specified divider. For us, our divider with be a set of regular expressions.

In the first split, we want to separate the URL string by either /d/ or /folders/ these are the two primary types of URLs you will find in your Google Drive. For example:

  • https://docs.google.com/spreadsheets/d/1_wO551p6YASDFEeqziXckFBLHqFpW7rHkjF_uJ-UQBQ/edit#gid=0
  • https://drive.google.com/drive/folders/1CUIsbfcVEE-fjOwYqhXITBsdf3AD3js4

In our regular expression, our backslashes need to be escaped with a forward slash. So to get:

  • /d/, we use, \/d\/
  • /folders/, we use, \/folders\/

We can have our regular expression check for either condition use the pipe ‘or’ divider |. Which will give us this regular expression:

/\/d\/|\/folders\//

This will split our string into to an array like this:

We only want the array item at [1] here. So we extract that before moving on to our next split.

.split(/\/d\/|\/folders\//)[1]

Now we want to remove any trailing data after the backslash. We do this by searching for the next backslash and splitting it and then grabbing the array item at [0].

.split(/\//, 1)[0]

This will give us our id.

Attempting to give permission to the user

We need to send our request to share the user to the Google Drive API inside a JavaScript try-catch statement. If a non-google email is shared, it will result in an error and we don’t want our script to stop.

First, we call the Google Drive API Permissions class and then use the insert method to share our students with their permissions. The insert method is going to take 3 arguments:

  1. permission – the permission payload we created above our loop.
  2. id – the ID of the file or folder.
  3. optional query parameter – This we want to NOT sent a notification email.

Resolving non-google friendly emails

If the email is not a Gmail or Google Workspace Domain email then the Drive app request will throw an error. This will be handled by the catch statement.

The catch statement will return an 'e' error event object. This will contain the ‘message’ property. If the nature of the error is that the email could not be added because it was not a Google-friendly email, the student’s email will be reported in the error message. Then we can return our catch statement with nonGoogleUser set to trueLines 38-41

We also want to make sure that any other errors that occurs are actually caught and stored so we will throw a new error with our 'e' error message.

 

Finally, we return our nonGoogleUser true or false result back to the main addUserToDriveElement().

 

Add Student To Sheet

The final task of the main function is to add the student to the ‘Students’ sheet tab.

addStudentToSheet(email, courseID, token) takes 3 parameters:

  1. email: the email of the student. Either Google-friendly or otherwise.
  2. courseID: the ID of the course.
  3. tokennull if the student has a Google-friendly email, otherwise this will be a string containing the token that the student will be sent.

First, we need to select the ‘Student’ Google Sheet tab. Line 9

Next, we will grab your locations date time stamp using the JavaScript new Date constructorLine 11

If we have a token, then we want to append our row with:

  • date timestamp
  • nothing here
  • course ID
  • non-Google friendly email
  • student token

If the student doesn’t have a token then we append the row with :

  • date time stamp
  • email
  • course ID

That’s it for sharing users to selected files and folders. Next, we will look at what to do with students with non-Google emails.

Dealing with non-Google emails

Once we have discovered that the student has a non-Google friendly email we need to send the student an email with a Google Form attached.

Before we get stuck into our code, we need to build our Form.

Connect the form to your Google Sheet Manager

In your Google Sheet Manager go to Tools > Create a new form.

Google Sheets teachable connector tutorial form new

A new window will appear with a Google Form editor. Before we work on our form, head back to your Google Sheets Manager and you will notice a 'Form Response1' sheet tab has been added to your workbook. Rename it to exactly: Access Request.

Google Sheets teachable connector tutorial form Access Request We will use this Form Sheet tab name in our code so it needs to be exact.

Creating your Google Form

Create the form

Your form should look like this:

Google Sheets teachable connector tutorial form creationSet your form title to:

Students Access For Google Drive Course Data

Next, add a short answer field and name it exactly, “Email”. We will be referencing this name in our code in a minute.

Make sure this item is set as required. 

Also select the vertical ellipsis at the bottom right and select response validation.

Set the short answer response validation to Text > Email.

Google Sheets teachable connector tutorial form creation validation

Next, go back to the three vertical ellipses in the bottom right and select Description. Enter the following description:

Please provide a Gmail or Google Workspace email to access the drive documents for the course.

Now, add another short answer item and call it exactly, Access Token. We will be using this title for the item again in our code so make it exact.

Set the item to Required and the description to:

Your exclusive access token.

Get the form Prefill URL

We want to be able to pre-fill in the student’s access token for them in the Google Form. To do this, select the vertical ellipsis in the top right of your form editor and then select Get pre-filled link.

Google Sheets teachable connector tutorial form prefill 1
Next, add something like 11111 to your Access Token item and select, Get link. A little popup will appear, select the Copy link button here.
Google Sheets teachable connector tutorial form prefill 2

Open up a text editor or something and paste in the URL. You should have something similar to this:

https://docs.google.com/forms/d/e/1FAIpQLSeOY584mfR_WOGt-Nsov2ycTypzrSXeFrlqOzocKK9Vd_HQ4Q/viewform?usp=pp_url&entry.1257072962=11111

Of course, your form ID and entry id will differ from the example above.  We will use this URL in a moment.

emailStudent()

When we discover a non-Google URL the main addUserToDriveElement() function call the emailStudent() function. This function takes 3 parameters all as strings:

  • email
  • studentName
  • courseName

Our first task is to get the form with our getFormURL() function which takes the form Sheet tab name “Access Request” as our argument. (More on this function in a moment) Line 15

Create a random-ish token.

Next, we create the unique student token that we will add to our Google Form.

Here I am lazily combining two random alphanumeric strings together to create a random number. Line 16

Setting up the email data

First, we set up our email subject. Here we use JavaScript template literals to create our string and include the course name at the end of the subject. Line 18

Next, we create the HTML for our email message to our students. First, we will add in the student’s name and course programmatically. Line 20

The most important thing to note here is the URL link ot the form. We first reference the form URL from our form variable we generated above. Next, we need to go to where we stored our prefill URL and copy this portion of it:

https://docs.google.com/forms/d/e/1FAIpQLSeOY584mfR_WOGt-Nsov2ycTypzrSXeFrlqOzocKK9Vd_HQ4Q/viewform?usp=pp_url&entry.1257072962=11111

?usp=pp_url&entry.1257072962=

Note that your entry number will change. So make sure you use your own number here.

Also, if you do wish to make a copy of this for another project, then the entry number won’t change thankfully.

Emailing the Student

Finally, we need to email the student. We will use a try-catch statement again just in case there is a weird email, but it should be okay if Teachable accepted it.

To send our email we make use of the sendEmail() method of the Google Apps Script GmailApp class.

The sendEmail() method takes the following parameters:

  • email
  • subject
  • body
  • options

We don’t put anything in the body because we want to use the htmlBody property of the options parameter.

Also, in our optional parameters, we can set noReply to true so that the user doesn’t try to reply to the email and also we can set the senders name to ‘Auto Reply’ to make it all cool and official.

If there is an error with sending the email we throw a custom error in the catch block. So we  do this at the end.

 

Finally, we return the token back to addUserToDriveElement().

getFormURL(sheetName)

This function is called from the emailStudent() function. It takes the sheet name of the Google Form sheet tab in the Google Sheets Manager. This should be, ‘Access Request’.

The published form URL is returned.

The reason we use this approach ass opposed to hard coding in the Form URL is that we might want to make a copy of our Google Sheets Manager and attached form and that means the Google Form URL will change.

First, we grab our active Spreadsheet and then get the form sheet by name and its associated form URL. Lines 8-9

We use this form URL in the Google Apps Script FormApp class with the  openByUrl() method. Next, we grab the published URL with the getPublishedUrl() method.

This is all returned back to the emailStudent() function.

setFormTrigger()

Back in the main addUserToDriveElement() function we call the setFormTrigger() if a user does not have a Google-friendly account.

We only want to set this trigger once and only if we have a user in our school who has enrolled with a non-Google friendly email.

We will store a property called, 'hasTrigger' in our Google Apps Script Properties Service. Our first task is to check if there is a property by this name and if it has been set to true. If there is no such property, it will be set to null. Lines 8-9

If there is no such property, then we want to create a trigger. Line 12

To do this we use the Google Apps Script ScriptApp class’ trigger builder. First, we set the newTrigger to call the function 'tokenSubmit'. Then we will specify that it is for the current active spreadsheet and set the rule that the trigger should occur when a form response is submitted to the Google Sheet. Lines 14-17

Then we will set our Properties Service  'hasTrigger' property to true for next time.

tokenSubmit(e)

When a student resubmits their Google-friendly email we need to first verify that there is a matching token in our ‘Students’ sheet. If there is, we need to share them on their course files and folders and add them to our ‘Students’ list.

The tokenSubmit(e) function is triggered when a student submits their form with their Google-friendly email. The onFormSubmit trigger carries an Object with the student’s Google Form input information as a parameter.

I’ve left an example in the code for you to see how it will look.

First, we grab an object of names values that should look like this (Line 17):

Next, we need to grab the range from Col A through to F of our Students sheet tab and grab the values as a 2d arrayLines 20-25

We will set a row number to the first email row under our header. We will use this to find the actual row in the Sheet where our corresponding user will be. Line 27

Find the corresponding row to the token

Next, we will use the JavaScript Find method to determine where the token is in the array if it exists.

Javascript Find MethodThis function will iterate through our 2d array trying to find the row that contains our matching access token and ensure it already has not been filled in.

Find takes a row parameter and can take an optional indexing parameter.

If row 4 (Column E) matches our Access Token from our form and row 5 (Column F – Token Submit Data) is empty then we will update the rowNum and add the new email to Column B and the current Timestamp to Column F.

Sharing the student with the course

If a matchRow exists we want to share the student with the course and record their new email.

We used the same JavaScript reduce method we used in our main run function (Yeah yeah, refactor it, Yagi!).

Then we send the results to the givePermission() function.

Finally, we get the range of the current student in our ‘Students’ sheet tab and update the row. Line 54

Redeploying  your Webapp

You now need to go back and redeploy your script for it to work with your Teachable webhook.

Before you do give your script a run. Maybe try and run the doGet() function. It will come up with an error, but it will also go through authorising all of your scopes.

Next, click the Deploy button on the top right of your Google Apps Script editor. Select, Manager Deployments.

Google Sheets teachable connector final deployment

Select the pencil icon to edit the deployment.

Then change the version to New version. 

Finally, select Deploy.

You are all done!

Conclusion

That’s it you made it through. How did you go? Any errors you had to work through?

If you are looking for a full copy of the code and the connected Sheet and Slide, to simply copy and paste in and run, plus a heap of extra bonus content you can sign up to the corresponding teachable course for a measly $2.95. Yep, less than the price of a cuppa and you would really help me out to keep this site running and making great content for you.

Instantly share ONLY Teach:able Students to selected Google Drive Files and Folders

OR…

If you have found the tutorial helpful, why not shout me a coffee? I'd really appreciate it.

I hope you found this tutorial helpful and you have been able to implement it in your own Teachable site. I would love to hear what site you used it on in the comments. Plus it is a bit of promotion for your own course!

~Yagi

Create custom prefilled Google Forms links in custom emails with Google Apps Script

Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form.

Great, that’s cool. But that wasn’t what got me excited. They had exposed the raw URL link to the form in the email and I noticed that there were some references to my name, my support number and a few other things in the URL query parameters.

I clicked the link to the Google Form and, as expected, the Google Form appeared with these values prefilled into my form.

We this is a pretty cool convenience, I thought. How did they get all the query paths to each form item?

A couple of days passed and I had a chance to figure it all out.

In this tutorial, I’ll walk you through accessing the prefill tool in Google Forms. Then, if you are keen on doing some coding, we’ll create a little custom feedback form for unique users that we will deliver via email.

Let’s play!

Google Forms prefill tool

Accessing the Google Forms prefill tool

First, take a look at my example Google Form:

Go ahead and type forms.new in your Chrome browser address bar and create a few form items so you can play along.

Once you are done, got to the top right next to your avatar and you will see a vertical ellipsis. Give it a good old click.

A popup window will appear. Four items down and you will see the menu item, Get a pre-filled link. Go on, you know you want to click it. I won’t judge.

Google Forms menu buttons ot Get pre-filled link
Click to Expand!

A new window will appear in your browser with a sample of your form. Go ahead and fill out any part of the form that you want to have prefilled.

We’ll fill out the first three items in our form. Here, take a look:

Google Forms prefill screen
Click to Expand!

As you can see above I have added my name (Yagi the Goat), a ticket number (6047) and issue (Login – Passwords).

You might have noticed down the bottom left of the screen a grey box with the prompt, Prefill responses, and then ‘Get link’.

Go ahead and scroll down to the bottom of your form and click the Get link button (1).

Google Forms prefill get link & copy link
Click to Expand!

Then click the COPY LINK button in the grey bar (2).

Paste your link in a new browser tab and hit enter to check that the pre-fill is what you wanted.

If you are happy with the prefill results, then paste the pre-fill link somewhere safe for you to use later.

You should end up with a URL a little like this:

https://docs.google.com/forms/d/e/1FAIpQLSd4QDc4MRkoERExe9KeMLww9P7VNRHFOfpBLwX_Mo-g5TJ0Vw/viewform?usp=pp_url&entry.1046214884=Yagi+the+Goat&entry.2009896212=6047&entry.415477766=Login+-+Passwords

You should be able to see some of the pre-fill items in your URL that you added earlier. We’ll go onto this later if you are following along to the Google Apps Script portion of this tutorial.

 

Why would you use a pre-fill in a Google Form?

At first, I was a little lost at the usefulness of using a standard static pre-fill for your Google Form. Surely not all people on your form will need to choose the same thing. I mean, you may as well leave it out of the form, right.

However, after a bit of noggin scratching, I thought that maybe you could use a static prefill like this for a standard response to help most users skip filling in unnecessary parts of the form while still making it flexible enough for the user to change the form if they need to.

When it does become an awesome tool is when you can use the URL generated and update fields to customise it for each user.

In the next part of this tutorial, we will do just that with the help of some Google Apps Script and then add our form to a custom email.

 

Create a custom prefilled form link and email it

In this portion of the tutorial, we are going to create a custom pre-filled form link by altering our copied pre-filled form link and then send a custom email to a user with their name and their own unique Google Form link.

The example

Let’s assume we have our very own tech support team. After we complete each ticket, our team are eager (yeah right!) to find out how well they performed in their support of the client.

The team stores each completed ticket details in a Google Sheet like below:

Support ticket Google Sheets for Google Forms prefill V2
Click to Expand!

Looking at the image of the Google Sheet above, we only want to send an email to those clients whose checkbox in column I is unchecked – indicating that they haven’t received and email yet.

We then want to send an email to our users with a message and a link to our unique pre-filled Google form.

For example, our last user, Andrew Bynum, would get an email like this:

Custom feedback email with link to prefilled Google Form
Click to Expand!

Then when Andrew clicked on the form link he would be navigated to his own pre-filled Google Form with the first 3 items filled in like below :

Custom Google Form pre-fill for specific user
Click to Expand!

The anatomy of the pre-fill URL bar

That was generated with this bespoke URL:

https://docs.google.com/forms/d/e/1FAIpQLSd4QDc4MRkoERExe9KeMLww9P7VNRHFOfpBLwX_Mo-g5TJ0Vw/viewform?entry.1046214884=Andrew+Bynum&entry.2009896212=11007&entry.415477766=Billing&gxids=7628

If you look carefully, you will see some of the input we put in our form when we were using the Google Forms pre-fill tool.

https://docs.google.com/forms/d/e/1FAIpQLSd4QDc4MRkoERExe9KeMLww9P7VNRHFOfpBLwX_Mo-g5TJ0Vw/

This portion of the URL directs the user to the Google Form, with the ID of the form in blue above between the last two forward slashes.

viewform?entry.1046214884=Andrew+Bynum&entry.2009896212=11007&entry.415477766=Billing&gxids=7628

Next, you can see 3 occurrences of entry followed by a number (in red) then equals to the pre-fill input we added (in green). Note that if a prefill item has a space, it is replaced with a plus (+) symbol.

We start to write out our code we can replace these pre-filled inputs with a variable that can update for each user we send our form to.

Time to check out the code to see how we do this.

The Code

This is a pretty basic procedural code so we will simply pack it into one function. No need to go crazy here:

Main variables

Variables to update

We need to first set up some main variables that we will reference in our project. First, we will get access to the Google Sheet that contains the ticket data for our clients – the Tickets file we mentioned earlier – using the SpreadsheetApp class.

We then call the openById() method which takes one argument, the file id. This can be found in the URL and should look similar to the one in the example. This is then put in the SS variable. Line 10

Next, we need to get to the sheet tab our data is in. For us, this is Ticket. So we reference this sheet tab name with our getSheetByName() method and store it in our SHEET variable. Line 11

We will want to indicate what row our user data starts because we don’t want to include our headers. Here we set our ROW_START variable to 2 because our first user is in row 2.

Getting data range and values

Our next task is to get the range of all the data we need to add our pre-fill values, emails and client name data along with our checkbox to see if we need to email that user. We may as well select all the columns and grab the last row.

To grab the full range of our data we use the getRange() method. Which can take many types of arguments, but for us, we want to give it 4 number values:

  • Row start
  • Column start
  • Number of rows
  • Number of columns

We’ll add our ROW_START in our…um…row start argument. Our column start in the first column. Then we grab the last row, which will likely change often by using the getLastRow(). This will update as new entries come in.  We then subtract this by the row start and add 1 to exclude the header. Line 13

To then get the values of the range we use our new range variable and call the getValues() method. This will produce a 2d array of all the data on our sheet. Line 14

Keeping track of emails sent.

Our checkboxes in column keeps track of who we have sent our feedback form to. We will update the checkbox with a tick if we have sent the form using some code.

Before we jump into our loop through each ticket we need to keep track of where the boxes are unticked and where the row of unticked boxes finish. We do this by setting up an object to store untick start and end rows that we will preset as false and update as we loop through the rows.

If you wanted to speed things up in a bigger Google Sheet you could store the start row in a Properties Service like in the post below, but that’s beyond the scope of this tutorial.

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Looping through our data and setting up our column variables

Now that we have the values of our Google Sheet in our VALUES variable, we want to loop through the 2d array and set some variables to each column of data we want to use in our script. We use the forEach method for our loop here with the first argument being the array containing all the cell data in the row and the second one, the row index:

Next, we need to assign some variables to each relevant row item that we will use in either our email or our pre-fill. To do this we will use some destructuring to cleanly build our variables:


The columns in our sheet contain the following:

  • Date
  • Name
  • Email
  • Ticket #
  • Issue
  • Details
  • Response
  • Status
  • Feedback Sent

The bolded items are the only columns we want to use. In our destructured variable assignment, we create an array of all the variables we want to use and put a blank comma space between the variables we don’t want to use.

Creating the first name variable

It’s kinda weird these days to address someone by their first and last name in an email greeting. Some people even find it a little insincere or annoying. So we might want to just stick to the more popular informal first name.

To get our first name, or fname, we use the Javascript substring method to just get the first part of our string up to just before the first space. The substring method takes 2 arguments. The start position and end position. We find out the end position by using the indexof method that searches a string of text and if it finds the corresponding value, it will report the position of the value, but if the value does not exist it will report -1.

The resulting code would look like this:

Now, we are not certain if our users have put in a second name, or even have one for that matter. So if we just created our fname varaiable with this code we would get a weird error if we had a single name.

To fix that, we are going to use a ternary operator that we will first use to check if the name variable is a single name or not. Here again, we use the indexof method to check if there is a positive number. If so we will use the code above to generate our name. Otherwise, we will use just the name. Check out the full line of code:

Swapping spaces between words for “+”

When we create our custom pre-fills we noticed that spaces were repaced with plus symbols “+” in the URL. We want to keep the full name and the issues in our prefill and we know that both items potentially contain spaces in the text. To change the spaces to plus symbols, we will use the Javascript replace method with the help of a little bit of regular expressions.

The replace method takes two arguments, the item to search for and the item you want to replace it with. Because the item we are searching for is a space it’s good practice to use a regular expression rather that ” ” to be certain you catch it. Our regular expression looks like this:

The \s is the symbol for spaces. The two / mean anything between. The g is the symbol for global. So essentially this expression is saying that is is looking for any occurrence of a space all over (globally) in the string.

We’ll update the two original variables (which will upset the functional programming purists, but hey, it’s only a small bit of code) so our two lines will look like this:

Sending off our email

In the next section of our function (Lines 33-46), we check to see if we need to send an email, and if we do, we send it away with our pre-filled link to our form.

First, we use an if statement to check if the current feedback cell is false, then we are good to send the email.

Sendemail()

Next, we invoke the GmailApp Google Apps Script class and then use the sendEmail method. The sendEmail() method can take a few different argument structures, but I like to use the full method approach that takes the following:

  1. Recipient: The email of the person you are sending your email to.
  2. Subject: What your email is about.
  3. Body: We’ll put in a placeholder here, “see HTML body” because we want to use HTML to make our email look fancy.
  4. Options: The are a lot of options you can put inside the curly braces {} of this object, but for us, we just want to add htmlBody. Which allows us to add HTML to our email.

Let’s have a look at the sendEmail() method so far:

The HMTL Email

We will use template literals to create our string of HTML text. Template literals start and end with backticks (`). If you want to add a variable into the string all you need to do is add ${your variable}. The other bonus is that you can happily put your string on new lines of your code without having to close and concatenate your string each time.

Let’s take a look at our htmlBody value:

You can see that it all looks like pretty standard HTML text separated by paragraph tags <p> and breaks </br>. We’ve added in the first name (fname) in the greeting at the start and then created a link to our pre-filled form that we have customised with our variables.

Here is what each entry looks like:

  • entry.1046214884=${name}
  • entry.2009896212=${ticket}
  • entry.415477766=${issue}

Once this part is complete the emails are all sent off. Time to update our Google Sheet to show we have done this job.

Updating the checkboxes

The checkbox process occurs at the end in two stages here. First as we are iterating through our forEach loop we need to keep a record of the first unchecked box and the last one.

Remember earlier that we had set up the variable, uncheckedBoxRange, before we started the loop. Now we want to check if this is the first time we have found an unchecked box. If it is we want to update uncheckedBoxRange.start with the current index plus the ROW_START value to get the row number and also update the uncheckedBoxRange.end.

If we have already found the first occurrence of an unchecked box, we skip updating the start value and just update the end value.

Outside our loop, we then need to use our uncheckedBoxRange object values to update our checkbox columns in our Google Sheet.

First, we need to get the total number of emails we sent. We do this by subtracting the uncheckedBoxRange.end from the start and add 1.

We then want to create a string of true values equal to the uncheckedCount. This can be done fairly cleanly by the new Array constructor that can take an argument to generate amount of values in an array.

Next, we use the fill method to identify what we want to fill each array value with. For us, this is a child array with the value true in each. Why a new array inside our main array? Because each row of a sheet is its own array.

We then use the Google Apps Script getRange() method again to select our range referencing our start row of unchecked boxes, column nine, the total number of unchecked boxes. We don’t have any other columns to worry about so we don’t need a fourth argument.

Finally, we use the setValues() method inserting our newly created array of true (or ticks) into our checkboxes.

Conclusion

To run your code from the Google Apps Script IDE simply click on run and follow the prompts:

Alternatively, you could set a time trigger to run your code daily or weekly or when the Google Sheet changes, or have a button or menu item that you click in your sheet to run the code.

Here are a few tutorials on the topic:

So what do you think? Would you use pre-fill in your own project? I would love to hear how you applied custom pre-fill. It’s always interesting to see what creative things people develop.


Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? I can connect you with a trusted freelancer. I have a team of hand-picked experts ready to help.

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

Google Apps Script: WebApp, HtmlService, LockService; Google Sheets

In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress.

What’s a chain story, Yagi? 

Maybe you did this in school. Someone wrote the first part of a story. You then gave that story to someone else to continue writing. They then pass the story onto someone else to write the next part. And so on and so forth. In the end, the story is read out and everyone laughs at the direction the story went – except that one kid silently raging of their lack of control of the narrative.

Why are we making this? How’s it going to help me?

Well, for one, I thought it would be fun. More importantly, this will allow us to have a look at how Google Apps Scripts communicates client to server-side and vice versa in a little more advanced environment than our previous tutorial. It will also give us an opportunity to look at some more parts of Google Apps Script as they relate to creating a WebApp.

Our chain story WebApp tutorial will also give us an opportunity to look at some of the pitfalls of using WebaApp. Particularly when using the execute as me permissions. Finally, this will then launch us into our follow up tutorial on updating the WebApp to execute as the user rather than me, the owner of the app.

This tutorial is the second part of the WebApp series. However, if you can read a bit of JS, CSS and HTML, you should be able to follow along and if you get stuck you can always go back to the first tutorial:

Google Apps Script: How to create a basic interactive interface with Web Apps

Let’s get started…

The Example: An interactive chain story

Embedded below is our interactive Chain Story web app. If you are feeling creative, read the story so far and then add your part to the story. It has been written by readers just like you:

Continue reading “Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets”

Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript 

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

UNIQUE demo Google Sheets

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. Maybe you are drawing in data from another source like an API, BigQuery or a database, or presenting your unique data in another format.

Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat 🐐. This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language. And of course, we’ll focus our tutorial more on a Google Apps Script because that’s kinda our thing.

We’ll start off with a very basic example of creating a unique array from a 1-dimensional array in our code:

UNIQUE Array generated from a 1d array in GAS

…and then build ourselves up to a final example by grabbing multi-column range of Google Sheet data from which we create a unique list and then display the total sum corresponding to each unique cell value in each of our associated unique columns. Something like this:

UNIQUE 2-Array of multiple columns with result set in GAS
Click to Expand!

As we go through our examples I’ll display the runtime the entire code. Runtime is not a perfect indicator of the performance of the script there are other factors that will influence the runtime, but it will do for our purposes. I think you will be surprised by how fast these scripts run.

Before we get started, let’s take a quick look at the sample data…

Continue reading “Creating Unique Ranges from 2D Arrays in Google Apps Script”

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data

Google Apps Script, Google Sheets, Javascript

Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet?

I know that I have come across these conditions a number of times in the past. Maybe you have a task list that you want to send your team each time a task is allocated to them. Once they let you know that they are done, by say, entering a set of values or checking a task complete box on the row they need to work on, you want to be able to remove their edit permissions from your sheet.

In this post, we have created a Google Apps Script that will add and remove editors to a Google Sheet based on the spreadsheet’s data. More specifically, this script will:

  • Grab the users name and email in each row along with whether or not they have complete the task or if the Google Sheet has been shared and sent to the user.
  • Share the assigned user to the Google Sheet.
  • Send an email to the user. A separate Google Sheet tab is added to the sheet so an administrator can add their custom email message.
  • Automatically check a reference column of checkboxes indicating that the assigned user has been shared as an editor on the Google Sheet and an email has been sent to them.
  • Once the task has been complete the user check the “Edit Complete” checkbox in their assigned row.
  • Either automatically each day or when the Google Sheets administrator clicks the button,  each user who has completed all assigned tasks is removed from having edit permissions to the Google Sheet.

The best way to probably understand this script is through an example…

Continue reading “Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data”