Ruby から Google スプレッドシートを生成する
一連の手順
サービスアカウントの準備
- https://console.cloud.google.com/ → 左上プルダウン → 新しいプロジェクト
- API とサービス → ライブラリ → Drive API と Sheets API を有効化
- API とサービス→ 認証情報 → 認証情報を作成 でサービスアカウントとやらを作って JSON をダウンロードする (google_acount.json にリネームしておく)
スプレッドシート生成
続いて関連 gem をインストールする。
gem i google-api-client
require する。
require "google/apis/sheets_v4"
require "google/apis/drive_v3"
require "googleauth"
認証する。
json_key_io = StringIO.new(Pathname("./google_acount.json").expand_path.read)
scope = [Google::Apis::SheetsV4::AUTH_SPREADSHEETS, Google::Apis::DriveV3::AUTH_DRIVE_FILE]
authorizer = Google::Auth::ServiceAccountCredentials.make_creds({json_key_io: json_key_io, scope: scope})
authorizer.fetch_access_token!
json_key_io は File.open("google_acount.json") でもよいが、Rails Credentials などを想定すれば JSON 文字列を StringIO でラップする方が扱いやすい。
続いてスプレッドシートを扱う何かを用意し、
sheets_service = Google::Apis::SheetsV4::SheetsService.new
sheets_service.authorization = authorizer
スプレッドシートを作る。
spreadsheet = Google::Apis::SheetsV4::Spreadsheet.new(properties: {title: "新しいスプレッドシート"})
spreadsheet = sheets_service.create_spreadsheet(spreadsheet)
このとき、Spreadsheet クラスからインスタンスを作った時点でスプレッドシートが作れたように感じるが、それはただの引数の入れ物なので、さらに SheetsService#create_spreadsheet に渡さないといけない。
これでもうできた。スプレッドシートの URL や ID もわかる。
spreadsheet.spreadsheet_url # => "https://docs.google.com/spreadsheets/d/1JSJw58ioUtU3KGYNiHFYvVjxKfi1fevqvVRgvkKfeKw/edit"
spreadsheet.spreadsheet_id # => "1JSJw58ioUtU3KGYNiHFYvVjxKfi1fevqvVRgvkKfeKw"
見れない問題
ところがアクセスすると認証を求められる。
require "faraday"
Faraday.get(spreadsheet.spreadsheet_url).status # => 401
自分で作ったのになぜ認証を求められるのかはわからない。
そこで Google Drive を扱う何かを用意し、
drive_service = Google::Apis::DriveV3::DriveService.new
drive_service.authorization = authorizer
「誰でも URL を知っていれば見れる」モードにする。
permission = Google::Apis::DriveV3::Permission.new(type: "anyone", role: "reader")
drive_service.create_permission(spreadsheet.spreadsheet_id, permission)
これで自分も見れるようになる。
Faraday.get(spreadsheet.spreadsheet_url).status # => 200
セルに書き込む
次にハッシュの配列を表形式で書き込む。
rows = [
{ "ID" => 1, "名前" => "ありす", },
{ "ID" => 2, "名前" => "ぼぶ", },
{ "ID" => 3, "名前" => "きゃろる", },
]
を、単に配列の配列に変換し、
values = [rows.first.keys] + rows.collect(&:values)
values # => [["ID", "名前"], [1, "ありす"], [2, "ぼぶ"], [3, "きゃろる"]]
範囲形式も作る。
width = values.first.size # => 2
height = values.size # => 4
range = "Sheet1!R1C1:R#{height}C#{width}" # => "Sheet1!R1C1:R4C2"
R1C1:R4C2 は縦が 1..4 で横が 1..2 の範囲という意味になる。そんなん values を見ればわかるじゃんという気もするが、左上からとは限らないので正確に明示しないといけないようだ。
それらを引数にして書き込む。
value_range = Google::Apis::SheetsV4::ValueRange.new(range: range, values: values)
sheets_service.update_spreadsheet_value(spreadsheet.spreadsheet_id, range, value_range, value_input_option: "RAW")
ブラウザで開く。
system "open #{spreadsheet.spreadsheet_url}"
するとこうなる。
簡単に使う
以上をブラックボックス化して
MyGoogleSheet.new(rows).call
MyGoogleSheet の中身
# gem i google-api-client
require "google/apis/sheets_v4"
require "google/apis/drive_v3"
require "googleauth"
class MyGoogleSheet
def initialize(rows)
@rows = rows
end
def call
values = [@rows.first.keys] + @rows.collect(&:values)
width = values.first.size
height = values.size
range = "Sheet1!R1C1:R#{height}C#{width}"
value_range = Google::Apis::SheetsV4::ValueRange.new(range: range, values: values)
sheets_service.update_spreadsheet_value(spreadsheet.spreadsheet_id, range, value_range, value_input_option: "RAW")
system "open #{spreadsheet.spreadsheet_url}"
end
private
def spreadsheet
@spreadsheet ||= yield_self do
spreadsheet = Google::Apis::SheetsV4::Spreadsheet.new(properties: {title: "新しいスプレッドシート"})
spreadsheet = sheets_service.create_spreadsheet(spreadsheet)
permission = Google::Apis::DriveV3::Permission.new(type: "anyone", role: "reader")
drive_service.create_permission(spreadsheet.spreadsheet_id, permission)
spreadsheet
end
end
def sheets_service
@sheets_service ||= Google::Apis::SheetsV4::SheetsService.new.tap do |e|
e.authorization = authorizer
end
end
def drive_service
@drive_service ||= Google::Apis::DriveV3::DriveService.new.tap do |e|
e.authorization = authorizer
end
end
def authorizer
@authorizer ||= yield_self do
json_key_io = StringIO.new(Pathname("./google_acount.json").expand_path.read)
scope = [Google::Apis::SheetsV4::AUTH_SPREADSHEETS, Google::Apis::DriveV3::AUTH_DRIVE_FILE]
authorizer = Google::Auth::ServiceAccountCredentials.make_creds({json_key_io: json_key_io, scope: scope})
authorizer.fetch_access_token!
authorizer
end
end
end
だけで実行できるようにしておき、Array#display にひっかけておくと、
Array.class_eval do
def display
MyGoogleSheet.new(self).call
end
end
さっきのはこれだけでよくなる。
[
{ "ID" => 1, "名前" => "ありす", },
{ "ID" => 2, "名前" => "ぼぶ", },
{ "ID" => 3, "名前" => "きゃろる", },
].display
また Hash にもひっかけておくと
Hash.class_eval do
def display
collect { |k, v| { "Key" => k, "Value" => v } }.display
end
end
同様に内容を見ることができる。
ObjectSpace.count_objects.display
考えられる用途
対 Excel 人間
IT 業界にはスプレッドシートを標準的なテキストエディタだと思っている人たちが少なくない。私のような立場ではそのような人たちに合わせなければならない場合が多々あるため、一気に変換できればストレスも減る。
管理画面を作り込まないために
WEB で表形式を作り込むのは面倒な上に終わりがない。その上、いくらがんばったところで表計算ソフトウェアの機能性には敵わない。したがって利用者には簡潔な画面を出し、カスタマイズが必要であれば Google スプレッドシート上で自由にやってくださいという体にして逃げる。
CSV の代替として
CSV は文字コードの悩みがある。また利用者は結局スプレッドシートに取り込むのが目的だったりするため、それならば最初からスプレッドシートとして提供すればお互い楽である。相手が渋ったときは「Google スプレッドシートからは、CSV はもちろん PDF や Excel にも一発でエクスポートできますよ」でよい。
Discussion