みんな大好きExcelファイルをシステムで自動生成してメールで送付してほしいという案件にあたりました。そこで、みんな大好きGoogle様のAPIを使ってExcelファイルを生成しました。（※ なぜRubyXLという便利なgemを利用しなかったのかはこの記事の最後をご参照ください。） Google APIは以前も使いましたが、バージョンがあがっているみたいなので調べ直しました。

条件

テンプレートファイルがあるので、そのファイルを元に値を変更したい
VLOOKUPなどの式が入っているので、ファイルを開いた時に式が再計算されている状態にしたい

準備

Googleのアカウントを作成する

みんな持ってると思うので省略します

Google Drive APIを有効にする

Google Developers Console で Google Drive API を有効にするアプリケーションの登録にアクセスして、Google Drive APIを有効にしてください。

Google Sheets APIを有効にする

Google Sheets APIが無効な状態でAPIを操作しよとすると、こんな感じのエラーがでるので、予め有効にしておくといいと思います。

Google::Apis::ClientError: forbidden: Google Sheets API has not been used in project before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=xxxxxx then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.
from .../vendor/bundle/ruby/2.2.0/gems/google-api-client-0.9.9/lib/google/apis/core/http_command.rb:211:in `check_status'

API Managerの概要でsheetと検索すると、Google Sheets APIが出てきます。あとはウィザードに従いAPIを有効にしてください。 gyazo.com

client_secretを得る

Quickstartに書いてありますが、APIアクセスするためにはclient_secretが必要です。
今回はOAuth client IDを使うので、quickstartに書いてあるとおりの手順で、client_secretを作成してください。

コード

さて、お待ちかねのコードの時間です。テンプレートファイルをコピーして、セルの値を更新して、メールに添付するという実装を行います。ついでですが、不要になったファイルは消します。

gemをインストールします。

Gemfile

gem 'google-api-client'
gem 'google_drive'

認証用のメソッドを作成する

quickstartを参考にしたコードです。
- quickstartではcredentialsをファイルにstoreしていますが、Redisに変更しています。
- quickstartでは必要最低限のSCOPEのみとなっていたため、Drive APIとSheets APIの両方を使えるSCOPEに変更しています。
client_secretはclient_secret.jsonという名前で、同じディレクトリにおいてください。
実行したらURLが表示されるので、ブラウザで認可したあとに表示される文字列をCLIに貼り付けてください。

require 'google/apis/sheets_v4'
require 'googleauth'
require 'googleauth/stores/redis_token_store'


OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'
CLIENT_SECRETS_PATH = 'client_secret.json'
SCOPE = [Google::Apis::SheetsV4::AUTH_SPREADSHEETS,
 Google::Apis::DriveV3::AUTH_DRIVE]

def authorize
  client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)
  token_store = Google::Auth::Stores::RedisTokenStore.new(redis: Redis.new)
  authorizer = Google::Auth::UserAuthorizer.new(
    client_id, SCOPE, token_store)
  user_id = 'default'
  credentials = authorizer.get_credentials(user_id)
  if credentials.nil?
    url = authorizer.get_authorization_url(
      base_url: OOB_URI)
    puts "Open the following URL in the browser and enter the " +
         "resulting code after authorization"
    puts url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI)
  end
  credentials
end

テンプレートファイルをコピーする

ファイルをコピーするにはDrive APIを利用します。

# Initialize the API

drive_service = Google::Apis::DriveV3::DriveService.new
drive_service.client_options.application_name = 'nyaahara sama'
drive_service.authorization = authorize

# example: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
drive_service.copy_file('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms')

かんたんですね!!

セルの値を変更する

セルの値を変更するにはSheets APIを利用します。

# Initialize the API
sheet_service = Google::Apis::SheetsV4::SheetsService.new
sheet_service.client_options.application_name = 'nyaahara sama'
sheet_service.authorization = authorize


value_range = Google::Apis::SheetsV4::ValueRange.new
value_range.range = 'A1:D1'
value_range.major_dimension = 'ROWS'
value_range.values = [['にゃ','あ','は','ら']]

# example: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
sheet_service.update_spreadsheet_value(
  '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
  value_range.range,
  value_range,
  value_input_option: 'USER_ENTERED',
)

これで、A1〜D1にそれぞれ「にゃ」「あ」「は」「ら」と入力されているはずです。縦に行きたかったら、下記のようにしてみてください。

value_range.range = 'A1:A4'
value_range.values = [['にゃ'],['あ'],['は'],['ら']]

value_range.major_dimension = 'ROWS'を変更してもいいのかもしれません。調べられていないです。

ダウンロードしてメールに添付する

今回はGoogle APIの使い方の紹介なので、メールに添付する部分は割愛しています。
ファイルのダウンロードはDrive APIのほうを使います。

# example: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
drive_service.export_file('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
                          'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                          download_dest: StringIO.new)

簡単ですね!!
download_destをFile.open('file_name', 'w')などにしてみても、動くと思いますよ!!
ちなみに第二引数はMIME typeですが、見つけるのにちょっと手間取りました。
- ここにあります。リンク集にも出しておきますね。

ファイルを削除する

ファイルの削除はDrive APIを使います。なんとなく予測できましたよね？

drive_service.delete_file('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms')

説明は不要ですね？

まとめ

Google Spread Sheetのファイルをコピーして、セルの値を編集したあとダウンロードして削除するまでを実装しました。
フィアル、シート、セルなどの新規追加や、ファイル移動など他の事もたくさんできるみたいなので、また書きたいと思います。
神ソースを発見したことで、早く実装できました。ありがとうございます。

参考リンク

RubyのGoogle API ClientでGoogle Driveを扱う - Spreadsheetの操作あり
MIME type一覧(Downloading Google Documents)
神ソース
- ありがとうございます!!
rubyからGoogleDriveのスプレッドシートに書き込む(自分のブログ)

なぜ、GoogleSpreadSheetを利用したのか

RubyXLを利用して作成したExcelファイルをWindowsで開くとVLOOKUPなどの式に値が反映されていなかったのですよ。。。トホホ。

railsがんばる子

Ruby on Railsがんばる子です。胡蝶蘭のECサイトを運営しています。

rubyでGoogle Sheets APIとGoogle Drive APIを使ってみた。

条件

準備