Python で Google Sheets の複数シートを操作する方法等

Photo by Clément H on Unsplash

Table of Contents [Disable]

• 1.Google Sheets の複数シートを操作する方法
  • 1.1.やりたいこと
  • 1.2.処理手順
  • 1.3.問題発生
• 2.ソースコード
• 3.関連記事

Google Sheets の複数シートを操作する方法

やりたいこと

所有している株式の値動きを Google sheets で管理している。
週に一度、証券会社のサイトを閲覧し、数字をシートに転記していた。
転記は面倒だし、ミスもあるので、自動化したい。

1. データは、証券会社からダウンロードした CSV 形式ファイル。現時点では手動でダウンロードする。
2. 1 銘柄、1 シートで管理している。
3. シートの 1 行目は見出し。2 行目以降は CSV 形式ファイルから転記するデータ。
4. 週に一度行追加する。行は日付の降順に並べる。（常に 2 行目をコピーして、2 行目に挿入）
5. 列には、左から「日付」「銘柄コード」「銘柄名」「保有数量 」「取得コスト（円）」「現在値（円）」「取得金額（円）」「評価額（円）」「評価損益（円）」「利益率」が並ぶ。なお、1 銘柄、1 シートなので、「銘柄コード」「銘柄名」は同じ値が続く。
6. 「日付」は処理した日付をセットする。
7. 「保有数量 」「取得コスト（円）」「現在値（円）」は CSV 形式ファイルから取得したデータをセットする。
8. 「取得金額（円）」「評価額（円）」「評価損益（円）」「利益率」は数式。

処理手順

ソースコードに沿って処理手順を説明する。
1 行目から 24 行目までは、「 Python で Google Sheets にアクセスするための手順」を参照のこと。［］内はソースコード内の位置

1. 今日の日付を YYYY/MM/DD 形式で取得する。［ 30 行目］
2. Google Sheets の複数シートをシートの数だけ順次処理していく。［ 33 行目、 34 行目］
3. シートの 2 行目をコピーし、 2 行目に挿入する。数式をコピーする場合、row_value のオプションとして 'FORMULA' が必要。また、insert_row のオプションとして 'USER_ENTERED' も必要。（参考： ValueRenderOption  |  Sheets API  |  Google Developers、ValueInputOption  |  Sheets API  |  Google Developers ）［ 38 行目］
4. 日付を更新する。［ 41 行目］
5. 数式（「取得金額（円）」「評価額（円）」「評価損益（円）」「利益率」）を 3 行目の数式をコピーして、 2 行目の同列に貼り付ける。［ 44 行目、 45 行目］
6. 銘柄名を検索キーワードとしてセットする。［ 48 行目］
7. CSV ファイルを読み込み、ヘッダは読み飛ばし、明細のみリスト化する。［ 53 行目～ 61 行目］
8. リストからキーワード（銘柄名）を検索する。ヒットすれば、CSV 形式ファイルの「保有数量 」「取得コスト（円）」「現在値（円）」でシート内の同項目を更新する。［ 64 行目～ 67 行目］

問題発生

キーワードはワークシートから取得しており、検索ループ内でワークシートをアクセスしていたため、下記のようなエラーが発生していた。

そこでキーワードを検索ループ外で変数にセットすることにした。
つまり、「if r.count(ws.cell(2,2).value) > 0:」を「kw = ws.cell(2,2).value」と「if r.count(kw) > 0:」に分けることにより、ワークシートへのアクセス回数を減らして解決した。

申請すれば上限を上げることも可能らしいが、まずは自分のコードを見直すべきであろう

＜エラーメッセージ＞
gspread.exceptions.APIError: {'code': 429, 'message': "Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:（以下省略）

ソースコード

	import gspread
	import datetime
	import csv
	# =======================================================================================
	# Google Sheets にアクセスするための準備（スプレッドシートを開くまで）
	# =======================================================================================
	# Googleのサービスにアクセスするための service 変数を生成
	from oauth2client.service_account import ServiceAccountCredentials
	# API の記述
	scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
	# 認証情報設定（ダウンロードしたjsonファイル名を設定）
	credentials = ServiceAccountCredentials.from_json_keyfile_name('＜json ファイル名＞', scope)
	# Google API にログイン
	gc = gspread.authorize(credentials)
	# 共有設定したスプレッドシートキーを変数 SPREADSHEET_KEY に格納
	SPREADSHEET_KEY = '＜スプレッドシートキー＞'
	# 共有設定したスプレッドシートへのアクセス
	ss = gc.open_by_key(SPREADSHEET_KEY)
	# =======================================================================================
	# 複数シートの２行目をコピーし、２行目の上に挿入する
	# =======================================================================================
	# 今日の日付を取得する。
	dt = datetime.date.today().strftime('%Y/%m/%d')
	# シートの数だけ繰り返す
	for i in range(0, len(ss.worksheets())):
	ws = ss.get_worksheet(i)
	# 2行目をコピーして2行目に挿入する。
	# ws.insert_row(ws.row_values(2, 'FORMULA'), 2, 'USER_ENTERED')
	ws.insert_row(ws.row_values(2), 2, 'USER_ENTERED')
	# 今日の日付に書き換える
	ws.update_cell(2, 1, dt)
	# 3行目7列目以降の式を2行目の各列にコピーする
	for i in range(7,11):
	ws.update_cell(2, i, ws.cell(3, i, 'FORMULA').value)
	# 検索キーワードをセット
	kw = ws.cell(2,2).value
	# ===================================================================================
	# csv の読み込み
	# ===================================================================================
	with open('New_file.csv') as f:
	lst = csv.reader(f)
	# ヘッダー部分を読み飛ばす
	for i in range(1, 11):
	hdr = next(lst)
	# 明細部分をリスト化する
	for r in lst:
	# キーワードがあれば、同じリスト内のデータ項目でセルを更新する
	if r.count(kw) > 0:
	ws.update_cell(2,4,r[7])
	ws.update_cell(2,5,r[11])
	ws.update_cell(2,6,r[14])

view raw update_gsheets.py hosted with ❤ by GitHub