How to Open a Website in New Window from Google Sheets Menu

May 07, 2022

This tutorial how to open a web page in new window from a menu option in Google Sheets

Let’s say you have built an add-on for Google Sheets that adds a new menu item to the sheets UI. You would now like to add an option in the menu that, when clicked, will redirect the user to your website without the user having to click any other button.

For instance, in this demo Google Sheet, we have a parent menu and a sub-menu that opens the underlying website in the new window.

Google Sheets open Url

As a first step, we’ll add a custom menu in the Google Sheet and invoke it from the onOpen function so the menu is always available when a user opens your Google Sheet.

const onOpen = () => {
  const ui = SpreadsheetApp.getUi();
  const parentMenu = ui.createMenu('👩🏻‍💼  Digital Inspiration');
  parentMenu.addItem('Visit our website', 'openWebsite');
  parentMenu.addToUi();
};

2. Add HTML for Website Redirection

Create a new file url.html in the Apps Script editor and add the following code.

The JavaScript uses the window.open method to open the URL in a new window since we have set the target to _blank.

<!DOCTYPE html>
<html>
  <body>
    <a href="<?= url; ?>" target="_blank">Click here</a> to open the webpage.
  </body>
  <script>
    var windowReference = window.open('<?= url; ?>', '_blank');
    if (windowReference !== null) {
      google.script.host.close();
    }
  </script>
</html>

If you would like to open the website in a fixed size popup, instead of a new window, the function would be written as:

<script>
  var windowFeatures = 'popup';
  var windowReference = window.open('<?= url; ?>', 'scriptWindow', windowFeatures);
  if (windowReference !== null) {
    google.script.host.close();
  }
</script>

The return value of the window.open method will be null if the window has been blocked by the browser’s built-in popup blockers.

The popup can be positioned anywhere on the script and resized to a specific height and width by modifying the windowFeatures variable as below:

// before
var windowFeatures = 'popup';

// after
var windowFeatures = 'left=100,top=100,width=320,height=320';

Please see the MDN docs for best practices around solving a few usability problems related to links opening secondary window.

3. Open link from Google Sheets

Next, we’ll write the Apps Script function that will be invoked from the menu and launch the website in a new window / tab.

const openWebsite = () => {
  const htmlTemplate = HtmlService.createTemplateFromFile('url.html');
  htmlTemplate.url = 'https://digitalinspiration.com/';
  const htmlOutput = htmlTemplate.evaluate().setHeight(50).setWidth(200);
  const ui = SpreadsheetApp.getUi();
  ui.showModelessDialog(htmlOutput, 'Open Website');
  Utilities.sleep(2000);
};

It is necessary to add the sleep function as opening the window may take a second or two. If sleep is omitted, the Spreadsheet dialog will open and instantly close without launching the website.

Google Apps Script Google Sheets

ShareWhatsAppTwitterFacebook

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory