Google Script to make a hyperlink (for a button)

Question

I don't know much about script, but I am trying to make a clickable button on Google Sheets that would hyperlink to another website. For some reason google sheets doesn't let you hyperlink a google drawing. :/

I know how to assign script to a button (aka a google drawing inserted into my google sheet), but I don't know the actual script that would hyperlink the button to another website.

Any help would be appreciated!

Nikko J. · Answer 1 · 2020-12-15T19:08:02.160

Follow these steps:

Create button by going to Insert -> Drawing.
Draw the button then click Save and Close.
Once you created your button, Go to Tools -> Script Editor
Paste the following code by Stephen M. Harris:

Your code should look like this:

//change the value of url to your desired url.
function openUrl( url="https://stackoverflow.com/questions/10744760/google-apps-script-to-open-a-url" ){
  var html = HtmlService.createHtmlOutput('<html><script>'
  +'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
  +'var a = document.createElement("a"); a.href="'+url+'"; a.target="_blank";'
  +'if(document.createEvent){'
  +'  var event=document.createEvent("MouseEvents");'
  +'  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'                          
  +'  event.initEvent("click",true,true); a.dispatchEvent(event);'
  +'}else{ a.click() }'
  +'close();'
  +'</script>'
  // Offer URL as clickable link in case above code fails.
  +'<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="'+url+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
  +'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
  +'</html>')
  .setWidth( 90 ).setHeight( 1 );
  SpreadsheetApp.getUi().showModalDialog( html, "Opening ..." );
}

Click Save or Ctrl + S.
Run the code and authorize the script to access your data.
Go back to your Spreadsheet where the button is located.
Right click the button and click the vertical 3 dots
Click Assign Script and type the function name "openUrl" ("This is the function from your Apps Script")
Press then button

Note: This might not work on your first run due to PopUp Blocker, allow the site and re-click the button.

Google Script to make a hyperlink (for a button)

1 Answers1