Create Google Chart on web page with data from private Google Sheet
Tim Hill
<html>
<head>
<title>Graph from sheet</title>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="demo.js" type="text/javascript"></script>
<script src="https://apis.google.com/js/auth.js?onload=init"></script>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
// Load the Visualization API and the corechart package.
google.charts.load('current', {'packages':['line', 'corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(initialize);
// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function initialize() {
var opts = {sendMethod: 'auto'};
// Replace the data source URL on next line with your data source URL.
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1mF-0U2Nh3YsZOvHBaDaBZTOS-J3IQNAbshHdBQYhJts/gviz/tq?sheet=SEO&headers=1&tq=', opts);
// Send the query with a callback function.
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var options = {
title: 'Organic Search Performance',
width: 900,
height: 500,
// Gives each series an axis that matches the vAxes number below.
series: {
0: {targetAxisIndex: 0},
1: {targetAxisIndex: 1},
2: {targetAxisIndex: 0},
3: {targetAxisIndex: 1}
},
vAxes: {
// Adds titles to each axis.
0: {title: 'Impressions'},
1: {title: 'Click Through Rate'}
},
explorer: {
axis: 'horizontal',
maxZoomOut:2,
keepInBounds: true
}
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<button id="authorize-button" style="visibility: hidden">Authorize</button>
<!--Div that will hold the pie chart-->
<div id="chart_div"></div>
</body>
</html>
But I don't want to share the link to the sheets publicly. However when I set the sheets to private the chart no longer works.
I've followed the instructions here: https://developers.google.com/chart/interactive/docs/spreadsheets#authorization adding in the demo.js file with my clientID.
The result is if I'm signed out of Google I need to login and get asked if I'm OK for the page to access my Google Sheets but I don't want to get the page to ask for permission to access the Sheets of the visiting user, I want it to access the sheets in my account so I think this is a different type of Auth???
Basically when my page loads I want it to try and fetch a specific sheet. Google Sheets will say "woah, that's private" and my script will say "Its OK I have credentials"
I've looked high and low for a way to do this but can't even seem to find any discussions about it. Is this actually even possible?
Pierre-Hedzer Marchi
Pierre