Import data from Google SheetsThis is beta!

Share RGraph:   

Summary
You can now use the Google Sheets import utility to fetch data directly from your Google Sheets spreadsheet without having to convert it into an intermediate format

[No canvas support]
A chart fetching data from a Google Sheet

Introduction

As of February 2016 there is now a library that allows you to integrate easily with Google Sheets and pull data directly from those spreadsheets. This is a easier than having the spreadsheet exported to 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, dynamically, from this spreadsheet. This spreadsheet is actually powering a few demos, each worksheet a different one. Here is the code that generates the chart:

<!-- Include the necessary RGraph libraries -->
<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 key of the spreadsheet 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:E1');
        
        // Get the data from the sheet as the data for the chart
        var data   = [
            sheet.get('B2:E2'), // January
            sheet.get('B3:E3'), // February
            sheet.get('B4:E4'), // March
            sheet.get('B5:E5'), // April
            sheet.get('B6:E6'), // May
            sheet.get('B7:E7')  // June
        ];

        // Create and configure the chart; using the information retrieved above
        // from the spreadsheet
        var bar = new RGraph.Bar({
            id: 'cvs',
            data: data,
            options: {
                backgroundGridVlines: false,
                backgroundGridBorder: false,
                labels: labels,
                xlabelsOffset: 5,
                colors: ['red','#0f0','blue','#eee'],
                shadow: false,
                strokestyle: 'rgba(0,0,0,0)',
                scaleZerostart: true,
                noxaxis: true,
                gutterLeft: 50,
                gutterBottom: 70,
                key: key,
                keyBoxed: false,
                keyPosition: 'gutter',
                keyTextSize: 12
            }
        }).wave();
    });
</script>
    

Setting up a chart that's integrated with 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>
    Obviously you'll need to change the path to wherever the code is on your system.
  2. A Google Sheets apreadsheet 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).
  3. Publish the sheet to the web
    Kind of important to do this or you won't be able to access it with RGraph. The Publish to the web... option is towards the bottom of the file menu as this image shows:

  4. Get the information 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 described below.

Retrieving data from your Google Sheet

Retrieving information from the spreadsheet is facilitated by the .get() method on the sheets object. Using this you can use the cell identifiers the same as are in the actual sheet. You use it, for example, like this:

Demos


Comments

Add a new comment...