GSheet Range Display Error Using Google Render API in Google Apps Script Web App
I am trying to display a Google Spreadsheet range using the Google Spreadsheet Render API through a Google Apps Script web app. I am planning to deploy a web application to Google Site. When I copy the code into the Google Apps Script Web App Project and deploy the web application, the response is "Bad Request [400]". How to debug further?
Alternative solutions:
- Manual Effort: Google sites have 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 modify the table range. Known issue that crashes gadgets when editing HTML content (google-apps-script -issues # 572).
Google Spreadsheet source (fake public data) https://docs.google.com/spreadsheets/d/1miOxJ3jPCLE66mWcZmd2q-1YAu6so-GbxjcmIDtXUV4
JS Fiddle script works. I can query google spreadsheet and draw google render spreadsheet. https://jsfiddle.net/xcghpgmt/6/
Also works as a fragment.
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>
source to share
You have not sandboxed your application properly, so sanitizing caja disables jsapi.
Edit:
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 sandbox operator altogether from the function include()
as this is the app you need for sandboxing. Better yet, remove include()
and use this in Index.html instead:
<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>
source to share