概要
ruby から Google スプレッドシートの Read/Write を行ってみました
SpreadSheet の API バージョンは v4 になります
認証も OAuth を使った認証と API Key を使った認証の 2 つの方法を紹介します
環境
- macOS 10.14.4
- Ruby 2.6.2p47
ライブラリインストール
gem "google-api-client"
bundle install --path vendor
API の有効化
このページ にアクセスすると自動的に Quickstart のプロジェクトの作成と SpreadSheet API の有効化、認証情報を作成してくれます
credentials.json
もダウンロードできるのでしましょう
API コンソール から作成されたプロジェクトや認証情報を確認することができます
Getting Started (with OAuth)
とりあえずコピペで動作するサンプルがあるのでそれを動かしてみます
認証が OAuth なので一旦ブラウザで認証して得られたトークンをターミナルに貼り付けます
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
として実行するとアクセスしてほしい 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 の認証画面になるので認証ユーザを選択します
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKolYE9RiO2QJdefi1QTs04tAWDUZIl4pyYNJrA3mMNOeb3vUgm8xs-zOb5mqwvkddzukbx2r6_9IkvJvtjTh8lo6v0Db9vhsxsC5YR83r95CToBirqwfoBfQN7YX9-6vqskACLe5e6ftL/s400/sheet_api1.png)
「QuickStart」というアプリから SpreadSheet の読み込み権限を与えても OK か確認する画面になるので許可しましょう
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHcSKavvyq1MyL2hQWuzmVEjxpmYQYKIzSkDfmksSIDTk66dDlgLuTyZbKJlhnWHn8GjRPeF3UBcU9S-CcMTWL9v_wpStxjVmfobC1lkpZ-ugT0k4NxrsZhFEURUHxutpuNFOMti37TKQf/s400/sheet_api2.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjW3FsXjYY7kIaLKDX1nYQTm2el91-cRVJnTBuHA4Z1WvK15zvfJGA5_r6YHyyrl3hy4_LnTlKfoPPsF7PwyXl5Hqvs5WmMvj7dYEL55HE5FcietquJlbkKcTEe34Lubnupofh2DwVVkRLZ/s400/sheet_api3.png)
するとトークンが得られるのでそれをターミナルに貼り付けます
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmssOdKk_zJluueXMqy8DxetqvCJ1wHpeyTKFcSooSIcz8KYkN0aY7U18IrEfMzoH7qYTNMvIDPBT-EJ5ajSP7Q4e331T1YK-x4Ng0KKMclJs28ajG-42klcYpg2ZXR-1KX_y1_4lb1116/s400/sheet_api4.png)
トークンは token.yaml
に保存されて以降はそれを使ってアクセスするようになります
認証に成功するとテスト用のスプレッドシートから情報を読み込んで表示してくれます
ちなみに Google アカウントの「サードパーティによるアクセス」を確認すると許可した「Quickstart」アプリが追加されていることが確認できます
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNtbJ_G_WzJoQgrefijHrg6tyOEjqLTW9Msr1t5SLryXTZpRi2ZP77MSwx4j_n1elc99QM0QX6UjHMuz7MmaALvihJdefS_buPaqMUccRZ2fl853dMJhpI_SfjT8fzmuFDyl5OMsPK2ocY/s400/sheet_api5.png)
API key を使って認証する
基本は OAuth を使いましょう
ですが、自分のスプレッドシートにしかアクセスしなかったりバッチなど CLI 環境しか使えない場合にはブラウザ認証するのは面倒です
なので、例のごとく API Key を使った認証でスプレッドシートにアクセスしてみます
スプレッドシートを共有モードにする
API Key を使ってスプレッドシートにアクセスするにはスプレッドシートを共有モードにする必要があります
スプレッドシートにアクセスして「共有」から共有可能なリンクを取得できるようにしましょう
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYdZAd86_59-rgjZoir5FtJLZ7UZ1jbho7mdgC1MHhnj9wFK2MGnyrlOgKGHmhlfsGPUxejUX-MqUdklS4CL20cL9Qsdngb1nVCuq-N8pu1dqxtU-0UNfZrgK2H3WxzPBqILIS5kueH3cz/s400/sheet_api6.png)
またスプレッドシートの ID も取得しておきましょう
ID は共有可能リンクにも含まれているオムニバーに表示されているランダムな文字列になります
API Key の作成
コンソールの認証情報管理画面から「API キー」を作成しましょう
このキーを使って API をコールします
とりあえず今回はテストなので制限なしで作成しました
API Key を使ってアクセスするスクリプト
とりあえず Read してみます
ちなみにアクセスするスプレッドシートは以下のような感じでデータを入れています
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2D9DZ2KhtuGGJt5J2-mNo_GQSeh20o-q1OEge8TE8Fc-zfNzl0GLMFWLWr2WtE3B568kTpqsFR1AozfTO5yYVMxytpZjuInB1BnBfGNph51q2syOCrlK1nDjnTelPK8bM3ac9m5Vrr0c6/s200/sheet_api7.png)
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
これで実行すると 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 に書き換えます
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
先ほどと同じようにブラウザに 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 つ更新されているので成功です
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHgnzt6Y2p9x9_NXSHZy8VwuU14GACLfWiopp1_qhdOAP-W9pBRjVSBijqOtyiG1nYl2uludyP-w4cDUSkl8uKBco1JbHguxfi6WghCU4CLrRLzLWpMT4Qi-8v7I_fILejbRz2JqF1ZX5D/s200/sheet_api9.png)
最後に
Ruby から Google スプレッドシートを操作してみました
認証部分に少しクセがあるので注意が必要です
書き込み等行う場合は OAuth 認証が必須で、読み込みだけであれば API Key だけでもいけます
API の呼び出し方がよくわからない場合は参考サイトにある URL から API Explorer を使って実際にブラウザコールしてみるのが手っ取り早いと思います
なお自分はすでにプロジェクトを削除して認証情報などもありませんがテストなどで作成したプロジェクトなどの場合はセキュリティのために削除するようにしましょう
参考サイト