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

by
Advertisements

Assume you’ve created a Google Sheets add-on that adds a new menu item to the application’s user interface. In the future, you’d like to include a link in the menu that, when clicked, takes the user directly to your website.

A parent menu and a sub-menu that launches the underlying website in a new window are examples of this.

Add Menu in Google Sheets

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.

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

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>


Open Window in Popup

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

Advertisements 
<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';

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.

Advertisements 
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);
};

Because it might take a few seconds to open the window, the sleep feature is a need. Spreadsheet dialogue will appear, but no webpage will be launched if sleep is not used.

Leave a Comment