The Google Sheets connector

Written by Richard Heyes, RGraph author, on 23rd July 2018

• Introduction
• What is Google Sheets?
• Some example code
• Can I use the Sheets connector without RGraph - but with my own code?
• Licensing of the RGraph Sheets connector
• Further reading

Introduction

A common choice that you must make when you have charts created by frontend javascript is how to get data from server-side to client-side.

One option is for when you're using the Google Sheets web-based spreadsheet application (if you're not using it already you should definitely consider it as it's very useful - though obviously, it's not suitable for every situation).

What is Google Sheets?

If you're unaware of Google Sheets then you're in for a pleasant surprise. Google Sheets is the web-based spreadsheet application that's part of Google Docs - Google's web-based Office application.

It's free to use so don't run away screaming "Too expensive!" and anyone can set up a Google account and start using it. In fact, if you use Gmail then you don't have to set up an account - just go to Google Docs and start creating documents.

It may not have all of the features that Excel does but it does have some major advantages:

• It's free to use by anyone
• It has excellent collaboration and sharing features
• It's very portable and can be accessed anywhere - at your desk and on the road

Taking the first point - as mentioned anyone can use it free of charge. Google accounts come with over 10Gb of online storage - which is enough for many, many documents. You can buy more too if you wish at quite a low price.

And an example of the second point - previously I have edited one of my online documents whilst on the phone to someone who was in another part of the country who was viewing the document. As I edited it the changes I was making were visible to the viewer as I made them - in real-time! If that's not the definition of online collaboration I don't know what is!

Some example code

Here's a simple example of how you would use the Google Sheets connector. There's not much to it and if you want further information there's a link to the documentation page below.

<script>
    // Call the Sheets importer and give it the unique key of the spreadsheet
    // (you can get this out of the URL of the sheet) and a function that is
    // passed the sheets object that you can use to get the information from
    // the sheet.
    //
    // UPDATE
    //
    // From version 6.02 an extra argument to the constructor has
    // become necessary - your OAuth ID. This ID is required by
    // Google to gain access to their APIs. Getting hold of one
    // is very simple and you can read how to get one by reading
    // the setup instructions on the sheets documentation page.
    new RGraph.Sheets('YOUR OAUTH ID', '1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E', function (sheet)
    {
        // The 'A' column is used as the labels
        var labels = sheet.get('A2:A5');
        
        var data   = [
            sheet.get('B2:E2'), // January
            sheet.get('B3:E3'), // February
            sheet.get('B4:E4'), // March
            sheet.get('B5:E5'), // April
        ];

        new RGraph.SVG.Line({
            id: 'cc',
            data: data,
            options: {
                backgroundGridVlines: false,
                backgroundGridBorder: false,
                xaxisLabels: labels,
                colors: ['#A8E6CF','#DCEDC1','#FFD3B6','#FFAAA5'],
                shadow: true,
                xaxis: false,
                yaxis: false,
                marginLeft: 50,
                marginBottom: 70,
                linewidth: 5,
                marginInner: 15,
                spline: true,
                title: 'A chart demonstrating the Google Sheets connector'
            }
        }).draw();
    });
</script>

Can I use the Sheets connector without RGraph - but with my own code?

This could be useful as you don't always want to use RGraph but yet you still want to connect to a Google Sheets spreadsheet and then do something with your own code.

Starting from version 4.67 the Google Sheets code will have no dependencies on the rest of the RGraph codebase so you'll be able to use it independently if you want to.

Licensing of the RGraph Sheets connector

And with regards to licensing questions remember RGraph is GPL licensed - so this means you can do as you please with the code.

Further reading

Read the documentation page for the Google Sheets connector:

• The Google Sheets documentation page