access a google sheet entry from the embedded html file site

Larry Green Source

I am trying to use embedded html inside the right sidebar in a google sheet via google apps script. to access a cell of the sheet and display it on a html5 canvas element. I have created a Code.gs file and an html index file ahd have successfully gotten the spread sheet to generate a right sidebar whose content is the HTML file which includes an Canvas element. This worked using

function openSidebar() {
    var html = HtmlService.createHtmlOutputFromFile('index');
    SpreadsheetApp.getUi().showSidebar(html);
}

Now I want to display some of the spreadsheet cell entries on the Canvas element. My first task is to use this method to create a box plot on the sidebar. I have five cells that contain the five point summary: min, Q1, Median, Q3, Max, but cannot get the canvas element to access these values. Here is what does not work:

Accessing the cells directly between the <script> </script> tags by calling the range such as:

var range = statsSheet.getRange(4,4);
var min = range.getValue();

It doesn't work since statsSheet is created on the Code.gs file.

The two lines of code do work to find the cell value if they are directly in the Code.gs file, but then min cannot be accessed by the code within the html file.

I have also tried to create a separate .gs file (boxPlot.gs) and import it using

<script src = "boxPlot.gs"> </script>

but that still does not work. I can't access the variable min wither it is a global variable or a return value of a function in the boxPlot.gs file.

I would appreciate any help on how to turn the pseudo code: <html> create canvas;<script> add min which resides at cell (4,4) as text onto the canvas; </script></html>

Thanks

Thanks for the help, but I am still having troubles getting the variable's value passed from the Code.gs file to the <script> </script> code inside the index.html. Using what you suggested, I was able to get the doSomething() function to run, but the return value could not be read in. For example inside the Code.gs file, I have doSomething(){ return("Hello World"); }

and in the html page, I have

 <script>
      console.log(google.script.run.doSomething());
 </script>

This puts "undefined" on the console instead of "Hello World". Of course, I want to be able to read in more complicated elements like form entries, but if I can't even get it to read in the "Hello World" return value, the complicated tasks will never work. Any suggestions would be much appreciated.

javascriptgoogle-apps-scriptgoogle-spreadsheethtml5-canvassidebar

Answers

answered 3 years ago Sandy Good #1

You must use either the google.script.run Client Side API to be able to access server side code from HTML.

Google Documentation - google.script.run

or use scriptlets that will run a .gs server function, and add content to the HTML before it is served. The scriptlets only run when the sidebar is first opened. So use scriptlets for initial content when the sidebar is first opened.

The terminology is "Templated HTML"

Google Documentation - Templated HTML

To receive a return value back from the server to the HTML Client Side JavaScript you must use the .withSuccessHandler(name_of_function_to_receive_return) method.

Google Documentation - withSuccessHandler Method

You are using test code of:

google.script.run.DoSomething()

In order to receive a "return" back from the server, you must use the withSuccessHandler().

google.script.run
  .withSuccessHandler(functionToHandleTheReturn)
  .DoSomething()

<script>
  function functionToHandleTheReturn(theReturnedValue) {
    alert('here is the returned value: ' + theReturnedValue;
  };
</script>

comments powered by Disqus