1

I'm making an spreadsheet in google sheets where I've got several links to pre-filled google forms https://docs.google.com/forms . I added a 'button' in the spreadsheet which, when you click it, navigates to the cell containing the right link to the pre-filled form (16 cells to the right of the current active cell), which looks like;

function GoToSite(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// Returns the active cell
var cell = sheet.getActiveCell();
cell.offset(0,16).activate();
};

However, I don't manage to get the spreadsheet to open this link. I know that in a normal excel file, you would use something looking like;

Sub openwebpage ()
... 
End Sub

And you would use "FollowHyperlink"/ "create an IE object" / ".Navigate" or maybe even "Sendkeys" (if you enter alt+enter when the cell containing the pre-filled link is selected, this opens the webpage). But all these options are in combination with sub .. end sub and I don't manage the macro to recognise this. It seems to only work with 

function example (){
...
};

Maybe I'm doing something totally wrong. Is there someone who would know how to open the link in the cell by using macros? Or how to use "Sub ... End Sub" in an google sheet?

Thanks

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Dieuwertje Drexhage
  • 11
  • 3
  • VBA (Sub / End Sub) and JavaScript are fundamentally different. The closest you can come is to use the server functions to fetch the webpage, and then display the response HTML in a modal dialog. This is also because Google Apps Script runs on a remote server, not your browser (Excel VBA is running on your local machine, with access to your operating system, browser, etc.) – tehhowch Sep 25 '18 at 14:38
  • Thank you! I am not yet familiar with all the differences between VBA/JavaScript etc. I'm trying to figure it out and keep learning every day. This should help me further – Dieuwertje Drexhage Sep 26 '18 at 09:18
  • @DieuwertjeDrexhage How did you get on with your Google Sheets problem with opening hyperlinks? – Tedinoz Oct 14 '18 at 02:57
  • @Tedinoz I've got to admit I have not really figure it out yet, when I have I'll let you know! – Dieuwertje Drexhage Oct 17 '18 at 12:19
  • 1
    Does this answer your question? [Google Apps Script to open a URL](https://stackoverflow.com/questions/10744760/google-apps-script-to-open-a-url) – TheMaster Aug 06 '20 at 08:17

1 Answers1

0

You can paste the below code in your Script Editor and pass through a url as its parameter. 

/**
 * Open a URL in a new tab.
 */
function openUrl( 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 ..." );
}

The solution's author points out that you will not be able to run this code in the Script Editor directly and you'll need to Assign the script to a drawing or button.

Google Apps Script to open a URL

JeffM
  • 26
  • 1