Creating Links from Custom Menus and Buttons in Google Sheets with Google Apps Script

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.

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.

Adding links to buttons drawings and menu items in Google Sheets with Google Apps Script

You can find a link to the starter sheet below:

The Starter Sheet

The Code

To attempt to transform a menu item or button into a link we use a Google Apps Script modal dialogue box as an intermediary. We can use HTML, JavaScript and CSS in these dialogues in the same way we would in a website.

This means that we can run the JavaScript window.open() command globally as soon as the dialogue opens which will open the link in a new tab (On most occasions) IF the user has given permission to unblock pop-ups for Google Sheets in their browser.

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

onOpen()

The onOpen() function is a built-in simple trigger in Google Apps Script that can run scripts when the Google Sheet (Or Google Doc, Slide or Form) opens.

In our example, we are using onOpen() to create our custom menu “Links”. We do this by using the SpreadsheetApp class’ getUi() instance class that allows us to build on the existing user interface. The UI class contains the Google Apps Script createMenu() builder which has its own set of methods to construct a custom menu.  The builder takes a menu name as an argument, which we have defined as “Links”.

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.

The menu then needs to be built using the addToUi() method.

You can get a better understanding of menus in this beginner’s tutorial.

openYagi() openYT()

 

Each sub-menu directs to its own function. Both of these functions are identical and simply add the relevant URL for each button and then calls the openUrl() function.

These functions also have a secondary purpose for us. We can attach these function names to each of our buttons.

You can change out these functions to your own URL and names, just make sure you update the addItem methods in the create menu builder.

You can learn a lot more about how to run script from buttons, diagrams and images in Google Sheets with Apps Script in this tutorial:

https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/

However, the basics are:

  1. Right-click the image.
  2. Select the vertical ellipses from the top right of the image.
  3. Select ‘Assign Script’.
  4. Paste in your function (without the braces () ).
  5. Select ‘Ok’.

openUrl(url)

The openUrl() function contains the selected URL as a parameter.

The is the function that gets all of our work done.

blob(The HTML)

blob gif for the chapter on html blob content for dialog boxes in apps script

Our blob text is basically our HTML page that will make up our modal dialogue box. The constant variable blob looks like this:

Keep in mind that the above HTML is encapsulated in template literals (backticks (`)).

I’ve set the stylesheet to use Google’s recommend CSS package for Add-on, sidebars and dialogues for ease and consistency. Line 5

The Script tags

Let’s skip the div for now and head down to the content in the script tags. Lines 16-25

First, we set a variable named, urlLinked to window.open("${url}"). This is a JavaScript DOM method that opens a URL when called. In our case, we have added our selected URL as a template literal tag. Line 18

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 nullWe 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:

Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script

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.

Backup dialog box if url did not automatically open google apps script

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:

  1. Let me know in the comments below. It’s always good to get alternate perspectives.
  2. 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

simulated mouse clicking in demolition man
Simulated mouse event first seen in Demolition Man

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.

External link warning dialog Google Apps Script

Add a links page as a dialogue or sidebar

Another really useful approach is to create a separate links page as a dialogue or sidebar. This keeps all of your links in one location and out of your main document.

This is probably my main go-to and my clients tend to learn quickly how to get to these links to find resources and more instructions that complement the document or sheet that they are working in.

You can see some examples of this in this tutorial:

Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script

Add a links sheet tab

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.

Adding links to images in Google Sheets

Conclusion

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.

Create and Publish a Google Workspace Add-on with Apps Script Course

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.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi

Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script

If you’ve landed on this page you’re probably wondering why your hyperlinks are not working in your Google Workspace dialogue (dialog for my U.S. friends) box or sidebar.

This affects all locations where you can build a sidebar or dialogue with Google Apps Scripts, Sheets, Docs, Slides and Forms.

You might even hit F12 in your browser to inspect the code and found this dreaded error:

Unsafe attempt to initiate navigation for frame with origin ‘https://docs.google.com’ from frame with URL ‘https://n-yyi3lctp…<<fileID>>…-0lu-script.googleusercontent.com/userCodeAppPanel’. The frame attempting navigation of the top-level window is sandboxed, but the flag of ‘allow-top-navigation’ or ‘allow-top-navigation-by-user-activation’ is not set.

linking from a Google Workspace sidebar or dialog error Unsafe attempt to initiate navigation for frame

Why your Links aren’t working in your Google Workspace Dialogs and Sidebars

So what’s going on?

Dialogues and sidebars in Google Workspace are set in iframes. Essentially, this is a nested webpage on your main page. Take a look at the examples, below. I’m in Chrome here and I have selected the Developer Tools Element Selector (Ctrl + Shift + C for PC) and clicked on the Sidebar and Dialogue box respectively.

For the Sidebar.

Google Workspace Sidebars are in iframes

And for the dialogue box.

Google Workspace Dialogs are in iframes

When you create a simple HTML hyperlink in your anchor tag like this:

<a href="https://yagisanatode.com">Website</a>
You are asking the iframe to open a URL in its parent window, your Sheet, Doc, Slide or Form, and browsers generally don’t like to let you do this.

The Solution

The solution is really easy. Simply add target="_blank" to your anchor element:

<a href="https://yagisanatode.com" target="_blank">Website</a>
The target ‘_blank’  attribute will generally open the link in a new tab for most browsers. Note that a user may change their settings to open in a new window instead of a new tab.
Target ‘_blank’ implicitly adds the rel="noopener" behaviour on most browsers to prevent the destination link from tampering with the original source.

The video

Example Implementation

If you want to see an example implementation you can get your own copy of the sample Sheet with bound Apps Script from the link below:
Easter eggs, a bonus Google Sheet formula for linking images in a cell and a fun way to call object methods from a Ui method are all in the example sheet.
Once you made a copy of the Sheet, click on the ‘More’ custom menu item and select from the Sidebar or Dialogue box implementation to see it in action. You will have to Authorise the scopes the first time though.
To look at the code, go to Extensions > Apps Script.
Have fun!

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

Create and Publish a Google Workspace Add-on with Apps Script Course

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.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides (Updated Feb 2022)

Google Apps Script: Dev Tools, Color Picker, Side Bar, Custom Prompt, HtmlService, onOpen, Sidebar, Dialog Box

I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code is returned to Google Apps Script to be used in the App. The problem is that it is really hard to get a good colour match between the palette and Google’s own colour range that is accessible from the fill or text colour buttons.

Take a look at the comparison between the HTML color input element and the Google Sheet background colour palette in the image below.

Color input vs google sheet palette colours

That’s not a user-friendly tool to match colours with the standard Google palette.

So in the back of my mind, I had always wanted to create a tool for a sidebar or dialogue box that would allow the user to easily access the standard colours or use the custom palette provided by the HTML color input.

After finding a bit of time in my recent summer break I came up with this.

Google Color array picker

And this is how it looks in action:

(Note: add-ons has been updated to Extensions)

Continue reading “Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides (Updated Feb 2022)”

Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

Google Apps Script: Time Triggers, ClockTriggerBuilder, ScriptApp

One of the most powerful things about Google Apps Script is the ability to automatically complete tasks with time-driven triggers. You can set up your code to run any one of your functions in your script by the minute, hour, day, week, or month. You can even set it to run based on a specific date.

Have a look at the time-driven trigger options below:

    • Specific Date and Time: Based on: YYYY-MM-DD HH: MM format
    • Minute: Every minute or every 5, 10, 15 or 30 minutes.
    • Hour: Every hour or every 2, 4, 6, 8 or 12 hours.
    • Day: At a time within an hourly bracket. For example:
      • Midnight to 1 am,
      • 3 pm to 4 pm
  • Month: On a specific day of a calendar month at a specific time. For example:
      • Every 3 day of the month at between 3 am and 4 am.

There are two approaches to setting up these time-based triggers. The first one is by simply using the Trigger creator inside the G Suite Developer Hub. This allows you to select the function that you want to add a trigger to for your project and fill out a simple form to determine when you want your trigger to run.

Gsuite Developer hub time trigger

Alternatively, you can create a time trigger programmatically using the ClockTriggerBuilder class.

In this tutorial, we will build on a previous project we’ve called The Badger, that contains a task check off Google Sheet. If the user does not check off their sheet by the due date, then we can send them an HTML email reminder.

Google Apps Script email remind from Google Sheet The Badger 2

Don’t worry, I will provide the starter code and the Google Sheet for you in a minute. However, if you want to learn how I created the email reminder for The Badger you can follow the tutorial here:

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

If you want to see how I created the Spreadsheet, you can check out this link:

Google Sheets: How to create a task completion check sheet.

Setting Up

Continue reading “Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list”

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen

Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue?

I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task.

Reminding staff to complete a task can be time consuming and irritating. What if you could send an email to all those staff members who haven’t completed the task with a simple click of the button?

In this tutorial, we will learn how to create a Google Apps Script HTML email mail merge service that will send an email to a staff member who has not completed their task, indicated by a missing tick on a tickbox.

We will use the sample sheet below:

the badger Google Sheet

The Google Sheets reminder is triggered by a custom menu item.  You can see it at the top right of the image.

When the reminder is sent, it collects the rows of staff that have not indicated that they have completed the task.  It then sends an email to those staff members  with the reminder below:

HTML Email Reminder Google Apps Script
Get it! Badger! Ha!

As you can see, I’ve added some custom HTML to highlight the header and modify the width of the image. The email contains a number of unique pieces of data gathered from the Google Sheet Row:

  • The name of the user.
  • The deadline.
  • The name of the sheet (In this case, Tester).
  • The number of days overdue.

It also contains a link to the check sheet for the user to update once they have completed their task.

Back at The Badger Google Sheet, an alert window appears once the code is completed and also indicates any emails that could not be sent.

To get started you can Make a Copy of the Google Sheet by following this link:

Continue reading “Google Apps Script: Create an HTML email reminder service from Google Sheet data.”