Google スプレッドシート

このページでは、Google スプレッドシートで Google グラフを使用する方法について説明します。

1. はじめに
2. スプレッドシートにグラフを埋め込む
3. 別のスプレッドシートからグラフを作成する

はじめに

Google グラフと Google スプレッドシートは緊密に統合されています。Google スプレッドシートは Google スプレッドシート内に配置できます。また、Google グラフから Google スプレッドシートからデータを抽出することもできます。このドキュメントでは、両方の方法について説明します。

どの方法を選択しても、基になるスプレッドシートが変更されるたびにグラフが変更されます。

グラフをスプレッドシートに埋め込む

スプレッドシートにグラフを含めるのは簡単です。スプレッドシートのツールバーで [挿入]、[グラフ] の順に選択すると、グラフの種類とさまざまなオプションを選択できます。

別のスプレッドシートからグラフを作成する

通常は、データ表にデータを入力し、そのデータを使ってグラフを描画することで、Google グラフが作成されます。代わりに Google スプレッドシートからデータを取得するには、スプレッドシートにクエリを実行して、グラフ化するデータを取得します。

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);
}

これが起こるのは、Google スプレッドシートが、データの並べ替えとフィルタリングに Google グラフのクエリ言語をサポートしているため、クエリ言語をサポートするシステムであれば、データソースとして使用できる点です。

グラフは、明示的に承認しない限り、対象のユーザーの権限を使用することができません。スプレッドシートは誰でも閲覧できるか、このページでエンドユーザーの認証情報を明示的に取得する必要があります（認証セクションを参照）。

Google スプレッドシートをデータソースとして使用するには、その URL が必要です。

1. 既存のスプレッドシートを開きます。このスプレッドシートは、ビジュアル表示で求められる形式になっている必要があります。また、適切に表示権限が設定されている必要があります。「[ウェブで一般公開] または [リンクを知っている全員] の権限は最も簡単ですが、このセクションの説明では、そのスプレッドシートを設定したとします。スプレッドシートを「非公開」にして個々の Google アカウントにアクセス権を付与することで制限できますが、下記の認証手順に従う必要があります）。
2. ブラウザから URL をコピーします。 特定の範囲の選択の詳細については、クエリソースの範囲をご覧ください。
3. google.visualization.Query() の URL を指定します。このクエリは、次のオプション パラメータをサポートしています。
   • headers=N: ヘッダー行の数を指定します。ここで、N はゼロ以上の整数です。これらはデータから除外され、データテーブルの列ラベルとして割り当てられます。このパラメータを指定しない場合、スプレッドシートは行の行数を推定します。すべての列が文字列データの場合、このパラメータのない行をスプレッドシートで特定するのが難しい場合があります。
   • gid=N: 複数シートのドキュメントの中で、最初のシートにリンクしない場合にどのシートにリンクするかを指定します。 N はシートの ID 番号です。ID 番号を確認するには、そのシートの公開バージョンに移動し、URL で gid=N パラメータを探します。このパラメータの代わりに sheet パラメータを使用することもできます。Gotcha: Google スプレッドシートは、ブラウザに表示されたときに URL の gid パラメータを再配置することがあります。ブラウザからコピーする場合は、すべてのパラメータが URL の # マークより前にあることを確認してください。（例: gid=1545912003）。
   • sheet=sheet_name: 最初のシートにリンクしない場合にリンク先のマルチシート ドキュメント内のシートを指定します。sheet_name は、シートの表示名です。例: sheet=Sheet5

完全なコード例を次に示します。

このグラフを描画するには、次の 2 つの方法があります。1 つは gid パラメータを使用する方法、もう 1 つは sheet パラメータを使用する方法です。ブラウザにどちらの URL を入力しても、グラフの結果やデータは同じになります。

GID

    function drawGID() {
      var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?gid=0&headers=1&tq=' + queryString);
      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('chart_div'));
      chart.draw(data, { height: 400 });
    }

シート

    function drawSheetName() {
      var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);
      query.send(handleSampleDataQueryResponse);
    }

    function handleSampleDataQueryResponse(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('chart_div'));
      chart.draw(data, { height: 400 });
    }

クエリのソース範囲

クエリソース URL は、クエリで使用するスプレッドシートの部分（特定のセル、セル範囲、行、列、スプレッドシート全体など）を指定します。範囲を指定するには、次のように「range=<range_setIamPolicy>」構文を使用します。

https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?range=A1:C4
   

構文の例を以下に示します。

• A1:B10 - 範囲 A1 ～ B10
• 5:7 - 行 5 ～ 7
• D:F - 列 D ～ F
• A:A70 - 列 A の最初の 70 セル
• A70:A - 行 70 から末尾までの A 列。
• B5:5 - 行 5 の末尾までの B5
• D3:D - D3 列 D の末尾へ
• C:C10 - C 列の先頭から C10 列まで

承認

Google スプレッドシートでは、Google Visualization API（/tq リクエスト）を介して非公開スプレッドシートにアクセスするには、エンドユーザーの認証情報が必要です。

注: 「リンクを知っている全員が閲覧できる」スプレッドシートには、認証情報は必要ありません。スプレッドシートの共有設定の変更は、承認を実装するよりもはるかに簡単です。

リンクの共有ができない場合、デベロッパーは、Google Sheets API の範囲で承認されている OAuth 2.0 認証情報（https://www.googleapis.com/auth/spreadsheet）を渡すようにコードを変更する必要があります。

OAuth 2.0 について詳しくは、OAuth 2.0 を使用した Google API へのアクセスをご覧ください。

例: OAuth を使用した /gviz/tq へのアクセス

前提条件: Google デベロッパー コンソールからクライアント ID を取得する

Google の Identity Platform との統合に関する詳細な手順については、Google ログインと Google API Console プロジェクトとクライアント ID の作成をご覧ください。

エンドユーザーの OAuth トークンを取得するには、まずプロジェクトを Google Developer Console に登録し、クライアント ID を取得する必要があります。

1. デベロッパー コンソールで、新しい OAuth クライアント ID を作成します。
2. アプリケーションの種類として [ウェブ アプリケーション] を選択します。
3. 名前のみを選択します。参照専用です。
4. ドメイン（および任意のテストドメイン）の名前を [承認済みの JavaScript 生成元] に追加します。
5. [承認済みのリダイレクト URI] は空白のままにします。

[作成] をクリックした後、後で参照できるようにクライアント ID をコピーします。この演習では、クライアント シークレットは必要ありません。

サイトを更新して OAuth 認証情報を取得する。

Google が提供する gapi.auth ライブラリを使用すると、OAuth 認証情報を取得するプロセスを大幅に簡素化できます。以下のコードサンプルでは、このライブラリを使用して認証情報を取得し（必要に応じて承認をリクエスト）、取得した認証情報を /gviz/tq エンドポイントに渡します。

demo.html

<html>
<body>
  <button id="authorize-button" style="visibility: hidden">Authorize</button>
  <script src="./demo.js" type="text/javascript"></script>
  <script src="https://apis.google.com/js/auth.js?onload=init"></script>
</body>
</html>

demo.js

// NOTE: You must replace the client id on the following line.
var clientId = '549821307845-9ef2xotqflhcqbv10.apps.googleusercontent.com';
var scopes = 'https://www.googleapis.com/auth/spreadsheets';

function init() {
  gapi.auth.authorize(
      {client_id: clientId, scope: scopes, immediate: true},
      handleAuthResult);
}

function handleAuthResult(authResult) {
  var authorizeButton = document.getElementById('authorize-button');
  if (authResult && !authResult.error) {
    authorizeButton.style.visibility = 'hidden';
    makeApiCall();
  } else {
    authorizeButton.style.visibility = '';
    authorizeButton.onclick = handleAuthClick;
  }
}

function handleAuthClick(event) {
  gapi.auth.authorize(
      {client_id: clientId, scope: scopes, immediate: false},
      handleAuthResult);
  return false;
}

function makeApiCall() {
  // Note: The below spreadsheet is "Public on the web" and will work
  // with or without an OAuth token.  For a better test, replace this
  // URL with a private spreadsheet.
  var tqUrl = 'https://docs.google.com/spreadsheets' +
      '/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq' +
      '?tqx=responseHandler:handleTqResponse' +
      '&access_token=' + encodeURIComponent(gapi.auth.getToken().access_token);

  document.write('<script src="' + tqUrl +'" type="text/javascript"></script>');
}

function handleTqResponse(resp) {
  document.write(JSON.stringify(resp));
}

認証に成功すると、gapi.auth.getToken() は /gviz/tq リクエストに追加できる access_token を含むすべての認証情報の詳細を返します。

認証に gapi ライブラリを使用する方法について詳しくは、以下をご覧ください。

• JavaScript の Google API クライアント ライブラリを使用した認証
• CORS を使用して Google API にアクセスする方法

drive.file スコープの使用

上記の例では、Google Sheets API スコープを使用しています。このスコープは、ユーザーのスプレッドシートのコンテンツすべてに対する読み取りと書き込みのアクセス権を付与します。アプリケーションによっては、必要以上に制限が緩い場合があります。読み取り専用アクセスには、spreadsheet.readonly スコープを使用します。このスコープは、ユーザーのシートとプロパティに対する読み取り専用アクセス権を付与します。

drive.file スコープ（https://www.googleapis.com/auth/drive.file）は、Picker API を介して起動された Google ドライブのファイル選択ツールを使用してユーザーが明示的に開いたファイルにのみアクセス権を付与します。

選択ツールを使用すると、アプリケーションのフローを変更できます。ユーザーは、URL を貼り付けることや、上記の例のようにハードコードされたスプレッドシートを使用するのではなく、選択ツール ダイアログを使って、ページへのアクセスに使用するスプレッドシートを選択する必要があります。「Hello World」選択ツールの例に沿って、google.picker.ViewId.PHOTOS の代わりに google.picker.ViewId.SPREADSHEETS を使用します。