Google Apps Script - possible charts types

Question

I'm new to Google Apps Script so am just exploring if what I want to achieve is possible.

From a Google form, I need to retrieve and display on a separate document the chart created from data on each individual form submission. I know this can be done.

The problem I have is that the chart type I want does not seem to be available here.

The chart needs to show a category and two values. This could be done with a bar chart, height is one value and colour the other value - this looks as though it might be possible but I am not sure if the colour of the whole bar can be changed.

An alternative is the bubble chart, X axis for category, Y axis for one value and size for the other value - but this type of chart does not seem to be supported.

Answer 1

You can display any of the 25+ chart types provided by the Google Visualization API within the Google Apps Script HTML Service.

Below is a modified version of the Bubble Chart example. Instead of fixed data, we'll pull data from a spreadsheet. The chart will be displayed in a modal dialog, as an add-on within that spreadsheet.

The source data:

 A         B               C               D            E
ID  Life Expectancy Fertility Rate  Region          Population
CAN      80.66           1.67       North America   33739900
DEU      79.84           1.36       Europe          81902307
DNK      78.6            1.84       Europe           5523095
EGY      72.73           2.78       Middle East     79716203
GBR      80.05           2          Europe          61801570
IRN      72.49           1.7        Middle East     73137148
IRQ      68.09           4.77       Middle East     31090763
ISR      81.55           2.96       Middle East      7485600
RUS      68.6            1.54       Europe         141850000
USA      78.09           2.05       North America  307007000

Client Side

The rest of the design is pretty straight-forward, but for Apps Script programmers who aren't used to javascript use in the HTML service, especially the behaviour of asynchronous function calls & call-backs, it's what's happening in the client side code that's most interesting. Here's the basic flow.

• Present html page with a placeholder for the visualization.

• Load external JavaScript libraries. We'll be using jQuery (for easy manipulation of DOM) and of course Google's JavaScript API, aka jsapi, for the visualization objects.

• When the page loads, request a callback. We'll call that sendQuery(), as it will retrieve our spreadsheet data. This is a different approach than the original example that simply displayed a chart, because we're not just using hard-coded data.

• When the jsapi finishes loading, sendQuery() is called. It requests our data, and passes the asynchronous response to another callback, drawSeriesChart().

• Once data is received by drawSeriesChart(), draw the chart.

Options for retrieving data from spreadsheet

Since our visualization will be run in a browser (aka client-side), we need to get the info from the spreadsheet (aka server-side). Depending upon your specific needs, there are a few ways to retrieve that data.

1. Query via visualization API.

   For a published spreadsheet, this is a very flexible way to retrieve data. Your client-side js can specify the range of data you're interested in, and you can utilize the Query Language to manipulate the data you'll display without modifying the source spreadsheet.

   function sendQuery() {
     var opts = {sendMethod: 'auto'};
     var sheetId = "--- your sheet ID ---";
     var dataSourceUrl = 'https://spreadsheets.google.com/tq?key=%KEY%&pub=1'
                        .replace("%KEY%",sheetId);
   
     var query = new google.visualization.Query(dataSourceUrl, opts);
   
     // Specify query string, if desired.
   
     // Send the query with a callback function.
     query.send(drawSeriesChart);
   }
   

   Handy for situations where you don't own the source data, for example

2. Create a web service that will feed the spreadsheet data. This approach keeps the spreadsheet itself private.

3. Use direct communication between the server & client side scripts, via google.script.run. This way, the spreadsheet remains private. This example is very simple, as it gleans the entire spreadsheet, but you could extend it to manipulate your data by filtering, sorting, or adding further metadata for formatting.

   function sendQuery() {
   
     // Send the query with a callback function.
     google.script.run
           .withSuccessHandler(drawSeriesChart)
           .getSpreadsheetData();
   }
   

   This requires that function getSpreadsheetData() be implemented on the server side to return the desired data. That's shown in the actual code that follows.

Code.gs

Other than the usual yada-yada for menu creation, this file implements a getSpreadsheetData() function that we'll use to retrieve all the data from a sheet.

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Bubble Chart Example', 'showBubbleEx')
      .addToUi();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a dialog for a visualization.
 */
function showBubbleEx() {
  var ui = HtmlService.createTemplateFromFile('BubbleEx')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setWidth(450)
      .setHeight(350);
  SpreadsheetApp.getUi().showModalDialog(ui, "Bubble Chart Example");
}

/**
 * Return all data from first spreadsheet as an array. Can be used
 * via google.script.run to get data without requiring publication
 * of spreadsheet.
 *
 * Returns null if spreadsheet does not contain more than one row.
 */
function getSpreadsheetData() {
  var data = SpreadsheetApp.getActive().getSheets()[0].getDataRange().getValues();
  return (data.length > 1) ? data : null;
}

BubbleEx.html

This was adapted from the "Sheets add-on" template, and refers to the Stylesheet.html file included there.

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<!-- Below is the HTML code that defines the dialog element structure. -->
<div>
  <div id="series_chart_div" style="width: 400px; height: 300px;"></div>
  <div class="block" id="dialog-button-bar">
     <button id="dialog-cancel-button" onclick="google.script.host.close()">Cancel</button>
  </div>
</div>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('BubbleExJavaScript').getContent(); ?>

BubbleExJavaScript.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript"  src="https://www.google.com/jsapi"></script>

<script>
  // Load the Visualization API and desired package(s).
  google.load('visualization', '1.0', {'packages':['corechart']});

  /**
   * Run initializations on dialog load.
   */
  $(function() {
    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(sendQuery);

    // Assign handler functions to dialog elements here, if needed.

    // Call the server here to retrieve any information needed to build
    // the dialog, if necessary.
  });

  /**
   * Issue asynchronous request for spreadsheet data.
   */
  function sendQuery() {
    google.script.run
      .withSuccessHandler(drawSeriesChart)
      .getSpreadsheetData();
  }

  /**
   * Callback function to generate visualization using data in response parameter.
   */
  function drawSeriesChart(response) {

    if (response == null) {
      alert('Error: Invalid source data.')
      return;
    }
    else {
      var data = google.visualization.arrayToDataTable(response,false);

      var options = {
        title: 'Correlation between life expectancy, fertility rate and population of some world countries (2010)',
        hAxis: {title: data.getColumnLabel(1)},  // 'Life Expectancy'
        vAxis: {title: data.getColumnLabel(2)},  // 'Fertility Rate'
        bubble: {textStyle: {fontSize: 11}}
      };

      var chart = new google.visualization.BubbleChart(document.getElementById('series_chart_div'));
      chart.draw(data, options);
    }
  }  

</script>