このエントリーは、GMOアドマーケティング Advent Calendar 2018 の 12/04 の記事です。
GMOアドマーケティングとしては初のAdvent Calendar参戦です。
はじめに
こんにちは。
GMOアドマーケティングのT.Iです。
以前に私が執筆した以下の記事の後半で、RubyとGoogle スプレッドシート(以下: スプレッドシート)の連携について触れました。
社内ではNetatmoの数値の可視化以外にも、自社プロダクトの目標に対する進捗の可視化や特定条件の売上の定点観測など、様々なシーンでRubyとスプレッドシートを活用してレポートを自動的に作成しております。
今回は上記の記事で紹介できなかったRubyとスプレッドシートの連携方法と、その使い方について紹介します。
1. Google API Consoleで前準備
Ruby側での実装の前に、まずはGoogle API Console(APIライブラリ)からGoogle Drive APIとGoogle Sheets APIを有効化します。
まずはじめに APIライブラリ にアクセスし、「新しいプロジェクト」から新規プロジェクトを作成します。
作成したプロジェクトを選択し、「Google Drive API」と「Google Sheets API」を有効化します。
続いて、「APIとサービス」> 「認証情報」へと進み、「認証情報を作成」>「OAuth クライアント ID」を選択します。
その後に表示される同意画面で必要事項を入力し、「アプリケーションの種類」から「その他」を選択して作成すると、クライアントIDとクライアントシークレットが発行されます。
2. google-drive-rubyの導入
Rubyとスプレッドシートの連携にはgoogle-drive-rubyを使います。
基本的にはGitHubのREADMEに書いてる通りですが、自分がやったことを書きます。
2-1. google-drive-rubyのインストール
適当なディレクトリに以下のようなGemfileを用意して 「bundle install --path vendor/bundle」を実行します。
|
1 2 3 |
source 'https://rubygems.org' gem 'google_drive' |
2-2. 初回の認証
初回実行時のみAPIの認証が必要になります。
Google API Consoleで作成したクライアントIDとクライアントシークレットから、以下のようなjsonファイルを作成します。
今回は「config.json」というファイル名で作成しました。
|
1 2 3 4 |
{ "client_id": "xxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com", "client_secret": "xxxxxxxxxxxxxxxxxxxxxxxx" } |
次に、jsonファイルと同じディレクトリに以下のようなコードを作成し、実行します。
※ bundle exec をつけて実行する必要があります
※ プログラムを実行する前に、作成したスプレッドシートに適当な名前をつけて保存しておきましょう
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
require "google_drive" session = GoogleDrive::Session.from_config("config.json") # https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxx/ # 事前に書き込みたいスプレッドシートを作成し、上記スプレッドシートのURL(「xxx」の部分)を以下のように指定する sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxx").worksheets[0] # スプレッドシートへの書き込み sheets[1,1] = "hello world!!" # シートの保存 sheets.save |
実行すると
1. Open this page: https://accounts.google.com/xxx
と表示されるので、出力されたURLをブラウザで開き、表示された文字列を選択してターミナルに貼り付けます。
これで初回の認証が完了するので、以降は自由にスプレッドシートを操作することが可能です。
※この認証が完了すると先ほど作成したjsonファイルが自動で更新されます
上記のサンプルコードでは、指定したスプレッドシートの「A1」の位置に「hello world!」と出力するよう記述したものなので、正常に動作した場合はスプレッドシート側も更新されています。
3. google-drive-rubyの機能紹介
google-drive-rubyでは様々な機能が実装されていますが、実際に私がよく使っている機能をいくつか紹介します。
3-1 書き込みたいスプレッドシートの指定
スプレッドシートの指定方法はいくつかあります。
私の場合は、spreadsheet_by_key でキーを指定し、worksheet_by_titleでタイトルを指定することが多いです。
|
1 2 3 4 5 6 7 |
require "google_drive" session = GoogleDrive::Session.from_config("config.json") # スプレッドシート内で下タブに表示されているシートの1番目のものを取得 # [1]や[2]と指定することで、順にワークシートを取得することが可能 sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxx").worksheets[0] |
|
1 2 3 4 5 6 7 8 9 |
require "google_drive" session = GoogleDrive::Session.from_config("config.json") # シートを名前で指定する sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxx").worksheet_by_title("シート名") # なお、スプレッドシートはキーではなく、URL全体でも指定することが可能 sheets = session.spreadsheet_by_url("https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxx/").worksheet_by_title("シート名") |
3-2 スプレッドシートへの書き込み
書き込み方法もいくつかありますが、基本的にはsheets[行番号,列番号]での書き込み方さえ知っておけば不便はないと思います。
|
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") # 処理対象のスプレッドシートを指定 sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxx").worksheet_by_title("シート名") # A1セルに「hello world!!」と書き込む # [行番号, 列番号]で指定することが可能 sheets[1,1] = "hello world!!" # A2セルに書き込む sheets[2,1] = "A2 sample" # B3セルに書き込む # 以下のようにセルを直接指定することも可能 sheets["B3"] = "B3 sample" # 数式を書き込むことも可能 sheets["C1"] = 3 sheets["C2"] = 5 sheets["C3"] = "=C1+C2" # E4セルを起点に複数の配列を書き込む array_first = [1, 2, 3, 4, 5] array_second = [6, 7, 8, 9, 10] sheets.update_cells(4, 5, [array_first, array_second]) # 書き込み後はsaveする sheets.save |
3-3 スプレッドシートの情報を取得
スプレッドシートに書き込まれている情報の取得方法です。
sheets[行番号, 列番号] での指定さえ覚えておけば問題ないですが、状況によっては input_value も活躍します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
require "google_drive" session = GoogleDrive::Session.from_config("config.json") # 処理対象のスプレッドシートを指定 sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxx").worksheet_by_title("シート名") # C3セルの情報を取得 # シート側で数値が書き込まれていても文字列が返ってくるので要注意 # [行番号, 列番号]の指定方法と、「C3」のようにセルを直接指定する方法がある p sheets[3, 3] p sheets["C3"] # C3セルの値や数式を取得 # 配列形式で取得すると「シートに表示されている文字(数値)」が返ってくるが、 # input_valueでは「数式や値そのもの」が返ってくる p sheets.input_value(3, 3) # 配列形式やinput_valueではシートの値を文字列として取得できるが、 # numeric_valueでは数値として取得することが可能 p sheets.numeric_value(2, 3) |
3-4 スプレッドシートやワークシートの作成
日毎や月毎にワークシートやスプレッドシートを作成することが多いので、社内ではadd_worksheetやcopyをよく活用してます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
require "google_drive" session = GoogleDrive::Session.from_config("config.json") sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxxxxxxxxxxxxx") # 新規スプレッドシートの作成 # マイドライブ内に作成される session.create_spreadsheet("スプレッドシート名") # 既存のスプレッドシートに新規シート(ワークシート)を追加 sheets.add_worksheet("シート名") # 作成済みのスプレッドシートをコピーして保存 sheets.copy("コピー後のシート名") # 新規作成(コピー)したスプレッドシートのURLやタイトルの取得 new_sheets = session.create_spreadsheet("新規スプレッドシート名") p "https://docs.google.com/spreadsheets/d/#{new_sheets.id}" p new_sheets.title |
3-5 ファイルやディレクトリの操作
Google Drive上でのディレクトリ操作についてのコードです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
require "google_drive" session = GoogleDrive::Session.from_config("config.json") # Google Drive上にディレクトリを作成する ## ディレクトリを作成する場所をGoogle DriveのURLで指定する drive_dir = session.collection_by_url("https://drive.google.com/drive/folders/xxxxxxxxxx") ## 上記で指定したディレクトリ内に新たにディレクトリを作成する drive_dir.create_subcollection("ディレクトリ名") # スプレッドシートをコピーして指定したディレクトリに保存(移動) ## コピー元のスプレッドシートを取得 sheets = session.spreadsheet_by_key("xxxxxxxxxxxxxxxx") ## シートのコピー copy_sheet = sheets.copy("コピー後のシート名_001") ## 保存したいディレクトリのURLを指定 drive_dir = session.collection_by_url("https://drive.google.com/drive/folders/xxxxxxxxxx") ## コピーしたシートを先程指定したディレクトリ内に保存 drive_dir.add(copy_sheet) |
さいごに
今回はRubyとGoogleスプレッドシートの連携について紹介しました。
スプレッドシートの連携といえばGoogle Apps Scriptがありますが、個人的にRubyの方が扱いやすく、より高度なことを行おうとするとRubyの方が実装が早いことがほとんどなので、GASよりもRubyを使ってスプレッドシートを操作することが多いです。
例えば、データベースとの連携や特定の環境へ接続したいなどの要件が発生した場合、Rubyで実装した方がストレスなく開発が行えることが多いと思います。
一度環境さえ整えてしまえばRubyでの実装もGASと同じぐらい簡単なので、気になる方はぜひ試してみてください。
告知
明日は、2018年下期に開催したライトニングトーク大会についてのお話です。
お楽しみに！
クリスマスまで続くGMOアドマーケティング Advent Calendar 2018
ぜひ今後も投稿をウォッチしてください！
■Wantedlyページ ～ブログや求人を公開中！～
https://www.wantedly.com/projects/199431
■エンジニア採用ページ ～福利厚生や各種制度のご案内はこちら～
https://www.gmo-ap.jp/engineer/
■エンジニア学生インターン募集中！ ～有償型インターンで開発現場を体験しよう～
https://hrmos.co/pages/gmo-ap/jobs/0000027
2016卒のWebエンジニア。
採用やマネジメントもやってます。
