Google Spread SheetのデータをMySQLにインポートしてみる

#はじめに
みなさん、はじめまして
人生で始めてQiita書きます、お手柔らかにお願いします

私は現在テクニカルサービス部門の開発グループにいます
とは言っても、最近なかなかコーディングをする機会が減っており、そんな折アドベントカレンダーを書いてはいかが？というお話をいただいたので、チャレンジしてみようかなと思いました

弊社では業務の中で **Google Spread Sheet** を利用しており、実際 Google Spread Sheet や Google Apps Script 関連記事が投稿されていたりします

https://qiita.com/Hokuto-Niimura/items/43b3d97fd52a5030cbdd

https://qiita.com/hitsumabushi845/items/7e5d7c27cd8b8e1fbc8f

今回は、そんなみんな大好きGoogle Spread Sheetのデータを、DBに格納してみようと思います

#主な流れ

1. Google APIの有効化
- サンプルデータ、シートの準備
- PythonからSpread Sheetの情報を取得
- 取得してきた情報をMySQLに格納

#前提条件

- Googleアカウントを保有している
- Python3系の開発環境がある
- MySQLのデータベースがある

# Google APIの有効化

まずは、お手持ちのGoogleアカウントでログインした状態で [Google Developer Console](https://console.developers.google.com/?hl=JA "Google Developer Console") にアクセスします

##プロジェクトを作成する

「プロジェクトの選択」をクリック
![001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/ab4db4dd-7da9-bfb5-ca97-b997cd55cce8.png)

「新しいプロジェクト」から新しいプロジェクトを作ります
![002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/a07c5595-4605-94fc-60dd-a8553b49e245.png)

「プロジェクト名」を入力して作成します
　今回は、「tanaka-gss-test」という名前で作ってみます
![003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/2bc6ea60-2228-4eb6-92d8-49147ff0571c.png)

無事にプロジェクトが作成されました
![004.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/44b3dedb-fc3d-d442-d3e8-40e6ff13738c.png)

##Google Spread SheetのAPI（Google Sheets API）を有効化する

APIライブラリで、Google Spread Sheet用のAPIを探します
![005.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/9cfc9336-fd2d-23fa-1f4c-c0f4a73af554.png)

「Google Sheets API」が見つかるので、選択します
![006.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/daca3824-d1ef-61b2-425b-dbcf5009e4e0.png)

APIを有効にします
![007.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/0816e92b-20cd-7226-3aad-f06ca5a7a0e6.png)

すると、Google Sheets APIが有効になります
![008.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/4770c57c-fb38-1b56-2e82-4043781e9383.png)

##OAuth同意

外部からアクセスを許可するためにOAuth同意をします
Google Sheets APIから、「同意画面を設定」を選択します
![009.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/7b9ae419-5d6b-dea4-9eab-e87327a79422.png)

User Typeを「内部」か「外部」かどちらか選択して、作成します
![010.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/ed7c94f2-2102-8987-3108-7122dfd4417f.png)

:::note warn
注意
個人アカウントのため「外部」を選択していますが、Google Workspaceユーザの場合は「内部」を選択した方が安全かと思います。
:::

次に、アプリ情報を入力します
今回は、必須項目である下記の情報だけ入力します

| 必須項目 | 入力内容 |
|:-|:-|
| アプリ名 | 任意のアプリケーション名（変更不可） |
| ユーザーサポートメール | Googleアカウント |
| メールアドレス | 自分のメールアドレス |
![011-01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/3e640be6-ef50-7505-bd37-6113c7b86638.png)

次に、スコープを設定します
「スコープを追加または削除」を選択します
![012.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/6e9b8d51-8ff8-9897-1652-3b854e116c08.png)

認証のスコープに入れるAPIを選択します
今回はGoogle Spread Sheetのデータを参照しかしないので
**Google Sheets API** の範囲 `.../auth/spreadsheets.readonly` を選択します
必要に応じて、他のAPIも追加します
![013.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/4dce67f4-9003-cd59-a2a9-ef4073793737.png)

「機密性の高いスコープ」の部分に
先ほど選択したAPIが表示されるようになります
![014.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/b9097813-b6a3-83d7-26cf-b16efffbd37e.png)

次にテストユーザを決めます
このアプリの公開ステータスが「テスト中」に設定されている間も、アクセスできるユーザを設定します
今回は自分のアカウントだけを設定します

「＋ADD USER」を選択します
![015.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/b7eabce9-3695-5363-0406-628cca4b868e.png)

サンプルなので `dummy@gmail.com` と入力していますが
実際は存在するGoogleアカウントを入力してください
![015-01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/689f090a-e5b3-8d9b-01d7-517f2c1fc506.png)

先ほど入力したGoogleアカウントが表示されました
![015-02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/00590bc5-f5f3-423b-b4f4-c79274d3fa5e.png)

OAuth同意ができました
![016.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/a7c1ca87-4c01-fd76-acc0-d7197b6b80dd.png)


##認証情報の設定
いよいよ、この章最後の部分になります
あともう少しです、頑張ってください！

認証情報から、「＋認証情報を作成」を選択します
![017.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/5f358a91-c35b-c8c2-3077-6e95d2c089c9.png)

「OAuthクライアントID」を選択します
![018.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/7291eac4-58e7-96f0-0405-44914543ad65.png)

アプリケーションの種類に
このアプリを実行するプラットフォームを選択します

- ウェブ アプリケーション
- Android
- Chromeアプリ
- iOS
- テレビと入力が限られたデバイス
- デスクトップアプリ
- ユニバーサルWindows プラットフォーム

が選択でき、自分に該当するするものを選択します
今回は「デスクトップアプリ」を選択します

「名前」は、コンソール画面上で識別するためだけのもののため
適当に識別できる名前をつけてやります
![019.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/9657795a-a1a2-63ae-17a9-5a95b2f4b6e2.png)

OAuthクライアントが作成されました
ここで **クライアントID** と **クライアント シークレットキー** が表示されます
また「JSONをダウンロード」を選択すると、これらの情報が記載されている`client_secret_1234567890-xyzxyzxyzxyzxyz.apps.googleusercontent.com.json` みたいなJSONファイルがダウンロードされます
あとで利用するので、大切に保管してください
![020.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/9a6aa365-5e0d-a742-ec39-03a2d214cb8f.png)

#サンプルデータ、シートの準備

さて、Google API側の設定はようやく終わりました
ここから実際に読み込むためのデータの準備をしていきたいと思います

##サンプルデータの作成
[個人情報テストデータジェネレーター](https://testdata.userlocal.jp/ "個人情報テストデータジェネレーター") という個人情報サンプルデータをランダムで生成してくれるサービスを利用します

このサービスが結構便利で、氏名、年齢、生年月日などの個人情報を10000レコードまで作成してくれます
今回はここから、

- 氏名（漢字、ひらがな）
- 年齢
- 生年月日
- 性別
- 血液型
- 郵便番号
- 住所

を選択して、1000件のサンプルデータを作成します
![generator.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/f9a5edbd-f5e5-a3c4-333f-07d79a5a4a70.png)

##Google Spread Sheetにインポート

次に、今回利用するGoogle Spread Sheetを新規作成します
そして、「ファイル」＞「インポート」を選択します
![import.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/608c893d-cf8a-11fb-9c72-951f8e319e2a.png)

「アップロード」を選択し、先ほど作成した個人情報サンプルデータをCSV取込します
![import2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/35fd6aa4-1e62-0748-679c-8092c400b5d6.png)

こんな感じで、CSVの内容がSpread Sheetに反映されます
今回はシート名を **questionnaire** にしました、のちほど使います
![spreadsheet.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/f561dd3b-2956-3110-0237-8b589fc93f39.png)

#PythonからSpread Sheetの情報を取得
今回はPythonを使って、さきほど作成した Spread Sheetにアクセスします

まず必要なライブラリをインストールします

```
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
```

次に、ソースコードを用意します

```python:GssDataImporter.py
import os

from google.auth.transport.requests import Request
from google_auth_oauthlib.flow import InstalledAppFlow
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build

SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
TOKEN_JSON_FILE = 'token.json'

CLIENT_SECRET_FILE = 'D:\\workspace\\python\\GssImportTest\\credentials\\client_secret.json'
SPREADSHEET_ID = '1234567890ABCDEFGHIJKLMN'
RANGE_NAME = 'questionnaire!A2:H1001'


def get_questionnaire():
    credentials = None
    if os.path.exists(TOKEN_JSON_FILE):
        credentials = Credentials.from_authorized_user_file(TOKEN_JSON_FILE, SCOPES)

    if not credentials or not credentials.valid:
        if credentials and credentials.expired and credentials.refresh_token:
            credentials.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)
            credentials = flow.run_local_server(port=0)
        with open(TOKEN_JSON_FILE, 'w') as token:
            token.write(credentials.to_json())

    service = build('sheets', 'v4', credentials=credentials)
    result = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()

    return result.get('values', [])


def main():
    res = get_questionnaire()
    for row in res:
        print(row[0], row[1], row[2], row[3], row[4], row[5], row[6])


if __name__ == "__main__":
    main()

```

**CLIENT_SECRET_FILE** 
さきほど「OAuthクライアント」作成時にダウンロードしたJSONファイルの場所を指定します

**SPREADSHEET_ID** 
取込対象のGoogle Spread SheetのURLに書いてあるIDを指定します
`https://docs.google.com/spreadsheets/d/1234567890ABCDEFGHIJKLMN/` というURLであれば、この `1234567890ABCDEFGHIJKLMN` 部分になります

**RANGE_NAME**
シートの名称と範囲を `[シート名]![範囲]` という形で指定します
範囲は指定したい領域の左上のセルから右下のセルになります

| - | A | B | C | D | E | F | G | H |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 |   |   |   |   |   |   |   |   |
| 2 |   | ○ | ○ | ○ | ○ | ○ | ○ |   |
| 3 |   | ○ | ○ | ○ | ○ | ○ | ○ |   |
| 4 |   | ○ | ○ | ○ | ○ | ○ | ○ |   |
| 5 |   | ○ | ○ | ○ | ○ | ○ | ○ |   |
| 6 |   |   |   |   |   |   |   |   |

例えば、*test* シートの B2セルからG5セルまでを対象にしたい場合は `test!B2:G5` と指定します

今回用意しているのは 

*questionnaire* というシート
カラムAの「氏名」から、カラムH「住所」まで
1行目はタイトル行なので、2行目から1000行後の1001行目

を利用したいので、`questionnaire!A2:H1001` を指定しました

今回用意しているSpread Sheetをもう一度
![data.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/ebf86868-2578-1a78-6d68-af01ad386ef1.png)

---

さて、実際に実行してみましょう
アクセストークンがなく、初めてのアプリからのアクセスの場合は、Google Chromeが勝手に起動してログイン認証が求められます

ここで、自分のログイン中のアカウントを選択します
![021.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/c9d29e0b-afe6-b6ed-3dc2-4d53c4980c05.png)

公開ステータスが「テスト」のものですよ、の警告がでます
Continueを選択します

![022.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/7d127ded-0a18-8759-d99a-0f8df436e0d8.png)

:::note warn
注意
今回は自分が開発者のもののためContinueを選択しますが、そうではない場合は信頼できるデベロッパーか確認しましょう
:::

アプリがあなたのアカウントにアクセスしますの確認が来ます
Continueを選択します
![023.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/028dc835-635c-ef1f-02bf-659b0a4b6870.png)

すると、Python側で処理が続行されます

```
Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=************************
松岡 大輔 まつおか だいすけ 20 2001/03/18 男 B 839-3912
宮城 康彦 みやぎ やすひこ 31 1990/08/12 男 A 584-9795
宮里 康文 みやざと やすふみ 51 1969/12/20 男 B 666-2986
坂本 弥生 さかもと やよい 68 1953/07/28 女 A 182-5377
中村 竜介 なかむら りゅうすけ 40 1981/01/07 男 A 121-0792
・・・
・・・
・・・
平賀 直樹 ひらが なおき 66 1955/05/23 男 A 336-3182
斉藤 明子 さいとう あきこ 42 1978/12/23 女 O 191-1756

プロセスは終了コード 0 で完了しました
```

Spread Sheetの内容がコンソールに出力されました！

#取得してきた情報をMySQLに格納

さきほどSpread Sheetから取得してきたデータはリスト型で返ってきています
これをそのままMySQL向けにexecutemanyで突っ込んでやります

まずは、取り込む先のDBにテーブルを作りましょう
下記のSQLをすでに用意しているDBで実行します

```sql:create_questionnaire_table.sql
CREATE TABLE `test`.`questionnaire` (
  `name` VARCHAR(128) NULL,
  `furigana` VARCHAR(128) NULL,
  `age` INT NULL,
  `birthday` DATE NULL,
  `gender` VARCHAR(16) NULL,
  `blood_type` VARCHAR(8) NULL,
  `postal_code` VARCHAR(16) NULL,
  `address` VARCHAR(256) NULL);
```

次にPython側で、MySQLに接続する準備をします
まずは必要なライブラリのインストールを行います

```
pip install --upgrade mysql-connector-python
```

さらに、さきほどSpread SheetにアクセスしたPythonコードに下記のコードを追加します
DBのuser、password、host、databaseは、適宜自分の環境に置き換えてください

```python
def insert_data(questionnaire_data):
    conn = None
    try:
        conn = mysql.connector.connect(
            user='root',
            password='root',
            host='localhost',
            database='test')

        c = conn.cursor()

        sql = 'insert into test.questionnaire values (%s, %s, %s, %s, %s, %s, %s, %s)'
        c.executemany(sql, questionnaire_data)
        conn.commit()

        c.close()

    except Exception as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()
```

メインメソッドも書き換えます

```python
def main():
    res = get_questionnaire()
    insert_data(res)
```

全体的に見ると、こんな感じになるかと思われます

```python:GssDataImporter.py
import os

import mysql.connector
from google.auth.transport.requests import Request
from google_auth_oauthlib.flow import InstalledAppFlow
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build

SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
TOKEN_JSON_FILE = 'token.json'

CLIENT_SECRET_FILE = 'D:\\workspace\\python\\GssImportTest\\credentials\\client_secret.json'
SPREADSHEET_ID = '1234567890ABCDEFGHIJKLMN'
RANGE_NAME = 'questionnaire!A2:H1001'


def get_questionnaire():
    credentials = None
    if os.path.exists(TOKEN_JSON_FILE):
        credentials = Credentials.from_authorized_user_file(TOKEN_JSON_FILE, SCOPES)

    if not credentials or not credentials.valid:
        if credentials and credentials.expired and credentials.refresh_token:
            credentials.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)
            credentials = flow.run_local_server(port=0)
        with open(TOKEN_JSON_FILE, 'w') as token:
            token.write(credentials.to_json())

    service = build('sheets', 'v4', credentials=credentials)
    result = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
    return result.get('values', [])


def insert_data(questionnaire_data):
    conn = None
    try:
        conn = mysql.connector.connect(
            user='root',
            password='rootroot',
            host='localhost',
            database='test')

        c = conn.cursor()

        sql = 'insert into test.questionnaire values (%s, %s, %s, %s, %s, %s, %s, %s)'
        c.executemany(sql, questionnaire_data)
        conn.commit()

        c.close()

    except Exception as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()


def main():
    res = get_questionnaire()
    insert_data(res)


if __name__ == "__main__":
    main()
```


さて、Pythonコードを実行して、テーブルの方を見てみましょう
![mysql.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2302622/0ddb7659-7c8e-ba81-0bab-ff832aa830fd.png)

テーブルの中にコードが無事に入ってきました

---

いかがだったでしょうか

Spread Sheetに溜めている情報と社内にあるデータを組み合わせて、データ加工したり分析、BIツールで可視化したり、いろんな用途で使えそうですね

今回はただデータを引っ張ってくるだけでしたが、もちろんSpread SheetをAPIから変更することも可能です
詳しくは [Google Sheets for Developers](https://developers.google.com/sheets/api/ "Sheets API") を参考にしてみてください