GoogleDriveに行と列の位置を指定して値を書き込むのが面倒なのでヘッダを指定して書き込めるWriterを書いてみた

ことの起こり

"google_drive" という Ruby gem があります。

これを使うと Ruby のコードから Google Drive にアクセスして操作することができます。

仕事で Google Spreadsheet に書き込みに使っているのですが、既存のコードではセルへの書き込みがべた書きになっていて使い勝手が今ひとつ。

require 'google_drive'

# ご自身のアクセスキーは Google Cloud console でご用意ください
session = GoogleDrive::Session.from_service_account_key('paht/to/your_account_key.json')

# spreadsheet = session.create_spreadsheet('テストスプレッドシート') # 新しく作成する場合
spreadsheet = session.spreadsheet_by_title('テストスプレッドシート')

# worksheet = spreadsheet.add_worksheet('べた書きシート') # 新しく作成する場合
worksheet = spreadsheet.worksheet_by_title('べた書きシート')

worksheet[1, 1] = 'Alice'
worksheet[1, 2] = 'Bob'
worksheet[1, 3] = 'Charlie'

worksheet[2, 1] = 1
worksheet[2, 2] = 2
worksheet[2, 3] = 3

worksheet[3, 1] = 11
worksheet[3, 2] = 22
worksheet[3, 3] = 33

worksheet[4, 1] = 111
worksheet[4, 2] = 222
worksheet[4, 3] = 333

worksheet.save

これでは例えば Alice と Bob の間に David を追加したいとなったとき、Bob と Charlie の列は挿入する位置をすべてずらさなければなりません。

スプレッドシートはヘッダ行で列を特定できるのですから、挿入するときもヘッダで指定したいものです。

書き込みクラス

そんなわけで。ヘッダで列を指定できるクラスを書いてみました。

コードは GitHub Gist にも置いてあります。

SheetWriter · GitHub

class SheetWriter
  class Row
    def initialize(writer, headers, index)
      @writer = writer
      @headers = headers
      @index = index
    end

    def []= (header, value)
      @writer[@index, @headers.index_of(header)] = value
    end
  end

  class Headers
    def initialize(writer, headers, initial_index)
      @writer = writer
      @indices = headers.zip(initial_index..).to_h
      @next_header_index = @indices.size + initial_index
      @initial_index = initial_index

      @indices.each { |header, index| writer[initial_index, index] = header }
    end

    def add(header)
      @writer[@initial_index, @next_header_index] = header
      @indices[header] = @next_header_index
      @next_header_index = @next_header_index.succ
    end

    def index_of(header)
      add(header) if !@indices.has_key?(header)

      @indices[header]
    end
  end

  def initialize(sheet, headers: [], initial_index: 1)
    @sheet = sheet
    @headers = Headers.new(self, headers, initial_index)
    @last_row_index = initial_index
  end

  def next_row
    @last_row_index = @last_row_index.succ
    Row.new(self, @headers, @last_row_index)
  end

  def []= (row_index, column_index, value)
    @sheet.append(row_index, column_index, value)
  end
end

使ってみる

require 'google_drive'
require_relative 'sheet_writer'

session = GoogleDrive::Session.from_service_account_key('paht/to/your_account_key.json')

# spreadsheet = session.create_spreadsheet('テストスプレッドシート') # 新しく作成する場合
spreadsheet = session.spreadsheet_by_title('テストスプレッドシート')

# worksheet = spreadsheet.add_worksheet('writerを使ったシート') # 新しく作成する場合
worksheet = spreadsheet.worksheet_by_title('writerを使ったシート')

# インタフェース変換のアダプタ
class Adapter
  def initialize(worksheet); 
    @worksheet = worksheet
  end

  def append(row_index, column_index, value)
    @worksheet[row_index, column_index] = value
  end
end

writer = SheetWriter.new(Adapter.new(worksheet))

row = writer.next_row

row['Alice'] = 1
row['Bob'] = 2
row['Charlie'] = 3

row = writer.next_row

row['Bob'] = 22
row['Charlie'] = 33
row['Alice'] = 11

row = writer.next_row

row['Charlie'] = 333
row['Alice'] = 111
row['Bob'] = 222

worksheet.save

無事スプレッドシートに書き込めました。

ちなみに。 worksheet オブジェクトは []= で操作できますが、より一般的なメソッド呼び出しで操作できるようにしたいため、あえて #append で操作するように定義しています。そのため、インタフェースを変換するためのアダプタを使っています。

特異メソッドを使って操作するオブジェクトにメソッドを直接定義するという方法もありますが、オブジェクトごとに使えるメソッドが違うことを把握するのも面倒なので、使い所は選びそうです。

def worksheet.append(row_index, column_index, value)
  self[row_index, column_index] = value
end

writer = SheetWriter.new(worksheet)

David を追加する

「Alice と Bob の間に David を追加したい」ばあいの面倒がどのようになったか見てみます。

# ここまで上のコードと同じなので省略

# 先にヘッダの順序を指定
writer = SheetWriter.new(Adapter.new(worksheet), headers: %w(Alice David Bob Charlie))

row = writer.next_row

row['Alice'] = 1
row['Bob'] = 2
row['Charlie'] = 3
row['David'] = 4 # 追加

row = writer.next_row

row['Bob'] = 22
row['Charlie'] = 33
row['Alice'] = 11
row['David'] = 44 # 追加

row = writer.next_row

row['Charlie'] = 333
row['Alice'] = 111
row['Bob'] = 222
row['David'] = 444 # 追加

worksheet.save

ヘッダの順序をあらかじめ指定しておくことで、列の順序を気にすることなくヘッダで指定した位置に値を設定することができるようになりました。

配列に出力する

#append が定義されているオブジェクトなら何にでも出力できる利点を享受してみましょう。

たとえば配列には Array#append が定義されているので、そのまま出力対象のオブジェクトに指定できます。

require_relative 'sheet_writer'

my_sheet = []

# 配列の添字は 0 始まりなので initial_index に 0 を指定
writer = SheetWriter.new(my_sheet, initial_index: 0)

row = writer.next_row

row['Alice'] = 1
row['Bob'] = 2
row['Charlie'] = 3

row = writer.next_row

row['Bob'] = 22
row['Charlie'] = 33
row['Alice'] = 11

row = writer.next_row

row['Charlie'] = 333
row['Alice'] = 111
row['Bob'] = 222

pp my_sheet

結果。

[0, 0, "Alice", 1, 0, 1, 0, 1, "Bob", 1, 1, 2, 0, 2, "Charlie", 1, 2, 3, 2, 1, 22, 2, 2, 33, 2, 0, 11, 3, 2, 333, 3, 0, 111, 3, 1, 222]

Array#append は引数の複数の値をフラットに追加するので Enumerable#each_slice で少しみやすくします。あわせて行と列の位置の順に並べ替えます。

pp my_sheet.each_slice(3).sort

[[0, 0, "Alice"], [0, 1, "Bob"], [0, 2, "Charlie"], [1, 0, 1], [1, 1, 2], [1, 2, 3], [2, 0, 11], [2, 1, 22], [2, 2, 33], [3, 0, 111], [3, 1, 222], [3, 2, 333]]