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:
This article should have been titled “Creating Links from Custom Menus and Buttons in Google Sheets with Google Apps Script: And Why it’s probably not a good idea”, but you know, I got to appease the SEO gods.
There is no natural or “out-of-the-box” way to create hyperlinks for custom menu items and buttons in Google Sheets. The solution I am providing below is a somewhat hacky approach, that I am not fond of and I will suggest some better alternatives in my summary.
However, there are a few occasions where you may feel forced into a corner as a developer to provide direct links from custom menus, buttons and images in Google Sheets. With this in mind, let’s get cracking.
Table of Contents
The Example and Starter Sheet
In the example, I will provide custom links to my homepage and YouTube channel via a custom menu. I will also add an image link and button (Drawing) link.
The Complete Code
* Google Apps Script simple trigger that is run when the sheet is opened.
* Run when Yagisanatode.com is selected from the "Links" menu or the Yagisanatode Icon is clicked.
* Executs openUrl with selected URL payload.
* Run when "YouTube" is run from the "Links" menu or when the red YouTube button is clicked.
We then use the addItem() method to build two sub-menus, ‘Yagisanatode.com’ and ‘YouTube’. This method takes a title that will appear in the menu and a function name that will be executed when the menu item is clicked.
If the URL is able to be opened in a new tab urlLinked will return an object or for our purpose a “truthy’ result so we know everything worked and we can close the dialogue automatically with google.script.host.close(). Lines 19-20
If the URL could not be opened in a new tab (likely a result of your browser’s pop-up blocker) then urlLinked will return null. We then need to select our HTML div with an id of “blocked” and unhide it. Line 21-22
The “blocked” div
Our “blocked” HTML div is unhidden when the browser prevents the script from automatically opening the new window.
The first thing we need to do is give the user a link to the URL they were looking for. You will notice that we use window.open() again instead of the URL directly. This is because dialogues and sidebars in Google Sheets are embedded iframes. You can learn more about this here:
Next, we provide a message explaining what is going on and how they might want to fix it in future.
Finally, we provide a close button for them to exit from the dialogue box should they have an aversion to clicking the “x” in the top right of the box.
Why directly linking from Menu items images and buttons is not a great approach.
The user still needs to authorise the script
This might be fine if you have some links say to an external help page as a part of a larger project, but it is probably not a sound idea to create a simple project that just produces links from menus or buttons. The user is already going to be annoyed that they have to go through the Google Apps Script permission process.
Perhaps if you are publishing an add-on this might be okay, because the permission process is a lot smoother.
It doesn’t always work
As you can see in the script above, we have included a failsafe if a pop-up blocker is enabled. More often than not this will be the result of running the script unless the user specifically unblocks popups in their browser for Google Sheets.
It’s hacky – Google is probably not amused
There are always a few bugs and better implementations that arise in large software projects like Google Sheets. However, in this case, I get the feeling that Google has a very good reason for you to not directly link from menu bars and buttons in this way.
If you disagree:
Let me know in the comments below. It’s always good to get alternate perspectives.
You could always make a feature request on Google’s Issue Tracker (feel free to link your issue in the comments below).
Workarounds and alternatives
Simulated mouse clicking
There is a clever bit of code by Stephen M Harris that used the same modal dialogue event but first sets a timeout on the dialogue to close. The script then generates an anchor element followed by a scripted mouse click event which is a common workaround to prevent pop-up blockers from running.
Stephen also handles for Firefox’s idiosyncracies with his script too.
Personally, I think this is a little too forced for my liking, but many devs would disagree and I have seen a number of Google Workspace Marketplace apps using Stephen’s code. So, what do I know? 🤷♂️
You can find a link to the solution here on StackOverflow. If anything, give him some upvote love for the clever workaround.
External link dialogue warning
As you’ve traversed the Internet, have you ever seen a dialogue that warns you that you are about to navigate to an external link?
Yeah, this is one extra step for the user and that is a definite downside, but I also think that it better conforms with how Google intended links to be accessed from the UI.
This alternative is probably better suited to menu items rather than buttons and images.
Let’s go ahead and update the blob constant variable.
If you are working in Google Sheets and you want to get something out quickly, why not just make a “Links” sheet tab for users to access? You can always protect the sheet tab, users will still be able to access the link without editing the page.
Button and Images can be linked without code
In a previous tutorial, I cover a few approaches on how to apply hyperlinks to images in cells in Google Sheets. This way when the user hovers over the link, they will see the link (with preview) that they can click on.
Well, this was probably not the satisfying answer you were, hoping for, sorry. But we have covered a number of ways for you to provide links to users from the somewhat ethically dubious to some good alternatives.
I would love to hear what your solutions were in the comments below and how you went about implementing them. Not only does it interest me, but others may find your perspectives and use cases helpful for your own projects.
Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my ‘Hire me!’ page. I occasionally pick up projects. If I'm unable to right now, I can still help you find a great trusted freelancer.
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.
You’ve created an awesome Google Apps Script web app for your secret society within your Google Workspace organisation or …dom! dom! DOM! … the world. The problem is that you only want to share your web app with the worthy. Those selected few. 🐐🛐🛐🛐
How do you do this? How to prevent this most coveted of apps from reaching the wrong hands?
It’s actually surprisingly simple.
In this tutorial, we will explore how to validate selected users to provide access to your web app. For our example, we validate users based on whether or not they have edit access to a Google Drive file ( a common occurrence). In the discussion, we will also look at alternative ways of validating emails.
One of the bonuses of the approach we will go through is that it can also be easily adapted for use in Google Workspace Add-ons, and Editor Add-ons like sidebars and dialogue boxes.
We’ll start off with an example and then move to a quick-use guide for those of you who just want to get in and apply the code to your own project. Then for those who want to know how it all works, I’ll dive into the details.
Google Apps Script: WebApp [updated Dec 2021 – With thanks to Greg S]
In this tutorial, we will cover how you can get a unique temporary access key from a user accessing your WebApp that lasts for 30 days.
Temporary access keys allow you to track users as they use your WebApp over time while still providing anonymity to the user by providing only an access key to that user. Rather than, say, use their name or email address.
Why is this important? Well, you might want to limit the number of times a user submits a form on your WebApp. If you can get a user’s access key unique to them then you can store the number of attempts by the user and check it before the data is submitted.
For example, in a previous post, we created a chain story that we might want to limit the number of times our users contribute to our story to once a day.
NOTE! This tutorial is pretty much standalone. However, it will require some basic knowledge of Google Apps Script WebApp and HTML. Don’t worry if some basic setup parts are not covered in this tutorial, I’ll link to how to do these bits if you need some more instruction.