MENU CLOSE

【Google Sheets API】
スプレッドシートのデータを
JSONで取得する

Google Sheets API スプレッドシートのデータをJSONで取得する
Google Sheets API スプレッドシートのデータをJSONで取得する

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

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

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

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

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リファラーで制限しておけば問題ないでしょう。

APIキーの制限(アプリケーション)

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の制限

設定し終えたら、「保存」ボタンを押して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で取得したものを設定します。

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

Sheets API 取得結果

うまく取得できました。

参考

エンドポイントの詳細は下記です。
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化したい場合はおすすめです。

記事一覧

RELATED

AVIF [ gulpでjpg・pngからAVIF画像を生成 ]
inoue
inoue
TIPS

gulpでjpg・pngからAVIF画像を生成する

MutationObserverでDOMを監視[PhotoSwipe(v5)+Swiper(v8)連携]
inoue
inoue
TIPS

【JS】MutationObserverでDOMを監視[PhotoSwipe(v5)+Swiper(v8)連携]

Google Business Profile API [ PHPでクチコミ一覧取得 ]
inoue
inoue
TIPS

【PHP】Google Business Profile APIを使ってクチコミを取得する

window.matchMedia hoverを判定
inoue
inoue
TIPS

【window.matchMedia】メディアクエリでhoverが使えるデバイスを判定

lax.js
inoue
inoue
TIPS

lax.jsの使い方【スクロール連動アニメーションの実装】

Swiper v6 → v7
inoue
inoue
TIPS

Swiper(v7)へのアップグレード対応

NEW POSTS

netlify UPDATE BUILD IMAGE [ビルドイメージの更新]
inoue
inoue
TRY

【netlify】ビルドイメージを更新 [ Ubuntu Xenial 16.04 → Ubuntu Focal 20.04 ]

screendot screenshot API
inoue
inoue
TRY

スクリーンショットのAPI「screendot」を使ってみた

Dart Sass + Gulp
inoue
inoue
TRY

DartSassがなかなか辛かったのでGulpを修正してみた

WordPress add_rewrite_rule
inoue
inoue
TIPS

【WordPress】add_rewrite_ruleでリライトルールを追加する

PhotoSwipe
inoue
inoue
TIPS

PhotoSwipeを使って画像をポップアップ表示する【Swiper(v7)との連携あり】

svg-audiovisualizerAudioVisualizer [ howler.js + SVG ]
inoue
inoue
TRY

【howler.js】ストリーミング音源からオーディオビジュアライザーを作る

ブログ記事一覧
  • HOME
  • TIPS
  • 【Google Sheets API】 スプレッドシートのデータをJSONで取得する