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

この記事は、[ZOZO #11 Advent Calendar 2024](https://qiita.com/advent-calendar/2024/zozo) 7日目の記事になります。
昨日は私（@zz_takumi_ito) の「PowerShellでEdgeの自動操縦について」の記事でした。

## はじめに

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

## 結論

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

## Google Spreadsheets APIの利用

### Spreadsheet の準備

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

### Python 側の準備

#### requirements.txt

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

```plaintext
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
```

- インストール

```python
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ファイルは別スクリプトが準備していたものを利用

```json
{
    "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に更新日付を記録

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

![2024-11-07-02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/82941/e794c5ac-a883-1ae6-4821-968fd6203611.png)

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

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

## 簡易検索

![2024-11-07-04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/82941/0c892047-3cba-3dd3-57ef-b7b016aaea13.png)

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

```plaintext
=IFNA(INDEX(レンタルPC_シリアル,MATCH($D9,レンタルPC_端末名,0)),"見つかりません")
```
![2024-11-07-05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/82941/1b79896d-6ef5-2614-61e7-3952285586fe.png)

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

```plaintext
=countif(Intune_PrimaryUserUPN,D18)
```

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

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

## まとめ

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

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