RubyからGoogle Sheets上のデータを読み書きするメモ[Google API経由]

Google Sheets（昔はGoogle Spreadsheetsと呼ばれてた）のデータをRubyから読み書きする方法を調べても、情報が古くてOAuthの認証などの手順が変わっていたり、リフレッシュトークンの取得の細かい部分が無かったりするので、2016年現在の方法をまとめました。

※こちらの記事は手順とコードのメモですので、キャプチャ画像有りの詳細手順を確認されたい場合はこちらをご確認ください。
[2016最新版]Google SheetsにRubyプログラムからアクセス・書き込みを行う手順

前準備：GoogleアカウントへのOAuth認証

Google Developers Console上で、Google Drive APIを有効にする

Google Developers Consoleに使用したいGoogleアカウントでログインします。初めてアクセスした場合は、Developers Console上に一つもプロジェクトが作られていないので、Getting started画面が表示されます。

Getting started画面

この画面で、APIを利用するのボックスをクリックします。すると、ポップアップが表示されるので、好きな名前でプロジェクトを新規作成してください。

自動的に作成したプロジェクトのホーム画面（以下の画像の画面）に飛ばされると思いますので、プロジェクトの左メニューから、「APIと認証」-「API」を選び、右のメイン画面から「Drive API」を探してクリックしてください。

APIを有効にするボタンを押せば、Google Drive APIが利用できるようになります。

これでDrive APIの有効化が完了です。

複数のGoogleアカウントを所有していて、マルチアカウント（右上のメニューから使いたいGoogleアカウントを選択できる機能）を使用していると、自分の使いたいアカウントでGoogle Developer Consoleにログインできないことがあります。その場合は、Chromeなどのシークレットタブ機能を開いて、シークレットモードでDeveloper consoleを開くとうまくいきます。

認証情報の追加、OAuthクライアントIDの作成

続いて、OAuth認証で使用するクライアントIDを作成します。左メニューの「APIと認証」-「認証情報」を選択すると、認証情報が一つも作成されていない場合、以下のAPI認証情報ポップアップ画像のようにAPI認証情報の新規作成を促されますので、「OAuth2.0クライアントID」作成を選択します。

API認証情報ポップアップ画像

確認画面が表示されたら、「同意画面を設定」をクリック。

OAuth認証を行う際に表示される同意画面の作成を行いますが、「サービス名」のみ入力すればOKです。他の項目は必須項目でないので、必要ならあとから追加すればいいですね。

すると次にクライアントID作成画面が表示されますので、「その他」を選択します。これはMac上で走らせるスクリプトを使うので、他の項目でぴったり来る項目が無いからです。もし、スマホアプリなど当てはまる項目があれば、それを選択してください。

これでクライアントID作成が完了です。画面上にクライアントID一式が表示されますが、これらをコピペして使うわけではないので、特に文字の羅列の控えなどは不要です。

実際にアプリからGoogle Drive（Google sheets）にアクセスする際に使う、認証情報一式が記載されたjsonファイルをダウンロードするために、下矢印ボタンを押して下さい。

これで認証情報の追加、クライアントIDの作成が完了。

リフレッシュ・トークン(refresh token)を取得する

ここから最も複雑な操作である、リフレッシュ・トークン(refresh token)の取得を行います。

先ほどの手順でダウンロードした、こんな感じのなが～い名前の名前のjsonファイル
client_secret_474352116113-52e....ui23u1.apps.googleusercontent.com.json
の中身を確認してください。

client_secret_xxxx.json

{
    "installed":
    {
        "client_id":"679357212348-52eh246FooBarBazkawndi3v7ajk6w8.apps.googleusercontent.com",
        "project_id":"my-project",
        "auth_uri":"https://accounts.google.com/o/oauth2/auth",
        "token_uri":"https://accounts.google.com/o/oauth2/token",
        "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
        "client_secret":"FooBarBazHXZl069mskl2Nkq5mi",
        "redirect_uris":
        [
            "urn:ietf:wg:oauth:2.0:oob",
            "http://localhost"
        ]
    }
}

本当は上記の中身は改行されずに1行になっていますが、見やすいように改行とインデントを加えています。

この中から、client_id、redirect_urisを取り出し、以下のURLの該当部分に当てはめて、そのURLに対してブラウザのURLバーからリクエストを行います。redirect_urisは2つあって迷いますが、urn:ietf:wg:oauth:2.0:oobのほうを使います。

https://accounts.google.com/o/oauth2/auth?client_id=[先ほど取得したクライアントID]&redirect_uri=[先ほど設定したリダイレクトURI]&scope=https://www.googleapis.com/auth/drive&response_type=code&approval_prompt=force&access_type=offline

そうするとブラウザ上で下図のように認証を求める画面が表示されますので、「許可」を選択

OAuth認証許可画面

そうすると、次の画面で認証コードが表示されますので、忘れずにコピーします。あとは先程と同じようにjsonファイルからclient_id、client_secretの値をコピってきて、以下のコマンドをターミナルから実行します。

curl -d client_id=[クライアントID] -d client_secret=[クライアントシークレット] -d redirect_uri=[リダイレクトURI] -d grant_type=authorization_code -d code=[認証コード] https://accounts.google.com/o/oauth2/token

リフレッシュトークン取得に成功すると、以下のようなjson形式の返り値が得られます。この中のrefresh_tokenがリフレッシュトークンになります。（下記の返り値はそれっぽい文字列を当て込んだ偽物なので、使えません。安心して下さい、変えてますからm(_ _)m ）

$ curl -d client_id=679357212348-52eh246FooBarBazkawndi3v7ajk6w8.apps.googleusercontent.com -d client_secret=FooBarBazHXZl069mskl2Nkq5mi -d redirect_uri=urn:ietf:wg:oauth:2.0:oob -d grant_type=authorization_code -d code=4/MMgXtsAkasfynmUMPasdkd5hn6P-bsasdtpwFzFOPassafd https://accounts.google.com/o/oauth2/token
{
  "access_token" : "uja12.asurun1_xlaksjdfask11ASssboqvBeB1-s-UPASlX0S-JJkqSUYReQKk23raljfaASAD221dfa-q",
  "token_type" : "Bearer",
  "expires_in" : 3600,
  "refresh_token" : "1/eFasdfsfDzpUQ0TjiJt-dkVuIN57WFHJfQNNyqNh7DDFSAFASDFun6zK6XiATaASDF"
}

ちなみに、

{
  "error" : "invalid_grant",
  "error_description" : "Invalid code."
}

や、

{
  "error" : "invalid_grant",
  "error_description" : "Code was already redeemed."
}

のエラーが返ってきた場合は、ブラウザにURLを打ち込んでコードを取得したところまでもどり、新規のコードを発行して再度curl実行してください。

これでようやく、Google SheetsにアクセスするRubyプログラムを作れる段階まできました。

参考

Google DriveのスプレッドシートにRubyでアクセスする方法

Google drive : redirect_uri_mismatch - Stack Overflow

RubyスクリプトからGoogle Sheetsにアクセスする

あとは、上記で入手した各種認証情報を使って、スプレッドシートへのアクセスするテストスクリプトをRubyで書いていきます。

google-drive gemのインストール

Rubyからのアクセスにはgoogle_driveというその名もその通りのgemが必要ですので、以下のようなGemfileを作成して、

# A sample Gemfile
source "https://rubygems.org"

gem 'google_drive'

bundlerでGemをインストールします。

$ bundle install --path .bundle
Fetching gem metadata from https://rubygems.org/........
Fetching version metadata from https://rubygems.org/...
Fetching dependency metadata from https://rubygems.org/..
Resolving dependencies...
Installing i18n 0.7.0
Installing json 1.8.3 with native extensions
Installing minitest 5.8.2
Installing thread_safe 0.3.5
Installing addressable 2.3.8
Installing extlib 0.9.16
Installing multi_json 1.11.2
Installing multipart-post 2.0.0
Installing jwt 1.5.2
Installing little-plugger 1.1.4
Installing memoist 0.12.0
Installing retriable 1.4.1
Installing mini_portile 0.6.2
Installing oauth 0.4.7
Installing multi_xml 0.5.5
Installing rack 1.6.4
Using bundler 1.11.2
Installing tzinfo 1.2.2
Installing launchy 2.4.3
Installing autoparse 0.3.3
Installing faraday 0.9.2
Installing logging 2.0.0
Installing nokogiri 1.6.6.2 with native extensions
Installing activesupport 4.2.4
Installing signet 0.6.1
Installing oauth2 1.0.0
Installing googleauth 0.4.2
Installing google-api-client 0.8.6
Installing google_drive 1.0.2
Bundle complete! 1 Gemfile dependency, 29 gems now installed.
Bundled gems are installed into ./.bundle.

Rubyプログラムの作成

これで必要なものは一通りそろったので、以下のようにスプレッドシートの行と列を出力させるスクリプトを書いて下さい。以下のYOUR_XXXXというところには、これまで使ってきた各種キーをセットします。

ひとつ見慣れない「YOUR_SPREAD_SHEET_KEY」という所がありますが、ここには、GoogleスプレッドシートのURLの/d/と/edetで挟まれたランダム文字列を入力します。

https://docs.google.com/spreadsheets/d/23assdkfa-AASDFASlkasfslasdf23kl-IN123kkdsj/edit#gid=1186906454

がアクセスしたいスプレッドシートのURLならば、23assdkfa-AASDFASlkasfslasdf23kl-IN123kkdsjがスプレッドシートキーになります。

test_connect_ss.rb

require "rubygems"
require "google_drive"

def show_spreadsheet
  client_id     = "YOUR_CLIENT_ID"
  client_secret = "YOUR_CLIENT_SECRET"
  refresh_token = "YOUR_REFRESH_TOKEN"
  client = OAuth2::Client.new(
    client_id,
    client_secret,
    site: "https://accounts.google.com",
    token_url: "/o/oauth2/token",
    authorize_url: "/o/oauth2/auth")
  auth_token = OAuth2::AccessToken.from_hash(client,{:refresh_token => refresh_token, :expires_at => 3600})
  auth_token = auth_token.refresh!
  session = GoogleDrive.login_with_oauth(auth_token.token)
  ws = session.spreadsheet_by_key("YOUR_SPREAD_SHEET_KEY").worksheets[0]

  # レコード数を取得
  p ws.num_rows
  # カラム数を取得
  p ws.num_cols
end

show_spreadsheet

あとは、これを実行して、

$ bundle exec ruby test_connect_ss.rb
67
44

という感じで行と列のカウント数が戻ってきたら完成です。

書き込みは上記のwsというスプレッドシートオブジェクトを変更してsaveメソッドを叩くだけです。

#59行、50列目のセルに"test_input!"という文字列を書き込み
ws[59, 50] = "test_input!"
ws.save

認証などの前準備が大変でしたけど、Gemを使えば結構簡単にRubyから操作できますね！
これで書類仕事を自動化して楽するなど、いろいろできそうです。
ぜひ使ってみてくださいませー。