I have written a script that creates a new "Report" every time it is ran. This is done by generating a new Google Spreadsheet, which then displays a filtered set of values based off inputs in the original spreadsheet it is tied to. Below is the code:
The script is activated by a custom menu bar. An example of the menu bar is here below:
Upon clicking into the custom menu bar, I would like the newly generated "report" spreadsheet to open. Since this script creates a new spreadsheet each time it is ran, the URL will be different each time, therefore, I am not sure how to incorporate the variable URL into an HTML file. Below are some lines of code I have used to open files upon using the custom menu bar:
In the above image, "+ ssNewURL +" is where the known URL to the desired destination would go. I attempted to reference a variable I used earlier (ssNewURL) which gets the URL of the newly generated sheet, but it did not work. I have tried to get around the formatting issue of the HTML file which requires the URL to be a string; I've tried changing locations of double "" quotations, and single '' quotations. Whatever I have tried, the HTML file refuses to open.
I am extremely new to coding, I understand the logic behind it, however, I am very unfamiliar with every function/formatting of Google Scripts coding.
Any suggestions or workarounds for getting the HTML part of the code to open the dynamically changing URL would be greatly appreciated!
When you call window.open(), the URL also needs to be enclosed in quotation marks.
let htmlOutput = HtmlService.createHtmlOutput(
"<script type='text/javascript'>" +
"window.open('" + url + "', '_blank');" +
"google.script.host.close();" +
"</script> "
);
Another option would be to pass the URL of your spreadsheet to the HtmlTemplate object as a property:
let template = HtmlService.createTemplateFromFile("popup");
template.url = url;
return SpreadsheetApp.getUi().showDialog(template.evaluate());
Calling evaluate() on an HtmlTemplate object will execute the embedded JS code and place all variables you passed to the template in scope.
popup.html
<body>
Opening your spreadsheet...
<input type='hidden' id="hidden-field" value='<?!= url ?>' />
<script>
var url = document.getElementById("hidden-field").value;
window.open(url, "_blank");
google.script.host.close();
</script>
</body>