株式会社 hacomono 開発チームの門田です。
API経由でスプレッドシート側に記載されている情報を取得・削除する操作を実装した際の導入手順を備忘録としてまとめようと思います。
やりたい事
WEBサービス側でボタンを押した際に、スプレッドシートの特定の箇所に記載されている情報を取得しWEBサービス側に表示、同時にワークシート側の情報は削除する。
準備
- スプレッドシート側の準備
Google Cloud Platform でまずは必要な準備を始めます。 まずは今回の連携に使用するプロジェクトを作成します。 作成後に、「APIとサービス」>「ライブラリ」より今回の実装に使用する下記の二つのライブラリ「Google Drive API」と「Google Sheets API」を有効にします。
次に連携するための認証情報を作成します。 「認証情報」ページを開き、「＋認証情報を作成」より 「OAuth クライアントID」を選択します。
今回の実装ではデスクトップアプリに該当するのでそれを選択し、名前にはわかりやすい名称を設定をし作成します。 作成後、クライアントIDとクライアントシークレット が発行されます。これらを使ってjsonファイルを一つ作成します。
{ "client_id": "XXXXXXXXXXXXX発行されたクライアントIDXXXXXXXXXXXXXXXXXXX", "client_secret": "XXXXXXXX発行されたクライアントシークレットXXXXXXXXXXXXXX" }
次に、これらの情報を使いリフレッシュトークンを発行する必要があります。ここから先は開発環境で処理を行う必要があり、実装に必要なライブラリとして'google_drive'を環境にインストールします。
gem 'google_drive'
次にリフレッシュトークン発行を実行するためのファイルを作成します。
「session.rb」
require 'google_drive' session = GoogleDrive::Session.from_config("google_drive.json")
上記のファイルを準備したら、下記の手順で処理を実行します。
$ bundle exec ruby session.rb //実行後 1. Open this page: //ここに表示されるURLページにアクセスする
googleアカウントログインと、表示される権限の許可を行うと最終的に下の様なページに移動し、認証用コードが表示されていますのでこれをコピーします。
ターミナル側には次の処理として下記の文章が表示されているはずですので認証コードを貼り付けてEnterで実行します。
2. Enter the authorization code shown in the page: //ここに貼り付ける
実行後、成功すれば最初に作成したjson（本記事でのgoogle_drive.json）が書きかわり、新たにscopeとrefresh_tokenが記載されます。これで設定ファイル側の準備は完了となります。
{ "client_id": "XXXXXXXXXXXXX発行されたクライアントIDXXXXXXXXXXXXXXXXXXX", "client_secret": "XXXXXXXX発行されたクライアントシークレットXXXXXXXXXXXXXX" "scope": [ "XXXXXXXXXXXXXXXXXXXXXX", "XXXXXXXXXXXXXXXXXXXXXX" ], "refresh_token": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" }
ワークシートにアクセスする
フロント側でボタンを叩いた際に実行されるAPIのUsecaseのファイルで実際にシートにアクセスする処理を書きます。
「get_usecase.rb」
require "google_drive" //フロント側に返すデータの入れ物 response_data = [] session = GoogleDrive::Session.from_config("google_drive.jsonへのアクセスパス") sheets = session.spreadsheet_by_key("アクセス先のシートのID")
アクセス先のシートのIDは、ワークシートを開いた際のURLの 「https://docs.google.com/spreadsheets/d/{{ここの部分}}/edit#gid=0」 になります。
また、今回取得するのはワークシートの一つ目のシートを対象とします。 そして取得するのは下記の様な形で記載されている情報とします。
require "google_drive" //フロント側に返すデータの入れ物 response_data = [] session = GoogleDrive::Session.from_config("google_drive.jsonへのアクセスパス") sheets = session.spreadsheet_by_key("アクセス先のシートのID") //1つ目のシートを対象とする target_sheets = sheets.worksheets[0] //A1の内容とA2の内容を指定し、response_dataにいれる。 response_data.push({ text: target_sheets[1,1], description: target_sheets[1,2] })
行と列を指定して情報を取得する場合には、上記の様に target_sheets["行番号","列番号"]で指定します。 （列番号ではシート上ではA,B,C...のアルファベット表記ですが、数字に置き換えて指定します） そのためA1の内容は[1,1]とし、B1の内容は[1,2]という形で取得できます。
取得した後にシート側の情報を消したいので、取得後に削除します。削除する場合は取得とは違いdelete_rowsという行単位での削除関数となります。
そのため、第一引数で行番号を指定し、第二引数でそこから数えて何行削除するのかという関数となっています。 今回は1行目のみを削除したいので、1行目から数えて1行ということでdelete_rows(1,1)と指定します。
また、シート側に対して何らかの操作を行った後には編集後のデータをセーブしてあげる必要があるため、今回の削除操作を行った後にはsave関数を実行します。
require "google_drive" //フロント側に返すデータの入れ物 response_data = [] session = GoogleDrive::Session.from_config("google_drive.jsonへのアクセスパス") sheets = session.spreadsheet_by_key("アクセス先のシートのID") //1つ目のシートを対象とする target_sheets = sheets.worksheets[0] //A1の内容とA2の内容を指定し、response_dataにいれる。 response_data.push({ text: target_sheets[1,1], description: target_sheets[1,2] }) //取得後にシート側の内容を削除する。 target_sheets.delete_rows(1, 1) //シート操作を保存する。 target_sheets.save //最後にresponse_dataをフロント側へ返す処理を行って完了。 response_data
これによりシート側の1行目が削除され、下記の様な状態になります。
次に同様にフロント側からボタンを押してAPIを叩いた際には、「テスト2です。」「説明文2です。」が取得され、以降も順々に一行目取得→一行目削除→二行目以下の行が一行目に繰り上げ...が繰り返されていく状態になります。
補足：シート側にデータを書き込む場合には指定した箇所に入れてあげるだけで良いので、
target_sheets[1,1] = "上書きです。" target_sheets.save
重複処理を（なるべく）避ける回避処理
ボタンを押したユーザーに対して、先ほどの情報を返すという形にしたい訳ですが仮に複数のブラウザから同時にボタンを押した場合には削除が完了するまでの間であれば複数の環境から同一の値が取得できてしまいます。
そのため、最初にアクセスした際にまず始めに処理を開始する時刻を書き込み「現在この行は処理中です」という状態が分かる形にします。
まず取得・削除を行う前に３列目に対して現在時刻を書込みます。 行と列を指定した箇所に上書き内容を入れて、saveしてあげればシート側に書き込みができます。
require "google_drive" //フロント側に返すデータの入れ物 response_data = [] session = GoogleDrive::Session.from_config("google_drive.jsonへのアクセスパス") sheets = session.spreadsheet_by_key("アクセス先のシートのID") //1つ目のシートを対象とする target_sheets = sheets.worksheets[0] //lock時間記録 target_sheets[1 ,3] = "#{Time.now}" target_sheets.save //A1の内容とA2の内容を指定し、response_dataにいれる。 response_data.push({ text: target_sheets[1,1], description: target_sheets[1,2] }) //取得後にシート側の内容を削除する。 target_sheets.delete_rows(1, 1) //シート操作を保存する。 target_sheets.save //最後にresponse_dataをフロント側へ返す処理を行って完了。 response_data
そしてその処理を行う前に、時刻が書き込まれているかどうかを確かめるための処理を追加します。
require "google_drive" //フロント側に返すデータの入れ物 response_data = [] session = GoogleDrive::Session.from_config("google_drive.jsonへのアクセスパス") sheets = session.spreadsheet_by_key("アクセス先のシートのID") //1つ目のシートを対象とする target_sheets = sheets.worksheets[0] //lock時間を確認 lock_time = target_sheets[1, 3] return ”エラー処理” if lock_time.present? //上記で存在しない事が確認できたらlock時間記録 target_sheets[1 ,3] = "#{Time.now}" target_sheets.save //A1の内容とA2の内容を指定し、response_dataにいれる。 response_data.push({ text: target_sheets[1,1], description: target_sheets[1,2] }) //取得後にシート側の内容を削除する。 target_sheets.delete_rows(1, 1) //シート操作を保存する。 target_sheets.save //最後にresponse_dataをフロント側へ返す処理を行って完了。 response_data
完全とはいえませんが、いったんの回避策となります。 あと実行中の処理が完了すればその行は時刻の列ごと削除されるので、次の行は取得可能状態に戻ります。 （外部からの同時アクセスの制限をシート側で設定などできれば良いんですが...できるのだろうか） 時刻を書き込んだけれどもその先で処理に失敗し、ずっと時刻が残り続けてしまうケースを想定して書込み時刻より3分以上経過している場合にも取得可能とする、みたいな処理も入れてあげるとより良いかとも思います。
以上で今回の目的となるAPIを介してシート側のデータ取得と削除が行えます。フロント側から取得行数や書き込みたい内容をパラメーターでもらったりすればシートを保存先として社内向けなどの簡単なアプリケーションなどの実装が行えるんじゃないでしょうか。 最初に行った認証周りが一番手間だとは思いますので、下準備さえできれば他の関数を利用して色んな事が行えそうです。
