Make charts by importing data from Google Sheets

Summary: You can now 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.

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.

A chart fetching data from a Google Sheet

Introduction
Setting up a chart that uses Google Sheets
- Accessing different worksheets
Fetching data from your spreadsheet
Demos
How to get data from Google Sheets with PHP and use it with RGraph
External links

Introduction

As of February 2016 there is now a library 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 cross domains.

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 spreadsheet's 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('1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E', function (sheet)
    {
        // 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>

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 three arguments instead of two.

So normally you would use this:

new RGraph.Sheets('1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E', function (sheet)
{
    // Create the chart here
});

And to access the other sheets you would update the above code and add an extra number in the argument list like this:

new RGraph.Sheets('1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E', 2, function (sheet)
{
    // Create the chart here
});

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>
```
Obviously you'll need to change the path to wherever the code is on your system.
Create a spreadsheet.
Bit of a no-brainer this one. Here's one that is 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 first argument that you pass when creating the RGraph.Sheets object (see the example code above).
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:
Get the data from your spreadsheet and use it in your chart
That's all there is to it. Just get the data with the get() method as shown below.

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 which are 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 and use it with RGraph

Introduction to the PHP version
Download the PHP code
An example of the code

Introduction to the PHP version

If you want to get your data from Google Sheets but don't want to use 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

You can download the library 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 the ID of the spreadsheet
    $sheet = new RGraph_Sheets('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() or require() 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 only argument that you should pass to the constructor is the key to the spreadsheet. How to setup a spreadsheet to use with the connector is shown here.

<?php
    $sheet = new RGraph_Sheets('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 which contains a string option. This has the effect that instead of an array being returned by the get() 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 more 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 access the raw JSON in the $sheet->json_raw variable. The PHP function file_put_contents() can be used to write the file to disk.

References

Google Sheets