Google スプレッドシートをRubyで操作する

Google スプレッドシートを Ruby で扱う方法を確認できたので方法をメモがてら記事にします。
正直、Google Spreadsheets を操作するよりも、アプリの登録のアクセスキーの取得のほうがよほど面倒なので、そこからメモします。

目次

参考

準備

前提

GCP へのログインをしていることを前提して以下をすすめていきます。

プロジェクトを作成

今回はプロジェクトの選択があるので、新規に作成します。
ヘッダー部分から開きます。

「新しいプロジェクト」を開きます。

プロジェクト名を任意に設定して、作成します。

現在のプロジェクトを作成したものに変えておきます。

ライブラリの追加

[API とサービス]=>[ライブラリ]の順に開きます。

Google Drive API と Google Sheets API それぞれを有効にします。


OAuth 同意画面

[API とサービス]=>[OAuth 同意画面]の順に開きます。

私のカウントは Gsuite のアカウントではないので、「外部」を設定します。

以下の情報を登録します。

  • アプリ情報
    • アプリ名
    • ユーザーサポートメール
  • デべロッパーの連絡先情報
    • メールアドレス

「スコープを追加または削除」を開きます。

以下の 2 つにチェックを入れて更新します。

  • Google Drive API - Google ドライブのすべてのファイルの表示、編集、作成、削除
  • Google Sheets API - Google ドライブのスプレッドシートの表示、編集、作成、削除


スコープにチェックを入れた項目が増えているので、「保存して次へ」で進めます。

自分しか使わないものなので、「ADD USERS」自分自身のメールアドレスを登録しておきます。「保存して次へ」で進めます。

認証情報

[API とサービス]=>[認証情報]の順に開きます。

認証情報を作成から「OAuth クライアント ID」を選択して進めます。

アプリケーションの種類を「デスクトップアプリ」に設定し、「作成」で進めます。

クライアント ID、クライアントシークレットの値が表示されるので、控えておきます。

実装

アクセス確認

最初に、API へのアクセスができるかだけを確認します。

次の手順で用意を進めます。

1
2
3
4
5
6
7
# 適当なディレクトリで

bundle init
# Gemfileが作成されるので、
# gem "google_drive" を追記

bundle install --path=vendor/bundle

続けて、アプリケーション本体のapp.rbと認証情報を記載したconfig.jsonを作成します。

app.rb
1
2
3
4
require 'google_drive'

# 認証確認
session = GoogleDrive::Session.from_config("config.json")
config.json
1
2
3
4
{
  "client_id": "控えておいたクライアントID",
  "client_secret": "控えておいたクライアントシークレット"
}

こちらをbundle exec ruby ./app.rbで起動します。
すると以下のように、以下のように URL を開くことを求められます。

表示された URL をブラウザで開くと、次の画面になります。
公開していないアプリケーションではないためか警告が出ますが進めます。

権限を求められるので、順番に許可します。



権限を許可すると、コードが表示されるのでコンソールに貼り付けます。

エラーにならなければ、問題ありません。

config.jsonをこのタイミングで見ると、scopeとrefresh_tokenの 2 つが書き足されています。

config.json
1
2
3
4
5
6
7
8
9
{
  "client_id": "控えておいたクライアントID",
  "client_secret": "控えておいたクライアントシークレット",
  "scope": [
    "https://www.googleapis.com/auth/drive",
    "https://spreadsheets.google.com/feeds/"
  ],
  "refresh_token": "リフレッシュトークン"
}

Google SpreadSheets を操作する(読み込み)

スプレッドシートの API は、以下のような階層になっていました。

  • スプレッドシート
    • ワークシートの配列
      • ワークシート
        • 行(以下は配列として取得できる)
          • 列

スプレッドや Excel でも馴染みあるというか、まぁそうなるだろう階層化がされています。

以下のように、app.rbを編集しデータにアクセスします。

app.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
require 'google_drive'

session = GoogleDrive::Session.from_config("config.json")

# アクセス先のスプレッドシートのIDを設定
spreadsheet = session.spreadsheet_by_key("スプレッドシートのID")
puts spreadsheet.inspect

# ワークシートの配列を取得
worksheets = spreadsheet.worksheets
puts worksheets.inspect

# ワークシートを個別で取得
worksheet = spreadsheet.worksheet_by_title('sheet1')
puts worksheet.inspect

# 行の配列を取得
rows = worksheet.rows
puts rows.inspect

# セル単位で取得
cel = rows[0][0]
puts cel

# セルをワークシートから直接取得することもできる
cel = worksheet[1,1]
puts cel

こちらをbundle exec ruby ./app.rbで実行すると以下のように表示されます。

1
2
3
4
5
6
7
> bundle exec ruby .\app.rb
#<GoogleDrive::Spreadsheet id="スプレッドシートのID" title="testsheet">
[#<GoogleDrive::Worksheet spreadsheet_id="スプレッドシートのID", gid="0", title="sheet1">]
#<GoogleDrive::Worksheet spreadsheet_id="スプレッドシートのID", gid="0", title="sheet1">
[["1", "5"], ["2", "6"], ["3", "7"], ["4", "8"]]
1
1

以上の方法でスプレッドシートの中身を取得できました。

Google SpreadSheets を操作する(書き込み)

続けて書き込みを試します。app.rbを以下のように編集して試します。

app.rb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
require 'google_drive'

session = GoogleDrive::Session.from_config("config.json")

# アクセス先のスプレッドシートのIDを設定
spreadsheet = session.spreadsheet_by_key("スプレッドシートのID")

# ワークシートの配列を取得
worksheets = spreadsheet.worksheets

# ワークシートを個別で取得
worksheet = spreadsheet.worksheet_by_title('sheet1')

# 書き換えるときはワークシートで操作する
worksheet[1,1] = 123

# 計算式を設定することもできます
worksheet[2,1] = '= 10 + 10'

# 他のセルを参照させるのは、やや面倒
worksheet[3,1] = '= B1 + B2'

# 関数も使用できる
worksheet[4,1] = '=CONCAT("A","B")'

# 保存は明示的にsaveメソッドを呼び出す
worksheet.save

# ワークシートの最新の情報を再度読み込むこともできる
worksheet.reload

こちらをbundle exec ruby ./app.rbで実行するとスプレッドシートは以下のようになります。

今回は、ruby から Google SpreadSheet を操作してみました。
基本的には RDS を使うのでしょうが速度の要求もあまりなくテーブルの管理したいのであれば選択肢にできそうです。
CLI を作るときのデータベースとしてはかなり良さそうです。

ではでは。