【Python】仕事を効率よくしませんか？ Colaboratoryで「スプレッドシート操作」を自動化

1 はじめに
2 スプレッドシートとは
3 事前準備
4 ワークシートの操作
5 セルの操作
6 操作する上での注意
7 ちょっとした参考コード
8 さいごに
9 オススメの書籍
- 9.1 Pythonについて興味を持ってくれた方へ
- 9.2 PythonでWebスクレイピングしてみませんか？

はじめに

仕事を効率よくしませんか？Pythonで「Excel作業」を自動化

上記の記事でExcelの自動操作を紹介しました。

しかし、「Colaboratory」で行うにはあまりにも不便すぎます。

何故かというと、

毎回Excelファイルをアップロードしないといけない
確認の際ダウンロードしないといけない

上記の二つが主な理由です。

これを解決する方法を今回紹介します。

Googleのスプレッドシートです。（Googleスプレッドシート）

すごく簡単に説明すると「Web上のExcel」

この「スプレッドシートを」使うことで、上記の不便さを解消していきたいと思います。

スプレッドシートの読み書きなどを以下で紹介していきます。

スプレッドシートとは

Googleアカウントを持っていれば無料で使用できる表計算ソフトです。

また、Web上にあるので共有などが簡単にできます。

以下のGoogleのドキュメントを参考にしてください。

スプレッドシートでできること

使い方自体は調べてください。

ここでは自動操作についての紹介なので省略します。

事前準備

環境構築がいらない！？Colaboratoryを使ってPython動かしてみた

上記の記事などを参考にして「Colaboratory」の使い方を知りましょう。

では、先に進みます。

まず、スプレッドシートを操作するには

そのファイルがある「GoogleDrive」をマウントする必要があります。

from google.colab import auth
import gspread
from oauth2client.client import GoogleCredentials

# GoogleDrive認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

上記のコードを使うか、

「Colaboratory」の左のメニューにあるボタンからできます。

マウントができたら操作可能になります！

注意

この方法だと1時間でマウントが切れてしまいます。

長い時間の操作が必要な場合、以下の記事を参考にして見てください。

Colaboratoryでスプレッドシートの操作をする際の時間制限

ワークシートの操作

ss_url = "スプレッドシートのURL "
ss = gc.open_by_url(ss_url)
ss = gc.open('ファイル名')
ss = gc.open_by_key('スプレッドシートのキー')

上記のコードでスプレッドシートを指定します。

スプレッドシートのキーは、

「https://docs.google.com/spreadsheets/d/スプレッドシートのキー/edit#gid=0」

ワークシートの作成

#ワークシートを作成
newWorksheet = ss.add_worksheet(title="NewSheet", rows="10", cols="10")

「title」で名前を指定し、「rows」「cols」で行数と列数を指定できます。

ワークシートの選択

#ワークシートの選択
worksheet = ss.get_worksheet(0)

上記のコードで開くことができます。

「get_worksheet(0)」で最初にあるワークシートを選択しています。

ワークシートの削除

#ワークシートの削除
ss.del_worksheet(worksheet)
#ここの「worksheet」は上記で選択したワークシートになります。

「del_worksheet()」で削除できます。

ここで注意が必要なのは選択したワークシートを消せる。という点です。

ワークシートを選択するときのようにインデックスでの指定ではないので注意してください。

セルの操作

上記の画像のスプレッドシートで説明していきます。

セルの値の取得

val = worksheet.acell('B1').value
#出力>1

val = worksheet.cell(1, 2).value
#出力>1

上記のコードは両方とも同じセルを指定しています。

セルの関数の取得

val = worksheet.acell('B1', value_render_option='FORMULA').value
#出力>'=A1'

「value_render_option=’FORMULA’」を指定することで関数が取得できます。

複数のセルの値の取得

#行の値を取得
rowValues_list = worksheet.row_values(1)
#['あ','1','a']

#列の値の取得
colValues_list = worksheet.col_values(1)
#['あ','い','う','え','お','か', 'き', 'く', 'け','こ']

allValues_list = worksheet.get_all_values()
# [['あ', 'a', '1'], 
#  ['い', 'b', '2'],
#  ['う', 'c', '3'],
#  ['え', 'd', '4'],
#  ['お', 'e', '5'],
#  ['か', 'f', '6'],
#  ['き', 'g', '7'],
#  ['く', 'h', '8'],
#  ['け', 'i', '9']]

それぞれ「行の値の取得」、「列の値の取得」、「すべての値を取得」します。

セルへの書き込み

worksheet.update_acell('B1', 'こうせ')
worksheet.update_cell(1, 2, 'こうせ')

両方とも「B1」の位置「こうせ」と書き込みます。

cell_list = worksheet.range('A1:B10')
for cell in cell_list:
	cell.value = '更新する値'
worksheet.update_cells(cell_list)

上記のコードはまとめて値を書き込めます。

操作する上での注意

読み書きのアクセスが過度に多いとAPIerrorが出ます。

ユーザーごとに100秒あたり100件のリクエスト
1回のプログラムで設定できる最大値は1,000件
さらに1秒あたり10件

読み込みや書き込みを一つずつ行っていてはデータがかなり多いとすぐ制限が来ます。

これを回避するには、読み込みや書き込みはまとめてしましょう。

ちょっとした参考コード

from google.colab import auth
import gspread
from oauth2client.client import GoogleCredentials


# GoogleDrive認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

ss_url = "スプレッドシートのURL"
ss = gc.open_by_url(ss_url)
#ワークシートを作成（これが二番目に来る）
newWorksheet = ss.add_worksheet(title="NewSheet2", rows="10", cols="10")
#一番前のワークシートを選択
worksheet = ss.get_worksheet(0)
#二番目のワークシートを選択
worksheet1 = ss.get_worksheet(1)
#先ほど選択した二番目のワークシートを削除
ss.del_worksheet(worksheet1)

list = [1,2,3,4,5,6,7,8,9]

cellrange = 'B1:B9'
cell_list = worksheet.range(cellrange)

#まとめて更新
for i,cell in enumerate(cell_list):
  cell.value = list[i]
worksheet.update_cells(cell_list)