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

はじめに

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

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

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

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

主な流れ

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

前提条件

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

Google APIの有効化

まずは、お手持ちのGoogleアカウントでログインした状態で Google Developer Console にアクセスします

プロジェクトを作成する

「プロジェクトの選択」をクリック

「新しいプロジェクト」から新しいプロジェクトを作ります

「プロジェクト名」を入力して作成します
　今回は、「tanaka-gss-test」という名前で作ってみます

無事にプロジェクトが作成されました

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

APIライブラリで、Google Spread Sheet用のAPIを探します

「Google Sheets API」が見つかるので、選択します

APIを有効にします

すると、Google Sheets APIが有効になります

OAuth同意

外部からアクセスを許可するためにOAuth同意をします
Google Sheets APIから、「同意画面を設定」を選択します

User Typeを「内部」か「外部」かどちらか選択して、作成します

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

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

必須項目	入力内容
アプリ名	任意のアプリケーション名（変更不可）
ユーザーサポートメール	Googleアカウント
メールアドレス	自分のメールアドレス

次に、スコープを設定します
「スコープを追加または削除」を選択します

認証のスコープに入れるAPIを選択します
今回はGoogle Spread Sheetのデータを参照しかしないので
Google Sheets API の範囲 .../auth/spreadsheets.readonly を選択します
必要に応じて、他のAPIも追加します

「機密性の高いスコープ」の部分に
先ほど選択したAPIが表示されるようになります

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

「＋ADD USER」を選択します

サンプルなので [e-mail] と入力していますが
実際は存在するGoogleアカウントを入力してください

先ほど入力したGoogleアカウントが表示されました

OAuth同意ができました

認証情報の設定

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

認証情報から、「＋認証情報を作成」を選択します

「OAuthクライアントID」を選択します

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

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

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

「名前」は、コンソール画面上で識別するためだけのもののため
適当に識別できる名前をつけてやります

OAuthクライアントが作成されました
ここで クライアントID と クライアント シークレットキー が表示されます
また「JSONをダウンロード」を選択すると、これらの情報が記載されているclient_secret_1234567890-xyzxyzxyzxyzxyz.apps.googleusercontent.com.json みたいなJSONファイルがダウンロードされます
あとで利用するので、大切に保管してください

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

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

サンプルデータの作成

個人情報テストデータジェネレーター という個人情報サンプルデータをランダムで生成してくれるサービスを利用します

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

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

を選択して、1000件のサンプルデータを作成します

Google Spread Sheetにインポート

次に、今回利用するGoogle Spread Sheetを新規作成します
そして、「ファイル」＞「インポート」を選択します

「アップロード」を選択し、先ほど作成した個人情報サンプルデータをCSV取込します

こんな感じで、CSVの内容がSpread Sheetに反映されます
今回はシート名を questionnaire にしました、のちほど使います

PythonからSpread Sheetの情報を取得

今回はPythonを使って、さきほど作成した Spread Sheetにアクセスします

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

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

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

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をもう一度

---

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

ここで、自分のログイン中のアカウントを選択します

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

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

アプリがあなたのアカウントにアクセスしますの確認が来ます
Continueを選択します

すると、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で実行します

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は、適宜自分の環境に置き換えてください

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()

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

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

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

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コードを実行して、テーブルの方を見てみましょう

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

---

いかがだったでしょうか

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

今回はただデータを引っ張ってくるだけでしたが、もちろんSpread SheetをAPIから変更することも可能です
詳しくは Google Sheets for Developers を参考にしてみてください