Google Sheets import utility
- 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 uses ajax
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.
This spreadsheet is powering a few demos; each worksheet powers a different demo.
<!-- 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> <!-- Define the canvas tag --> <canvas id="cvs" width="700" height="300">[No canvas support]</canvas> <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, marginTop: 55, 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).
-
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 accessed via the File menu on the Share submenu.
-
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.
-
Get the data from your spreadsheet and use it in your chart
That's all there is to it. 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 column = obj.get('A');
This fetches the A column.
-
// Fetch the 1 row row = obj.get('1');
This fetches the 1 row.
-
// Fetch certain cells (in a row) row = obj.get('A1:E1');
This fetches the cells A1 to E1 (inclusive).
-
// Fetch certain cells (in a column) column = obj.get('A1:A11');
This fetches the cells A1 to A11 (inclusive).
-
// Fetch a matrix of cells (a 2D array) column = obj.get('B2:E13');
This fetches the cells B2 to E13 (inclusive).
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> <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. In this example of a URL,
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 ajavascript
string which you can inject directly into yourhtml
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.