Google スプレッドシート

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

はじめに

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 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 パラメータを使用することもできます。注意: Google スプレッドシートをブラウザで表示したときに、URL の gid パラメータが並べ替えられることがあります。ブラウザからコピーする場合は、すべてのパラメータが URL の # マークの前にあることを確認してください。例: gid=1545912003。
   • sheet=sheet_name: 最初のシートにリンクしない場合に、リンクするマルチシート ドキュメント内のシートを指定します。sheet_name はシートの表示名です。例: sheet=Sheet5

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

以下に、このグラフを描画する 2 つの方法を示します。1 つは gid パラメータを使用し、もう 1 つは sheet パラメータを使用します。どちらの URL をブラウザに入力しても、グラフに対して同じ結果やデータが生成されます。

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

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

承認

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

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

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

前提条件: Google Developer Console からクライアント 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 は、OAuth 認証情報の取得プロセスを大幅に簡略化する gapi.auth ライブラリを提供しています。以下のコードサンプルでは、このライブラリを使用して認証情報を取得し（必要に応じて承認をリクエスト）、取得した認証情報を /gviz/tq エンドポイントに渡します。

<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>

// 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 スコープを使用しています。このスコープは、ユーザーのすべてのスプレッドシート コンテンツへの読み取りと書き込みのアクセス権を付与します。アプリケーションによっては、必要以上に制限が緩やかな場合があります。読み取り専用アクセス権の場合は、ユーザーのシートとそのプロパティへの読み取り専用アクセス権を付与する spreadsheets.readonly スコープを使用します。

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

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