こんにちは、アルバイトのarakiです．

寒さが厳しくなっていますが、少し怖い話(?)があったので共有します．

僕は以前、毎日0時頃にDBからその日の更新分をスプレッドシートへと書き込むRubyプログラムを書きました．するといつの日からか、そのプログラムが走っているサーバが夜な夜な異常に重くなるという現象が発生したのです．怖いですね．

最初はDBから取り出したデータが大きすぎたのかと思ったのですが、原因は意外なところにありました．

それまで、google_driveというGemを使ってスプレッドシートへの書き込みを行っていたのですが、このGemを使って書き込みをするときにシート全体にアクセスしていたのです．そのため、シートのサイズが大きくなるほど、サーバへの負担も重くなる、というカラクリでした．

今回はそんな問題を解決するために、HTTP通信で直接スプレッドシートのAPIを叩く方法を紹介します．

今回やること

今回の例では、SampleDBのfruitsテーブルの内容をスプレッドシートに書き込む、という処理をします．v

fruitsテーブルの内容は下の画像の通りです．

ソースコードは以下の通りです．[クライアント ID]などのようにしている部分は、自分のクライアントIDなどに置き換えてください．

require 'active_record'
require 'json'
require 'net/http'
require 'pp'

# データベースとの接続処理
config = {
  adapter:  'mysql2',
  host:     'localhost',
  username: 'root',
  password: '',
  database: 'SampleDB'
}
ActiveRecord::Base.establish_connection(config)

# fruitsテーブル．
class Fruit < ActiveRecord::Base
end

# アクセストークンを取得する．
def fetch_access_token
  uri = URI.parse('https://www.googleapis.com/oauth2/v4/token')
  request = Net::HTTP::Post.new(uri)

  # APIの認証データ．
  request.set_form_data(
    'client_id'     => [クライアント ID],
    'client_secret' => [クライアント シークレット],
    'grant_type'    => 'refresh_token',
    'refresh_token' => [refresh_token]
  )

  req_options = {
    use_ssl: uri.scheme == 'https',
  }
  response = Net::HTTP.start(uri.hostname, uri.port, req_options) do |http|
    http.request(request)
  end

  response_body= JSON.parse(response.body)
  response_body['access_token']
end

# 書き込みリクエストのためのjsonを生成する．
def cell_appending_json(sheet_id, records)
  rows = records.map do |record|
    row = record.attributes.values.map do |value|
      key = value.class.superclass == Numeric ? 'numberValue' : 'stringValue'
      { userEnteredValue: { key => value } }
    end
    { values: row }
  end

  request_hash = {
    requests: [{
      appendCells: {
        sheetId: sheet_id,
        rows:    rows,
        fields:  'userEnteredValue'
      }
    }]
  }

  puts 'requestの中身'
  pp request_hash
  request_hash.to_json
end

# 書き込みリクエストを行う．
def request_cell_appending(spreadsheet_id, request_json)
  access_token = fetch_access_token

  endpoint = 'https://sheets.googleapis.com/v4/spreadsheets/'
  uri = URI.parse("#{endpoint}#{spreadsheet_id}:batchUpdate")

  request = Net::HTTP::Post.new(uri)
  request['Authorization'] = "OAuth #{access_token}"
  request['Content-Type'] = 'application/json'
  request.body = request_json
  req_options = {
    use_ssl: uri.scheme == 'https',
  }

  response = Net::HTTP.start(uri.hostname, uri.port, req_options) do |http|
    http.request(request)
  end

  # リクエストに対するレスポンスを返す．
  JSON.parse(response.body)
end

# 以下メイン処理

# スプレッドシートのurlの/d/から/editまでの間．
spreadsheet_id = [スプレッドシートのID]
# スプレッドシートのurlのgid=に続く数字．
sheet_id = [シートのID]

records = Fruit.all

json = cell_appending_json(sheet_id, records)
puts 'レスポンス'
pp request_cell_appending(spreadsheet_id, json)

ソースコードの内容

fetch_access_token

fetch_access_tokenメソッドでは、アクセストークンを取得しています．

アクセストークンは、その名の通りAPIにアクセスするためのものですが、短期間で無効になってしまうので、今回は実行の度に取得するようにしています．

request.set_form_dataの部分では、APIを使えるクライアントのIDなどを指定します．例ではソースコードにそのまま貼っていますが、jsonファイルとして別のところで保管し、それを読み込む等でも良いでしょう．

後の部分では、HTTP通信のための各種処理を行い、最後にアクセストークンを返すようにしています．

cell_appending_json

HTTPリクエストで使うJSONを生成しています．

処理内容は、与えられたrecordsの各値をSheets APIのRequestsオブジェクトの形式に挿入するというものです．

引数のsheet_idは、スプレッドシートのシートを区別するためのもので、URLのgid=から後にある数字列です．

ちなみに、key = value.class.superclass == Numeric ? 'numberValue' : 'stringValue'は、スプレッドシートに書き込む値が数値か文字列かを指定しています．もし{ userEnteredValue: { 'stringValue' => value } }と指定してvalueが数値だと、リクエスト時にエラーが発生します．なお、他にもboolValue等が指定できるようなので、必要があれば調べてみるといいかもしれません．

request_cell_appending

HTTPリクエストを送り、スプレッドシートの書き込みを行います．

リクエストを送るURLはhttps://sheets.googleapis.com/v4/spreadsheets/[spreadsheet_id]:batchUpdateとなっています．つまり、Sheets APIのbatchUpdateメソッドを使っているということですね．

結果

実行すると以下のように出力されます．

requestの中身以下のハッシュが、JSON化されてリクエストのbody部になることがわかりますね．

また、レスポンス以下のハッシュは、リクエストに対するレスポンスのbody部です．エラーが起きず正常に処理されたため、repliesの値は空になっています．シートに書き込まれない等の不具合が起きた場合は、このレスポンスを読めば解決できるかもしれません．

$ruby sample.rb
requestの中身
{:requests=>
  [{:appendCells=>
     {:sheetId=>0,
      :rows=>
       [{:values=>
          [{:userEnteredValue=>{"numberValue"=>1}},
           {:userEnteredValue=>{"numberValue"=>1}},
           {:userEnteredValue=>{"stringValue"=>"apple"}},
           {:userEnteredValue=>{"numberValue"=>170}},
           {:userEnteredValue=>{"stringValue"=>2019-11-09 14:05:40 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>2}},
           {:userEnteredValue=>{"numberValue"=>2}},
           {:userEnteredValue=>{"stringValue"=>"peach"}},
           {:userEnteredValue=>{"numberValue"=>400}},
           {:userEnteredValue=>{"stringValue"=>2019-11-09 14:06:19 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>3}},
           {:userEnteredValue=>{"numberValue"=>3}},
           {:userEnteredValue=>{"stringValue"=>"banana"}},
           {:userEnteredValue=>{"numberValue"=>200}},
           {:userEnteredValue=>{"stringValue"=>2019-11-09 14:13:51 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>4}},
           {:userEnteredValue=>{"numberValue"=>1}},
           {:userEnteredValue=>{"stringValue"=>"orange"}},
           {:userEnteredValue=>{"numberValue"=>230}},
           {:userEnteredValue=>{"stringValue"=>2019-11-10 15:08:31 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>5}},
           {:userEnteredValue=>{"numberValue"=>2}},
           {:userEnteredValue=>{"stringValue"=>"lemon"}},
           {:userEnteredValue=>{"numberValue"=>240}},
           {:userEnteredValue=>{"stringValue"=>2019-11-10 15:08:48 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>6}},
           {:userEnteredValue=>{"numberValue"=>3}},
           {:userEnteredValue=>{"stringValue"=>"kiwi"}},
           {:userEnteredValue=>{"numberValue"=>400}},
           {:userEnteredValue=>{"stringValue"=>2019-11-10 15:09:11 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>7}},
           {:userEnteredValue=>{"numberValue"=>1}},
           {:userEnteredValue=>{"stringValue"=>"strawberry"}},
           {:userEnteredValue=>{"numberValue"=>360}},
           {:userEnteredValue=>{"stringValue"=>2019-11-10 15:09:33 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 10:19:37 UTC}}]},
        {:values=>
          [{:userEnteredValue=>{"numberValue"=>8}},
           {:userEnteredValue=>{"numberValue"=>3}},
           {:userEnteredValue=>{"stringValue"=>"kaki"}},
           {:userEnteredValue=>{"numberValue"=>170}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 14:44:39 UTC}},
           {:userEnteredValue=>{"stringValue"=>2020-01-19 14:46:12 UTC}}]}],
      :fields=>"userEnteredValue"}}]}
レスポンス
{"spreadsheetId"=>[スプレッドシートのID],
 "replies"=>[{}]}

そして、スプレッドシートには無事データが書き込まれ、下の画像のようになりました．

おわりに

以上になります．

今回は空いている一番上のセルに値を書き込んでいくプログラムを書きましたが、リファレンスのSheets APIのRequestsオブジェクトのページを参考にすれば、シートの追加や削除、オートフィル、データの整列など多様な処理が行えます．もちろん、batchUpdate以外のメソッドを使うこともできるでしょう．

Gemを使うと面倒な処理を勝手にやってくれて、処理を簡単に書けることが 多いです．一方で、今回の例のようにオーバーヘッドで思わぬ問題が発生することもあります．そういうときは、Gemを使わず直接APIを叩いてしまうのも有効かもしれませんね．それでは．