Google SpreadSheetのデータをJSON形式で取得する方法

今回はGoogle Sheets APIを使って、スプレッドシートのデータをJSON形式で取得する方法について。 Sheets APIはシートのデータを取得するためのエンドポイントがあり、取得方法も様々ですが、ここでは1番手っ取り早い方法をメモしています。

手順1、Google API ConsoleでAPIを有効化する

Google API Consoleにアクセスして新規プロジェクトを作成。

ライブラリからGoogle Sheets APIを検索して有効化します。

手順2、APIキーの取得

Google Sheets APIを使うにはAPIキーが必要になります。
手順1で作成したプロジェクトで「認証情報」→「CREATE CREDENTIALS（認証情報を作成）」→「APIキー」と進みます。

するとAPIキーが作成されます。

APIキーはあとで使うのでコピーしておきましょう。ちなみに、ここでコピーせずに閉じても「認証情報」→「APIキー」→「鍵を表示」で後から確認できます。

APIキーはこのままだとどこからでも使えてしまうので、悪用を防ぐ為に必ずキーを制限します。「認証情報」から作成したAPIキーをクリックするとAPIキー編集画面が表示されます。ここでアプリケーションの制限とAPIの制限があるので設定します。

※WEBサイトから使うのでなく、ただ単にJSONデータが取得できるかどうかを試したいだけの場合はこの設定は飛ばして大丈夫です。

アプリケーションの制限

アプリケーションの制限は下記のようにHTTPリファラーで制限しておけば問題ないでしょう。

example.comのところを利用するWebサイトのドメインに設定しておくと、そのドメインからのみアクセスを許可します。ここではワイルドカード指定ができるので、example.com/*とすると、example.com/以降にどんな文字列が来てもそれを許可します。サブドメインをすべて許可したい場合は*.example.com/*とすればOKです。このあたりの説明は、APIキーの制限時にHTTPリファラーを指定すると細かい説明が表示されるようになっていますのでそちらも確認を。

ちなみにlocalhostなどローカル環境でテストしたい場合は下記のように追加設定可能です。

ただし、ローカルで扱う場合は本番用とテスト用でAPIキーを分けて、テストではテスト用のダミーのスプレッドシートを別途用意する、などして使った方が良いかと思います。

APIの制限

続いてAPIの制限。
今回はGoogle Sheets APIが使いたいだけですが、もしもAPI制限をしなかった場合、このAPIキーさえあればアクセス可能なAPIがたくさんあります。これも悪用されるケースが考えられるので、Google Sheets APIのみを選択してそれ以外はこのAPIキーでは使えないように制限しておきます。

設定し終えたら、「保存」ボタンを押してAPIキーの制限は完了です。

手順3、スプレッドシートの作成

実際にJSONで取得したいスプレッドシートを作成します。
ここではテスト用にデータ内容のサンプルを用意しました。左下のシート名は後で使いますので、任意で名前を変更しておきます。ここでは「fruits」としておきます。シートが作成できたら右上の「共有」に進みます。

共有設定で一般的なアクセスに箇所を「リンクを知っている全員」に変更します。

これで設定は完了。
リンクをコピーしておきます。

手順4、スプレッドシートIDの取得

手順3でコピーしたリンクからスプレッドシートIDを取得します。
下記のURLの{スプレッドシートID}となっている部分の文字列です。

https://docs.google.com/spreadsheets/d/{スプレッドシートID}/edit?usp=sharing

手順5、エンドポイントを使ってJSONを取得

Google Sheets APIのエンドポイントを使ってJSONを取得します。

https://sheets.googleapis.com/v4/spreadsheets/{スプレッドシートID}/values/{シート名}?key={APIキー}

スプレッドシートIDは手順4で取得した文字列を。
シート名はスプレッドシートの画面左下のシートの名前を入れます。複数シートがある場合はどれか一つを設定します。
APIキーは手順2で取得したものを設定します。

これでアクセスしてみると、下記のようなデータが取得できます。

うまく取得できました。

参考

エンドポイントの詳細は下記です。
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

Googleのドキュメントに掲載されているサンプルはこちら。https://developers.google.com/sheets/api/samples/reading#read_a_single_range

参考にさせていただいた記事
https://macoblog.com/google-spreadsheet-json/

ハマったところ

実際プログラムから使う時にハマったところをメモ。

APIキーをリファラーで制限して、PHPのcurlを使って取得するときに取得できない

PHPのcurlでJSONを取得しようとした時に、APIキーをリファラーで制限していない場合は取得できて、リファラーで制限しなかった場合は取得できる、という現象が起きました。

何回も試して「なんで弾かれるん！？」ってなっていましたが、実はただの凡ミス。
curlのオプションにCURLOPT_REFERERが足りていませんでした。

<?php
function curl_get_contents( $url, $timeout = 60 , $header = null){
  $ch = curl_init();

  if(empty($header)){
    $header = [
      "Content-Type: application/json"
    ];
  }

  curl_setopt( $ch, CURLOPT_HTTPHEADER , $header);
  curl_setopt( $ch, CURLOPT_URL, $url );
  curl_setopt( $ch, CURLOPT_HEADER, false );
  curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
  curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT, $timeout );
  curl_setopt( $ch, CURLOPT_TIMEOUT, $timeout );
  curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, false);
  curl_setopt( $ch, CURLOPT_FAILONERROR, true);
  curl_setopt( $ch, CURLOPT_REFERER, 'https://example.com/'); // ← このオプションが不足していたので追記。example.comは利用するサイトのドメイン

  $result = curl_exec( $ch );

  curl_close( $ch );
  return $result;
}

$sheet_id   = 'スプレッドシートID';
$sheet_name = urlencode('シート名');
$api_key    = 'APIキー';
$url        = "https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/values/${sheet_name}?key=${api_key}";
$result     = curl_get_contents($url);
?>

HTTPリファラーで制限するのに、空のままリクエストしていたみたいで、そりゃ弾かれるわっていう。笑
ただのミスですが、忘れがちなのでメモしておきます。

curlでリファラー書き換えたらOKってことはアレなんですけど・・・って感じですが、とりあえず解決。

・・・っていうかGoogle公式のPHPクライアントライブラリ使えよって話なんですけどね。
今回はとにかく手っ取り早く使いたかったので、わざわざライブラリ使うのめんどくさいなーて思って避けたら変なところでハマってしまいました。まだまだ勉強不足です。

さいごに

今回はスプレッドシートの内容をJSON形式で取得する方法についてメモしました。
Google Sheets APIで1番手っ取り早い方法を使いましたが、Google API Consoleを使ったことがある人なら15〜30分くらいでできそうです。サクッと手元のデータをAPI化したい場合はおすすめです。