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:
I just had a recent email from a reader who asked how to tidy up a user’s inputted name from say, a Google Form so that all the first letters of each work in the name are capitalised in the same way that the Google Sheets Proper function does.
I thought it would be a good idea to provide a quick reference for the reader and myself for future projects.
Did you know that you can easily create an interactive webpage web app that you can embed in your own website or your Google Workspace domain for internal use with your organisation with Google Apps Script?
Perhaps you want to create a small online app using resources you have in your Google workspace, like Google Sheets or BigQuery.
Maybe the functionality of Google Forms is not enough and you want to build a custom form for something like a seat booking form with a way for users to register a bunch of participants and immediately see if there are seats available. Or create a form where each answer creates a new set of questions that you want to add to a Google Sheet in our own way.
What if you wanted to road test a new app idea and want to use the development speed of Google Apps Script to get a proof of concept up and running in a flash?
Fortunately for you, Google Apps Script has you covered with the ability to deploy Web Apps.
Even if you have been working in the Google Apps Script environment for a while, getting started on Web Apps can be a bit of a daunting task. It’s just that little bit different that it can make you apprehensive at first. However, let me tell you that once you master the few basics, you will be smashing out Web Apps in no time.
In this tutorial, we will go over the very basics of creating your very first Web App. Don’t worry it will be a little bit more useful and interactive than a “Hello World” app. We will get the user to enter a value client-side. Then send it server-side for calculation and then return it back for display client-side.
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.
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 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…
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…