PythonでGoogle Spread Sheetのフィルターを操作してみた

2022.03.15

こんにちは、CX事業本部 IoT事業部の若槻です。

今回は、PythonでGoogle Drive上に保存されているGoogle Spread Sheet(Googleスプレッドシート)のフィルターを操作してみました。

Googleスプレッドシートのフィルターは2種類ある

そもそもGoogleスプレッドシートのフィルターには、Basic FilterFilter 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向けのライブラリには、主要なものとしてgspreadgoogleapiclientの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を設定しています。

filter_sheet_gspread.py

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を設定しています。

filter_sheet_googleapiclient_basic.py

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を作成しています。

filter_sheet_googleapiclient_view.py

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を適用した様子です。addFilterViewcriteriaで指定した通り、4列目を文字列モニカの一致でフィルターできています。

まとめ

  • gspread
    • Basic Filterのみ対応
    • criteriaによる絞り込みは設定できない
    • 記述は比較的簡潔
  • googleapiclient
    • Basic FilterとFilter Viewsのいずれも対応
    • criteriaによる絞り込みも設定可能
    • 記述は比較的複雑

参考

以上