Googleスプレッドシートをプログラムから操作

2017/05/18
APIの高速化方法について追記記事を書きました。
[GoogleスプレッドシートAPIの高速化](http://qiita.com/howdy39/items/2fb95bb7b52cb0bb00ab)

---

GoogleI/O 2016で[Spread Sheet API v4](https://developers.google.com/sheets/)がリリースされました。
動画は[こちら](https://www.youtube.com/watch?v=Gk-xpjgUwx4)です。
盛り上がるのはAndroidなどデバイスにかかわる話だったり新サービスだったりするのですが、私が一番盛り上がったのはこのリリースだったりします:smile: 
簡潔に書くと **今までの貧弱だったAPIがものすごく強化されシステムに組み込めるレベルになりました。**

本記事ではスプレッドシートをプログラムから操作する方法と、実際に何ができるのかを解説していきます。

サンプルとして以下の様なスプレッドシートを作成します。
![updatespreadsheets.gif](https://qiita-image-store.s3.amazonaws.com/0/35595/ebafc446-162f-210b-766c-9cbff399a529.gif)



## スプレッドシートAPIとは
その名の通り[Googleスプレッドシート](https://www.google.com/intl/ja_jp/sheets/about/)をWebシステムやスマホアプリなどプログラム経由で操作することが可能なAPIです。
元々[バージョン3](https://developers.google.com/google-apps/spreadsheets/)があったのですがとても貧弱でした。
今回リリースされたバージョン4でブラウザ上でできること大抵できる様になりました。




## できること

### データ系
ファイルの作成や値の取得・変など。

* スプレッドシートの作成
* スプレッドシートのファイル名などの変更
* シートのコピー
* セルの書式・値・枠線・検証・メモ
* セルの結合
* 条件付き書式
* 行・列のサイズ変更や挿入・削除など
* フィルタ・フィルタ表示
* 名前付き範囲・保護範囲
* グラフ

### 操作系
* オートフィル (AutoFillRequest)
* コピー・切り取り・貼り付け (CutPasteRequest, CopyPasteRequest)
* シートの検索→置換 (FindReplaceRequest)
* CSVやHTMLの特殊貼り付け (PasteDataRequest)
* セルに格納されたCSVなどの分割 (TextToColumnsRequest)
* 指定範囲をソート (SortRangeRequest)

## できないこと
* 図形描画・画像の作成（移動・リサイズ・削除はブラウザ画面で対象のオブジェクトを適当に動かして、HTTPリクエストを覗いてオブジェクトIDを見れば一応出来ます・・・）


## とりあえず試してみよう
細かいことは後から解説することにして実際に試してみます。

本来APIを実行するアプリケーションをGoogleに自分で登録しないといけません。
ただちょっとAPIを試すときは面倒です。
そのため本記事では[OAuth Playground](https://developers.google.com/oauthplayground/)というAPIの検証や実行が簡単にできるサイトを利用します。



## 認可まで
APIを実行するプログラム（システム）に対して自分が持つスプレッドシートへのアクセスを許可しなければなりません。
OAuth Playgroundというシステムに対してAPI実行を許可します。

### Step 0 OAuth Playgroundにアクセス
ブラウザから[OAuth Playground](https://developers.google.com/oauthplayground/)にアクセス

### Step 1 使うAPIを選択&認可
画面左上にAPIを選ぶボックスがありますが、`Spreadsheet v3`となっており古いAPIしか選べません。
以下の`Spreadsheet v4`用の文字列(Scope)を直接入れて`Authorize API`を押下

```Scope
https://www.googleapis.com/auth/spreadsheets
```

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/d9eefacc-e845-30fb-eb37-8e690dc26753.png)


認可を押下
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/379d1c65-de6d-9f88-5828-6a83b8ec7074.png)



### Step 2 認可コードをアクセストークンに変換
`Exchange authorization code fortokens`を押下します。

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/0f3a62de-60d8-dbe4-2753-e2983644e951.png)


アクセストークンに交換できました。
このアクセストークンは自身のスプレッドシートへのAPIアクセスが許可されているトークンです。

※この画面はすぐ閉じられてStep 3の画面に勝手に遷移します。Step 2を押して再表示できます。
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/1a9a7203-d1ce-1a75-8cf7-86d1d29e4dc9.png)




#### APIが急に使えなくなった場合
アクセストークンは1時間で使えなくなってしまうので期限が切れたら使えなくなります。
横の`Refresh access token`ボタンを押すとアクセストークンを再取得できます。
リフレッシュトークンというのを使ってアクセストークンを取得しているのですが、OAuth playgroundのリフレッシュトークンも無効化されることがあります。
その場合Step1からやりなおしてください。


### 準備完了
この画面が表示されればAPIを実行する準備が完了です。
枠内に実行したいAPIの内容を入力していきます。
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/be42615c-c18e-0ef3-94d2-cd223f1a1b3d.png)



## APIを実行して簡単な表を作る
スプレッドシートをAPIから作って簡単な表とグラフを作ってみます。
ありがちな例で成績表でも作ってみます。
※APIリクエストの解説は作成後にするのでとりあえず実行して雰囲気を掴んでみてください。


### スプレッドシートを作成するAPIを実行

#### Request
Http Methodを`POST`にする
Request URIに`https://sheets.googleapis.com/v4/spreadsheets`を入力

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/63440e99-38db-96f1-411c-5833437f391b.png)



#### Response
`HTTP/1.1 200 OK`と出ています。成功したということですね。

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/760f3023-c8bf-d8ef-2722-8973f0a6d072.png)


切れていますが下の方に出ている情報がスプレッドシート全体の情報です。
このレスポンスの`spreadsheetId`が重要です。スプレッドシートのファイルを特定する世界でユニークなキーになります。

```json:スプレッドシート作成時のレスポンス
{
  "spreadsheetId": "1C43NYMcg80QDmWt5tBZQbVgGx4qv4N6-T_x7VUTp4ko", 
  "properties": {
    "locale": "ja_JP", 
    "timeZone": "Asia/Tokyo", 
    "autoRecalc": "ON_CHANGE", 
...
```


#### 画面で確認
自身の[マイドライブ](https://drive.google.com/drive/my-drive)に作られます。
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/be175209-3185-4c90-74a2-47c509f2eee2.png)


作った直後はなにも書かれていません。
![Kobito.nRWBZV.png](https://qiita-image-store.s3.amazonaws.com/0/35595/31d6cfcf-8997-5e70-8094-75c653235787.png "Kobito.nRWBZV.png")

またこのファイルを開いた際のURLを見てください。
`https://docs.google.com/spreadsheets/d/1C43NYMcg80QDmWt5tBZQbVgGx4qv4N6-T_x7VUTp4ko/edit#gid=0`のようになっていると思います
この数字と文字列の羅列は先ほど出てきた`spreadsheetId`になっています。
gidが`sheetId`です。シートを特定するためのIDです。

#### Tips マイドライブ以外の場所に作りたい
スプレッドシートAPIはあくまでスプレッドシートを操作するAPIです。
`マイドライブ以外に作る`だったり`ファイルの共有をする`などスプレッドシートではなくGoogleドライブの機能になる範囲は[Drive API](https://developers.google.com/drive/)を使います。




### スプレッドシートのファイル名を変更する
無題のスプレッドシートから成績表に変更します。

#### Request
Http Methodは`POST`
Request URIに`https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate`
{spreadsheetId}は先ほどファイルを作った時のレスポンスに記載されています。

例:`https://sheets.googleapis.com/v4/spreadsheets/1C43NYMcg80QDmWt5tBZQbVgGx4qv4N6-T_x7VUTp4ko:batchUpdate`

※以降は全て上記部分は固定のため記載を省略します

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/fa137be2-f9d7-283b-21d4-d66efc674f8b.png)



`Enter request body`を押下して以下の内容を入力

```json:body
{
  "requests": [
    {
      "updateSpreadsheetProperties": {
        "properties": {
          "title": "成績表"
        },
        "fields": "title"
      }
    }
  ]
}
```

![Kobito.79hnU6.png](https://qiita-image-store.s3.amazonaws.com/0/35595/cc083f7c-57d5-c6b4-c384-4e4f4135ee7b.png "Kobito.79hnU6.png")



#### Response
特に重要ではないので省略
※以降は記載を省略します。


#### API実行後の画面
ファイル名が書き換わっていますね。

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/6fe6b1fa-3d4f-a03b-d1fd-4795fb8e85df.png)




### データを書き込む
ここからはリクエストのbodyだけ変更して投げていくだけです。
サイズが大きいのでgistでupします。そちらからコピペして実行してください。

#### Request body
https://gist.github.com/howdy39/c1d8133f7d0c21ef83baab3085490338#file-updatecells-json


#### API実行後の画面
![Kobito.ISvGkN.png](https://qiita-image-store.s3.amazonaws.com/0/35595/6e8af647-ea4f-0c2e-1c12-e30b2a083124.png "Kobito.ISvGkN.png")



### 枠線を引く
枠線を入れます。

#### Request body
https://gist.github.com/howdy39/c1d8133f7d0c21ef83baab3085490338#file-updateborders-json

#### API実行後の画面
![Kobito.YuRbvu.png](https://qiita-image-store.s3.amazonaws.com/0/35595/b0982fe4-8c83-ab81-e8f4-7f82b0d38b1a.png "Kobito.YuRbvu.png")


### 条件付き書式を入れる
50点以下を赤点とみなし、色付けしてみます。

#### Request body
https://gist.github.com/howdy39/c1d8133f7d0c21ef83baab3085490338#file-addconditionalformatrule-json

#### API実行後の画面
![Kobito.YnIJ5P.png](https://qiita-image-store.s3.amazonaws.com/0/35595/ca207dd3-65e1-ceb3-6ff2-0fd9a723a0a8.png "Kobito.YnIJ5P.png")



### グラフを入れる
棒グラフを作ってみます。

#### Request body
https://gist.github.com/howdy39/c1d8133f7d0c21ef83baab3085490338#file-addchart-json

#### API実行後の画面
![Kobito.zLTcjK.png](https://qiita-image-store.s3.amazonaws.com/0/35595/8cc9f219-238d-8108-9022-bcc8dca7c57b.png "Kobito.zLTcjK.png")



## APIの解説
詳細は[公式の解説](https://developers.google.com/sheets/guides/concepts)を見てください。
ここでは重要な部分のピックアップとAPIの調べ方のコツを書いておきます。
APIの調べ方がわかっていれば自分で調べられますし:+1: 



### Requestの概要
リクエストは以下の記述になります。
スプレッドシートのプロパティを書き換えるときは`updateSpreadsheetProperties`を使いました。
またfieldsプロパティは値を更新するときに必要なようです。

```json:リクエストの内容
{
  "requests": [
    {
      "リクエスト命令の種類": {}
    }
  ]
}
```



### 調べ方 正攻法
リファレンスから調べます。
[Batch update operations](https://developers.google.com/sheets/guides/batchupdate#batch_update_operations)


Objectが変更したい内容で右の3列が操作種別です。
ここからリンクを辿って入れるべき値を調べていきます。
![Kobito.OCJavm.png](https://qiita-image-store.s3.amazonaws.com/0/35595/ae4290ca-ed5b-a76c-b87b-476d2855f01e.png "Kobito.OCJavm.png")



### 調べ方 コツ
実はほとんど項目を調べていません。
特にグラフなんて絶対に自分で作れる気がしないです。:sweat_smile: 
どうしているかというと以下のように調べています。

1.スプレッドシートの情報を画面からAPIで抜き出す
2.ブラウザから直接スプレッドシートを操作してAPIで行いたい作業をする
3.スプレッドシートの情報を画面からAPIで抜き出す
4.1と3の差分を見る

差分を見ています。
そこから設定したい項目の情報をリクエスト情報に入れていました。

#### スプレッドシートの情報をAPIで抜き出す方法
Http Methodを`GET`にする
Request URIに`https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}?includeGridData=true`を入力



## 操作系 解説
操作系の命令は上で書いたコツが使えないのではまりそうなとこだけメモしておきます。

### [AutoFillRequest](https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#autofillrequest)
オートフィルです。
セルの右下をドラッグするやつですね。

```
{
  "useAlternateSeries": boolean,
  "range": {
    object(GridRange)
  },
  "sourceAndDestination": {
    "source": {
      object(GridRange)
    },
    "dimension": enum(Dimension),
    "fillLength": number,
  },
}
```

#### useAlternateSeries
オートフィル実行時にwinだとctrl,macだとoptionを押しながら行うと挙動が変わりますがそれと同じ挙動をさせるかどうかです。

#### rangeとsourceAndDestination
どちらかを利用します、両方書くのはNGです。
rangeが1つのセル選択からどこまでの範囲に対してオートフィルを行うかを指定。
sourceAndDestinationは複数のセル範囲から行方向か列方向のどちらかに何セル分オートフィルを行うかというような指定の違いがあります。


###[FindReplaceRequest](https://developers.google.com/sheets/guides/batchupdate#batch_update_operations)
文字列の検索→置換です。

```
{
  "find": string,
  "replacement": string,
  "matchCase": boolean,
  "matchEntireCell": boolean,
  "searchByRegex": boolean,
  "includeFormulas": boolean,

  // Union field scope can be only one of the following:
  "range": {
    object(GridRange)
  },
  "sheetId": number,
  "allSheets": boolean,
  // End of list of possible types for union field scope.
}

```


#### matchCase, matchEntireCell, searchByRegex, includeFormulas
画面の以下の項目と同じです。
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/1aeb932c-778f-001e-3025-38aba8636628.png)

#### range, sheetId, allSheets
いずれか一つを指定します。画面の以下の項目と一緒です。
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/d1730b4b-53b7-4a6c-b2c7-07f64f7e400b.png)


### [PasteDataRequest](https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#pastedatarequest)
ファイル→インポートに近いですが少し違います。
ファイルではなく**文字列**の特殊貼り付けって感じです。

```
{
  "coordinate": {
    object(GridCoordinate)
  },
  "data": string,
  "type": enum(PasteType),

  // Union field kind can be only one of the following:
  "delimiter": string,
  "html": boolean,
  // End of list of possible types for union field kind.
}
```


#### delimiter,html
どちらかを指定。
delimiterに","とか入れればCSVが読み込めます。

htmlにtrueでhtmlを文字列にして読み込めます。
例）`<p>1st</p><br>2nd<br>3rd`を指定
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/160f4686-e5e7-ac43-fab8-262d18cbd262.png)



### [TextToColumnsRequest](https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#pastedatarequest)
PasteDataRequestに似てますがすでにセルに格納されている文字列を対象に列に展開します。

```
{
  "source": {
    object(GridRange)
  },
  "delimiter": string,
  "delimiterType": enum(DelimiterType),
}
```

例
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/2975e0a6-4297-2ac2-794f-094377b263b3.png)

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/18135319-44d8-cfa1-a1f0-65edf0b7a814.png)




###[SortRangeRequest](https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#sortrangerequest)
指定範囲をソートします。

例）A1:B4を範囲にしてA列で昇順
![image](https://qiita-image-store.s3.amazonaws.com/0/35595/4f7b372f-272c-f845-9f9b-d9194cbf671c.png)

![image](https://qiita-image-store.s3.amazonaws.com/0/35595/8c512c93-2259-0b93-6728-26b5a0df69a3.png)



## 実際に作って見ようとする方へ
ここまで読んでじゃあ作ってみようかとなった方向けに書いておきます

### プロジェクトの作り方や認可の仕組み的な話
自身でアプリケーションを作る場合、Googleに登録する必要があります。
[面倒な集計は自動化してGoogleスプレッドシートに書き出す方法](https://qiita.com/kinpira/items/00b9567cec17bee635d5)

また認可の仕組みなどもある程度知っておいた方が良いです。
[Google APIのAccess Tokenをお手軽に取得する](http://qiita.com/shin1ogawa/items/49a076f62e5f17f18fe5)

パフォーマンスとか気にするレベルでやることになったら見といたほうが良いです。
[GoogleAPIを高速化しよう](http://qiita.com/howdy39/items/2eca13609901a22fcbb8)



### ライブラリ
Google APIはライブラリが用意されています。
[ガイドページ](https://developers.google.com/sheets/guides/concepts)の左のメニューにあります。

基本的には使ったほうが楽です。
ただ`Spread Sheet API v4`は出たばかりなのでまだ用意されてない言語が多いと思います。
現状使うならアクセストークンを取るとこまでライブラリでやって、API実行は自前のプログラム発行が無難かと思います。

### codelab
codelabに実際のアプリの例が登録されています。
https://codelabs.developers.google.com/codelabs/sheets-api/index.html#0

ただ個人的にですが、APIの使ってもらうための資料としてはうーん、、という感じでした。。
Webフロントエンド、Google API JavaScript Client、NodeJSに詳しい人にこれは良いかもかもしれませんが、そうじゃない人には苦痛かなぁと。



## あとがき
本記事内では簡単な表しか作っていませんが、提供されている機能を利用すれば帳票レベルのリッチな内容が作れそうです。

またGoogleスライドを同様に操作できる`Slide API`を開発中だそうです。
これができると○○報告書みたいなプレゼン資料もプログラムから作れるようになりますね。