GoogleのAPIの使い方を知らない初心者におすすめ
今回の目的はQiitaのエントリをベースにGoogleのAPIを叩いて何かしたい人向け（初心者想定）にGoogle Spread Sheets APIを実際に叩いてみた結果を伝えること。
特にAPIを使ったことのない人や、JSON形式ってなにそれ美味しいの系男子、とりあえずGCP（Google Cloud Platform）で何ができるのか知りたい人とかを想定してる。
やったこと
以下のエントリを実施
qiita.com
個人的にやりたいことはGoogle Cloud Platform上のアプリケーションからPOSTされたJSONファイルをAPIを使って別アプリのスプレッドシートに反映させるということ。
そのためには本来両方のアプリでOAuth認証しないといけないが、今回はまずは実験的に使ってみるという目的のもと、サクッとGoogleのさまざまなAPIを使えるOAuth Playgroundというアプリケーションを利用する。
実際のコード
Step 2Exchange authorization code for tokens
HTTPリクエスト
POST /oauth2/v4/token HTTP/1.1 Host: www.googleapis.com Content-length: 184 content-type: application/x-www-form-urlencoded user-agent: google-oauth-playground client_secret=************&grant_type=refresh_token&refresh_token=1%2FUwvwdXfZ_Ipfie3QdtiA4GomltlI-syaCbh29SbwJ_6A0NOMap3eaFZ435zdBJsW&client_id=407408718192.apps.googleusercontent.com
HTTPレスポンス
HTTP/1.1 200 OK
Content-length: 199
X-xss-protection: 1; mode=block
X-content-type-options: nosniff
Transfer-encoding: chunked
Expires: Mon, 01 Jan 1990 00:00:00 GMT
Vary: Origin, X-Origin
Server: GSE
-content-encoding: gzip
Pragma: no-cache
Cache-control: no-cache, no-store, max-age=0, must-revalidate
Date: Sun, 24 Jun 2018 04:10:48 GMT
X-frame-options: SAMEORIGIN
Alt-svc: quic=":443"; ma=2592000; v="43,42,41,39,35"
Content-type: application/json; charset=UTF-8
{
"access_token": "ya29.GlvkBezttva6sdQ4H-LWRKYFSzIeLMy3vzEwjp3uBvLOqr4IOJVSXZ_SxjcJMsTsrIQhNT5S-f3ih43IU0B9ChpXJN5C3u6cx70zD82VNEPtKugadGQQNmPBUXst",
"token_type": "Bearer",
"expires_in": 3600
}
Step 3Configure request to API
新規スプレッドシートを作成
https://sheets.googleapis.com/v4/spreadsheetsにPOSTメソッドでSend the requestする
HTTPリクエスト
POST /v4/spreadsheets HTTP/1.1 Host: sheets.googleapis.com Content-length: 0 Content-type: application/json Authorization: Bearer ya29.GlvkBezttva6sdQ4H-LWRKYFSzIeLMy3vzEwjp3uBvLOqr4IOJVSXZ_SxjcJMsTsrIQhNT5S-f3ih43IU0B9ChpXJN5C3u6cx70zD82VNEPtKugadGQQNmPBUXst
HTTPレスポンス
HTTP/1.1 200 OK
Content-length: 1134
X-xss-protection: 1; mode=block
Transfer-encoding: chunked
Vary: Origin, X-Origin, Referer
Server: ESF
-content-encoding: gzip
Cache-control: private
Date: Sun, 24 Jun 2018 04:14:33 GMT
X-frame-options: SAMEORIGIN
Alt-svc: quic=":443"; ma=2592000; v="43,42,41,39,35"
Content-type: application/json; charset=UTF-8
{
"spreadsheetId": "121-IGSzxXLscQiP3y3HyXhM--f9q-O_6fNhk1-v9ShE",
"properties": {
"locale": "ja_JP",
"timeZone": "Etc/GMT",
"autoRecalc": "ON_CHANGE",
"defaultFormat": {
"padding": {
"top": 2,
"right": 3,
"left": 3,
"bottom": 2
},
"textFormat": {
"foregroundColor": {},
"bold": false,
"strikethrough": false,
"fontFamily": "arial,sans,sans-serif",
"fontSize": 10,
"italic": false,
"underline": false
},
"verticalAlignment": "BOTTOM",
"backgroundColor": {
"blue": 1,
"green": 1,
"red": 1
},
"wrapStrategy": "OVERFLOW_CELL"
},
"title": "\u7121\u984c\u306e\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8"
},
"sheets": [
{
"properties": {
"sheetType": "GRID",
"index": 0,
"sheetId": 0,
"gridProperties": {
"columnCount": 26,
"rowCount": 1000
},
"title": "\u30b7\u30fc\u30c81"
}
}
],
"spreadsheetUrl": "https://docs.google.com/spreadsheets/d/121-IGSzxXLscQiP3y3HyXhM--f9q-O_6fNhk1-v9ShE/edit"
}
該当のスプレッドシートを確認する
HTTPリクエスト
HTTPレスポンス
HTTPリクエスト
HTTPレスポンス
HTTPリクエスト
HTTPレスポンス
HTTPリクエスト
HTTPレスポンス
HTTPリクエスト
HTTPレスポンス
HTTPリクエスト
HTTPレスポンス
HTTPリクエスト
POST /v4/spreadsheets HTTP/1.1 Host: sheets.googleapis.com Content-length: 0 Content-type: application/json Authorization: Bearer ya29.GlvcBSID_YzBKiDlHpZn-9jg3r-BBUJvYG5HNhYGn3PrOmZWvqHZijkTxde8wTtptBnLHC7Xe1Aoo4aCjMMF8HUQ8SrvVm55NtEFeX22yph598KqXOuSncLCVHaa
HTTPレスポンス
HTTP/1.1 200 OK
Content-length: 1152
X-xss-protection: 1; mode=block
Transfer-encoding: chunked
Vary: Origin, X-Origin, Referer
Server: ESF
-content-encoding: gzip
Cache-control: private
Date: Sat, 16 Jun 2018 05:05:03 GMT
X-frame-options: SAMEORIGIN
Alt-svc: quic=":443"; ma=2592000; v="43,42,41,39,35"
Content-type: application/json; charset=UTF-8
{
"spreadsheetId": "1VfmCpGFTGpSFoY8grUXGXiqb2J1TgSCBFpOO0yjJbss",
"properties": {
"locale": "ja_JP",
"timeZone": "Etc/GMT",
"autoRecalc": "ON_CHANGE",
"defaultFormat": {
"padding": {
"top": 2,
"right": 3,
"left": 3,
"bottom": 2
},
"textFormat": {
"foregroundColor": {},
"bold": false,
"strikethrough": false,
"fontFamily": "arial,sans,sans-serif",
"fontSize": 10,
"italic": false,
"underline": false
},
"verticalAlignment": "BOTTOM",
"backgroundColor": {
"blue": 1,
"green": 1,
"red": 1
},
"wrapStrategy": "OVERFLOW_CELL"
},
"title": "\u7121\u984c\u306e\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8"
},
"sheets": [
{
"properties": {
"sheetType": "GRID",
"index": 0,
"sheetId": 0,
"gridProperties": {
"columnCount": 26,
"rowCount": 1000
},
"title": "\u30b7\u30fc\u30c81"
}
}
],
"spreadsheetUrl": "https://docs.google.com/a/hidev.solutions/spreadsheets/d/1VfmCpGFTGpSFoY8grUXGXiqb2J1TgSCBFpOO0yjJbss/edit"
}
ここ貼り忘れた
HTTPリクエスト
POST /v4/spreadsheets/1VfmCpGFTGpSFoY8grUXGXiqb2J1TgSCBFpOO0yjJbss:batchUpdate HTTP/1.1
Host: sheets.googleapis.com
Content-length: 2892
Content-type: application/json
Authorization: Bearer ya29.GlvcBSID_YzBKiDlHpZn-9jg3r-BBUJvYG5HNhYGn3PrOmZWvqHZijkTxde8wTtptBnLHC7Xe1Aoo4aCjMMF8HUQ8SrvVm55NtEFeX22yph598KqXOuSncLCVHaa
{
"requests": [
{
"addChart": {
"chart": {
"spec": {
"title": "å½èªãç®æ°ãçç§",
"basicChart": {
"chartType": "COLUMN",
"legendPosition": "RIGHT_LEGEND",
"axis": [
{
"position": "BOTTOM_AXIS"
},
{
"position": "LEFT_AXIS"
}
],
"domains": [
{
"domain": {
"sourceRange": {
"sources": [
{
"startRowIndex": 0,
"endRowIndex": 4,
"startColumnIndex": 0,
"endColumnIndex": 1
}
]
}
}
}
],
"series": [
{
"series": {
"sourceRange": {
"sources": [
{
"startRowIndex": 0,
"endRowIndex": 4,
"startColumnIndex": 1,
"endColumnIndex": 2
}
]
}
},
"targetAxis": "LEFT_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"startRowIndex": 0,
"endRowIndex": 4,
"startColumnIndex": 2,
"endColumnIndex": 3
}
]
}
},
"targetAxis": "LEFT_AXIS"
},
{
"series": {
"sourceRange": {
"sources": [
{
"startRowIndex": 0,
"endRowIndex": 4,
"startColumnIndex": 3,
"endColumnIndex": 4
}
]
}
},
"targetAxis": "LEFT_AXIS"
}
],
"headerCount": 1
},
"hiddenDimensionStrategy": "SKIP_HIDDEN_ROWS_AND_COLUMNS"
},
"position": {
"overlayPosition": {
"anchorCell": {
"columnIndex": 5
},
"offsetXPixels": 34,
"offsetYPixels": 15,
"widthPixels": 470,
"heightPixels": 290
}
}
}
}
}
]
}
HTTPレスポンス
HTTP/1.1 200 OK
Content-length: 1376
X-xss-protection: 1; mode=block
Transfer-encoding: chunked
Vary: Origin, X-Origin, Referer
Server: ESF
-content-encoding: gzip
Cache-control: private
Date: Sat, 16 Jun 2018 05:25:45 GMT
X-frame-options: SAMEORIGIN
Alt-svc: quic=":443"; ma=2592000; v="43,42,41,39,35"
Content-type: application/json; charset=UTF-8
{
"spreadsheetId": "1VfmCpGFTGpSFoY8grUXGXiqb2J1TgSCBFpOO0yjJbss",
"replies": [
{
"addChart": {
"chart": {
"chartId": 1861209020,
"position": {
"overlayPosition": {
"anchorCell": {
"columnIndex": 5
},
"widthPixels": 470,
"offsetYPixels": 15,
"offsetXPixels": 34,
"heightPixels": 290
}
},
"spec": {
"hiddenDimensionStrategy": "SKIP_HIDDEN_ROWS_AND_COLUMNS",
"titleTextFormat": {
"fontFamily": "Roboto"
},
"fontName": "Roboto",
"basicChart": {
"domains": [
{
"domain": {
"sourceRange": {
"sources": [
{
"endRowIndex": 4,
"startRowIndex": 0,
"startColumnIndex": 0,
"endColumnIndex": 1
}
]
}
}
}
],
"chartType": "COLUMN",
"legendPosition": "RIGHT_LEGEND"
},
"title": "\u56fd\u8a9e\u3001\u7b97\u6570\u3001\u7406\u79d1"
}
}
}
}
]
}
