Google Sheets import utility
You can use the Google Sheets import utility to fetch data directly from your Google Sheets spreadsheet. This documentation also applies to the SVG Google Sheets connector.
A chart fetching data from a Google Sheet
- Introduction
- Important note for version 6.02
- Setting up a chart that uses Google Sheets
- Accessing different worksheets
- Fetching data from your spreadsheet
- Demos that show the Google Sheets connector
- How to get data from Google Sheets with PHP
- External links
Introduction
From February 2016 there has been a library bundled with RGraph that allows you to integrate easily with Google Sheets and fetch data from those spreadsheets.
This is a lot easier than exporting the spreadsheet to a CSV and then using that - especially as the RGraph CSV import library is AJAX-based and AJAX cannot normally cross domains.
This Google Sheets import code can now be used standalone - so you don't need to include the RGraph core library making it easier for you to use it with your own applications or code.
The chart above is getting data from this spreadsheet. This spreadsheet is actually powering a few demos; each worksheet powers a different demo. Here is the code that makes the chart:
<!-- Include the necessary RGraph libraries --> <script src="RGraph.common.core.js"></script> <script src="RGraph.common.sheets.js"></script> <script src="RGraph.common.key.js"></script> <script src="RGraph.bar.js"></script> <script> // Create a new RGraph Sheets object using the OAuth ID, spreadsheets key and // the callback function that creates the chart. // // The RGraph.Sheets object is passed to the callback function as an argument so it // doesn't need to be assigned to a variable when it's created new RGraph.Sheets( 'YOUR OAUTH ID', // OAuth ID '1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E', // Sheet key/ID 'Bar chart', // The name of the sheet - this can be omitted and defaults to Sheet1 function (sheet) // The callback function which creates the chart { // Get the labels from the spreadsheet by retrieving part of the first row var labels = sheet.get('A2:A7'); // Use the column headers (ie the names) as the key var key = sheet.get('B1:D1'); // Get a 2D array of data from the sheet for the chart var data = sheet.get('B2:D7'); // Create and configure the chart; using the information retrieved above // from the spreadsheet new RGraph.Bar({ id: 'cvs', data: data, options: { backgroundGridVlines: false, backgroundGridBorder: false, xaxisLabels: labels, xaxisLabelsOffsety: 5, colors: ['red','black', '#66f'], colorsStroke: 'rgba(0,0,0,0)', shadow: false, yaxis: false, marginLeft: 40, marginBottom: 35, marginRight: 40, marginInner: 15, key: key, keyBoxed: false, keyPosition: 'margin', keyTextSize: 12, textSize: 12, axesColor: '#aaa', title: 'First half-year results', titleSize: 18, labelsAbove: true, labelsAboveSize: 8, labelsAboveOffset: -1 } }).wave(); }); </script>
Important note for version 6.02
Version 6.02 contains updated Google Sheets library code that's necessary for accessing Google Sheets API v4. v3 of the API no longer appears to be supported. It's not just a straightforward upgrade however - you'll need to get an OAuth access key in order to access the Google Sheets API. This is detailed in the setup instructions in the section below.
Setting up a chart that uses Google Sheets
-
Include the Google Sheets library
The first thing to do is include the RGraph Google Sheets library:<script src="RGraph.common.sheets.js"></script>
Change the path to wherever the code is on your system. -
Create a spreadsheet.
Bit of a no-brainer this one. Here's the spreadsheet that's used for the RGraph demos. You can use it too if you want (you just can't change it!). The key to use is this (CTRL+C to copy it to your clipboard):RGraph.Sheets
object (see the example code above). -
Get yourself an OAuth/API key
You can do that on the Google website
You need to choose Credentials from the menu on the left-hand side and then
+ Create credentials near the top to create a new API key to use in the
Sheets objects constructor (as the first argument). Here's a screenshot showing what you want:
-
Publish the sheet to the web
It's important to do this or you won't be able to access it with RGraph. The Publish to the web... option is at the bottom of the file menu as this image shows:
-
Allow access to the spreadsheet
This step may not be necessary for you but I've included this for completeness. You may need to add viewers
using the Share button in the top right of the screen like this image shows:
-
Get the data from your spreadsheet and use it in your chart
That's all there is to it. Just get the data with theget()
method as shown below.
Accessing different sheets
If you use multiple worksheets on your spreadsheet (ie multiple pages) Then you can access them
by changing the RGraph.Sheets
constructor to use four arguments instead of three.
So normally you would use this:
// Your OAuth ID
var oauth = 'YOUR OAUTH ID';
// The key that identifies the spreadsheet
var spreadsheet_key = '1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E';
new RGraph.Sheets(oauth, spreadsheet_key, function (sheet)
{
// Create chart here
});
And to access the other sheets you would update the above code and add an extra argument in the constructor like this:
// Your OAuth ID
var oauth = 'YOUR OAUTH ID';
// The key that identifies the spreadsheet
var spreadsheet_key = '1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E';
// Name of sheet (Sheet1 is the default name)
var sheet_name = 'Sheet1';
new RGraph.Sheets(oauth, spreadsheet_key, sheet_name, function (sheet)
{
// Create chart here
);
Fetching data from your Google Sheet
Fetching information from the spreadsheet is done with the get()
method on the sheets object. Using this you can use the cell identifiers the
same as what is in the actual spreadsheet. You use it like
this:
-
// Fetch the A column
This fetches the A column.
column = obj.get('A');
-
// Fetch the 1 row
This fetches the 1 row.
row = obj.get('1');
-
// Fetch certain cells (in a row)
This fetches the cells A1 to E1 (inclusive).
row = obj.get('A1:E1');
-
// Fetch certain cells (in a column)
This fetches the cells A1 to A11 (inclusive).
column = obj.get('A1:A11');
-
// Fetch a matrix of cells (a 2D array)
This fetches the cells B2 to E13 (inclusive).
column = obj.get('B2:E13');
Demos that show the Google Sheets connector
You can see examples of the Google Sheets connector in the examples in the download archive. They all have the word "sheets" in the filename.
How to get data from Google Sheets with PHP
Introduction to the PHP version

If you want to get your data from Google Sheets but don't want to use the JavaScript connector (maybe you want the data to integrate with your backend processes or you just don't want people to be able to tell that you use Google Sheets) then there's now (May 2019) a PHP version of the original JavaScript Google Sheets connector.
The API of the PHP version of the connector is very similar to the JavaScript version and, as already mentioned, it allows you to both fetch data from your Google Sheets spreadsheet and integrate with any server-side code/processes that you have or simply hide the fact that you are using Google Sheets (PHP code is not visible to the user like JavaScript is).
Using the PHP version of the connector is very simple and if you've used the JavaScript connector it will be very familiar to you.
Download the PHP code
The PHP library is included in the download archive as
the libraries/RGraph.common.sheets.php
file or
You can download the source code here.
There are no dependencies so you can copy the code into a
file on your system, include it in your page and then start
using it.
An example of the code
Since PHP runs on the server an example page wouldn't be particularly useful. So here's the source-code of a full page that you can copy and paste into a page on your server.
<?php
// Include the PHP Sheets library
require('RGraph.common.sheets.php');
// Create the RGraph Google Sheets PHP object using your OAuth ID and the ID of the spreadsheet
$sheet = new RGraph_Sheets('YOUR OAUTH ID', '1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E');
// An example of fetching a column of data
// $col = $sheet->get('A');
// print_r($col);
// An example of fetching a single cell
// $cell = $sheet->get('A2');
// print_r($cell);
// An example of fetching a whole row of data
// $row = $sheet->get('1');
// print_r($row);
// An example of fetching a range of cells from a row
// $row = $sheet->get('A1:E1');
// print_r($row);
// An example of fetching a range of cells from a column
// This is the data from the Sheet
$col = $sheet->get('E2:E13');
// An example of fetching a matrix of cells
// $matrix = $sheet->get('A2:E13');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<!-- Update these paths to suit your system -->
<script src="RGraph.common.core.js"></script>
<script src="RGraph.bar.js"></script>
</head>
<body>
<h1>A chart using the RGraph Google Sheets PHP import utility</h1>
<canvas id="cvs" width="750" height="250">[No canvas support]</canvas>
<script>
new RGraph.Bar({
id: 'cvs',
data: [<?php echo implode(',', $col) ?>],
options: {
xaxis: false,
yaxis: false,
backgroundGridVlines: false,
backgroundGridBorder: false,
xaxisLabels: ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'],
xaxisLabelsBold: true,
colors: ['Gradient(#faa:red)']
}
}).wave();
</script>
</body>
</html>
1. Include the PHP library
Include the library. Adjust the path to wherever you've put
it on your system. Feel free to use the PHP functions
include()
include_once()
require()
or
require_once()
as you
prefer.
<?php
require_once('RGraph.common.sheets.php');
?>
2. Create the Sheets object
After including the code you can now create the object in a similar
way to any other object in PHP. The two arguments that you should
pass to the constructor are your OAuth ID and the key to the
spreadsheet. You can find the key to your spreadsheet in the URL (eg here
the key to the spreadsheet is highlighted in green:
https://docs.google.com/spreadsheets/d/1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E/edit#gid=0
).
To get an OAuth ID you can follow the instructions in the setup documentation above.
<?php
$sheet = new RGraph_Sheets('[OAUTH ID]', '1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E');
?>
3. Fetch the data from your spreadsheet
This is done used the get()
method which supports five
ways of getting hold of your data.
-
1. Fetch a single cell from the spreadsheet
This fetches the contents of the cell that you specify and returns the data to you.
$cell = $sheet->get('A1');
-
2. Fetch a row of data from the spreadsheet
This fetches the contents of all of the cells in the row that you specify and returns the data to you.
$row = $sheet->get('2');
-
3. Fetch a column of data from the spreadsheet
This fetches the contents of all of the cells in the column that you specify and returns the data to you.
$column = $sheet->get('A');
-
4. Fetch a range of cells in a specific column from your spreadsheet
This fetches the contents of the cells in the range that you specify and returns the data to you. The range is part of a column.
$column = $sheet->get('A2:A13');
-
5. Fetch a range of cells in a specific row from your spreadsheet
This fetches the contents of the cells in the range that you specify and returns the data to you. The range is part of a row.
$row = $sheet->get('A2:E2');
-
6. Fetch a matrix of cells from your spreadsheet (eg A2:E13)
This fetches the contents of the cells in the matrix that you specify and returns the data to you. This could be used to build a grouped or stacked Bar chart for example.
$matrix = $sheet->get('B2:E13');
The
get()
function can take a second argument which should be an associative array that contains astring
option. This has the effect that instead of an array being returned by theget()
function the array will be encoded as a JavaScript string which you can inject directly into your HTML code like this:$data_str = $sheet->get('B2:E13', ['string' => true]); print($data_str);
Reading the JSON from a file
If performance is a concern then you might want to cache the JSON that's returned by Google by fetching it and writing it to a file instead of reading it on every request.
You can do this by writing the JSON to a file (every 10 minutes for example or
an interval that's appropriate to your use case)
and on subsequent calls use a
file:
location instead of just giving the ID of the Sheet.
$sheet = new RGraph_Sheets('file:mySheet.json');
To write the JSON to a file you can create the object using your OAuth ID and spreadsheet key and
then access the raw JSON in the $sheet->json_raw
variable. The PHP function
file_put_contents()
can be used to write the text to a file.