Pythonでスプレッドシートを操作する時のライブラリgspreadでもよく使う操作をまとめました。
個人的によく使用する操作をピックアップしたので、詳細を知りたい場合は下記の公式サイトを参照ください。
https://gspread.readthedocs.io/en/latest/
スプレッドシートを扱うための事前準備
from google.colab import auth
auth.authenticate_user()
# スプレッドシート連携するのに必要なライブラリをダウンロード
!pip install gspread
import gspread
from google.auth import default
creds, _ = default()
# 認証処理
gc = gspread.authorize(creds)
認証なしで実行したい場合は下記の記事を参考にしてください。
https://qiita.com/plumfield56/items/3d9e234366bcaea794ac
gspreadのコード一覧
gcの操作
名前、URL、IDからスプレッドシートのworkbookを取得するメソッド
| 説明 | コード例 |
|---|---|
| 名前でworkbookを取得 | gc.open_by_key('TITLE') |
| IDでworkbookを取得 | gc.open_by_key('ID') |
| URLでworkbookを取得 | gc.open_by_url('URL') |
| workbookの作成 ※マイドライブに作成される | gc.create('NAME') |
workbookの操作
workbookを操作して、タイトルやシートの取得をするメソッド
| 説明 | コード例 |
|---|---|
| workbookのタイトル取得 | workbook.title |
| workbookのID取得 | workbook.id |
| indexでworksheetを取得 | workbook.get_worksheet(0) |
| 名前でworksheetを取得 | workbook.worksheet("sheet1") |
| 全てのworksheetを取得 | workbook.worksheets() |
| worksheetの作成 | workbook.add_worksheet(title="A worksheet", rows="100", cols="20") |
| worksheetの削除 | workbook.del_worksheet(worksheet) |
下記は少し特殊でworkbookからDataFrameを使ってデータを更新することが可能です。
workbook.values_update(
# sheet!A1などセルを指定
target_cell,
# パラメーターを指定してシートへ張り付ける場合
params={
'valueInputOption': 'USER_ENTERED'
},
# 二次元のリストで反映させたい内容を記載
body={
'values': [df.columns.values.tolist()] + df.values.tolist()
}
)
worksheetの操作(データ取得や検索)
worksheetを操作して、指定範囲のデータ取得、データ更新、削除などを行うメソッド
データを取得するメソッド一覧
| 説明 | コード例 |
|---|---|
| worksheet名を取得 | worksheet.title |
| worksheetIDを取得 | worksheet.id |
| A1形式でセルの値を取得 | worksheet.acell('B1').value |
| R1C1形式でセルの値を取得 | worksheet.cell(1, 2).value |
| A1形式でセルの関数を取得 | worksheet.acell('B1', value_render_option='FORMULA').value |
| R1C1形式でセルの関数を取得 | worksheet.cell(1, 2, value_render_option='FORMULA').value |
| ●行目のデータをリストで取得 | worksheet.row_values(1) |
| ●列目のデータをリストで取得 | worksheet.col_values(1) |
| worksheetの全データをリストで取得 | worksheet.get_all_values() |
| worksheetの全データを辞書で取得 | worksheet.get_all_records() |
データを更新するメソッド一覧
| 説明 | コード例 |
|---|---|
| A1形式でセルの更新 | worksheet.update_acell('B1', 'word') |
| R1C1形式でセルの更新 | worksheet.update_cell(1, 2, 'word') |
| 指定範囲での更新 | worksheet.update('A1:B2', [[1, 2], [3, 4]]) |
データを検索するメソッド一覧
| 文字の検索 | worksheet.find("word") |
| 正規表現での検索 | worksheet.find(re.compile(r'正規表現'))|
| 文字の検索(一致する全て) | worksheet.findall("word") |
| 正規表現での検索(一致する全て) | worksheet.findall(re.compile(r'正規表現'))|| 範囲を配列で取得
上記のfindメソッドで検索したセルの場所は下記で取得可能です
cell = worksheet.fine('serch_word')
print(f'値:{cell.value}、列のindex:{cell.col}、行のindex:{cell.row}')
上記の findメソッドで列を取得した場合は数値で返ってくるので、
アルファベットで知りたい場合は変換する必要があります。
その場合は下記の関数で変更できます。
def conv_num_to_col(num):
if num <= 26:
return chr(64 + num)
else:
if num % 26 == 0:
return conv_num_to_col(num//26-1) + 'Z'
else:
return conv_num_to_col(num//26) + chr(64+num%26)
gspredにあるutilsを使用した書き方を追加しました。
https://qiita.com/plumfield56/items/7ae3323bc57750849ea0
cell操作のメソッド
| 説明 | コード例 |
|---|---|
| セルの値取得 | cell.value |
| セルの行インデックス取得 | cell.row |
| セルの列インデックス取得 | cell.col |
pandasを使ったデータ取得からシートへの反映
import pandas as pd
# リストで取得したデータをデータフレームにする
df = pd.DataFrame(worksheet.get_all_records())
# データフレームをリストに反映させる
worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())
# パラメーターを指定してシートへ張り付ける場合
workbook.values_update(
# sheet!A1などセルを指定
target_cell,
params={
'valueInputOption': 'USER_ENTERED'
},
body={
'values': [df.columns.values.tolist()] + df.values.tolist()
}
)
日付(数値)を取得する方法
日付を取得したいけど基本的にはテキストで取得してしまうので、
日付を取得するためには一工夫が必要になります。
まずvalues_getメソッドを使用する必要があります。
values_getメソッドはworkbookに使用するメソッドなので、rangeにシート名!範囲と指定をした上でパラメータでvalueRenderOptionをFORMULAにすることによってセルの中の関数を取得することが可能になります。
workbook.values_get(range, params={'valueRenderOption':'FORMULA'})
パラメータの設定の詳細は公式ドキュメントを参照ください。
そうするとmajorDimension, range, valuesをキーにした辞書(dict)型で返ってくるのでvaluesを取り出せばlistの二次元配列が取得できます。
workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']
ただしこの状態だと日付は数値データとして取得されてしまうので、数値データを日付へ関数で変換してあげる必要があります。
values= workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']
df = pd.DataFrame(values, columns = values[0])
df[column_name] = pd.to_timedelta(df[column_name].astype('float'), unit='D') + pd.to_datetime('1900/01/01')
# 数値データを日付データへ変換
# テキストデータが混じっていた場合とNoneが入っているとエラーにあるのでその場合はそのままreturnする
from datetime import datetime, timedelta
def conv_num_to_date(data):
if type(data) == str:
return data
elif data is None:
return data
else:
return str(datetime(1899, 12, 30) + timedelta(days=int(data)))[:10]
values= workbook.values_get(range, params={'valueRenderOption':'FORMULA'})['values']
df = pd.DataFrame(values, columns = values[0])
df[column_name].map(lambda data: conv_num_to_date(data))