Zenn
Closed40

rubyからスプレッドシートに読み書きする

Ruby
Google Sheets
inomotoinomoto

Railsなシステムからスプレッドシートにカジュアルにデータの書き出しがしたい。
必要な情報やgemなどを調べる。

inomotoinomoto

サードパーティだとこれ google_drive。
他にも spreadsheet というのがあるが、これは名前の割にExcel用。
https://github.com/gimite/google-drive-ruby

中身を見てみると、前述の公式gemのラッパーにような動作をしている。
このgemをそのまま使うとdependenciesでnokogiriとか入ってちと嫌なので、これを参考に公式のを使うのがよさそう。

inomotoinomoto

該当の公式gemのコードはこちら; https://github.com/googleapis/google-api-ruby-client/blob/master/google-api-client/generated/google/apis/sheets_v4/service.rb

ここにはAPIを使うメソッドが整然と並んでいるだけ。とはいえ認証さえできればこれを追えばよいと見える。

ここで親クラスを追う; https://github.com/googleapis/google-api-ruby-client/blob/5df5dfd691b498b3eefbd96abb3d38b6aa69a574/google-apis-core/lib/google/apis/core/base_service.rb#L93
def authorization=(authorization) など、諸々ベースな属性が見える。

inomotoinomoto

次は認証まわり。微妙に面倒な気がする。

inomotoinomoto

今回は人間のアクセスというよかRails appからシステムとしてアクセスする用途なので、これに従ってServiceAccountを生やして認証jsonを使うのが適切っぽい。
https://github.com/googleapis/google-api-ruby-client/blob/master/docs/usage-guide.md#getting-automatic-credentials

GOOGLE_APPLICATION_CREDENTIALS環境変数にファイルのパスを書けとある。
値ではなくファイルとして配置するということは、コンテナ環境で使うには一工夫が必要か。

inomotoinomoto

概要はわかるがGCPのIAMまわりがよくわからない。おそらくservice accountに権限をつけねばならないと思うのだが。

inomotoinomoto

Driveに適当なフォルダを作り、スプレッドシートを作成し、idをコピー。

jsonを用意し、gem "google-apis-sheets_v4"だけ書いたGemfileを用意し、jsonのパスをいれた環境変数をセットしてirbを起動。そして以下

require 'google/apis/sheets_v4'
api = Google::Apis::SheetsV4::SheetsService.new
api.authorization = Google::Auth.get_application_default
sheet = api.get_spreadsheet('xxxxxxxx')

そしてエラー。Google::Apis::ClientError (Invalid request)

inomotoinomoto
begin
  api.get_spreadsheet('xxxxx')
rescue Google::Apis::ClientError => e
  puts e.inspect
end

The request is missing a valid API key.とある。authを指定できてないっぽい。

inomotoinomoto

ひとまず Google::Auth.get_application_defaultの結果の中身を見ても問題のjsonは正しく認識されてるようにしか見えない。

inomotoinomoto

ここの通りにやったらできた。
https://recruit.gmo.jp/engineer/jisedai/blog/google-api-client_with_ruby/

require 'google/apis/sheets_v4'

api = Google::Apis::SheetsV4::SheetsService.new
authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: File.open("/app/cred.json"),
  scope: %w(
    https://www.googleapis.com/auth/drive
    https://www.googleapis.com/auth/drive.file
    https://www.googleapis.com/auth/spreadsheets
  )
)
authorizer.fetch_access_token!
api.authorization = authorizer
api.get_spreadsheet('xxxx')
inomotoinomoto

ここまでまとめ:

認証まわり

GCPのプロジェクトを生やしてそこにservice accountを作る。とりあえずnameがあればok。
メールアドレスが付与され、Driveのユーザとして見えるので、フォルダとかファイルに適当に共有で権限つけとく。

service accountのキーは新規鍵ってことでjsonを作っておく。

コード

gem "google-apis-sheets_v4"なGemfileを用意してbundleしておく。
再掲だがコードは以下。

require 'google/apis/sheets_v4'

api = Google::Apis::SheetsV4::SheetsService.new
authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: File.open("/app/cred.json"),
  scope: %w(
    https://www.googleapis.com/auth/drive
    https://www.googleapis.com/auth/drive.file
    https://www.googleapis.com/auth/spreadsheets
  )
)
authorizer.fetch_access_token!
api.authorization = authorizer
api.get_spreadsheet('xxxx')

service accountのjsonは/app/cred.jsonとして配置している。
get_spreadsheetの引数には適当につくったスプシのid。

inomotoinomoto

認証キーの受け渡しをもうちょいスマートにやりたい。
実アプリケーションがコンテナで動く想定だとファイルで受け渡すのは面倒。環境変数が望ましい。
が、モノの中身はjsonなので環境変数は状況によっては面倒になりうる。テンプレートエンジンを通る場合やshellを通る場合など、quoteや改行、スペースが大変面倒。

inomotoinomoto

一応、jsonをそのまま環境変数に投げるスタイルでも、

require 'google/apis/sheets_v4'
require 'stringio'

class SheetsService
  def initialize(credential:)
    @service = Google::Apis::SheetsV4::SheetsService.new.tap do |s|
      s.authorization = Google::Auth::ServiceAccountCredentials.make_creds(
        json_key_io: StringIO.new(credential),
        scope: %w(
          https://www.googleapis.com/auth/drive
          https://www.googleapis.com/auth/drive.file
          https://www.googleapis.com/auth/spreadsheets
        )
      ).tap(&:fetch_access_token!)
    end
  end

  def get_spreadsheet(id)
    @service.get_spreadsheet(id)
  end
end

puts SheetsService
  .new(credential: ENV['SERVICE_ACCOUNT_JSON'])
  .get_spreadsheet('xxx')
  .inspect

な感じで、

$ env SERVICE_ACCOUNT_JSON="$(cat cred.json)" bundle exec ruby main.rb

とすれば動く

inomotoinomoto

例の環境変数が読まれている（というかキーが定義されている？）のがこのあたり; https://github.com/googleapis/google-auth-library-ruby/blob/636e56042a7ac4963b09779f341654aa9b4c2f28/lib/googleauth/credentials_loader.rb#L40
で、service accountの場合はここで参照される; https://github.com/googleapis/google-auth-library-ruby/blob/636e56042a7ac4963b09779f341654aa9b4c2f28/lib/googleauth/service_account.rb#L73

...と、探るまでもなくjsonがどう読まれるかはすぐ上にあったわけだが。

inomotoinomoto

jsonの中身の値からそれっぽく

export GOOGLE_PROJECT_ID="test-000000"
export GOOGLE_CLIENT_EMAIL="test@test-000000.iam.gserviceaccount.com"
export GOOGLE_PRIVATE_KEY="----..."

としておき、

require 'google/apis/sheets_v4'
require 'stringio'

class SheetsService
  def initialize(project_id:, client_email:, private_key:)
    @service = Google::Apis::SheetsV4::SheetsService.new.tap do |s|
      s.authorization = Google::Auth::ServiceAccountCredentials.make_creds(
        project_id: project_id,
        issuer: client_email,
        signing_key: OpenSSL::PKey::RSA.new(private_key.gsub(/\\n/, "\n")),
        scope: %w(
          https://www.googleapis.com/auth/drive
          https://www.googleapis.com/auth/drive.file
          https://www.googleapis.com/auth/spreadsheets
        )
      ).tap(&:fetch_access_token!)
    end
  end

  def get_spreadsheet(id)
    @service.get_spreadsheet(id)
  end
end

puts SheetsService
  .new(
    project_id: ENV['GOOGLE_PROJECT_ID'],
    client_email: ENV['GOOGLE_CLIENT_EMAIL'],
    private_key: ENV['GOOGLE_PRIVATE_KEY'],
  )
  .get_spreadsheet('xxxx')
  .inspect

と用意して

bundle exec ruby main.rb

な感じで動いた。

inomotoinomoto

しかし結局private_keyの改行に対処する必要があった。上記ではprivate_key.gsub(/\\n/, "\n")としている。

結局鍵の中のスペースは残っているから受け渡しのダルさは多少残っているし、そもそも公開インタフェースとして用意されてるわけじゃないoptionに値を直接入れてるので、いきなりbreakingされても文句は言えない。微妙かも。

inomotoinomoto

もうbase64でええやんけ。

require 'stringio'
require 'base64'
require 'google/apis/sheets_v4'

class SheetsService
  def initialize(credential:)
    @service = Google::Apis::SheetsV4::SheetsService.new.tap do |s|
      s.authorization = Google::Auth::ServiceAccountCredentials.make_creds(
        json_key_io: StringIO.new(credential),
        scope: %w(
          https://www.googleapis.com/auth/drive
          https://www.googleapis.com/auth/drive.file
          https://www.googleapis.com/auth/spreadsheets
        )
      ).tap(&:fetch_access_token!)
    end
  end

  def get_spreadsheet(id)
    @service.get_spreadsheet(id)
  end
end

puts SheetsService
  .new(credential: Base64.decode64(ENV['SERVICE_ACCOUNT_JSON']))
  .get_spreadsheet(ARGV.first)
  .inspect
inomotoinomoto

ところでfetch_access_token!の有効期限とかどうなってるんだろうか。

inomotoinomoto

fetch_access_token!の戻り地にめっちゃexpires_idの値入ってたわ。手元で実行したやつだと1時間。
ということはサーバアプリケーションで使うならトークン管理が必要か。

inomotoinomoto

...そのくらい内部でやってくれてもいい気がするが、ざっとコードやREADMEを読んだ限りではわからない。1h程度なら試せばいいか。

inomotoinomoto

sleep 3605 挟んでやってみたが、普通に通った感がある。内部でやってくれてるのかも。
念の為、内部のtokenを前後で確認したいところ。

inomotoinomoto

次はこんな感じで

...
  def get_spreadsheet(id)
    puts "> token: #{@service.authorization.access_token}"
    puts "> expires_at: #{@service.authorization.expires_at}"
    @service.get_spreadsheet(id)
  end
end

service = SheetsService.new(credential: Base64.decode64(ENV['SERVICE_ACCOUNT_JSON']))
puts service.get_spreadsheet(ARGV.first).spreadsheet_id
puts Time.now
sleep 3605
puts service.get_spreadsheet(ARGV.first).spreadsheet_id
inomotoinomoto

なんかそもそもexpireされてないっぽい感じ？
時間立ってもtoken変わってないしexpires_atも変わってない。

なお試したコードは以下:

require 'stringio'
require 'base64'
require 'google/apis/sheets_v4'

class SheetsService
  def initialize(credential:)
    @service = Google::Apis::SheetsV4::SheetsService.new.tap do |s|
      s.authorization = Google::Auth::ServiceAccountCredentials.make_creds(
        json_key_io: StringIO.new(credential),
        scope: %w(
          https://www.googleapis.com/auth/drive
          https://www.googleapis.com/auth/drive.file
          https://www.googleapis.com/auth/spreadsheets
        )
      )
    end
    @service.authorization.fetch_access_token!
    puts "> token: #{@service.authorization.access_token}"
    puts "> expires_at: #{@service.authorization.expires_at}"
  end

  def get_spreadsheet(id)
    puts "> token: #{@service.authorization.access_token}"
    puts "> expires_at: #{@service.authorization.expires_at}"
    @service.get_spreadsheet(id)
  end
end

service = SheetsService.new(credential: Base64.decode64(ENV['SERVICE_ACCOUNT_JSON']))
puts Time.now
sleep 4000
puts service.get_spreadsheet(ARGV.first).spreadsheet_id
puts Time.now

結果

> token: zzzzzzzzzz-token-zzzzzzzzz
> expires_at: 2021-02-27 17:05:27 +0000
2021-02-27 16:05:28 +0000
> token: zzzzzzzzzz-token-zzzzzzzzz
> expires_at: 2021-02-27 17:05:27 +0000
xxxx_spreadsheet-id_xxxxx
2021-02-27 17:12:10 +0000

※tokenは伏せたが前後で同じだった

inomotoinomoto

find_file的な機能を入れようとするとdriveの方のAPIが必要になる。そりゃそうか。
親フォルダとファイル名指定でファイルを取得してみる

  def find(id)
    # DriveV3::DriveService#get_file with id=nil or id='' returns a empty(?) File object without any error.
    # So raise error explicitly if id is blank.
    raise "id is empty" unless id&.length > 0

    file = drive_service.get_file(id)
    # FIXME: want to raise a error when file is trashed, but DriveV3::File#trashed? don't work.

    sheets_service.get_spreadsheet(id)
  end

  def find_by_name(folder_id:, name:)
    files = drive_service.list_files(
      q: [
        "'#{folder_id}' in parents",
        "name = '#{name}'",
      ].join(" and "),
    ).files

    raise "file not found" if files.count.zero?
    raise "multiple files found for single name" if files.count > 1

    find(files.first.id)
  end
inomotoinomoto

driveのファイル構造の思想に反して、一般的なユーザのファイル構成の認識はファイルシステムのそれと同じ（親フォルダは一つだけ・同じフォルダ内に同名ファイルは一つだけ）な前提のもと、同名ファイルが複数あったらエラーするようにする。

SheetsV4::SheetsService#get_spreadsheetは存在しないidを指定しても大雑把なクライアントエラーしか返さないので、先に明示的にdriveのAPIでgetする。こっちはちゃんとnot foundな感じのエラーを返す。

inomotoinomoto

driveのget_fileにnilや空文字を指定した際に返ってくるFileオブジェクトは一体なんなのかはよくわからない。idもnilだし@kind="drive#fileListなのにfilesメソッド生えてないし...

また、service accountを使っているからなのかわからんが、File#trashed?が常にnilを返す。ゴミ箱に入っているかどうかに関わらず。
実用的にちと困るのだが。

inomotoinomoto
[19] pry(main)> file = service.drive_service.create_file
=> #<Google::Apis::DriveV3::File:0x000055817f6d9a10
 @id="140fQpbOe66zme8Vny0RZ7GaztOqL3ojw",
 @kind="drive#file",
 @mime_type="application/octet-stream",
 @name="Untitled">
[20] pry(main)> service.drive_service.update_file(file.id, {trashed:true})
=> #<Google::Apis::DriveV3::File:0x000055817fa5f810
 @id="140fQpbOe66zme8Vny0RZ7GaztOqL3ojw",
 @kind="drive#file",
 @mime_type="application/octet-stream",
 @name="Untitled">
[21] pry(main)> service.drive_service.get_file(file.id).trashed?
=> nil

えー。

※service.drive_serviceはDriveV3::DriveServiceのインスタンス

inomotoinomoto

シートの値を更新してみる。

inomotoinomoto

service -> Google::Apis::SheetsV4::SheetsServiceとして

  service.update_spreadsheet_value(
    spreadsheet_id,
    "A1",
    Google::Apis::SheetsV4::ValueRange.new(values: [[1, 2, 3]])
  )

Invalid request (Google::Apis::ClientError)

なんもわからん。

inomotoinomoto

rescueしてbinding.pryして、エラー内容を出せるようにする。

begin
  service.update_spreadsheet_value(
    spreadsheet_id,
    "A1",
    Google::Apis::SheetsV4::ValueRange.new(values: [[1, 2, 3]])
  )
rescue Google::Apis::ClientError => e
  puts (JSON.parse(e.body)["error"] rescue e.body)
end

※e.bodyはエラーによってはhtmlが返ってくることがあるので、JSON.parseをrescueして生データも返せるようにしておく

{"code"=>400, "message"=>"'valueInputOption' is required but not specified", "status"=>"INVALID_ARGUMENT"}

なるほど。

begin
  service.update_spreadsheet_value(
    spreadsheet_id,
    "A1",
    Google::Apis::SheetsV4::ValueRange.new(values: [[1, 2, 3]]),
    value_input_option: "USER_ENTERED",
  )
rescue Google::Apis::ClientError => e
  puts (JSON.parse(e.body)["error"] rescue e.body)
end

できたっぽい。

なおvalue_input_optionはここ。
https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption

inomotoinomoto

value_input_optionには USER_ENTERED とRAWが指定できるようなので、試してみる。

    Google::Apis::SheetsV4::ValueRange.new(values: [["=C1", 2, 3]]),
    value_input_option: "RAW",

A1には =C1 という値（文字列）が入る。

    Google::Apis::SheetsV4::ValueRange.new(values: [["=C1", 2, 3]]),
    value_input_option: "USER_ENTERED",

A1には =C1 という数式が入る。見た目上は 3 になる。

inomotoinomoto

range指定文字列のところには範囲が指定できる。

    "A1:D1",
    Google::Apis::SheetsV4::ValueRange.new(values: [["=C1", 2, 3]]),

値より範囲が大きい場合は特に問題なく成功する。

    "A1:B1",
    Google::Apis::SheetsV4::ValueRange.new(values: [["=C1", 2, 3]]),

{"code"=>400, "message"=>"Requested writing within range ['シート1'!A1:B1], but tried writing to column [C]", "status"=>"INVALID_ARGUMENT"}

狭い場合はエラーになる。

値の方が可変な場合に、思わぬ領域を壊さないように範囲を指定しておく、という使い方ができそう。

このスクラップは2021/03/07にクローズされました