PythonでGoogle Spread Sheetのフィルターを操作してみた
こんにちは、CX事業本部 IoT事業部の若槻です。
今回は、PythonでGoogle Drive上に保存されているGoogle Spread Sheet（Googleスプレッドシート）のフィルターを操作してみました。
Googleスプレッドシートのフィルターは2種類ある
そもそもGoogleスプレッドシートのフィルターには、Basic FilterとFilter Viewsの2種類があります。
Basic Filterは、スプレッドシート画面のフィルターボタンをクリックすると直ぐに適用できるフィルターで、作成できるのは1つのみです。
Filter Viewsは、[新しいフィルタ表示を作成]から作成できるフィルターです。名前をつけて保存し、好きな時に呼び出せます。
今回はそれぞれのフィルターに対する操作を試してみます。
やってみた
Google Cloud側の準備
Google Cloudの任意のプロジェクトで必要な準備を行います。
APIの有効化
「Google Sheets API」を有効化します。
サービスアカウントの作成
サービスアカウントの作成でサービスアカウントを作成します。任意のアカウント名を付けます。
[このサービス アカウントにプロジェクトへのアクセスを許可する]では、ロールに編集者を指定します。[完了]をクリックして作成を完了します。
サービスアカウントが作成されました。作成されたアカウントのメールは次節で使うので控えます。
キーのダウンロード
前節で作成したサービスアカウントのキーを作成してダウンロードします。
作成したサービスアカウントで[鍵を管理]をクリック。
[鍵を追加]-[新しい鍵を作成]をクリック。
キーのタイプJSONを選択して[作成]をクリック。
するとキーが記載されたJSONファイルがダウンロードされます。
Google Spread Sheetの共有設定
操作対象のスプレッドシートの共有設定で、前節で作成したサービスアカウントのメールアドレスを編集者権限で指定します。
Pythonでスプレッドシートのフィルターを操作する
Google Spread Sheets APIに対応したPython向けのライブラリには、主要なものとしてgspreadとgoogleapiclientの2つがあります。それぞれ使ってみます。
環境変数の設定
共通で使用する環境変数として、前節でダウンロードしたJSONファイル名と、操作対象のスプレッドシートのIDを設定します。
export JSON_FILE_NAME=<キーJSONファイル名> export SPREAD_SHEET_ID=<スプレッドシートID>
gspreadを使った場合
gspreadは、Google Sheets API v4に対応したPython向けのライブラリです。gspreadではBasic Filterの操作のみ対応しています。
必要なパッケージをインスールします。
pip install gspread oauth2client
次のスクリプトでは、set_basic_filter()により指定範囲のBasic Filterを設定しています。
import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
JSON_FILE_NAME = os.environ['JSON_FILE_NAME']
SPREAD_SHEET_ID = os.environ['SPREAD_SHEET_ID']
def create_client(jsonFileName):
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
jsonFileName, scope)
return gspread.authorize(credentials)
def main():
client = create_client(JSON_FILE_NAME)
sheet = client.open_by_key(SPREAD_SHEET_ID).sheet1
sheet.set_basic_filter("A1:E11") # フィルター作成。セル範囲を指定する。
main()
スクリプトを実行します。
$ python filter_sheet_gspread.py
するとシートの指定したセル範囲にBasic Filterを設定できました。ただしcriteriaによる絞り込みまでは出来ないようです。
またclear_basic_filter()を使用するとBasic Filterの解除も行えます。
googleapiclientを使った場合
googleapiclientは、Google APIを網羅的に使用できるライブラリです。
そしてgoogleapiclientのSheets APIでは、Basic FilterとFilter Viewsのいずれの操作にも対応しています。それぞれ試してみます。
必要なパッケージをインスールします。
pip install google-api-python-client oauth2client
次のスクリプトでは、SetBasicFilterRequestオブジェクトを作成して、スプレッドシートに対してbatchUpdate()により適用し、Basic Filterを設定しています。
import os
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery
JSON_FILE_NAME = os.environ['JSON_FILE_NAME']
SPREAD_SHEET_ID = os.environ['SPREAD_SHEET_ID']
SHEET_ID = 0 # 0は1枚目のシートの既定のID
def create_service(jsonFileName):
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
jsonFileName, scope)
return discovery.build('sheets', 'v4', credentials=credentials)
setBasicFilterRequest = {
'setBasicFilter': {
'filter': {
'range': {
"sheetId": SHEET_ID,
"startRowIndex": 0,
"endRowIndex": 11,
"startColumnIndex": 0,
"endColumnIndex": 5
},
'filterSpecs': [
{
'filterCriteria': {
'condition': {
'type': 'TEXT_EQ',
'values': {
'userEnteredValue': 'モニカ'
}
}
},
'columnIndex': 3
}
]
}
}
}
def main():
service = create_service(JSON_FILE_NAME)
service.spreadsheets().batchUpdate(
spreadsheetId=SPREAD_SHEET_ID,
body={
'requests': [setBasicFilterRequest]
}
).execute()
main()
スクリプトを実行します。
$ python filter_sheet_googleapiclient_basic.py
するとBasic Filterを設定できました。また4列目を文字列モニカの一致でフィルターできています。
次のスクリプトでは、addFilterViewオブジェクトを作成して、スプレッドシートに対してbatchUpdate()により適用し、Filter Viewsを作成しています。
import os
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery
JSON_FILE_NAME = os.environ['JSON_FILE_NAME']
SPREAD_SHEET_ID = os.environ['SPREAD_SHEET_ID']
SHEET_ID = 0 # 0は1枚目のシートの既定のID
def create_service(jsonFileName):
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
jsonFileName, scope)
return discovery.build('sheets', 'v4', credentials=credentials)
addFilterViewRequest = {
'addFilterView': {
'filter': {
'title': "testFilter",
'range': {
"sheetId": SHEET_ID,
"startRowIndex": 0,
"endRowIndex": 11,
"startColumnIndex": 0,
"endColumnIndex": 5
},
'criteria': {
3: {
'condition': {
'type': 'TEXT_EQ',
'values': {
'userEnteredValue': 'モニカ'
}
}
}
}
}
}
}
def main():
service = create_service(JSON_FILE_NAME)
service.spreadsheets().batchUpdate(
spreadsheetId=SPREAD_SHEET_ID,
body={
'requests': [addFilterViewRequest]
}
).execute()
main()
スクリプトを実行します。
$ python filter_sheet_googleapiclient_view.py
するとフィルターtestFilterが作成されました。
testFilterを適用した様子です。addFilterViewのcriteriaで指定した通り、4列目を文字列モニカの一致でフィルターできています。
まとめ
- gspread
- Basic Filterのみ対応
- criteriaによる絞り込みは設定できない
- 記述は比較的簡潔
- googleapiclient
- Basic FilterとFilter Viewsのいずれも対応
- criteriaによる絞り込みも設定可能
- 記述は比較的複雑
参考
以上
