google-drive-ruby を脱却して google-apis-sheets_v4 でスプレッドシートにアクセス！

ラブグラフでCTOをしております横江( @yokoe24 )です！

Googleフォームの回答結果をGoogleスプレッドシートに出力している場合に、
Googleスプレッドシートのデータを読み取ってデータベースに格納したい という場面があります。

google-drive-ruby の欠点

Ruby on Rails を使っている会社では google_drive (google-drive-ruby) という gem がよく使われていました。
GitHub のスター数も1,800以上ついていて、その人気ぶりが伺えます。

しかしこの gem の最終更新は、2025年3月現在、
RubyGems 上でも GitHub 上でも2021年4月が最後です。
もう４年近く更新されていません。

しかも

s.add_dependency('googleauth', ['>= 0.5.0', '< 1.0.0'])

と記載されているため、フォークをしない限りは googleauth gem を ver. 1.0 以降にバージョンアップすることもできません。

google-apis-sheets_v4 を使う

そこで、 google-drive-ruby を脱却して
google-apis-sheets_v4 を使う実装にすることを考えました。

GitHubリポジトリは google-api-ruby-client 下の
https://github.com/googleapis/google-api-ruby-client/tree/google-apis-generator/v0.16.0/generated/google-apis-sheets_v4 にあります。
ver. 1 を迎えてはいませんが、更新頻度や、ほぼ Google 公式のリポジトリっぽい雰囲気から考えるに、プロダクトに使えるレベルの gem かと思います。

そして、試行錯誤の結果、以下のようなクラスが出来上がりました！

# Google スプレッドシートを扱うためのクライアント
# google-apis-sheets_v4 gem に依存している
# （ドキュメント: https://googleapis.dev/ruby/google-api-client/latest/Google/Apis/SheetsV4/SheetsService.html ）
class GoogleSheetsClient
  attr_reader :service, :spreadsheet_id, :spreadsheet, :default_sheet_id, :default_sheet

  # インスタンス変数の値を定義
  #
  # @param [String] url スプレッドシートのURL。これが指定されている場合、引数 spreadsheet_id, default_sheet_id は無視され、URL文字列から認識され設定される
  # @param [String] spreadsheet_id スプレッドシートID
  # @param [Integer] default_sheet_id デフォルトのシートID
  def initialize(url: "", spreadsheet_id: "", default_sheet_id: nil)
    credentials = Google::Auth::UserRefreshCredentials.new(
      client_id:     ENV.fetch("GOOGLE_DRIVE_CLIENT_ID"),
      client_secret: ENV.fetch("GOOGLE_DRIVE_CLIENT_SECRET"),
      scope:         "https://www.googleapis.com/auth/drive",
      redirect_uri:  "https://example.com",
    )
    credentials.refresh_token = ENV.fetch("GOOGLE_DRIVE_REFRESH_TOKEN")
    credentials.fetch_access_token!

    @service = Google::Apis::SheetsV4::SheetsService.new
    @service.authorization = credentials

    if url.present?
      @spreadsheet_id, @default_sheet_id = spreadheet_id_and_sheet_id_by_url(url: url)
    else
      @spreadsheet_id = spreadsheet_id
      @default_sheet_id = default_sheet_id
    end

    set_spreadsheet_and_default_sheet_by_id(spreadsheet_id: @spreadsheet_id, sheet_id: @default_sheet_id)
  end

  # シートをIDから取得
  #
  # @param [Integer] id シートID
  # @return [Google::Apis::SheetsV4::Sheet]
  def sheet_by_id(id:)
    @spreadsheet.sheets.find { |sheet| sheet.properties.sheet_id == id }
  end

  # シートをタイトルから取得
  #
  # @param [String] title シートのタイトル
  # @return [Google::Apis::SheetsV4::Sheet]
  def sheet_by_title(title:)
    @spreadsheet.sheets.find { |sheet| sheet.properties.title == title }
  end

  # シートの指定された range のすべての値を取得
  #
  # @param [String] range 取得する範囲（例: "A1:B2"）。何も指定しない場合はシート全体の値を取得
  # @param [Google::Apis::SheetsV4::Sheet] sheet シート
  # @return [Array<Array<String>>] シートの値。すべて文字列で返る点に注意
  def fetch_sheet_values(range: "", sheet: @default_sheet)
    sheet_name_and_range =
      if range.present?
        "'#{sheet.properties.title}'!#{range}"
      else
        sheet.properties.title
      end

    data = @service.get_spreadsheet_values(@spreadsheet_id, sheet_name_and_range)
    data.values
  end

  # シートの指定された range に値を書き込む
  #
  # @param [String] range 書き込む範囲（例: "A1:B2"）もしくは起点となるセル（例: "A1"）
  # @param [Array<Array<String>>] values 書き込む値
  # @param [Google::Apis::SheetsV4::Sheet] sheet シート
  # @return [Google::Apis::SheetsV4::UpdateValuesResponse]
  def update_sheet_values(range:, values:, sheet: @default_sheet)
    sheet_name_and_range = "'#{sheet.properties.title}'!#{range}"
    value_range = Google::Apis::SheetsV4::ValueRange.new(values: values)

    @service.update_spreadsheet_value(@spreadsheet_id, sheet_name_and_range, value_range, value_input_option: "RAW")
  end

  # シートの複数の range に値を書き込む
  # update_sheet_values では１つの範囲にしか書き込めないが、このメソッドでは１度のAPIリクエストで複数の範囲に書き込める
  # 参考: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate?hl=ja
  #
  # @param [Array<Hash>] range_and_values_array 範囲と値のセット。[{ range: "A1:B2", values: [["a", "b"], ["c", "d"]] }, ...] のような形式
  # @param [Google::Apis::SheetsV4::Sheet] sheet シート
  # @return [Google::Apis::SheetsV4::BatchUpdateValuesResponse]
  def batch_update_sheet_values(range_and_values_array:, sheet: @default_sheet)
    # range_and_values_array の range にシート名を付与する
    range_and_values_array.each do |range_and_values|
      range_and_values[:range] = "'#{sheet.properties.title}'!#{range_and_values[:range]}"
    end

    batch_update_values_request = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new(data: range_and_values_array, value_input_option: "RAW")
    @service.batch_update_values(@spreadsheet_id, batch_update_values_request)
  end

  # シートの指定された範囲のセルの色や文字色を変更する
  #
  # @param [String] range 書き込む範囲（例: "A1:B2"）もしくは単体のセル（例: "A1"）
  # @param [Hash] cell_color セルの背景色
  # @option cell_color [Float] :red 赤の度合い（0.0〜1.0）
  # @option cell_color [Float] :green 緑の度合い（0.0〜1.0）
  # @option cell_color [Float] :blue 青の度合い（0.0〜1.0）
  # @param [Hash] text_color 文字色
  # @option text_color [Float] :red 赤の度合い（0.0〜1.0）
  # @option text_color [Float] :green 緑の度合い（0.0〜1.0）
  # @option text_color [Float] :blue 青の度合い（0.0〜1.0）
  # @param [Boolean] bold 太字にするかどうか
  # @param [Google::Apis::SheetsV4::Sheet] sheet シート
  # @return [Google::Apis::SheetsV4::BatchUpdateSpreadsheetResponse]
  def update_cell_format(range:, cell_color: nil, text_color: nil, bold: false, sheet: @default_sheet)
    # "A1" や "A1:B2" のような形式である range を Google::Apis::SheetsV4::GridRange.new の形式に直す
    start_column_number = self.column_number_by_label(label: range.match(/^[A-Z]+/)[0])
    start_row_number = range.match(/\d+/)[0].to_i

    if range.include?(":")
      end_column_number = self.column_number_by_label(label: range.match(/:([A-Z]+)/)[1])
      end_row_number = range.match(/\d+$/)[0].to_i
    else
      end_column_number = start_column_number
      end_row_number = start_row_number
    end

    # https://googleapis.dev/ruby/google-api-client/latest/Google/Apis/SheetsV4/GridRange.html に記載の通り、
    # index は 0 始まりのため start は -1 するのだが、end は「それを含めないところまで」という定義のため -1 が必要ない
    grid_range = Google::Apis::SheetsV4::GridRange.new(
      sheet_id: sheet.properties.sheet_id,
      start_column_index: start_column_number - 1,
      end_column_index: end_column_number,
      start_row_index: start_row_number - 1,
      end_row_index: end_row_number,
    )

    user_entered_format = []
    change_fields = []

    if cell_color.present?
      user_entered_format << {
        background_color: cell_color,
      }
      change_fields << "userEnteredFormat.backgroundColor"
    end

    if text_color.present?
      user_entered_format << {
        text_format: {
          foreground_color: text_color,
        },
      }
      change_fields << "userEnteredFormat.textFormat.foregroundColor"
    end

    if bold
      user_entered_format << {
        text_format: {
          bold: true,
        },
      }
      change_fields << "userEnteredFormat.textFormat.bold"
    end

    requests = [
      {
        repeat_cell: {
          range: grid_range,
          cell: {
            user_entered_format: user_entered_format,
          },
          fields: change_fields.join(","),
        },
      },
    ]

    @service.batch_update_spreadsheet(@spreadsheet_id, { requests: requests })
  end

  # シートの列名（A, B, C, ..., AA, AB, ...）を列番号に変換
  #
  # @param [String] label 列名
  # @return [Integer] 列番号
  def self.column_number_by_label(label:)
    a_to_z_size = ("A".."Z").to_a.length # A から Z までの文字数

    label.chars.reverse.each_with_index.sum do |char, idx|
      (char.ord - "A".ord + 1) * (a_to_z_size**idx)
    end
  end

  # シートの列番号を列名（A, B, C, ..., AA, AB, ...）に変換
  #
  # @param [Integer] number 列番号
  # @return [String] 列名
  def self.column_label_by_number(number:)
    a_to_z_size = ("A".."Z").to_a.length # A から Z までの文字数

    label = ""

    while number > 0
      # 1〜26列目は A 〜 Z で、2文字にはならない。よって、26 で割る前に自身を 1 引く必要がある
      add_num_for_a = (number - 1) % a_to_z_size # A からいくつアルファベットを移動させるか
      label = ("A".ord + add_num_for_a).chr + label # 26で割った余りを使って、列名となる文字を右から順に埋めていくイメージ
      number = (number - 1) / a_to_z_size # 26で割った値が1以上ならループは続く
    end

    label
  end

  private

  # spreadsheet_id と sheet_id をもとに Google::Apis::SheetsV4::Sheet を取得し @default_sheet に代入
  # 一致するシートIDが見つからないか、sheet_id が nil の場合は @default_sheet は nil になる
  # 参考: https://googleapis.dev/ruby/google-api-client/latest/Google/Apis/SheetsV4/Sheet.html
  #
  # @param [String] spreadsheet_id スプレッドシートID
  # @param [Integer] sheet_id シートID
  # @return [Google::Apis::SheetsV4::Sheet]
  def set_spreadsheet_and_default_sheet_by_id(spreadsheet_id:, sheet_id:)
    @spreadsheet = @service.get_spreadsheet(spreadsheet_id)

    sheets = @spreadsheet.sheets
    @default_sheet = sheets.find { |sheet| sheet.properties.sheet_id == sheet_id }
  end

  # スプレッドシートの URL をもとに Google::Apis::SheetsV4::Sheet を取得
  #
  # @param [String] url スプレッドシートURL
  # @return [Array<String, Integer>] スプレッドシートIDとシートID
  def spreadheet_id_and_sheet_id_by_url(url:)
    matched = url.match(%r{spreadsheets/d/([^/]+)})
    raise "Invalid URL" if matched.nil?

    spreadsheet_id = matched[1]

    matched = url.match(/gid=(\d+)/)

    sheet_id =
      if matched.nil?
        nil
      else
        matched[1].to_i
      end

    [spreadsheet_id, sheet_id]
  end
end

元々が google-drive-ruby だったので、

sheet_url = "https://docs.google.com/spreadsheets/d/XXXXX/edit?gid=0#gid=0"

credentials = ...... # 認証情報を得る処理
session = GoogleDrive::Session.from_credentials(credentials)
spreadsheet = session.spreadsheet_by_url(sheet_url)
sheet = spreadsheet.worksheet_by_title("フォームの回答")
values = sheet.rows

と今まで書かれていたコードを

sheet_url = "https://docs.google.com/spreadsheets/d/XXXXX/edit?gid=0#gid=0"

client = GoogleSheetsClient.new(url: sheet_url)
values = client.fetch_sheet_values

と書き換えるだけで、スプレッドシートの値を取得する目的が、URLを使いつつ同じように果たせるよう工夫されています。

なお、クラスメソッドの self.column_number_by_label など、
列の名前（A, B, C, AA, ...）と列番号を相互変換する処理 については、前回の記事で解説していますのでぜひそちらもご覧ください。

https://zenn.dev/lovegraph/articles/df0fe20774ab5c

google-drive-ruby からの脱却に成功！

こうして、 google-drive-ruby gem が不要になって、
googleauth を ver. 1 以上にバージョンアップできるようになり、
それに加えて googleauth に依存していた他のライブラリのバージョンも上げられるようになったのでした！ 🌼

めでたい！

他の会社でも使われているのを見たことがあるメジャーな gem ですので、
同じようにバージョンアップ周りで困っている方は、ぜひご参考になさってください！！💝