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 on to 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 over 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:
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:
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.
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:
…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:
As we go through our examples I’ll display the runtime of 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…
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. Three 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.
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:
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).
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 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.
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:
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:
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 :
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:
sendFeedbackEmail()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
/**
* Create an email with a prefill link to a Google Form
*/
functionsendFeedbackEmail(){
/** #############################################
* Globals
* ##############################################
*/
constSS=SpreadsheetApp.openById("1Jbgl82JbVVRhWWUs7JSVaDh2ai52DGD5Vs7XKF5Qq8s");//Update for your workbook
constSHEET=SS.getSheetByName("Ticket");// Update to your Google Sheet Tab
constROW_START=2;//After your header, where does your row start?
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 I 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.
1
let uncheckedBoxRange={start:false,end:false};
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.
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 forEachmethod 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:
1
VALUES.forEach((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:
1
let[,name,email,ticket,issue,,,,emailSent]=row;
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 Javascriptsubstringmethod 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 theindexofmethod 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:
1
name.substring(0,name.indexOf(" "))
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:
1
let fname=(name.indexOf(" ")!==-1)?name.substring(0,name.indexOf(" ")):name;
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:
1
/\s/g
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:
1
2
name=name.replace(/\s/g,"+");
issue=issue.replace(/\s/g,"+");
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 sendEmailmethod. The sendEmail() method can take a few different argument structures, but I like to use the full method approach that takes the following:
Recipient: The email of the person you are sending your email to.
Subject: What your email is about.
Body: We’ll put in a placeholder here, “see HTML body” because we want to use HTML to make our email look fancy.
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:
1
2
GmailApp.sendEmail(email,"Support Feedback","see HTML body",{
htmlBody:
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:
1
2
3
4
5
6
7
8
9
10
11
`
<p>Hi${fname},</p>
</br>
<p>We are constantly trying toimprove our ability toprovide fast andhelpful support foryou.</p>
<p>Please takeamoment tofill out the Feedback Form below on how we did with your recent ticket:</p>
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.
1
2
3
4
5
6
7
//Update your checkbox range.
if(!uncheckedBoxRange.start){
uncheckedBoxRange.start=index+ROW_START;
uncheckedBoxRange.end=index+ROW_START;
}else{
uncheckedBoxRange.end=index+ROW_START;
}
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.
1
let uncheckedCount=1+uncheckedBoxRange.end-uncheckedBoxRange.start;
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 n amount of values in an array.
Next, we use thefillmethod 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.
1
let ticks=newArray(uncheckedCount).fill([true]);
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.
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.
Need help with Google Workspace development?
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.
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…
Google Apps Script: SpreadsheetApp, addEditor/s, removeEditor/s, alert. Javascript: try…catch, forEach, join, push, template literal. Google Sheets
In this tutorial, we will go over the basics of adding users as Editors to Google Sheets with Google Apps Script. We’ll go through the process step-by-step, starting with two very basic codes and then progress on to error handling so your code doesn’t break for your user.
In Google Sheets just like Docs, Slides, Forms and Sites you can add co-editors to work on your projects. This is usually done straight from Google Drive or within the chosen Google file in the top right with the Share button.
The rules for sharing a specific user as an editor are pretty simple. The user must have either a Gmail (name@gmail.com) account, GSuite for Education domain account (name@yourdomain.com) or Google Workspace (formerly, Gsuite) account with an email in the workspace’s domain (name@yagisanatode.com).
Now that we have all the basics, let’s go ahead and write some Google Apps Script code. First of all, open a Google Sheet. It can be one that you want to use to add and remove editors with code on a project you are working on or just a practice Google Sheet. Then go to Extensions > App Script.