【Python】Google Sheets を使ったデータ読み書き【フリーランス案件】

この記事はこんな人におすすめ

・PythonによるGoogle Sheetsを読み込むまでの設定方法を知りたい方
・Pythonを使ってGoogle Sheets中のデータを読み書きしたい方
など

どうもこんにちは。コンです。

唐突に副業の話になるのですが
フリーランスサイトでPythonで何かできないかを探していると、多くの案件を目にすることができます。

画像解析に、株価の予想、スクレイピングを使ったデータ収集など定番なプログラミング案件から
人手不足の影響なのか、Pythonの講師も募集しているところも多いですね。

種類は色々あるのですが、共通して多かったのが
「クラウドサービスを通したデータのやり取り」これのスキルが必要な案件がとても多かったです。

例えばこんな案件がありました。

【案件】
YouTubeからデータをスクレイピングして、再生数と登録者数を取得して
スプレッドシートに記入
【報酬】
5万円

クラウドサービスにも色々あるのですが、今回はそのなかでもよく使われていた
Google Sheets(よくスプレッドシートと呼ばれます)へのPythonのデータの読み書きをやっていきたいと思います。

今回のお題
Google Cloud　を利用可能にする
Pythonコードを書く
おわりに

今回のお題

今回は以下のような身長と体重のデータが入ったスプレッドシートを用意しました。

Pythonを使ってこのデータを読み込んで、BMIを計算してCの列にBMIのデータを入力したいと思います。

Google Cloud　を利用可能にする

Pythonを使う前に、まずはGoogle Sheetsを使う環境構築から始めたいと思います。

これにはGoogle Cloudというクラウドサービスを使います。(よくGCPと呼ばれます)
Googleのインフラ上でウェブアプリケーションの作成・管理だったり、ストレージとして使うことができるサービスだそうです。

最初にまず、GCPを初めて使う方にとっては戸惑う画面になってしまう内容の話をします。

上の画像にも書いてあるように、300ドルの無料クレジットみたいな文言がありまして
使用するのに料金がかかるのでは？と不安に思う方も多いかもしれません。
でも結論から言うと大丈夫です。

上の画像は利用開始時に登録する画面になります。
支払い情報を記入する欄があるものの、右に書いてある通り「有料アカウント」にアップグレードしない限りは
料金は発生しません。

登録を済ませると、GCPトップの画面上に有効化するための表示がでます。

登録が終わればトップ画面に有効化ボタンが表示されるので、有効化さえしなければお金をいきなり取られることは無いです。

GCPの登録はそれほど複雑なものでは無いですが
もし、不安だよーと言う方がいれば、Google Cloudへの登録という別のサイト様が書いた記事も参考にしていただければと思います。

プロジェクトを作成する

GCPトップ画面上にあるMy First Project▼の▼をクリックするとプロジェクトの選択とあるので
今回は「新しいプロジェクト」と書いてある箇所をクリックします。

すると以下のような画面になので、自分の好きなようにプロジェクト名を入力して、「作成」ボタンをクリック。
しばらく待つと、プロジェクトが作成されて一覧にでてきます。

GCPのAPIを有効化する

プロジェクトの作成が終わったら、Pythonで読み込みを可能にするために
Google SheetsのAPIと、Google Sheetsが置いてあるGoogle DriveのAPIの２つを有効化します。

まずは対象プロジェクトが、先ほど作ったプロジェクト名であることを確認して
その横の検索ボックスで欲しいAPIを検索します。

まずはGoogle Sheetsと検索します。
すると以下のようなにGoogle Sheets APIという表示がでるのでこれをクリックします。

そうすると、Google Sheets APIを有効化できそうな感じのする画面がでますので
有効化ボタンをクリックします。

同じような手順をGoogle Drive APIでも実行します。

APIを有効化したら、認証のための設定をします。
メニューバーからAPIとサービスを選択し、認証情報をクリックで選択します。

そうすると、認証情報を管理するページがでるので
+認証情報を作成とでるので、今回は「サービスアカウント」を選択しクリックします。

そうするとサービスアカウントの詳細を設定する画面に遷移するので、サービスアカウント名を入力します。
あとは特に設定は必要ないため、完了ボタンをクリックします。

キーをダウンロード

完了を選択すると、まもなく認証画面の下の方にアカウントが作成されてされます。
さらにこのメールアドレスをクリックして、キーの設定をしていきます。

メールアドレスをダブルクリックすると、キーを設定する画面になります。
鍵を追加⇨新しい鍵を作成の順で選択します。

キーのタイプを「JSON」を選択した状態で「作成」をクリックして、jsonファイルをダウンロードします。

このjsonファイルを次から作るjsonファイルと同じディレクトリにおいて準備完了です。

test-key.jsonという名前にしました。test_gspread.pyが次に書いていくpythonコード。

さて、今回読み込ませるGoogle Sheetsにもjsonの設定をいれます。
Visual Studio Codeでjsonファイルを開くと中にメールアドレスが書いてあるので、
そのメールアドレスをGoogle Sheetsの共有設定に入力しておきます。

Pythonコードを書く

お題に書いた、A列に入った身長とB列に入った体重のデータが入ったGoogle Sheetsを読み込んで
そのデータを使ってBMIを計算して、C列に記入していきます。

コードはこんな感じになります。

import gspread
import numpy as np

#BMIを計算
def calc_bmi(list_data):
    int_list = [int(n) for n in list_data]
    #身長をcmからmに変換
    height = int_list[0]/100
    weight = int_list
    return weight/(height)**2


def main():
    #ダウンロードしたjsonファイルを使用する
    gc = gspread.service_account(filename="test-key.json")
    #シートを開く
    wks = gc.open("test-gspread").sheet1
    #シートの値を全て取得する
    data = wks.get_all_values()
    #BMIの計算をする
    result_map = list(map(calc_bmi, data))

    #BMIの値を記入する
    wks.update_acell('C1', 'BMI')
    for i in range(len(result_map)):
        cell_position = 'C'+str(i+2)
        wks.update_acell(cell_position, result_map[i])
    
main()

main()の中身の話が、今回の処理の中心なのですが
1. wks.get_all_values()で指定したスプレッドシートの値をリスト型で取得する
2. map関数を使ってBMIを計算して、リストで出力する
3. セルのポジション, C1にBMIという文字を入れ、C2〜には計算した　BMIを入力する。

というコードになります。
2のmap関数については、以前記事を書いたので参考にしていただけたら嬉しいです。