SpreadsheetをRubyで操作する
はじめに
Reporterを使ってiTunes ConnectのSales and Trendsを自動で取得する - 設計編で実装指針としては書いていたGoogle SpreadsheetとRubyの連携について書きます。
なお環境は下記の通りです。
環境 | version |
---|---|
OS | OS X EL Capitan 10.11.6 |
ruby | 2.3.1(rbenv) |
ライブラリ(gem)
google_driveというgemを利用します。参考ページは下記の通りです。
- Authorization(service account)
- Example to read/write spreadsheets
- API Documents
- RubyでOAuth認証なしにGoogleSpreadSheetのデータを取得
実装
事前準備
誰でも好き勝手にSpreadsheetを操作できるようにしてしまうのはまずいので認証が必要です。そのための準備をします。
サービスアカウントの作成
On behalf of no existing users (service account)こちらの手順に従ってもらえれば、認証用のjsonファイルがダウンロードされます。
スプレッドシートの共有設定
先ほど作成したサービスアカウントのメールアドレス(jsonに書いてあります)を操作対象のSpreadsheetで共有します。
bundlerのインストール
gemの管理をbundlerを利用したいのでbundlerをinstallします。
$ cd path/to/directory
$ rbenv exec gem install bundler
google_driveのインストール
Gemfileというファイルを作成して下記を書き込みます。
source 'https://rubygems.org' gem 'google_drive'
そしてgemをインストールします。
$ bundle install --path vendor/bundle
Example実装
Example to read/write spreadsheetsを実行してみます。
準備
- サービスアカウントを作成した時にダウンロードしたjsonをrubyプログラムと同じディレクトリに
config.json
という名前で置きます - 操作対象のスプレッドシートキーを取得します
https://docs.google.com/spreadsheets/d/<スプレッドシートキー>/edit
みたいな感じになってるのでそこから取得できます(ちゃんとどこかに書いてあるとは思いますが...)
サンプルコード
自分はgithubのコードにrequire 'bundler/setup'
を追加しないと動きませんでした。またsessionの作り方もサービスアカウントを使う場合に書き換えています。
require 'bundler/setup' require "google_drive" # Creates a session. This will prompt the credential via command line for the # first time and save it to config.json file for later usages. session = GoogleDrive::Session.from_service_account_key("config.json") # First worksheet of # https://docs.google.com/spreadsheet/ccc?key=pz7XtlQC-PYx-jrVMJErTcg # Or https://docs.google.com/a/someone.com/spreadsheets/d/pz7XtlQC-PYx-jrVMJErTcg/edit?usp=drive_web ws = session.spreadsheet_by_key(<スプレッドシートキー>).worksheets[0] # Gets content of A2 cell. p ws[2, 1] #==> "hoge" # Changes content of cells. # Changes are not sent to the server until you call ws.save(). ws[2, 1] = "foo" ws[2, 2] = "bar" ws.save # Dumps all cells. (1..ws.num_rows).each do |row| (1..ws.num_cols).each do |col| p ws[row, col] end end # Yet another way to do so. p ws.rows #==> [["fuga", ""], ["foo", "bar]] # Reloads the worksheet to get changes by other clients. ws.reload
APIピックアップ
Session作成
該当箇所: session = GoogleDrive::Session.from_service_account_key("config.json")
GoogleDrive/Sessionにあるように認証のやり方でメソッドが変わります。今回はサービスアカウントを利用したためExampleとは違い.from_service_account_key(json_key_path_or_io, scope = DEFAULT_SCOPE) ⇒ Object
を利用しました。
Spreadsheetの取得
該当箇所: session.spreadsheet_by_key(<スプレッドシートキー>)
GoogleDriveとかから見えるスプレッドシートの単位だと思います。キー以外にtitleとurlからも取得できます(参考)。
WorkSheet取得
該当箇所: ws = session.spreadsheet_by_key(<スプレッドシートキー>).worksheets[0]
スプレッドシート内でのシート(画面左下の+ボタンで追加できるもの)のことのようです。サンプルでは0番目のシートを取得していますが、titleやgid(urlを見るとわかります)などから取得することも可能です(参考)。
WorkSheetの操作
セルの値取得
例えば C2の値をとるならば c2 = ws[2, 3]
とやれば取れます。簡単ですね。
セルの値変更
C2に"てくてくテック"と入れる場合は下記のようになります。
ws[2, 3] = "てくてくテック" ws.save
最後の行や列の番号
ws.max_cols
やws.max_rows
で取得できます
行の挿入
#insert_rows(row_num, rows) ⇒ Object
を使います。row_num行目にrows行追加します。
e.g(ドキュメントより)
# Inserts 2 empty rows before row 3. worksheet.insert_rows(3, 2) # Inserts 2 rows with values before row 3. worksheet.insert_rows(3, [["a, "b"], ["c, "d"]])