HTML Visualization for Charts

Question

I'm trying to use the HTML visualization to show charts, similar to how the UiApp does (which is now deprecated). I've found this example:

.gs

function doGet() {

return HtmlService.createTemplateFromFile('html')
.evaluate() 
.setTitle('My WebApp')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
};

function doSomething() {
  return SpreadsheetApp.openById("ABC123").getActiveSheet.getRange(1, 1).getValue;
};

.html

<html>
<script>
function onSuccess(A1) {
  var value = A1
}

  google.script.run.withSuccessHandler(onSuccess)
      .doSomething();
</script>

<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load('visualization', '1', {'packages':['corechart', 'controls']});
  google.setOnLoadCallback(drawChart);



  function drawChart() {

    var dashboard = new google.visualization.Dashboard(
      document.getElementById('programmatic_dashboard_div'));

    // We omit "var" so that programmaticSlider is visible to changeRange.
    programmaticSlider = new google.visualization.ControlWrapper({
      'controlType': 'NumberRangeFilter',
      'containerId': 'programmatic_control_div',
      'options': {
        'filterColumnLabel': 'Donuts eaten',
        'ui': {'labelStacking': 'vertical'}
      }
    });


   programmaticChart  = new google.visualization.ChartWrapper({
    'chartType': 'PieChart',
    'containerId': 'programmatic_chart_div',
    'options': {
      'width': 300,
      'height': 300,
      'legend': 'none',
      'chartArea': {'left': 15, 'top': 15, 'right': 0, 'bottom': 0},
      'pieSliceText': 'value'
    }
  });

  var data = google.visualization.arrayToDataTable([

    ['Name', 'Donuts eaten', 'Gender'],
    ['Michael' , value, 'Male'],
    ['Elisa', 7, 'Female'],
    ['Robert', 3, 'Male'],
    ['John', 2, 'Male'],
    ['Jessica', 6, 'Female'],
    ['Aaron', 1, 'Male'],
    ['Margareth', 8, 'Female']
  ]);

  dashboard.bind(programmaticSlider, programmaticChart);
  dashboard.draw(data);
}

</script>
  </head>
  <body>
    <div id="programmatic_dashboard_div" style="border: 1px solid #ccc">
    <table class="columns">
    <tr>
      <td>
        <div id="programmatic_control_div" style="padding-left: 2em; min-width: 250px"></div>
        <div>
          <button style="margin: 1em 1em 1em 2em" onclick="changeRange();">
            Select range [2, 5]
          </button><br />
          <button style="margin: 1em 1em 1em 2em" onclick="changeOptions();">
            Make the pie chart 3D
          </button>
        </div>
        <script type="text/javascript">
          function changeRange() {
            programmaticSlider.setState({'lowValue': 2, 'highValue': 5});
            programmaticSlider.draw();
          }

          function changeOptions() {
            programmaticChart.setOption('is3D', true);
            programmaticChart.draw();
          }
        </script>
      </td>
      <td>
        <div id="programmatic_chart_div"></div>
      </td>
    </tr>
  </table>
</div>

This works perfectly fine, but rather than entering the data myself, I want to extract the data from a spreadsheet. For example:

SpreadsheetApp.openById("ABC123").getActiveSheet.getRange(1, 1).getValue;

I've tried placing that bit of code at the start of the script, in the middle, pretty much everywhere, but it just doesn't seem to work. Any ideas how to achieve this?

Thanks in advance

Alan Wells · Answer 1 · 2015-05-06T14:19:20.420

The variable that has the data for the chart is named data

var data = google.visualization.arrayToDataTable([Two D array here]);

So, in your onSuccess function, set the return values to data:

function onSuccess(A1) {
  var data = A1;
};

Right now, the chart is automatically loading when the page loads. That code in the HTML Script tag is running when the page loads. The code is not inside of a function, that would then be called. That's all fine, if you want that code to automatically run when the page loads.

google.load(

The above line of code runs automatically when the page loads. I'm mentioning this, because when you get the data from the spreadsheet, if the data is inside of a function, the time period for the data to be in it's variable, is only during the time when the function is running. After the function is done running, the value(s) in the variable inside the function are lost. If you run the code for the chart creation, and the code for the data retrieval in two separate instances, you need a way to store the data longer term, in order to get it passed to the chart creation code.

Maybe put the chart code in your success function:

function onSuccess(A1) {
  // Gets a cache that is specific to the current user of the script
  var cache = CacheService.getUserCache();
  cache.put('chartData', A1);

  google.load('visualization', '1', {'packages':['corechart', 'controls']});
  google.setOnLoadCallback(drawChart);
};

function drawChart() {
  //cache is available to use here
  var cachedData = cache.get('chartData');
  var data = google.visualization.arrayToDataTable(cachedData);
};

I have tried this, and while it works in normal html script, it doesn't seem to work when placed in within the — mgallegos, May 06 '15 at 12:47
Please look at my updated post to see how exactly I've tried it. Thanks — mgallegos, May 06 '15 at 13:03
What are you doing with that `globalVariable`? It's defined globally, but also locally within `onSuccess()`, so the assignment within that function will have no affect on the global. Further, when `drawChart()` is called, it will be in a different execution instance than the `onSuccess()` callback, so `globalVariable` will be blank. While it's a different situation, the caching solution from [this answer](http://stackoverflow.com/a/30055120/1677912) would apply in this case. — Mogsdad, May 06 '15 at 13:57
Thanks for pointing out the problem with the global. I added some code for using `cache`. — Alan Wells, May 06 '15 at 14:20

HTML Visualization for Charts

1 Answers1