Error showing GSheet range using Google Visualization API in Google Apps Script Web App

Question

I'm trying to display a Google Spreadsheet range using Google Table Visualization API via Google Apps Script Web App. I plan to deploy the Web App into a Google Site. When I copy the code to a Google Apps Script Web App Project and deploy the web app, the query response is "Bad Request [400]". How do I debug further?

Alternative Solutions:

Manual effort: Google Sites has a gadget to pull a Gspreadsheet range onto a webpage, but I want to do it programmatically for 70 different ranges and pages.
Pure Google Apps Script: I can programmatically create copies of a web page template containing Table Gadgets and edit table range. There is a known issue that breaks gadgets when you edit the HTML content (google-apps-script-issues #572).

Source Google Spreadsheet (fake public data) https://docs.google.com/spreadsheets/d/1miOxJ3jPCLE66mWcZmd2q-1YAu6so-GbxjcmIDtXUV4

JS Fiddle code works. I can query the Google Spreadsheet and draw the Google Visualization Table. https://jsfiddle.net/xcghpgmt/6/

Also works as a snippet.

function drawChart() {
    var key = '1miOxJ3jPCLE66mWcZmd2q-1YAu6so-GbxjcmIDtXUV4';
    var GID = 0;
    var range = 'A3:h18';
    var queryString = 'https://docs.google.com/spreadsheets/d/'+key+'/gviz/tq?gid='+GID+'&range='+range;
   
    // Set Data Source
    var query = new google.visualization.Query(queryString);

    // Send the query with callback function
    query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
    // Handle Query errors
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
    }

    // Draw Chart
    var data = response.getDataTable();
    var chart = new google.visualization.Table(document.getElementById('chart_div'));
    chart.draw(data);
}
google.load('visualization', '1', {packages:['table'], callback: drawChart});

<script type="text/javascript"  src="https://www.google.com/jsapi"></script>
<div id="chart_div"></div>

Code.gs

function doGet() {
  var html = HtmlService.createTemplateFromFile("Index").evaluate();
  html.setTitle("Dynamic Webpage");
  return html; 
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .getContent();
}

Index.html

<!DOCTYPE html>
<html>
<header>
</header>
<body>
 <div id="chart_div"></div>
</body>

<?!= include('JavaScript.html'); ?>

</html>

Javascript.html

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    function drawChart() {
    var key = '1miOxJ3jPCLE66mWcZmd2q-1YAu6so-GbxjcmIDtXUV4';
    var GID = 0;
    var range = 'A3:h18';
    var queryString = 'https://docs.google.com/spreadsheets/d/'+key+'/gviz/tq?gid='+GID+'&range='+range;

    // Set Data Source
    var query = new google.visualization.Query(queryString);

    // Send the query with callback function
    query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
    // Handle Query errors
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
    }

    // Draw Chart
    var data = response.getDataTable();
    var chart = new google.visualization.Table(document.getElementById('chart_div'));
    chart.draw(data);
}
google.load('visualization', '1', {packages:['table'], callback: drawChart});
</script>

Mogsdad Mogsdad · Accepted Answer · 2015-07-20T02:48:56

You haven't set the sandboxing for your application properly, so the caja sanitization is stripping out the jsapi.

Change:

function doGet() {
  var html = HtmlService.createTemplateFromFile("Index").evaluate();
  html.setTitle("Dynamic Webpage");
  return html; 
}

To:

function doGet() {
  var html = HtmlService.createTemplateFromFile("Index").evaluate();
  html.setTitle("Dynamic Webpage").setSandboxMode(HtmlService.SandboxMode.IFRAME);
  return html; 
}

You can remove the sandboxing statement altogether from the include() function, since it's the application you need to sandbox. Better yet delete include() and use this in Index.html instead:

<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>

Error showing GSheet range using Google Visualization API in Google Apps Script Web App

1 Answers