Closed2021/03/07にクローズ40

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

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

公式の関連gem https://github.com/googleapis/google-api-ruby-client
外から使う分には google-apis-<servicename>_<serviceversion> ってのを使えとのこと。スプレッドシートの場合は google-apis-sheets_v4になる。

READMEやディレクトリ名を見るに、定義あたりから自動生成された薄いAPIクライアントのようす。

サードパーティだとこれ google_drive。
他にも spreadsheet というのがあるが、これは名前の割にExcel用。

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

コードを追いかけるとgoogleのgemを使っているところにたどり着く; https://github.com/gimite/google-drive-ruby/blob/master/lib/google_drive/api_client_fetcher.rb

スプレッドシートはGoogle::Apis::SheetsV4::SheetsServiceをnewして使うが、認証情報などはインスタンスのattrに代入する感じに見える。

該当の公式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) など、諸々ベースな属性が見える。

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

最初に調べたときに出てきたページ。要するにGoogleAPIのプロジェクトを生やしてOAuthのデータを生成すればよい。

てか当然ながら公式ドキュメントありました。。。

ちょっと色々あるようなので読む

今回は人間のアクセスというよかRails appからシステムとしてアクセスする用途なので、これに従ってServiceAccountを生やして認証jsonを使うのが適切っぽい。

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

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

Drive的にはただのアカウントっぽい？招待して普通にユーザのように振る舞えばよいのかも

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)

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

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

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

ここの通りにやったらできた。

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')

ここまでまとめ:

認証まわり

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。

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

一応、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

とすれば動く

ライブラリのREADMEによると、jsonの中身のいくつかを環境変数で渡すやり方があるようだ。

これは所定の環境変数をライブラリが自動で読んでしまうが、ここの処理を見れば個別の値を引数などで渡して読ませることはできそうな気がする

例の環境変数が読まれている（というかキーが定義されている？）のがこのあたり; 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がどう読まれるかはすぐ上にあったわけだが。

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

な感じで動いた。

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

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

もう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

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

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

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

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

次はこんな感じで

...
  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

なんかそもそも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は伏せたが前後で同じだった

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

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

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

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

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

[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のインスタンス

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

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)

なんもわからん。

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はここ。

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 になる。

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にクローズされました