How to Open a Website in New Window from google sheet drop down list

Advertisements

Open a web page in a new window using google sheet drop down list with this guide.

A new menu option has been added to the Google Sheets UI by an add-on. In the future, you’d like to include a link in the menu that, when clicked, takes the user directly to your website.

Using this example Google Sheet, we can show how to access a website from a menu in a new tab or window using google sheet drop down list

Advertisements

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.

Advertisements

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

Advertisements

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

The sleep function is required because it may take a second or two to open the window. In the event that sleep is removed, the Spreadsheet dialog will open and immediately close without launching the web page.

Advertisements