RubyでGoogle SpreadSheetの式を再計算させる方法

Google SpreadSheets

Google Spreadsheet をAPIからコピーしたりした後など、数式の参照や計算が反映されていないことがおこりえる。 こういった時に、該当のセルの数式を再評価する必要がある。 最も手軽でてっとり早いやり方は、手動でセルの計算式を再評価させる方法だ。 ブラウザでスプレッドシートを開き、セルを選択し、Enterキーを押すと式が再評価される。 しかし、再評価させたい式が多数ある場合、作業が大変になってくる。

シート中の式の再評価をAPIから行う方法を見つけた。

この記事に書いてあるように、

  1. 数式のセルの値を数式として取り出すようにvalueRenderOptionFORMULAを指定
  2. その値をシートにそのまま入れ、ユーザーが入力したときと同じように処理するようにvalueInputOptionUSER_ENTEREDを指定

してAPIを呼ぶことで、数式を再計算させることができる。

google-apis-clientを使って、実装すると以下のようになる。

require 'google/apis/sheet_v4'
sheets = Google::Apis::SheetsV4::SheetsService.new

sheet_id = 'YOUR_SPREADSHEET_ID'
worksheet_title = 'SOME_TITTLE'

# 1. 数式は数式のままでセルの値を取り出す
values = sheets.get_spreadsheet_values(sheet_id, "#{worksheet_title}!$A$1:$YY", value_render_option: 'FORMULA')
# 2. ユーザーがUIで入力したのと同じようにオプションを追加
sheets. sheets.update_spreadsheet_value(sheet_id, values.range, values, value_input_option: 'USER_ENTERED')

ここで$A$1::$YYを指定しているのは、シート全体を選択するためである。 もし該当のセルの範囲がわかるようであれば、その範囲を指定すれば良い。

ということで、値を数式として入れ直すというAPIとしてはコストかかりそうなリクエストなのだけれど、これでシートの数式をいっきに更新できます。

参考