Open a URL using Google Apps Script without requiring additional user interaction.
Code also on StackOverflow.
Last active
February 7, 2025 22:59
-
-
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 hidden or 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 hidden or 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 ..." ); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.