Open a URL using Google Apps Script without requiring additional user interaction.
Code also on StackOverflow.
Last active
June 15, 2023 23:52
-
-
Save smhmic/e7f9a8188f59bb1d9f992395c866a047 to your computer and use it in GitHub Desktop.
Google Apps Script - Open a URL in a new tab (such as directly from clicking a menu item, w/o further user interaction).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Open a URL in a new tab. | |
| * https://gist.github.com/smhmic/e7f9a8188f59bb1d9f992395c866a047 | |
| */ | |
| function openUrl( url ){ | |
| var html = HtmlService.createHtmlOutput('<!DOCTYPE html><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. Click below:<br/><a href="'+url+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>' | |
| +'<script>google.script.host.setHeight(55);google.script.host.setWidth(410)</script>' | |
| +'</html>') | |
| .setWidth( 90 ).setHeight( 1 ); | |
| SpreadsheetApp.getUi().showModalDialog( html, "Opening ..." ); | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Open multiple URLs, each in a new tab. | |
| * https://gist.github.com/smhmic/e7f9a8188f59bb1d9f992395c866a047 | |
| */ | |
| function openAllUrls( allLinksRangeName ){ | |
| var i, urls, url, urlEscapedJs, urlEscapedHtml, html, htmlFallback = [], MAX_URLS_TO_OPEN = 15; | |
| allLinksRangeName = allLinksRangeName || 'ALL_LINKS'; | |
| // Fetch, flatten, and sanitize input | |
| urls = (function(){ | |
| var r = [], i, j, v, range, input, inputFormulas; | |
| range = (function(){ | |
| var i, allNamedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges(); | |
| for( i=0; i<allNamedRanges.length; i++ ){ | |
| if( allNamedRanges[i].getName() === allLinksRangeName ){ | |
| return allNamedRanges[i].getRange(); | |
| } | |
| } | |
| })(); | |
| if( !range ) throw new Error('Please define named range "'+allLinksRangeName+'".'); | |
| input = range.getValues(); | |
| inputFormulas = range.getFormulas(); | |
| for( i=0; i<input.length; i++ ){ | |
| for( j=0; j<input[i].length; j++ ){ | |
| v=inputFormulas[i][j] || input[i][j]; | |
| v = v && v.replace && v.replace( /^=\s*HYPERLINK\s*\(\s*"([^"]+).+/i, '$1' ); | |
| Logger.log(v); | |
| if( ( v && v.match && v.match(/^https?:\/\//i) ) // ignore non-URLs | |
| //|| ( (v=inputFormulas[i][j]) && (v) )) | |
| && ( r.indexOf(v) === -1 ) ){ // ignore duplicate URLs | |
| r.push(v); | |
| } | |
| } | |
| } | |
| return r; | |
| })(); | |
| if( !urls ) throw new Error('Unknown error.'); | |
| if( !urls.length ) throw new Error('There are no URLs in named range "'+allLinksRangeName+'".'); | |
| if( urls.length > MAX_URLS_TO_OPEN ) throw new Error('More than '+MAX_URLS_TO_OPEN+' URLs; Aborting. (TODO: prompt for confirmation and/or open in batches)'); | |
| html = '<!DOCTYPE html><html><script>'; | |
| for( i=0; i<urls.length; i++ ){ | |
| url = urls[i]; | |
| urlEscapedJs = url.replace(/"/g,'\\"'); | |
| urlEscapedHtml = url.replace(/"/g,'"'); | |
| html += '' | |
| +' var a = document.createElement("a"); a.href="'+urlEscapedJs+'"; a.target="_blank";' | |
| +'if(document.createEvent){var event=document.createEvent("MouseEvents");event.initEvent("click",true,true);a.dispatchEvent(event)}else{a.click()}'; | |
| htmlFallback.push('<a href="'+urlEscapedHtml+'" target="_blank">'+url.replace(/^https?:\/\//i,'')+'</a>'+"\n");//url.replace(/^https?:\/\/(.{0,60}).*/i,'$1')+'</a>'); // truncation handled via CSS | |
| } | |
| html += '' | |
| +'google.script.host.close();' | |
| +'</script>'+"\n" | |
| // Offer URL as clickable link in case above code fails. | |
| +'<style>body{word-break:break-word;font-family:sans-serif;} a{display: inline-block; line-height:1em; margin:0; font-size:12px; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; max-width: 100%;}</style>' | |
| +'<body>Failed to open automatically. Click below:<br>'+htmlFallback.join("\n<br>")+'</body>' | |
| +'<script>google.script.host.setHeight('+(40+(urls.length*18))+');google.script.host.setWidth(410)</script>' | |
| +'</html>'; | |
| html = HtmlService.createHtmlOutput( html ).setWidth( 90 ).setHeight( 1 ); | |
| SpreadsheetApp.getUi().showModalDialog( html, "Opening "+urls.length+" URLs ..." ); | |
| } |
For multiple URLs, please describe the 'allLinksRangeName' parameter and what it should be / look like.
Is it a cell with CSV of urls? What should be passed in to openAllUrls as 'allLinksRangeName'?
Krafty, it is a named range by the name of ALL_LINKS (menu option Data > Named Ranges)
In my usage the range is P2:P15. I just needed to populate the rest of the URL
Then in the App Script IDE when you hit run, you can see it loops through each one
And finally opens each one in it's own tab as promised :)
-Andy
Thanks @andrewprouty – How could I dynamically open a list of links? I'd like to pass in a CSV list of links or maybe an array.
That is what that did. The named range was P2:P which contained 5 links. The screenshot shows the formula in P1 which is an arrayformula to provide the rest of the path to construct each URL.
The heavy lifting of getting the the list of links from within the named range is what the code on this page did. I displayed the screenshot that showed it open those tabs for each.
I suppose what I mean by dynamic is that the links wouldn't necessarily need to appear in the sheet itself.
I want to take something like this:
and make a link like this:
https://www.google.com/search?q=Apple+Lettuce+Ford+Bronco
...but without having to list 'https://www.google.com/search?q=' in the sheet itself or make a formula. I would just store that in code since it doesn't change.
I can probably manage to refactor your code now that I understand better what it's doing.
Thanks! Will let you know how it goes.
New to Apps Script.
Having a problem getting this to run in a Google Spreadsheet on Chrome.
I copied the code for openUrl to the IDE, saved it. Pressed Run. Execution completed in the IDE.
In the spreadsheet, what I want to do is to have it open a list of URL in separate tabs, set up as follows:
But instead I get an error:
Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 21).
What are some reasons that openUrl would stop working or not work in the first place? Pop-up blockers? Or maybe there are some browser settings that will stop it?
Basically a colleague is having trouble running a macro that I made which uses openURL. It works for me and other colleagues but they are having an issue for some reason.
@kraftydevil This answer on StackOverflow may help.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is great, but how can i add a delay so people will see opening... for a longer time or whatever i put there as my message? i want that ui to show at least a few seconds, right now the link i provide almost opens instantly, so there is no way to read it