Google SpreadSheetのデータを Pythonで取得する

# 目的
## 概要
ヴェネクト株式会社のディレクター 小峰です。
今回の記事では、Google SpreadsheetのデータをPythonで取得する方法をご紹介します。
VENECT内での活用事例を踏まえつつ、下記の要素を説明します。

- どのPackageを利用すべきか
- アカウント認証をどのように行うか
- Google Spreadsheetのデータをどう取得するか
- 活用しやすいようにDataFrame変数に加工する

## 利用例
Google Spreadsheetに記載される情報をBigQueryに送信するために利用しています。担当者が手動で入力・管理したい情報をGoogle Spreadsheetのテンプレートで管理し、PythonのアプリケーションでBigQueryに送信しています。そのデータをシステム側で処理し、必要なデータに加工しています。

# プログラム実行環境
## 利用するPackage
Google Spreadsheetのデータにアクセスするために、__gspread__ というPackageを利用してデータ取得を行っています。Google公式が提供しているPackageよりも必要なデータにアクセスする処理がシンプルなので、こちらをおすすめします。

### インストール方法
pipで配布されていますので、下記のコマンドをコマンドプロンプト（MS）やターミナル（MAC）で実行すれば取得することができます。
```
pip install gspread
```

## API利用前の準備
### GCPAuth情報の取得方法
Spreadsheet APIですが、GCPのAuth情報が必須になります。そのため、GCPのAuth情報を取得するための手順を記載します。

### GCPのプロジェクトを作成する
作成手順は下記の公式Helpをご確認ください
> __GCP｜プロジェクトの作成と管理__
https://cloud.google.com/resource-manager/docs/creating-managing-projects?hl=ja

### API接続用の認証取得
APIでリクエストを行う際に、GCP認証用のJsonファイルが必要になります。GCPのプロジェクトを作成後、下記の手順で認証用Jsonファイルを取得できます。
1. ページ左側のメニューから「APIとサービス」＞「認証情報」を選択する
   ![スクリーンショット 2022-09-15 16.23.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/a362b1f4-44b8-8ea9-4eba-61cb76599c59.png)

2. 認証情報ページが表示されるので、ページ上部の「＋ 認証情報を作成」を選択する。次にサービスアカウントを選択する
   ![スクリーンショット 2022-09-15 16.23.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/ddd24d22-e838-4b1a-52ee-e80bc8011c26.png)

3. 必要項目を設定後、「完了」ボタンを押す
![68747470733a2f2f71696974612d696d6167652d73746f72652e73332e61702d6e6f727468656173742d312e616d617a6f6e6177732e636f6d2f302f323539383034332f37336237363466622d333137662d363434312d366432322d3239343866333133363261312e706e67-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/870b23df-e415-1f31-91cb-37b8deb33a6f.png)

4. 「​​サービスアカウント」の項目にある作成したアカウントを選択する
5. ページ上部のメニューにある「キー」を選択し、「鍵を追加」を選ぶ
   ![スクリーンショット 2022-09-15 16.25.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/c8a1332a-ead3-f8fb-b0b3-ea7f93280cfb.png)

6. 「JSON」形式を選択し作成、作成が完了すればAuth情報を記載するJSONファイルをダウンロードできる
   ![スクリーンショット 2022-09-15 16.26.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/3e04a62f-6c4c-52b4-30e7-f0869e410bbf.png)

結果、認証情報を記載したJsonが得られるため、管理しやすい場所で保管してください。

### APIの有効化
SpreadsheetのAPI利用には、GCPの側で下記の2つのAPIを有効化する必要があります。
- __Google Drive API__
- __Google Sheets API__

設定方法ですが、下記の手順です。
1. 「APIとサービス」＞「有効なAPIとサービス」から有効化APIの選択画面に遷移する
   ![スクリーンショット 2022-09-15 16.28.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/797a76db-6e79-8910-88ff-80af93171841.png)

2. ページ上部の「+ APIとサービスの有効化」を選択する
   ![スクリーンショット 2022-09-15 16.29.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/7323fbd4-fa62-3fd8-1ef7-9021719bef84.png)

3. APIライブラリに遷移するため、上記の2つのAPIを検索し、有効化する
   ![スクリーンショット 2022-09-15 16.29.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/b941c59c-83c8-9f7e-0c9c-b4fd15d753ee.png)
   ![スクリーンショット 2022-09-15 16.30.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/1c39e164-18d0-9e73-db56-93f7efbf9fc0.png)

### 対象SpreadsheetにAPIアクセスを許可する
APIによるスプレッドシートへのアクセスですが、 __CGCPAuth情報の取得方法__ で取得したJsonファイルに記載されるGoogleアカウントより行われます。
認証用のJsonファイルを確認し __Client_email__ の項目に記載されるGmailアカウントをコピーしてください。
その後、APIでアクセスさせたいGoogle Spfreadsheetを開き、下記の手順で対象のアカウントに権限を付与してください。
1. 右上の「共有」ボタンを押す
   ![スクリーンショット 2022-09-15 16.32.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/e352349a-2314-fedc-e2b9-f1fc26581097.png)

2. 「ユーザーやグループを追加」の欄に該当のGamilアカウントを貼り付ける
   ![スクリーンショット 2022-09-15 16.33.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2598043/bec15021-144e-cb31-ef08-3389906ece7d.png)

3. 権限を「編集者」にし「送信ボタンを押す」

上記で、APIを利用するための準備は完了になります。


# サンプルコード
次にPythonを利用してGoogle Spreadsheetの情報を取得するサンプルコードを記載します。

## 利用するPackage
今回の処理では、下記のPackageを利用します。
```Python
import pandas as pd
import numpy as np
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pytz
from google.cloud import storage
import os
```

Packageの解説をします。
- __Pandas__
  取得したGoogle Spreadsheetの データをDataFrame変数に加工するために利用します
  その結果、不要列と不要行の削除・列単位でのデータの加工など、実用的な処理を行うことができます
  VENECTでは、一度DataFrame変数に加工し適切な処理をした上でBigQueryに送信し業務に活用します。
  DataFrame変数に加工することでTable形式への変換もスムーズに進みます

- __Numpy__
  numpyに組み込まれている関数を利用します
<dd>※今回の処理では採用していませんが、Pandasと相性が良く、使う機会も多いため、一応記載します</dd>

- __gspread__
  今回のGoogle SpreadSheetのデータ取得の核となるPackageです
  データ取得・データの変換まで一括で行ってくれるためかなり利便性が高いです
  <dd>※Google公式のPackageを採用しても処理は可能ですが、gspreadの方がシンプルで使いやすいです</dd>

- __ServiceAccountCredentials__
  GCPの認証に利用します

- __Storage__
  GCPの認証に利用します
<dd>※Google Storage自体は関係ありません</dd>

- __OS__
  環境変数に認証用アカウントを設定するために利用します

## 認証情報の設定
下記がサンプルコードになります。
```Python
Auth = 認証用のJsonファイルのPath
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = Auth
storage_client = storage.Client()
buckets = list(storage_client.list_buckets())
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(Auth, scope)
Client = gspread.authorize(credentials)
```

Client変数にGoogle Spreadsheetの接続情報が格納されます。

## Google Spreadsheetの情報を取得する
先にサンプルコードを記載します。gspreadを利用することでかなりシンプルに取得できます。
### CASE1｜シート内のすべてのセルから情報を取得する
```Python
SpreadSheet = Client.open_by_key(”Google Spreadsheetのキー”)
RawData = SpreadSheet.worksheet("シート名"])
Data = pd.DataFrame(RawData.get_all_values())
```

上記3行だけで、対象シートの情報を取得しData Frame変数に内容を格納できます。

1行目で対象のGoogle Spreadsheetに接続します。
キー番号は、対象シートのURLに記載されていますので、そちらから取得してください。
https://docs.google.com/spreadsheets/d/”ID番号”/edit#gid=0

2行目で対象のシートに接続します。

3行目で対象シートのすべてのセルの情報を取得します。
この際に、下記のように2重のList変数で情報を取得します。
```
[
	[1行目の要素（セル毎にList要素として出力する）]
	[2行目の要素（セル毎にList要素として出力する）]
 	…..
]
```
一旦、細かい加工を先送りにし、DataFrame変数として定義します。（DataFrame変数に変形した方が、加工をしやすいため）

### CASE2｜特定のセルを指定し情報を取得する
 サンプルは下記です。
```Python
SpreadSheet = Client.open_by_key(Json["SPREADSHEET_KEY"])
RawData = SpreadSheet.worksheet(Json["WorkSheet"])
Val = RawData.acell('B1').value
```

サンプルとして __B1__ を記載していますが、AI Notion形式で特定セルを指定することで情報を取得できます。

:::note
__AI Notion__
- __Spread Sheet API Overview__
  https://developers.google.com/sheets/api/guides/concepts#a1_notation
- __シンプルな解説__
  - 以下の文法で記載します
  - シート名!セル位置
  - 例｜sheet1|A1
:::

:::note warn
- __レンジには対応していないことに注意してください。__
    - sheet1|A1:Z100 のように範囲指定はできません。
    - レンジで情報を取得したい場合、 __シート内のデータをすべて取得し、DataFrame変数での条件抽出を活用し、必要な情報にだけ取得する__ 処理で対処してください。
:::

:::note
他にもシート作成やセル値の代入もgspreadは可能です。
詳細は下記の公式Helpよりご確認ください。
- __MIT｜gspread__
  https://docs.gspread.org/en/v5.4.0/index.html
:::


## 情報を加工する
以下は、不要かもしれませんが弊社で採用している加工処理を紹介します。

### 1行目をColumn名にする
```Python
Data.columns =  list(Data.iloc[0])
Data = Data.drop(0, axis=0)
Data = Data.reset_index(drop=True)
```
「シート内のすべてのセルから情報を取得する」で取得したData Frame変数を対象にします。1行目をColumn名に設定し削除しています。合わせて自動設定されるIndexも削除します。

### 空白行を削除する
```Python
Data = Data[Data["列名"]] != 0]
Data = Data[Data["C列名"] != "0"]
Data = Data[Data["列名"]] != ""]
Data = Data[Data["列名"]] != "-"]
Data = Data.dropna(
   subset=[Json["列名"]],
   axis=0
)
```
指定した列の値が、空白や0である場合、削除する処理を行っています。


### 数値列内の0や空白, Nanを"-"で置換する
```Python
for Column in ["列名のリスト"]:
   for i in ["", "0", 0, "Nan", "None", "-", " ", "　", "   ", "   "]:
     Data[Column] = Data[Column].fillna(0)
     Data[Column] = Data[Column].replace(i, 0)
```
nanを0で置換しています。
BigQueryに送信する場合、nanは文字列判定されるエラーを誘発するため、本工程はかなり重要です。

### データ型を定義する
```Python
Data = Data.astype(
   "データ型を指定するDict"]
)
```
 
指定したDict変数に従い型変換をします。
必須ではないですが、BigQueryにデータ送信するため、エラー回避のため、DataFrame変数の各列のデータ型を定義しています。

以上となります。お読みいただきありがとうございました。