こんにちは、アルバイトのkoyamaです。

Googleスプレッドシートの内容をRubyのプログラム上で扱う時みなさんはどうしているでしょうか？ 以前私が紹介したgem、google-drive([入門編] RubyからGoogleスプレッドシートを操作する簡単チュートリアル)を使用するというのも一つの手です。 ではスプレッドシートが数千行あり、列も数十個にもおよぶ場合ではどうでしょう？。 google-driveはスプレッドシートの内容を全てメモリ上に展開するため、メモリから溢れたり処理が滞ってしまうことがあります。 このような大きなデータを持つスプレッドシートを扱う際にはGoogle Apps Script(以降GAS)を用いるのが有効です。

GASの設定

今回の目標はGASを用いてスプレッドシートの特定の情報を抜き出し、rubyのopen-uriを使ってそれを取得するというところまです。 まずは任意のスプレッドシートを作成します。 スプレッドシートの内容は例として以下の図のようにしておきましょう。

作成したスプレッドシートのメニューのツールからスクリプトエディタを選択します。

選択するとGASのエディタ画面が出てきますので以下のコードを入力します。

GASスクリプト

function doGet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  var data_range = sheet.getRange(1, 1, lastRow, lastCol);
  var data_set = data_range.getValues();

  var response = "";
  for(var row = 1; row < lastRow; row++) {
    if (data_set[row][2] != ""){
      continue ;
    }else{
      response += data_set[row].join(",")
      response += "\n"
    }
  };
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}

今回はチェックがついていない行のデータを取得するようにしました。 また、doGet関数を用いることでページにアクセスがあった時にアプリケーションを実行し、htmlかtextオブジェクトを返すことができます。 次にエディタのメニューの公開からウェブアプリケーションとして公開を選択します。 今回は例として画像のように設定します。

この時に実行可能な対象を匿名(anonymous)にすることでopen-uriから直接アプリのurlを渡してGASを実行できるようになります。

設定が完了するとアプリケーションのurlが発行されるのでこれをブラウザのurl欄にペーストして欲しかった値が帰ってくるかを確認しましょう。(chromeの場合はシークレットモードで立ち上げると見れます)
実際にブラウザで確認するとこのような感じになります↓

Rubyでアプリケーションにアクセス

ここまでできたら最後はrubyからurlを叩いて値を取得するだけです。 以下のrubyのプログラムを作成します。

rubyのコード

#test.rb
require 'csv'
require 'open-uri'

url = ""
charset = nil
html = open(url,"r") do |f|
  charset = f.charset
  f.read
end
response = html.gsub("\\n","\n").gsub(/^"|"$/,"")
p CSV.parse(response)

実際にrubyから取得できるかを見てみます。

$ ruby test.rb
[["10月", "神無月", nil], ["11月", "霜月", nil], ["12月", "師走", nil]]

狙い通りチェックのついていない行を取得できました。

まとめ

全体で数千行に渡る大きなシートを扱う際には、そのままではメモリを圧迫してしまうため手元で扱えるだけの量に絞る必要があります。今回使ったGASのdoGet関数などを使用し、データの前処理を行うことでメモリを圧迫せず快適にデータを扱うことができます。