ruby から google スプレッドシートを操作してみた

• 概要
• 環境
• ライブラリインストール
• API の有効化
• Getting Started (with OAuth)
• API key を使って認証する
  • スプレッドシートを共有モードにする
  • API Key の作成
  • API Key を使ってアクセスするスクリプト
  • あれ共有可能リンクなら API Key がなくても閲覧できるから API Key は必要ないんじゃないの
• Write してみる
• 最後に
• 参考サイト

概要

ruby から Google スプレッドシートの Read/Write を行ってみました
SpreadSheet の API バージョンは v4 になります
認証も OAuth を使った認証と API Key を使った認証の 2 つの方法を紹介します

環境

• macOS 10.14.4
• Ruby 2.6.2p47

ライブラリインストール

• bundle init
• vim Gemfile

gem "google-api-client"

• bundle install --path vendor

API の有効化

このページ にアクセスすると自動的に Quickstart のプロジェクトの作成と SpreadSheet API の有効化、認証情報を作成してくれます
credentials.json もダウンロードできるのでしましょう

API コンソール から作成されたプロジェクトや認証情報を確認することができます

Getting Started (with OAuth)

とりあえずコピペで動作するサンプルがあるのでそれを動かしてみます
認証が OAuth なので一旦ブラウザで認証して得られたトークンをターミナルに貼り付けます

• vim app.rb

require 'google/apis/sheets_v4'
require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'fileutils'

OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'.freeze
APPLICATION_NAME = 'Google Sheets API Ruby Quickstart'.freeze
CREDENTIALS_PATH = 'credentials.json'.freeze
TOKEN_PATH = 'token.yaml'.freeze
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

def authorize
  client_id = Google::Auth::ClientId.from_file(CREDENTIALS_PATH)
  token_store = Google::Auth::Stores::FileTokenStore.new(file: TOKEN_PATH)
  authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
  user_id = 'default'
  credentials = authorizer.get_credentials(user_id)
  if credentials.nil?
    url = authorizer.get_authorization_url(base_url: OOB_URI)
    puts 'Open the following URL in the browser and enter the ' \
         "resulting code after authorization:\n" + url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI
    )
  end
  credentials
end

service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

# https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
spreadsheet_id = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
range = 'Class Data!A2:E'
response = service.get_spreadsheet_values(spreadsheet_id, range)
puts 'Name, Major:'
puts 'No data found.' if response.values.empty?
response.values.each do |row|
  puts "#{row[0]}, #{row[4]}"
end

• bundle exec ruby app

として実行するとアクセスしてほしい URL が表示されるのでブラウザに貼り付けてアクセスします

Open the following URL in the browser and enter the resulting code after authorization:
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force&client_id=1234567890-abcdefghijklmnopqrstuvwxyz.apps.googleusercontent.com&include_granted_scopes=true&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets.readonly

OAuth の認証画面になるので認証ユーザを選択します

「QuickStart」というアプリから SpreadSheet の読み込み権限を与えても OK か確認する画面になるので許可しましょう

するとトークンが得られるのでそれをターミナルに貼り付けます

トークンは token.yaml に保存されて以降はそれを使ってアクセスするようになります

認証に成功するとテスト用のスプレッドシートから情報を読み込んで表示してくれます

ちなみに Google アカウントの「サードパーティによるアクセス」を確認すると許可した「Quickstart」アプリが追加されていることが確認できます

API key を使って認証する

基本は OAuth を使いましょう
ですが、自分のスプレッドシートにしかアクセスしなかったりバッチなど CLI 環境しか使えない場合にはブラウザ認証するのは面倒です
なので、例のごとく API Key を使った認証でスプレッドシートにアクセスしてみます

スプレッドシートを共有モードにする

API Key を使ってスプレッドシートにアクセスするにはスプレッドシートを共有モードにする必要があります
スプレッドシートにアクセスして「共有」から共有可能なリンクを取得できるようにしましょう

またスプレッドシートの ID も取得しておきましょう
ID は共有可能リンクにも含まれているオムニバーに表示されているランダムな文字列になります

API Key の作成

コンソールの認証情報管理画面から「API キー」を作成しましょう
このキーを使って API をコールします
とりあえず今回はテストなので制限なしで作成しました

API Key を使ってアクセスするスクリプト

とりあえず Read してみます
ちなみにアクセスするスプレッドシートは以下のような感じでデータを入れています

• vim app.rb

require 'google/apis/sheets_v4'

service = Google::Apis::SheetsV4::SheetsService.new
service.key = 'your_api_key'
spreadsheet_id = 'your_spreadsheet_id'
range = 'A1:B10'
response = service.get_spreadsheet_values(spreadsheet_id, range)
puts 'No data found.' if response.values.empty?
response.values.each do |row|
  puts "#{row[0]}, #{row[1]}"
end

• bundle exec ruby app.rb

これで実行すると A 列と B 列の 10 行目までの値が標準出力に表示されると思います

あれ共有可能リンクなら API Key がなくても閲覧できるから API Key は必要ないんじゃないの

と思うと思います
ですが API v4 からは API 経由で共有可能リンクにアクセスする場合は API Key がないと forbidden: The request is missing a valid API key. のエラーが発生するようになっています

Write してみる

では最後に Write です
残念ながら Write は OAuth2 認証でないと呼べない API なので認証を OAuth2 に書き換えます

• vim app.rb

require 'google/apis/sheets_v4'
require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'fileutils'

OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'.freeze
APPLICATION_NAME = 'Google Sheets API Ruby Quickstart'.freeze
CREDENTIALS_PATH = 'credentials.json'.freeze
TOKEN_PATH = 'token.yaml'.freeze
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

def authorize
  client_id = Google::Auth::ClientId.from_file(CREDENTIALS_PATH)
  token_store = Google::Auth::Stores::FileTokenStore.new(file: TOKEN_PATH)
  authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
  user_id = 'default'
  credentials = authorizer.get_credentials(user_id)
  if credentials.nil?
    url = authorizer.get_authorization_url(base_url: OOB_URI)
    puts 'Open the following URL in the browser and enter the ' \
         "resulting code after authorization:\n" + url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI
    )
  end
  credentials
end

service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

spreadsheet_id = 'your_spreadsheet_id'
range = 'A11'
data = Google::Apis::SheetsV4::ValueRange.new
data.major_dimension = 'ROWS'
data.range = 'A11'
data.values = [["hoge"]]
options = {
  value_input_option: 'RAW'
}
response = service.update_spreadsheet_value(spreadsheet_id, range, data, options)
puts response.updated_cells

• bundle exec ruby app.rb

先ほどと同じようにブラウザに OAuth の URL を貼り付けてアクセスしアプリからのアクセスを許可します
認証部分で変更している点はスコープです
書き込みの権限が必要なので以下のようにしています
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

書き込みに使うメソッドは update_spreadsheet_value になります
これに必要な引数は「スプレッドシート ID」と「書き換えるセル情報」と「データ」と「オプション」になります
その中でデータは Google::Apis::SheetsV4::ValueRange というクラスのオブジェクトでなければなりません (内部的にはただ JSON に変換しているだけ)

data = Google::Apis::SheetsV4::ValueRange.new
data.major_dimension = 'ROWS'
data.range = 'A11'
data.values = [["hoge"]]
options = {
  value_input_option: 'RAW'
}

major_dimension は range で指定した範囲で行 (ROWS) ベースで取得するのか列 (COLUMNS) ベースで取得するのかを決めることができます
例えば range=A1:B2,majorDimension=ROWS という感じでリクエストした場合は [[1,2],[3,4]] と返ってきます
range=A1:B2,majorDimension=COLUMNS という風にリクエストすると [[1,3],[2,4]] という風に返ってきます
今回は返り値を使うわけではないのでとりあえず ROWS を入れています

values は多重配列で指定します
とりあえず文字列を設定するだけの values にしています

options の value_input_option はデータが参照データなのか生データなのかなどを指定するためのオプションです
今回は文字列データをセルに書き込みたいので「RAW」を指定します

返ってくる値は UpdateValuesResponse になっています
とりあえず上記は更新できたセルの数を表示しています
1 つ更新されているので成功です

最後に

Ruby から Google スプレッドシートを操作してみました
認証部分に少しクセがあるので注意が必要です
書き込み等行う場合は OAuth 認証が必須で、読み込みだけであれば API Key だけでもいけます

API の呼び出し方がよくわからない場合は参考サイトにある URL から API Explorer を使って実際にブラウザコールしてみるのが手っ取り早いと思います

なお自分はすでにプロジェクトを削除して認証情報などもありませんがテストなどで作成したプロジェクトなどの場合はセキュリティのために削除するようにしましょう

参考サイト

• https://developers.google.com/sheets/api/quickstart/ruby
• https://developers.google.com/sheets/api/reference/rest/