Google スプレットシートで簡易検索

Last updated at 2024-12-06Posted at 2024-12-06

この記事は、ZOZO #11 Advent Calendar 2024 7日目の記事になります。
昨日は私（@zz_takumi_ito) の「PowerShellでEdgeの自動操縦について」の記事でした。

はじめに

コーポレートエンジニアリングでは持っているデータを見てもらう事が往々にしてあります。
今回は複数のマスタを持つデータからスプレットシートで簡易検索を作ってみます。

結論

Spreadsheets APIを利用することでローカルデータからシートの更新
IMPORTRANGEでシートから他のシートデータを参照
Match関数とFilter関数を利用することで簡易検索

Google Spreadsheets APIの利用

Spreadsheet の準備

更新対象となるSpreadsheetを一つ作ります
Google Cloud Platformのサービスアカウントを作成
- サービスアカウントの作成については今回は割愛
  - Spreadsheets API を使えるように権限を付与
- 例: facility@gsuite-corp-it-facility.iam.gserviceaccount.com
Spreadsheetの共有設定で、サービスアカウントに編集権限を付与

Python 側の準備

requirements.txt

Spreadsheets APIを利用するためのライブラリをインストール

cachetools==5.3.2
certifi==2023.11.17
charset-normalizer==3.3.2
google-api-core==2.15.0
google-api-python-client==2.111.0
google-auth==2.25.2
google-auth-httplib2==0.2.0
googleapis-common-protos==1.62.0
httplib2==0.22.0
idna==3.6
protobuf==4.25.1
pyasn1==0.5.1
pyasn1-modules==0.3.0
pyparsing==3.1.1
requests==2.31.0
rsa==4.9
uritemplate==4.1.1
urllib3==2.1.0

インストール

pip install -r requirements.txt

setting.json

serviceAccount.json は、Google Cloud Platformで作成したサービスアカウントのキーファイル
updateSheetId は、更新日付を記録するSpreadsheet ID
targets は複数指定でそれぞれのCSVファイルを同期するファイル、エンコード、シートのID、シート名を指定
- sheetId は、更新対象のシートのID
- utf-8-sig は、BOM付きUTF-8
CSVファイルは別スクリプトが準備していたものを利用

{
    "keyfilePath": ".\\serviceAccount.json",
    "sheetId": "16OUh6nT09KviuICRIL3caXRXUbGHLcdA7febzYlENtx",

    "updateSheetId": 1719969649,
    "updateSheetName": "更新情報",

    "targets":[
        {
            "csvFile": ".\\data\\rental.csv",
            "csvFileEncoding": "utf-8-sig",
            "sheetId": 1325229661,
            "sheetName": "レンタル"

        },
        {
            "csvFile": ".\\data\\intune.csv",
            "csvFileEncoding": "utf-8-sig",
            "sheetId": 1885211306,
            "sheetName": "Intune情報"
        },
        {
            "csvFile": ".\\data\\azureAD.csv",
            "csvFileEncoding": "utf-8-sig",
            "sheetId": 2122931125,
            "sheetName": "AzureAD情報"
        }
    ]
}

main.py

setting.json に記載されたCSVファイルをSpreadsheetに更新
更新日付を記録するSpreadsheetに更新日付を記録

# 設定ファイル読込
import json
json_file = open('.\\setting.json', 'r', encoding="utf-8")
setting = json.load(json_file)
SHEET_ID = setting["sheetId"]

# Google 認証とAPI利用の準備
import google.auth
from googleapiclient.discovery import build

# Sheets API認証
scopes = ['https://www.googleapis.com/auth/spreadsheets']
creds, _ = google.auth.load_credentials_from_file(setting["keyfilePath"], scopes=scopes)
service = build('sheets', 'v4', credentials=creds)

# シート情報の更新
def sheet_update(tabId, sheetName, array):
    rowMax = len(array)
    colMax = len(array[0])

    requests = []
    requests.append({
        'updateSheetProperties': {
            'properties': {
                'sheetId': tabId,
                'title': sheetName,
                'gridProperties': {
                    'rowCount': rowMax,
                    'columnCount': colMax
                }
            },
            'fields': 'gridProperties.rowCount,gridProperties.columnCount,title'
        }
    })

    body = {'requests': requests}
    service.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,body=body).execute()

    range_ = sheetName + "!R1C1:R" + str(rowMax+1) + "C" + str(colMax)
    v={}
    v['range']=range_
    v['majorDimension']="ROWS"
    v['values']=array
    value_input_option = 'USER_ENTERED'
    result = service.spreadsheets().values().update(spreadsheetId=SHEET_ID, range=range_, valueInputOption=value_input_option, body=v).execute()

    return result

## CSV情報の読み込み
import csv

for target in setting["targets"]:
    csv_file = target["csvFile"]
    csv_file_encoding = target["csvFileEncoding"]

    data=[]
    with open(csv_file, "r", encoding=csv_file_encoding ) as csv_org:
        reader = csv.DictReader(csv_org, delimiter=",", doublequote=True, lineterminator="\r\n", quotechar='"', skipinitialspace=True)
        data = [reader.fieldnames]
        for row in reader:
            r=[]
            for col in reader.fieldnames:
                r.append(row[col])            
            data.append(r)

    res = sheet_update(target["sheetId"], target["sheetName"], data)
    print(res)

# 更新日付の更新
from datetime import datetime
updateDate = [["データ取得日"],[datetime.today().strftime('%Y/%m/%d')]]
res = sheet_update(setting["updateSheetId"] , setting["updateSheetName"], updateDate)
print(res)

詳細は省いていますがCSVファイルの内容で各Spreadsheetを更新

Spreadsheet間の参照

Spreadsheetの自動更新が出来るようになるとあちこち利用場所が思いつきます
- 既存のシートを参照することで更新漏れ防止します
サービスアカウント(外部のユーザ含む)を招待出来ない共有ドライブであっても、閲覧している人の参照権限があればデータを参照することが可能

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/IdAbc", "レンタル!A:D")

IdAbc は参照先SpreadsheetのID
Spreadsheetを開いた際にレンタル!A:D のデータを読み込むことが可能
- 更新日付を記録しておくとPythonスクリプトでのSpreadsheetの更新が動いているか確認できます

簡易検索

データは１シートにまとめずデータ元毎シートを分ける
- Pythonスクリプトでは最大行・最大列を指定することでデータが減少した際に昔のデータが残るのを防ぐ
D9 に調べたい端末名を入力すると、D12:D22 に該当するデータが表示
- 検索で利用するデータについては名前付き範囲を利用してデータサイズの変更に対応
- 名前付き範囲でA:Bなど列全体を選択

=IFNA(INDEX(レンタルPC_シリアル,MATCH($D9,レンタルPC_端末名,0)),"見つかりません")

D9に入力された端末名を名前付き範囲レンタルPC_端末名と比較して一致する行を探す
一致した行の行数が戻ってきます
名前付き範囲レンタルPC_シリアルの2で戻ってきた行番目のデータを表示
一致しない場合は「見つかりません」と表示

=countif(Intune_PrimaryUserUPN,D18)

D9に入力された端末名を名前付き範囲Intune_DeviceNameと比較して一致する行を探す
一致した行の行数が戻ってくる
名前付き範囲Intune_PrimaryUserUPNの2で戻ってきた行番目のデータを表示(D18)
名前付き範囲Intune_PrimaryUserUPNで3のデータと同じ行をカウント
何台のIntune管理デバイスを保持しているかわかる

=FILTER(Intune_DeviceName,Intune_PrimaryUserUPN = D18)

D9に入力された端末名を名前付き範囲Intune_DeviceNameと比較して一致する行を探す
一致した行の行数が戻ってくる
名前付き範囲Intune_PrimaryUserUPNの2で戻ってきた行番目のデータを表示(D18)
名前付き範囲Intune_PrimaryUserUPNで3のデータと同じ行をフィルタ
Intune管理デバイスの保持している一覧がわかる

まとめ

今回は弊社のレンタルデバイスがあとどのくらいの期間使えるのかを問い合わせ者が自分で検索出来る簡易検索を作成しました
- レンタルの台帳では利用者と直接紐づいていないためIntuneのデバイス情報を経由して参照
- ユーザの所属がわかるようにするためAzureADの情報も参照
名前付き範囲とMatch, Indexを用いるとデータ数が大きくなっても画面更新の負荷が少ない
チーム内の作業ではExcel PowerQueryをよく利用していますが、共有を考えると権限の範囲指定や簡単な文字列の装飾、セルの保護が出来るスプレットシートは使いやすいのでお勧め

明日は@ksudateさんの記事になります。お楽しみに。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up