Google Charts

Google Spreadsheets

This page describes how to use Google Charts with Google Spreadsheets.

Introduction

Google Charts and Google Spreadsheets are tightly integrated. You can place a Google Chart inside a Google Spreadsheet, and Google Charts can extract data out of Google Spreadsheets. This documentation shows you how to do both.

Whichever method you choose, your chart will change whenever the underlying spreadsheet changes.

Embedding a Chart in a Spreadsheet

Including a chart in a spreadsheet is easy. From the Spreadsheets toolbar, select "Insert" and then "Chart", and you'll be able to choose the type of chart and select various options:

Creating a Chart from a Separate Spreadsheet

Typically, people create Google Charts by populating a data table and drawing the chart using that data. If you want to retrieve the data from a Google Spreadsheet instead, you'll query the spreadsheet to retrieve the data to be charted:

function drawChart() {
  var query = new google.visualization.Query(URL);
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  var data = response.getDataTable();
  var chart = new google.visualization.ColumnChart(document.getElementById('columnchart'));
  chart.draw(data, null);
}

This works because Google Spreadsheets support the Google Charts query language for sorting and filtering data; any system supporting the query language can be used as a data source.

Note that these charts run with the privileges of the person viewing them, so the spreadsheet must either assign view access to everyone, or to all people who will view the chart.

To use a Google Spreadsheet as a data source, you'll need its URL:

  1. Open an existing spreadsheet. This spreadsheet should have the format expected by your visualization, and it should have viewing privileges set properly. (View privileges of "Public on the web" or "Anyone with the link" will be easiest, but you can restrict view privileges to a set of people if you like, by keeping the spreadsheet "Private" and granting individual Google accounts access.)
  2. Copy the URL from your browser. It'll look something like this: http://docs.google.com/spreadsheet/ccc?key=.... See Query Source Ranges for details about selecting specific ranges.
  3. Provide the URL to google.visualization.Query() The query supports the following optional parameters:
    • headers=N: Specifies how many rows are header rows, where N is an integer zero or greater. These will be excluded from the data and assigned as column labels in the data table. If you don't specify this parameter, the spreadsheet will guess how many rows are header rows. Note that if all your columns are string data, the spreadsheet might have difficulty determining which rows are header rows without this parameter.
    • gid=N: Specifies which sheet in a multi-sheet document to link to, if you are not linking to the first sheet. N is the sheet's ID number: 0 for the first sheet, 1 for the second, and so on. You can also learn the ID number by navigating to the published version of that sheet and looking for the gid=N parameter in the URL. You can also use the sheet parameter instead of this parameter. Example: gid=5.
    • sheet=sheet_name: Specifies which sheet in a multi-sheet document you are linking to, if you are not linking to the first sheet. sheet_name is the display name of the sheet. Example: sheet=Sheet5.

Here's a complete example:

<html>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", '1', {packages:['corechart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
  var query = new google.visualization.Query(
      'https://docs.google.com/spreadsheet/ccc?key=0Atw2BTU52lOCdEZpUlVIdmxGOWZBR2tuLXhYN2dQTWc&usp=drive_web#gid=0');

  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  var chart = new google.visualization.ColumnChart(document.getElementById('columnchart'));
  chart.draw(data, { legend: { position: 'none' } });
}
</script>

<title>Data from a Spreadsheet</title>
</head>

<body>
<span id='columnchart'></span>
</body>
</html>

Query Source Ranges

The query source URL specifies what part of a spreadsheet to use in the query: a specific cell, range of cells, rows, or columns, or a whole spreadsheet. Here are a few examples that demonstrate the syntax:

  • A1:B10 - A range from cell A1 through B10
  • 5:7 - Rows 5-7
  • D:F - Columns D-F
  • A:A70 - The first 70 cells in column A
  • A70:A - Column A from row 70 to the end
  • B5:5 - B5 to the end of row 5
  • D3:D - D3 to the end of column D
  • C:C10 - From the beginning of column C to C10

認証が必要です

この操作には Google+ でのログインが必要です。

ログインしています...

この操作には Google デベロッパーに対する許可が必要です。