Open a URL using Google Apps Script without requiring additional user interaction.
Code also on StackOverflow.
Last active
May 16, 2024 18: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 ..." ); | |
| } |
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
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).