#はじめに

業務担当の方から、
「システムで取得したデータをスプレッドシートに出力したい」
という要望を頂きました。

ほほう！楽しそう！！（間違い）

というわけで。

過去シリーズ
【PHP】Google Ads APIをサンプルプログラム無しで使いたい！！
【PHP】Yahoo！広告APIでレポートデータを取得したい！
に引き続き、
composerを使った環境でないので、サンプルなしでAPIを叩きたい
というアツい思いのもとで（むしろもはやヤケクソ）、調査を開始。

従来どおり、推奨はサンプルプログラムを使ってやってね！のスタンスは変わりません。
基本的に自己責任でお願いします。。

軽くググったところ、qiitaでも他でも、検索結果がたんまりありました。
広告API調べたときは、検索結果の少なさに愕然としたわけですが、今回は違う！
よしこれ楽勝じゃね？と高をくくったね。甘かったね。。

#スプレッドシートとは

まずそもそも、スプレッドシートとは何か。

__天下のGoogleさんが提供する表計算ソフト__です。

見た目はExcelによく似てるけど、Web上で管理されていて、googleアカウントさえあれば無料で使えます。
自動保存だし、複数人で共有できます。べんり。

Excelとの違いはググるとたくさん出てくるので、気になる方は調べてみてください。

#Google Sheets API

「スプレッドシート」「API」で検索すると、すぐにSheetsAPIというのが出てきます。

Sheets API php quickstart

あ、はい。英語です。恐怖。。
日本語に翻訳してどうぞ。

ふむふむ、認証して。データ取ればいいわけね。

SheetsAPIに関して、とても親切に解説してくださっているページがたくさんありました。

★参考：
https://qiita.com/howdy39/items/ca719537bba676dce1cf
http://www.sharkpp.net/blog/2016/09/22/how-to-use-google-spreadsheets-api-for-php.html
※たくさんあったけど、この２つがとてもわかりやすくて助かりました。。

でもまあ、よく理解できないままに、
認証といえばこれしか知らないよ的な勢いで
広告APIでやった認証をコピペしてSheetsAPIを呼んでみました。

だっておんなじぐーぐるだし広告APIでもう認証通ってるんだからイケるよね！？・・・的な。

scopeが違うよそんなん使えねーよと怒られまくり、当然ながら撃沈・・・ずーん。。。
そうなの？API違うと認証も変わっちゃうの！？

で、ふと考えてみたわけです。
そもそも私、Googleの認証についてよく理解できてないんだよねってことに気づきました。
ディベロッパーコンソールであれこれやらされてるけど、一体何なのあれはと。

#Google API を使うための認証情報

ディベロッパーコンソールを開くと、「認証情報」ってメニューがあります。
認証情報を作成しようとすると、認証情報の種類を聞かれます。

APIキー
OAuthクライアントID
サービスアカウントキー

２つめは広告APIで使ったので「なんとなく知ってる」けど、ほかの２つはなにそれ美味しいの状態。。
軽く調べてみて、今回のに当てはまるか検討してみました。

##APIキー

一番簡単にAPI利用できる認証方法。
任意アカウントの扱いで、APIにGETパラメータとして渡すだけで使えちゃう便利キー。
キー自体に制限をかけないと危ないけど、お手軽にAPIを試したいときはこれで十分。

SheetsAPIでも利用はできたが、スプレッドシートの「共有設定」で制限がある場合は権限エラーになる。
スプレッドシートに制限をかけず、誰でも見れる触れる状態であれば、これで良さそう。

⇒今回は、運用担当の人のみ操作できるスプレッドシートにしないといけないので、これはNG。つぎ。

##OAuthクライアントID

OAuth2認証っていうガチガチ二段階認証。
認証とかいまいち分かっていなかった私は「リフレッシュトークン」「アクセストークン」の単語が出てきただけでテンパりました。。

①ディベロッパーコンソールで発行されるクライアントIDを使い、__「リフレッシュトークン」__を取得する
※具体的なリフレッシュトークンの取得方法はこちらの「認可コードの取得」あたりが詳しくてわかりやすい。

　必要なパラメータのついたURL（accounts.google.com～のやつ）を叩く
　※この際に必要なAPIのスコープを指定する（複数指定可）
　　↓
　画面でユーザーを聞かれるので選ぶ
　　↓
　リフレッシュトークンが画面に表示されるので、どこかにコピペしておく

②ディベロッパーコンソールで発行されるクライアントIDとクライアントシークレット、それから
さっきメモったリフレッシュトークンを使い、__「アクセストークン」__を取得する

③アクセストークンをheaderにセットしてAPI呼び出しを行う

SheetsAPIで利用してみたところ、①で「認可トークン」のようなものが表示され（※リフレッシュトークンではないのがポイント）、
その流れで②アクセストークンは取得できた。
「認可トークン」はワンタイムパスワードのようなものらしく、リフレッシュトークンのように使い回せないらしい？？
いちいち①からやりなおさなければならないのでプログラム的にいかがなものか。。

⇒今回は夜間バッチで動かす画面のないものを考えていたので、毎回画面を経由するこのやり方はNG。つぎ。

##サービスアカウントキー

OAutn認証と同じくガチガチ認証なんだけど、こちらはメールアドレスのような「アカウント」を持っているのが特徴。
サービスアカウントを作成した際に発行される秘密鍵を使って__「アクセストークン」__を取得します。

⇒アカウントがあれば、スプレッドシートの共有設定に入れられるじゃん！これだ！！

と、いうわけで。

ディベロッパーコンソールでサービスアカウントを作成し、
秘密鍵を作成すると、秘密鍵の入ったファイルが有無を言わさずダウンロードされました。

このファイルを必要なら名前を変え、安全な場所に配置します。
例として、apitest.jsonというファイル名に変えました。

こんなかんじ：

apitest.json

{
  "type": "service_account",
  "project_id": "{YOUR_PROJECT_ID}",
  "private_key_id": "{YOUR_PRIVATE_KEY_ID}",
  "private_key": "-----BEGIN PRIVATE KEY-----\n{YOUR_PRIVATE_KEY}\n-----END PRIVATE KEY-----\n",
  "client_email": "XXXXXXXX@XXXXXXXXX.iam.gserviceaccount.com",
  "client_id": "{YOUR_CLIENT_ID}",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/XXXXXXXXXXXXXXXX.iam.gserviceaccount.com"
}

private_keyってのがそれはそれは長い長～～い文字列で、時々改行コード\nも入ってたりして、
「このままでいいの！？」って戸惑ってしまうわけですが、そのままでいいんです！！

よし、これでイケる！気がする！！

#サービスアカウントで認証を行う

さて、サービスアカウントを取得できたら、
次に必要になるのが、__JWT__と呼ばれる秘密鍵です。

JWTはJson Web Tokenの略で、

JSONデータに署名や暗号化を施す方法を定めたオープン標準 (RFC 7519) である
(Wikipediaより引用)

とのこと。

★参考：
https://qiita.com/Naoto9282/items/8427918564400968bd2b
https://christina04.hatenablog.com/entry/2015/06/04/224159
https://gist.github.com/awa2/ada1b2d0d14179da0b359b877e4c136f

ふ～ん、なんかの形式に則って署名したらいいのね。。

・・・ってアルゴリズムタイプとかハッシュ値とかBase64エンコードとか署名とか
聞いたり知ったりしていることもちらほらあるけど、全体的に何言ってるかわからんわ！！

って心折れかけたんですけど。

googleのライブラリのfirebaseにですね。JWTを取るプログラムが落ちてました！！

JWT.php

サンプルを意地でも使わずにやってきた私ですが、
何と！このプログラムはどこにも依存していないcomposer要らずのやつなので！
ありがたく、拝借しました。。。

必要なのは、166行目からのencode()ですね。

では実際に、
サービスアカウントを利用してJWTを作成し、APIを利用するためのアクセストークンを取得してみましょう：

//トークンの有効時間をセット
$_time = strtotime("now");
$_time_end = $_time + 3000;

//認証ファイルを取得
$_json = file_get_contents("apitest.json");
$_jsonauth = json_decode($_json, true); //jsonを配列化

//認証の秘密鍵を取得
$_key = $_jsonauth["private_key"];

//認証のペイロード
$_payload = [
    "iss" => $_jsonauth["client_email"], //認証のclient_email
    "scope" => "https://www.googleapis.com/auth/spreadsheets", //spreadsheetを操作できるscope
    "aud" => $_jsonauth["token_uri"],  //認証のtoken_uri
    "exp" => $_time_end,
    "iat" => $_time,
];

//ライブラリを利用して認証に必要なJWT(JsonWebToken)を発行
$_jwt = JWT::encode($_payload, $_key, "RS256");

//curl START
$_curl = curl_init();

//OPTIONをセット
curl_setopt_array($_curl, [
	CURLOPT_URL => $_jsonauth["token_uri"],
	CURLOPT_POST => true,
	CURLOPT_RETURNTRANSFER => true,
	CURLOPT_TIMEOUT => 120,
	CURLOPT_HTTPHEADER => ["Content-Type: application/x-www-form-urlencoded"], //固定
	CURLOPT_POSTFIELDS => http_build_query([
		"assertion" => $_jwt,
		"grant_type" => "urn:ietf:params:oauth:grant-type:jwt-bearer", //固定
	]),
]);

//curl EXEC(文字列で取得)
$_resp = curl_exec($_curl);
//エラーハンドリング用
$_errno = curl_errno($_curl);

//curl END
curl_close($_curl);

//エラーハンドリング
if ($_errno !== CURLE_OK) {
    //エラー処理
}

//エラーでなければjsonを連想配列化        
$_jsonresp = json_decode($_resp, true);

//認証情報を取得
$_access_token = $_jsonresp["access_token"];
$_token_type = $_jsonresp["token_type"];


//headerにセット：実際にAPIを実行する際のheader（このあとで使う）
$_header = [
	"Content-Type: application/json", 
	"Accept: application/json",
	"Authorization: $_token_type $_access_token", 
];

アクセストークンが取れたらもうこっちのものだっ！！！

#SheetsAPIでスプレッドシート情報を取得してみる

ふつうにCURLで取得できます。
ポイントは、GET指定すること。POSTにするとエラーになりました。

//URL（対象となるスプレッドシートIDを入れる）
$_url = "https://sheets.googleapis.com/v4/spreadsheets/{SPREADSEET_ID}/";

//curl START
$_curl = curl_init();

//OPTIONをセット[GET]
curl_setopt_array($_curl, [
    CURLOPT_URL => $_url,
    CURLOPT_CUSTOMREQUEST => "GET",
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_TIMEOUT => 30,
    CURLOPT_HTTPHEADER => $_header,
    ]);

//curl EXEC(文字列で取得)
$_resp =  curl_exec($_curl);

//エラーハンドリング用
$_errno = curl_errno($_curl);

//curl END
curl_close($_curl);

#SheetsAPIでスプレッドシートの内容を取得してみる

ふつうにCURLで取得できます。
ポイントは、GET指定すること。POSTにするとエラーになりました。

//パラメータの入力データをurlencode
$_range = urlencode("test!A1:E"); //シート名!範囲

//URL（対象となるスプレッドシートIDを入れる）
$_url = "https://sheets.googleapis.com/v4/spreadsheets/{SPREADSEET_ID}/values/$_range";

//curl START
$_curl = curl_init();

//OPTIONをセット[GET]
curl_setopt_array($_curl, [
    CURLOPT_URL => $_url,
    CURLOPT_CUSTOMREQUEST => "GET",
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_TIMEOUT => 30,
    CURLOPT_HTTPHEADER => $this->header,
    ]);

//curl EXEC(文字列で取得)
$_resp =  curl_exec($_curl);

//エラーハンドリング用
$_errno = curl_errno($_curl);

//curl END
curl_close($_curl);

#SheetsAPIでスプレッドシートに出力してみる

ふつうにCURLで取得できます。
ポイントは、POST指定すること。

ちなみにこれは指定データを指定した場所に上書きするだけなので、
もともとあったデータで出力範囲外のセルはそのまま残ります。
色とかフォントもそのまま。あくまでデータ内容だけ上書きで出力します。


//URL（対象となるスプレッドシートIDを入れる）
$_url = "https://sheets.googleapis.com/v4/spreadsheets/{SPREADSEET_ID}:batchUpdate"

//出力するデータ配列
$_array = [
	"requests" => [[
		"updateCells" => [
			"start" => [
				"sheetId" => "0",
				"rowIndex" => "0",
				"columnIndex" => "0",
			],
			"rows" => [
                ["values" => [
                    ["userEnteredValue" => ["stringValue" => "ぽんた"]],
                    ["userEnteredValue" => ["stringValue" => "うさこ"]],
                    ["userEnteredValue" => ["stringValue" => "うさみ"]],
                    ["userEnteredValue" => ["stringValue" => "うさのすけ"]],
                    ["userEnteredValue" => ["stringValue" => "ぴよ"]],
                ]],
                ["values" => [
                    ["userEnteredValue" => ["stringValue" => "ただのたぬき"]],
                    ["userEnteredValue" => ["stringValue" => "でっかいうさぎ"]],
                    ["userEnteredValue" => ["stringValue" => "普通のうさぎ"]],
                    ["userEnteredValue" => ["stringValue" => "ちっちゃいうさぎ"]],
                    ["userEnteredValue" => ["stringValue" => "ひよこ"]],
                ]],
                ["values" => [
                    ["userEnteredValue" => ["stringValue" => "茶色"]],
                    ["userEnteredValue" => ["stringValue" => "どピンク"]],
                    ["userEnteredValue" => ["stringValue" => "オーガニックピンク"]],
                    ["userEnteredValue" => ["stringValue" => "薄い黄色"]],
                    ["userEnteredValue" => ["stringValue" => "黄色"]],
                ]],
            ],
			"fields" => "userEnteredValue",
		],
	]],
];

//curl START
$_curl = curl_init();

//OPTIONをセット[POST]
curl_setopt_array($_curl, [
    CURLOPT_URL => $_url,
    CURLOPT_POST => true,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_TIMEOUT => 30,
    CURLOPT_HTTPHEADER => $this->header,
    CURLOPT_POSTFIELDS => json_encode($input),
    ]);

//curl EXEC(文字列で取得)
$_resp =  curl_exec($_curl);

//エラーハンドリング用
$_errno = curl_errno($_curl);

//curl END
curl_close($_curl);

セルの色の操作とかもできるけど、基本的にやり方は同じ。
認証通っちゃえばあとはマニュアル通りでいけそう。

また色々試してみて、ひっかかったことがあれば追記します。

年末ず～～っとこれで悩んでいたので、ようやくすすめる嬉しさを込めて。
同じように悩んでいる方の力に慣れれば幸いです。