0

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:

Report Script

The script is activated by a custom menu bar. An example of the menu bar is here below:

Menu Bar Code

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:

HTML File Code

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!

MonleyP
  • 3
  • 1
  • Can you provide your script as the text data instead of the image data? – Tanaike Feb 05 '22 at 01:02
  • Welcome to StackOverflow. For future questions, please post code snippets inline instead of using screenshots. Be sure to check read this [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example#:~:text=Don't%20sacrifice%20clarity%20for,correctly%20formatted%20on%20Stack%20Overflow.) – Anton Dementiev Feb 06 '22 at 16:52
  • @Tanaike - apologies, I was out of office and did not see this until today. Anton's solution helped me. Going forward I will provide scripts as text data! – MonleyP Feb 07 '22 at 21:03
  • @AntonDementiev Thank you for the help! I will follow those guidelines going forward! – MonleyP Feb 07 '22 at 21:04
  • Thank you for replying. I noticed that an answer has already been posted. In this case, I would like to respect it. – Tanaike Feb 08 '22 at 00:10

1 Answers1

3

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>
Anton Dementiev
  • 5,451
  • 4
  • 20
  • 32