Google ColaboratoryでGoogleスプレッドシートを読み書きしてみる
前回のエントリでは、GoogleColabで画像表示ができるようになりました。これでOpenCVを使った画像処理も安心して勉強ができるようになりました。
では、そのほかのファイル、特にWebスクレイピングをした結果をGoogleスプレッドシートに格納といった用途はあるかなと思います。 具体的にはセルから値を取り出したり、格納したりとなりますが、それができるかを確認してみようと思います。
必要になるパッケージ
GoogleColabというかpythonでGoogleスプレッドシートのファイルを扱うには‘‘‘gspread‘‘‘というモジュールを用いると便利です。
特徴としては…
- Google Sheets API v4.
- Open a spreadsheet by its title or url.
- Extract range, entire row or column values.
- Python 3 support.
python3に対応しているので助かります。また、スプレッドシートを開く際にはファイル名（タイトル）かURLが指定できるので、共有設定の入っているようなGoogle Drive上のスプレッドシートに関してもデータの操作が行えるのが便利です。
gspreadパッケージのインストール
以下のコマンドでインストールすることができます。
!pip install gspread
既にインストール済みであれば-Uつけてインストールしますが今のところ不要の様です。
このように表示されればインストールは完了です。
gspreadパッケージを使用することで以前のエントリーのような特別なGoogle Driveのディレクトをマウントする作業なしで（というかこのパッケージとgoogle.colabパッケージ、oauth2clientパッケージを使用することで、直接Google Driveへアクセスする事ができます）
python側の標準的なファイルアクセス処理を行う場合にはマウントすることにもメリットはありますが、Googleスプレッドシートを単に読み込んだりするだけであればgspreadパッケージを使用するほうが楽かもしれません。（認証作業の回数も減りますし）
基本的な操作
基本的な操作としてはGithubのパッケージのドキュメントで抑えられると思いますが、念のため。
以下のテストの実行にあたっては、事前にマイドライブ上SpreadsheetSampleというスプレッドシートファイルを作成しておきます。存在しないとエラーが出ます！
Google Drive上は以下のような感じになっていて
ファイルは存在していればよいので、ワークシートは空で大丈夫です。
ではNotebookに以下のコードを張り付けて実行してみます。 内容としてはファイルをオープンして指定したセルに値を格納し、格納後さらに値を取得するという単純なものです。
from google.colab import auth from oauth2client.client import GoogleCredentials import gspread # 認証処理 auth.authenticate_user() gc = gspread.authorize(GoogleCredentials.get_application_default()) # 'SpreadsheetSample'というスプレッドシートの先頭ワークシートをオープン worksheet = gc.open('SpreadsheetSample').get_worksheet(0) # A1セルに'foo'という値を上書き worksheet.update_acell('A1', 'foo') # A2からC3のセルエリアに'bar'を一括で上書き cell_list = worksheet.range('A2:C3') for cell in cell_list: cell.value = 'bar' worksheet.update_cells(cell_list) # A1セルの値を取得し、表示 val = worksheet.acell('A1').value print(val) # A1セルを0,0とするようなセル指定で # 2，2（B2）の位置のセルを取得 val = worksheet.cell(2, 2).value print(val)
このコードを【Shift】+【Enter】で実行します。
すると、認証処理が実行されます。（一度認証すればインスタンス実行中は認証処理は不要の様です）
認証用のURLと認証キーのinputboxが表示されるので、URLのリンクをクリックします。 すると使用するDriveのアカウント選択に遷移します。
使用するアカウントを選択すると、使用可能な機能の確認画面に遷移します。
問題ないかを念のため確認して【許可】ボタンをクリックします。すると画面が遷移し、認証キーが発行されるのでこれを コピーして、Notebookのタブへ移動します。
あとは認証キーをinputboxに張り付けて
【Enter】キーを押せば認証が完了します。認証の完了後はPythonのコードが実行され結果表示に以下のような表示がされれば 正常に実行できました。
【実行結果】
foo bar
では、スプレッドシートの中身を確認してみます。
A1のセルに'foo'が入っていて、A2-C2、A3-C3の領域に'bar'が格納されていれば正常に動作しています。 これでスプレッドシートの値の取得や格納もNotebookのコードから実行ができるようになりました。 意外と簡単にできました。
マイドライブ以外のディレクトリに存在する既存のスプレッドシートを開いてみる
先ほどはマイドライブ内にあるスプレッドシートのファイル名を指定して開きましたが、今度は任意のフォルダにあるスプレッドシートを開いてみます。 マイドライブの下にsheetディレクトリを作成し、その中にsampleというスプレッドシートファイルを作成します。
以下の画面のような状況になります。
ではこれを開いてみます。
エラーの例（読まなくても問題ないので飛ばしましょう）
単純に以下のようなコードでいいのかなと思うのですが…
from google.colab import auth from oauth2client.client import GoogleCredentials import gspread # 認証処理 auth.authenticate_user() gc = gspread.authorize(GoogleCredentials.get_application_default()) # 'マイドライブ/sheet/sample'というスプレッドシートの先頭ワークシートをオープン worksheet = gc.open('./sheet/sample').get_worksheet(0)```
ダメです。エラーがでます。
エラーメッセージとしてはSpreadsheetNotFound:となっているのでパス指定に問題がありそうです。
何回か実験してみたのですが、python上でカレントディレクトリの変更をしたりしてみたのですが、パス指定を変えることが出来なさそうです。
Google Drive上のファイルは名前の方やパスを属性として扱い、基本的にはツリー構造ではなくフラットな構造になっているため、
基本はファイルの所在としてはマイドライブの下にあるのも、ある特定のディレクトリにあることも大きな差がないということの様です。
うまくいった例
前のエラーを踏まえて、ではどうするか？
ファイルをオープンする方法としてgspreadモジュールではkeyをしてする方法とURLから指定する方法があるようです。
ドキュメントには以下のような記述があります。
# If you want to be specific, use a key (which can be extracted from # the spreadsheet's url) sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE') # Or, if you feel really lazy to extract that key, paste the entire url sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
Google Driveに格納されるファイルには固有のキーがあるのでそれを使用するようです。URLも基本的には固有キーを使用したURLになっているので同じような感じです。 つまりキーがわかっていれば問題ありません。
ファイルの固有キーをつかって任意のフォルダのスプレッドシートへアクセス
スプレッドシートをブラウザで開いて
ブラウザのURL表示の中の
https://docs.google.com/spreadsheets/d/14TU4**********************************************Hxt1Y/edit#gid=0（自分のファイルのものを使用してください）
/d/のあとから次の/までの間が固有キーになります。それを使用して以下のようなスクリプトを実行します。
from google.colab import auth from oauth2client.client import GoogleCredentials import gspread # 認証処理 auth.authenticate_user() gc = gspread.authorize(GoogleCredentials.get_application_default()) # 'マイドライブ/sheet/sample'というスプレッドシートをオープン sh = gc.open_by_key('14TU4**********************************************Hxt1Y') worksheet = sh.get_worksheet(0) # set value worksheet.update_acell('A1', 'key')
スプレッドシートにも反映されています。
URLをつかって任意のフォルダのスプレッドシートへアクセス
スプレッドシートをブラウザで開いて
ブラウザのURLボックスの中身がそのままURLになります。（URLの末尾に/edit#gid=0がついています。本来はないものが正しいURLですが、末尾に編集情報がついていても問題はないようです）
このURLをつかってスプレッドシートへアクセスします。 以下のコードを実行します。
from google.colab import auth from oauth2client.client import GoogleCredentials import gspread # 認証処理 auth.authenticate_user() gc = gspread.authorize(GoogleCredentials.get_application_default()) # 'マイドライブ/sheet/sample'というスプレッドシートをオープン sh = gc.open_by_url('https://docs.google.com/spreadsheets/d/14TU4**********************************************Hxt1Y/edit#gid=0') worksheet = sh.get_worksheet(0) # set value worksheet.update_acell('A1', 'URL')
実行結果・編集したスプレッドシートがこのように変化すれば正常に実行されています。
オープン処理はできました
これで既存にあるスプレッドシートのファイルに関してはおおよそ処理できるようになりました。 これでもいいのですが…新規にファイルを作成することだってありますよね？
スプレッドシートの作成
先ほどの例ではあらかじめあるスプレッドシートを読み込んでいましたが、今度は新規に作成してみようと思います。
新規にスプレッドシートを作成してみる
マニュアルをみると以下のような記述で大丈夫のようです。
sh = gc.create('test_sheet')
では新規作成してみましょう。新規作成するスプレッドシートの名前は'test_sheet'にしてみます。
from google.colab import auth from oauth2client.client import GoogleCredentials import gspread # 認証処理 auth.authenticate_user() gc = gspread.authorize(GoogleCredentials.get_application_default()) # 'SpreadsheetSample'というスプレッドシートの先頭ワークシートをオープン worksheet = gc.create('test_sheet').get_worksheet(0) # A1セルに'foo'という値を上書き worksheet.update_acell('A1', 'Create') # A1セルの値を取得し、表示 val = worksheet.acell('A1').value print(val)
このコードを実行すると以下のようになります。
作成されたのはマイドライブの直下に作成されています。
スプレッドシートのセルも期待通り編集されています。
これでめでたしめでたし…となるはずなんですが、やっぱりなんとなくしっくりきません。 時系列のデータをファイルを作りつつ保存するような処理をするほうが比較的一般的ではないかと思います。
と入っても、gspreadモジュールでできるのは以下の2つになります。
どうやらgspreadモジュールだけでは難しいようです。
終わりに
長くなったのでこのあとは別のエントリーにしようと思います。 次回はディレクトリを指定してスプレッドシートの読み書きを作成するにはという感じになると思います。 （途中まで書いていてあまりにも長く感じたので分けました）
これまで、あんまりネットワーク上のストレージを使ったプログラムを使ったことがなかったので結構戸惑ったような感じでしたが、 こういう考え方や扱いがむしろ普通になってきているんしょうね。
【関連エントリ】
