Google Sheets import utility

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>

<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: 40,
                    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

  1. 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.
  2. 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):

    You can get this key out of the URL of your spreadsheet and you use it as the second argument that you pass (the first being your OAuth ID) when creating the RGraph.Sheets object (see the example code above).
  3. 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).
  4. 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.
  5. 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:

  6. Get the data from your spreadsheet and use it in your chart
    That's all there is to it. Get the data with the get 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:

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.

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.

External links