まだエクセルでレポート出力に消耗してるの？

エンジニアをしているとしばしば下記に遭遇する
現場からの
・「エクセルのテンプレートあるからデータいれて出力してよ」
・「エクセル出力とpdfにも出力したいんだよね〜」

なぜエンジニアが消耗してしまうのか？

• テンプレートとして用意されたものに「条件付きの色指定」とか「関数が組み込んであるもの」はまだ可愛い
  • マクロがゴリゴリ組んであったり、グラフが埋め込まれていると触ろうとしても対応していないライブラリとががある。辛い
• macとwindowsで印刷範囲設定とか色合いなどちょっと違うところがあるので見比べてデバッグ。辛い
• 最終的に使用している言語では出来なくてjavaのpoiに頼る

とにかく僕にとってエクセルを扱うのは鳥肌もの

本題：それってGoogleのSpreadsheetで出来るんじゃない？？？？

古き資産が残る会社にはexcelですべてやる傾向があるらしいが、最近はgoogleのspreadsheetを活用している会社を見たりする。
『ファイナルファンタジー レコードキーパー』の作り方：イベントレポート
spreadsheetにすればapi経由でデータを触れるし、現場に納得してもらえれば幸せになれる。

環境

• mac 10.10.2
• 言語：ruby
• 使用gem: google_drive

事前準備

• driveを扱う際に事前に下記でパラメータの指定をテストしておくと毎回テストしなくても済むので幸せになれます。
  • 試しに叩いてみる
• googleのapiを使用するので許可をしておく参考サイト
• 下記のコードは今から紹介するすべてに共通するので予め定義しておく

ruby

require "google/api_client"
require "google_drive"
session = GoogleDrive.saved_session({
  client_id: '*****************.apps.googleusercontent.com',
  client_secret: '**************' 
}.to_json)

色々触ってみる

指定ディレクトリ配下のファイル一覧を取得する

googledriveは通常の階層構造ではなくてそれぞれのディレクトリがIdで管理されている。
idはurlから簡単に取得できる
https://drive.google.com/drive/u/0/folders/[ここがid]

ruby

session.files(q: "'[ここにidいれる]' in parents").each do |file|
   p file.title
end

ファイルをコピーする

gemにもcopyというメソッドが存在するのだが、ファイル名しか指定できない。
つまり同じ階層にファイルがコピーされてしまう。
仮にディレクトリを指定しておきたい場合は下記を使用すると良さそう。

ruby

# ファイルを指定する。他にもfile_by_urlとかfile_by_idとかある
file = session.file_by_name('********')
copied_file = session.drive.files.copy.request_schema.new({
    title: '[コピー先のファイル名]',
    parents: [{id: '[コピー先のディレクトリのid]'}]
})
api_result = session.execute!(
    :api_method => session.drive.files.copy,
    :body_object => copied_file,
    :parameters => {"fileId" => file.id},
)
# spreadsheetをコピーしたらGoogleDrive::Spreadsheetがかえる
session.wrap_api_file(api_result.data)

フォルダ作成

ruby

file = session.drive.files.insert.request_schema.new({
    "title" => '[フォルダの名前]',
    "mimeType" => "application/vnd.google-apps.folder",
    parents: [{id: '[配下に置きたいディレクトリのid]'}]
})
api_result = session.execute!(
    :api_method => session.drive.files.insert,
    :body_object => file)
dir = session.wrap_api_file(api_result.data)
dir.id
=> フォルダのidが取得できる

シートに値を読み書きする

[y軸, x軸]で値を読み書きする

ruby

file = session.file_by_id('[ファイルのid]')
file.worksheets.each_with_index do |worksheet, idx|
  # A1に値を書き込む
  worksheet[1, 1] = "fuga#{idx}"
  # saveをしないと書き込まれないので注意
  worksheet.save
end

file.worksheets.each_with_index do |worksheet, idx|
  # A1に値を表示する
  p worksheet[1, 1]
end

シートを削除する

ruby

file = session.file_by_id('[ファイルのid]')
file.worksheets.each do |worksheet|
  if worksheet.title.match(/\Atemplate\d+\z/)
    worksheet.delete
  end
end

iframeとかで埋め込む用のurl取得

ruby

file = session.file_by_id('***************')
p file.embed_link
=> https://docs.google.com/a/speee.jp/spreadsheets/d/**********/htmlembed

ファイル出力

下記でファイル出力用のURLを出力できる。
csvとかxlsxはオプションは特になしなのでそのままで良いが、pdfに関しては横向きにするだとか
ヘッダを含めるかなどがあるのだが、このURLには最小限しか含まれてない。

ruby

file = session.file_by_id('[ファイルのid]')
file.export_links.to_hash.each do |type, url|
  p "type is #{type}, url is #{url}"
end

=> "type is application/pdf, url is https://docs.google.com/spreadsheets/export?id=*********&exportFormat=pdf"
   "type is text/csv, url is https://docs.google.com/spreadsheets/export?id=**********&exportFormat=csv"
   "type is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, url is https://docs.google.com/spreadsheets/export?id=*************&exportFormat=xlsx"

pdfのパラメータに関しては、スプレッドシートを開いてpdf出力する際にpostのリクエストが飛ぶので、formdataを見るとパラメータが分かるので、毎回プログラムからではなくて、ブラウザ上からオプションを指定してあげて最後にプログラムに落としこむと幸せになれそう。

全体を通して

レスポンスがすべてではなくて、実際のブラウザからの挙動からソースコードとか読んで試していくと大抵のことは実現できそう

参考にしたドキュメント

• rubydoc - google_drive
• google api reference