Google スプレッドシート

Stay organized with collections Save and categorize content based on your preferences.

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

はじめに

Google Charts と 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 Charts のクエリ言語によるデータの並べ替えとフィルタリングをサポートしているためです。クエリ言語をサポートしているシステムであれば、どれもデータソースとして利用できます。

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

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

  1. 既存のスプレッドシートを開きます。このスプレッドシートには、ビジュアル表示が期待する形式を使用し、適切な閲覧権限が設定されている必要があります。([ウェブ上で一般公開] または [リンクを知っている全員] の権限を選択する方法は簡単です。このセクションの手順では、スプレッドシートが設定されていることを前提としています。スプレッドシートを「非公開」にしたまま個々の Google アカウントにアクセス権を付与することで制限できます。ただし、その場合は下記の承認の手順を行う必要があります)。
  2. ブラウザから URL をコピーします。 特定の範囲の選択の詳細については、クエリのソース範囲をご覧ください。
  3. google.visualization.Query() に URL を入力します。このクエリでは、次のオプション パラメータを使用できます。
    • headers=N: ヘッダー行の数を指定します。N は 0 以上の整数です。これらはデータから除外され、データテーブルの列ラベルとして割り当てられます。このパラメータを指定しない場合、スプレッドシートではヘッダー行の行数が推測されます。すべての列が文字列データの場合、このパラメータがない行がスプレッドシートで判別されない可能性があります。
    • 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_expr>」構文を使用して指定します。次に例を示します。

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 - 列 A の 70 行目から最後まで
  • B5:5 - B5 から行 5 の末尾まで
  • D3:D - D3 列 D の末尾へ
  • C:C10 - C 列の先頭から C10 まで

承認

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

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

リンクの共有が適切なソリューションでない場合、デベロッパーは、Google Sheets API のスコープ(https://www.googleapis.com/auth/spreadsheet)に承認された OAuth 2.0 認証情報を渡すように、コードを変更する必要があります。

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

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

前提条件: Google Developers Console からクライアント ID を取得する

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

エンドユーザーの OAuth トークンを取得するには、まずプロジェクトを Google Developers 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 ライブラリを使用する方法については、以下をご覧ください。

drive.file スコープの使用

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

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

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