Integrating HighCharts with Google Spreadsheets and Sheets

5 min read

In this tutorial, we’ll show you how to create a Highcharts chart using data from a Google Spreadsheet. Highcharts is a popular JavaScript charting library that allows you to create interactive charts for your website or application. By using Google Spreadsheets as a data source, you can easily update your chart with new data without having to manually edit your code.

Prerequisites

Before we get started, make sure you have the following:

  • A Google account: You’ll need a Google account to access Google Spreadsheets.
  • Basic knowledge of spreadsheets: You should be familiar with basic spreadsheet concepts such as rows, columns, and cells.
  • Basic knowledge of Highcharts: You should have a basic understanding of how to create and customize Highcharts charts.
  • Knowledge of the Google Spreadsheet API: You’ll need to know how to create and use a Google Spreadsheet API key to access your data.
  • Basic knowledge of JavaScript: You should be comfortable with editing JavaScript code to customize your chart.

Step 1: Prepare the data

The first step in creating a Highcharts chart using data from a Google Spreadsheet is to prepare your data. Here’s how:

  1. Open your Google Spreadsheet: Log in to your Google account and open the Google Spreadsheet containing the data you want to use for your chart.
  2. Organize your data: Make sure your data is organized in a way that makes it easy to create a chart. For example, if you’re creating a column chart, you’ll want to have one column for categories and one column for each data series.
  3. Format your data: Highcharts can handle many different data formats, but it’s a good idea to format your data in a way that makes it easy to use. For example, you might want to format numbers as numbers (rather than text) and dates as dates.

Step 2: Create the chart

Now that your data is prepared, it’s time to create the Highcharts chart. Here’s how:

  1. Get the data from the Google Spreadsheet: Use the Google Spreadsheet API to retrieve the data from your spreadsheet. You’ll need to use your API key and the ID of your spreadsheet to do this. Make sure to restrict API access and set your spreadsheet to public and view-only. To get the SPREADSHEET_ID, you can find it in the URL of your spreadsheet.

Example URL
https://docs.google.com/spreadsheets/d/2qH56788aZlzWSEXecawZAyH64TBzuvVzFzifzS2323/
The Spreadsheet ID is: 2qH56788aZlzWSEXecawZAyH64TBzuvVzFzifzS2323

  1. Create a Highcharts chart: Use the Highcharts library to create a chart using the data from your Google Spreadsheet. You can customize the chart’s appearance and behavior using Highcharts options. Here’s an example of how you might create a column chart using Highcharts:

// Create the chart
Highcharts.chart('myAwesomeChart01', {
        chart: {
          type: 'column'
        },
        title: {
        text: 'My Chart Title 1'
        },
        data: {
          googleAPIKey: 'API_KEY',
        googleSpreadsheetKey: 'SPREADSHEET_ID',
            error: console.error
      }
  });
  1. Add the chart to your page: Once you’ve created the chart, you’ll need to add it to your web page or application. You can do this by adding a <div> element to your page and specifying its id in the Highcharts chart options. Make sure to include the following code on your page to load the necessary libraries:
<script type="text/javascript" src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/data.js" type="text/javascript"></script>
<script src="https://code.highcharts.com/modules/exporting.js" type="text/javascript"></script>
<script src="https://code.highcharts.com/modules/accessibility.js"></script>

And add this <div> element to your page where you want the chart to appear:

<div id="myAwesomeChart01" style="height: 450px"></div>

Step 3: Customize the chart

Once you’ve created your Highcharts chart using data from a Google Spreadsheet, you can customize its appearance and behaviour using Highcharts options. Here are some common customizations you might want to make:

  • Change the chart type: Highcharts supports many different chart types, including line, column, bar, pie, and more. You can change the chart type by setting the chart.type option.
  • Add a title and subtitle: You can add a title and subtitle to your chart using the title.text and subtitle.text options.
  • Customize the axes: You can customize the appearance and behavior of the axes using options such as xAxis.title.text, yAxis.labels.format, and xAxis.categories.
  • Add a legend: You can add a legend to your chart using the legend.enabled option. You can also customize the legend’s appearance and behavior using options such as legend.layout and legend.itemStyle.

For more information on customizing Highcharts charts, see the Highcharts documentation.

Adding data from multiple sheets

If your data is spread across multiple sheets within a single Google Spreadsheet, you can still use it to create a Highcharts chart. Here’s how:

  1. Specify the sheet name: When retrieving data from a Google Spreadsheet using the Google Spreadsheet API, you can specify the name of the sheet you want to retrieve data from using the googleSpreadsheetRange option. For example, if your data is on a sheet named “mysheetname”, you would set googleSpreadsheetRange: 'mysheetname'.

  2. Repeat for each sheet: If you have data on multiple sheets that you want to use in your chart, you’ll need to repeat this process for each sheet. You can then combine the data from each sheet into a single data series or use it to create multiple data series.

Here’s an example of how you might retrieve data from two sheets named “mySheet1” and “mySheet2” and use it to create two data series in a Highcharts chart:

 <script type="text/javascript">
    // Create the chart
    Highcharts.chart('containerA', {
        chart: {
          type: 'column'
        },
        title: {
          text: 'My Chart Title 1'
        },
        data: {
            googleAPIKey: 'API_KEY',
            googleSpreadsheetKey: 'SPREADSHEET_ID',
            googleSpreadsheetRange: 'mySheet1',
            error: console.error
        }
    });

   // Create the chart
  Highcharts.chart('containerB', {
        chart: {
          type: 'column'
        },
        title: {
        text: 'My Chart Title 2'
        },
        data: {
          googleAPIKey: 'API_KEY',
          googleSpreadsheetKey: 'SPREADSHEET_ID',
          googleSpreadsheetRange: 'mySheet2',
            error: console.error
      }
    });
</script>

Don't forget to add the div's in your HTML. 

Related post