LoginSignup
642
636

More than 1 year has passed since last update.

@howdy39(Tatsuya Nakano)

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

Last updated at Posted at 2016-05-21

2017/05/18
APIの高速化方法について追記記事を書きました。
GoogleスプレッドシートAPIの高速化

GoogleI/O 2016でSpread Sheet API v4がリリースされました。
動画はこちらです。
盛り上がるのはAndroidなどデバイスにかかわる話だったり新サービスだったりするのですが、私が一番盛り上がったのはこのリリースだったりします:smile:
簡潔に書くと 今までの貧弱だったAPIがものすごく強化されシステムに組み込めるレベルになりました。

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

サンプルとして以下の様なスプレッドシートを作成します。
updatespreadsheets.gif

スプレッドシートAPIとは

その名の通りGoogleスプレッドシートをWebシステムやスマホアプリなどプログラム経由で操作することが可能なAPIです。
元々バージョン3があったのですがとても貧弱でした。
今回リリースされたバージョン4でブラウザ上でできること大抵できる様になりました。

できること

データ系

ファイルの作成や値の取得・変など。

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

操作系

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

できないこと

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

とりあえず試してみよう

細かいことは後から解説することにして実際に試してみます。

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

認可まで

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

Step 0 OAuth Playgroundにアクセス

ブラウザからOAuth Playgroundにアクセス

Step 1 使うAPIを選択&認可

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

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

image

認可を押下
image

Step 2 認可コードをアクセストークンに変換

Exchange authorization code fortokensを押下します。

image

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

※この画面はすぐ閉じられてStep 3の画面に勝手に遷移します。Step 2を押して再表示できます。
image

APIが急に使えなくなった場合

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

準備完了

この画面が表示されればAPIを実行する準備が完了です。
枠内に実行したいAPIの内容を入力していきます。
image

APIを実行して簡単な表を作る

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

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

Request

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

image

Response

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

image

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

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

画面で確認

自身のマイドライブに作られます。
image

作った直後はなにも書かれていません。
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を使います。

スプレッドシートのファイル名を変更する

無題のスプレッドシートから成績表に変更します。

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

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

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

Kobito.79hnU6.png

Response

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

API実行後の画面

ファイル名が書き換わっていますね。

image

データを書き込む

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

Request body

API実行後の画面

Kobito.ISvGkN.png

枠線を引く

枠線を入れます。

Request body

API実行後の画面

Kobito.YuRbvu.png

条件付き書式を入れる

50点以下を赤点とみなし、色付けしてみます。

Request body

API実行後の画面

Kobito.YnIJ5P.png

グラフを入れる

棒グラフを作ってみます。

Request body

API実行後の画面

Kobito.zLTcjK.png

APIの解説

詳細は公式の解説を見てください。
ここでは重要な部分のピックアップとAPIの調べ方のコツを書いておきます。
APIの調べ方がわかっていれば自分で調べられますし:thumbsup:

Requestの概要

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

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

調べ方 正攻法

リファレンスから調べます。
Batch update operations

Objectが変更したい内容で右の3列が操作種別です。
ここからリンクを辿って入れるべき値を調べていきます。
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

オートフィルです。
セルの右下をドラッグするやつですね。

{
  "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

文字列の検索→置換です。

{
  "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

range, sheetId, allSheets

いずれか一つを指定します。画面の以下の項目と一緒です。
image

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

TextToColumnsRequest

PasteDataRequestに似てますがすでにセルに格納されている文字列を対象に列に展開します。

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

例
image

image

SortRangeRequest

指定範囲をソートします。

例）A1:B4を範囲にしてA列で昇順
image

image

実際に作って見ようとする方へ

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

プロジェクトの作り方や認可の仕組み的な話

自身でアプリケーションを作る場合、Googleに登録する必要があります。
面倒な集計は自動化してGoogleスプレッドシートに書き出す方法

また認可の仕組みなどもある程度知っておいた方が良いです。
Google APIのAccess Tokenをお手軽に取得する

パフォーマンスとか気にするレベルでやることになったら見といたほうが良いです。
GoogleAPIを高速化しよう

ライブラリ

Google APIはライブラリが用意されています。
ガイドページの左のメニューにあります。

基本的には使ったほうが楽です。
ただ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を開発中だそうです。
これができると○○報告書みたいなプレゼン資料もプログラムから作れるようになりますね。

642
636
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
642
636