GoogleスプレッドシートをAPIで自動連携する日曜RPA

背景

好きなYoutubeチャンネルの動画一覧を取得して、タグを付けてどの動画に誰が出演しているかを管理、検索したい場合があります。

日曜なのでそういう場合があります。

やりたいことと課題点

そういうものを作るには

• Youtubeから動画を取得する
• 動画一覧にタグを付ける
• タグを付けた動画一覧をHTMLに加工して出力
  と、3ステップあります。

手でやれば良いのですが、日曜なのでメンテナンス工数を減らしたいです。
とはいえ、目で見てタグを付けて精度をほぼ100％にしたいです。

理想としては、新しい動画が投稿されたらちょっとWEB開いてぽちっと操作するとタグが付いて、それ以外の外部サービスとのデータのやりとりとHTML作成は全部自動で行われる感じです。

すると、自動でデータが連携されていく流れのどこでタグ情報を付けるか、が課題となってきます。

やったこと

• 動画を自動取得（本記事では割愛）
• 取得した動画情報をGoogle スプレッドシートに自動連携
• Google スプレッドシート上でタグを人が編集
• Google スプレッドシートで付けたタグごと定期的に情報を取得しHTML出力

こんな感じになりました。

時間のある時にGoogle スプレッドシートだけ見てタグを付ければメンテナンスが行えるようになっています。また、タグが無くても通常の検索は出来るのでサービスレベルも一定は保たれます。
メンテナンスをしないと回らないのではなく、メンテナンスをするとより便利になるような状態を目指しました。

準備

Googleスプレッドシートを置くGoogleアカウントを用意して、以下を行っていきます。
・Google Drive APIを有効にする
・Google Sheets APIを有効にする
・サービスアカウントキーを作成し、秘密鍵を含むjsonファイルをダウンロードする
・ダウンロードしたjson内のclient_emailを、利用するGoogleスプレッドシートに編集権限として共有する

※こちらの記事を参考にしました。
PythonでGoogleスプレッドシートを編集

GoogleCloudPlatformに足を踏み入れるため少しややこしい感じも受けますが、システムから自動でアクセスするためのサービスアカウントを作るだけです。サービスアカウントキー作成時の「役割」も選択せずに進めてOKです。

ライブラリの準備

Google Sheets APIのクライアントライブラリである、gspreadを使いました。

sudo pip3 install --upgrade google-api-python-client
sudo pip3 install oauth2client
sudo pip3 install gspread

ファイル内では、以下のように書きます。
ダウンロードしたjsonファイルを、実行するプログラムと同じ場所に置いてください。今回は適当に、test4qiita_service.jsonという名前にしています。

sync_gspread.py（1/3）

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name('test4qiita_service.json', scope)

取得した動画情報をGoogle スプレッドシートに連携

sync_gspread.py（2/3）

## スプレッドシートに書き込むための、youtubeから取得した動画リスト
INPUT_FILE_PATH = '~/data/xxxxxx/master.tsv'

gc = gspread.authorize(credentials)
wks = gc.open('APItestSheet4Qiita').sheet1
cell_list = wks.range('A2:E2000')
recordnum=0;
with open(INPUT_FILE_PATH, 'r', encoding='utf8') as f_in:
        idx=0
        for line in f_in:
                recordnum+=1
                line = line.strip('\n').split('\t')
                for i in range(5):
                        cell_list[idx].value = line[i]
                        idx+=1

wks.update_cells(cell_list)

5列のファイルをGoogleスプレッドシートの2行目から張り付ける例です（↓の画像も参照）。
update_cellsで一括書き込みをしています。update_cellsを使わずに個々に書き込みをしていくと、すぐにクォータを使い切ってエラーになるので注意です。

こんな感じで、youtubeから取得した情報がGoogleスプレッドシートに書き込みできました。

Google スプレッドシート上でタグを手動編集

さっそくタグを付けたいところですが、例えばF列にタグ情報を付けて編集しても新しい画像が投稿されるとA列～E列に新しい動画情報が増えていくのでズレてしまいます。
これをF列にvlookupの式を記載することで横の列がずれることなく連携させます。
新しい動画が投稿されたら、vlookupの参照先のH～I列だけ編集してゆきます（↓画像参照）。

F列の内容.

=vlookup(A2,$H$2:$I$2000,2, FALSE)

Google スプレッドシートから読み込んだ結果を出力

手動の編集によって、A～F列にタグ付きの動画情報が出来ました。
これを、一気に読み込みます。

sync_gspread.py（3/3）

OUTPUT_FILE_PATH = '~/data/xxxxxx/master.tsv.gspread'

records = wks.get_all_values()
write_num=0
with open(OUTPUT_FILE_PATH, 'w', encoding='utf8') as f_out:
        for r in records :
                f_out.write( '\t'.join(r) +"\n")
                write_num+=len(r)
                if(recordnum*len(r) == write_num):
                        break

get_all_valuesはシート内のデータを全部持ってくるので、F列にあらかじめ書かれたvlookupの結果（#N/A）も持ってきてしまうため、取りたいデータが存在する行数まででループを止めてます。
また、このコードだとG～I列も持ってきてしまうのですが、vlookup用のシートを別に分ければ綺麗になります。ただ、日曜メンテナンス的にはシートを跨ぎたくないなぁと思うので今はこれで運用してます。

出来た & 所感

週に1回、数分程度の運用で、精度の高いタグ付けを出来る環境が実現できました。

例えば「ゲストで出演したメンバー以外の人を判定してタグ付け」なんて事は、当面は機械で行うのは難しいと思いますが、こうした仕組みだと人間が対応できます。
業務においては、自動的に粗めに作ったデータを人がチェックだけする、といった場合にも応用できそうです。Googleスプレッドシートを使うので、リモートワークにも適していそうですね。