Importing data from Google Sheets and creating charts with it

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.

Warning! This Google Sheets import code can now be used standalone - so you don't need to include the RGraph core library and thus it's easier for you to use it with your own applications or code.

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


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=""></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: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,
                xaxisLabels: labels,
                xaxisLabelsOffsety: 5,
                colors: ['#A8E6CF','#DCEDC1','#FFD3B6','#FFAAA5'],
                shadow: false,
                colorsStroke: 'rgba(0,0,0,0)',
                yaxis: false,
                marginLeft: 40,
                marginBottom: 35,
                marginRight: 40,
                key: key,
                keyBoxed: false,
                keyPosition: 'margin',
                keyTextSize: 12,
                textSize: 12,
                textAccessible: false,
                axesColor: '#aaa'

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

  4. 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:

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.

External links