【Python】gspreadでスプレッドシートの読み書きを自動化する

こんにちは、ミナピピン(@python_mllover)です。今回はPythonのgspreadというライブラリを使ってgoogleスプレッドシートを操作する(読み込み・書き出し・追記処理など)方法について紹介したいと思います。

Contents

前準備
gspreadでPythonからGoogleスプレッドシートを読み込み・書き出す

前準備

Pythonの設定

まずは「gspread」というライブラリをpipでインストールします。

# ライブラリのインストール
$ pip install gspread
$ pip install --upgrade oauth2client --ignore-installed six

GCPの設定

次にGCPプロジェクトを有効化します。まずGCPのダッシュボードにアクセスします

→ https://console.cloud.google.com/home/dashboard

アクセスしたら左のタブにある「APIとサービス」を選択し、画面中央上部付近にある「APIサービスの有効化」を選択し、「Google Drive API」を有効と「Google Sheets API」のAPIを有効化します。

https://console.cloud.google.com/apis/library/sheets.googleapis.com

https://console.cloud.google.com/apis/library/drive.googleapis.com

ここで上記ワードを入力するとAPIの検索ができます

→ https://console.cloud.google.com/apis/library

次にAPIを有効化したら認証情報から認証情報を作成「サービスアカウントキー」を選択します。

Google Cloud Platform

Google Cloud Platform lets you build, deploy, and scale applications, websites, and services on the same infrastructure as Google.

横の「サービスアカウントの管理」というボタンがあるのでクリックし、「サービスアカウントを作成」を選択し、新規にサービスアカウントを作成します。

サービスアカウントは名前を適当につけてあとは全部省略できるので、何も入力せずに続行で一番した完了を押せば作成完了です。

アカウントを作成したら上記のような状態になっていますので、右端の操作の下の「:」をクリックして「鍵の管理」を選択します。次に「鍵の追加」→「新しい鍵を作成」を選択して「JSON」を選択し作成します。

アカウントキーの種類は「JSON」を選択してください。

参照：https://qiita.com/akabei/items/0eac37cb852ad476c6b9

スプレッドシートの作成

まずはDrive上で適当なスプレッドシートを作成します。名前は適当で大丈夫です。スプレッドシートを作成したら、右上の「共有」のボタンを押してメールアドレスでユーザーを追加します。

ここの「ユーザーやグループを追加」の欄に先ほど生成したJSONファイルをメモ帳などのテキストエディタで開き、“client_email”: 部分に記載されているメールアドレス(“～～～～.gserviceaccount.com”)を追加します。

ここでユーザーを追加しておかないとAPIを有効化して後のPythonのコードを実行しても403エラーになるので注意してください、自分はこれで数時間ハマりました。。。。

gspreadでPythonからGoogleスプレッドシートを読み込み・書き出す

認証設定

import gspread
import json

#ServiceAccountCredentials：Googleの各サービスへアクセスできるservice変数を生成します。
from oauth2client.service_account import ServiceAccountCredentials 

#2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

#認証情報設定
#ダウンロードしたjsonファイル名をクレデンシャル変数に設定（秘密鍵、Pythonファイルから読み込みしやすい位置に置く）
credentials = ServiceAccountCredentials.from_json_keyfile_name('~~.json<秘密鍵のJSONファイル>', scope)

#OAuth2の資格情報を使用してGoogle APIにログイン
gc = gspread.authorize(credentials)

ノートブックを開く

# スプレッドシートの最初のシートを開く
wks = gc.open('<スプレッドシート名>').sheet1

シートは今回はファイル名で開いていますが、固有のシートIDでも指定することが可能です。

wks = gc.open_by_key('<スプレッドシートのID>').worksheet('<開きたいシート名>')

スプレッドシートのIDはスプレッドシートをブラウザから開いた際の上部のURLの以下に相当する部分になります。

「https://docs.google.com/spreadsheets/d/<スプレッドシートID>/edit#~~」

またそのままURLを指定して開くことも可能です

wks = gc.open_by_url('URL')

セルへの書き込み

まずは開いたシートに情報を書き込んでみましょう

試しに上記で開いたシートに「hello world」を入力してみます

# シートを開いてa1にhellow worldを書き込む
wks.update_acell('A1', 'Hello World!')
print(wks.acell('A1'))

# セルの内容を取得
a1 = wks.acell('A1').value
print(a1) # Hello World!

複数のセル(行ごと)への書き込み

append_row()で行を一気に挿入することができます。

行は上書きされず既にデータがある行の下に追記される仕様になっています。

# 複数のセルに値を入力（1行のみ）
datas = ["あ", "い", "う", "え", "お"]
wks.append_row(datas)

# 複数のセルに値を入力（複数行）
datas = [
          ["あ", "い", "う", "え", "お"],
          ["か", "き", "く", "け", "こ"],
        ]
 
for row_data in datas:
    wks.append_row(row_data)

行ごと列ごとのセル内容を読み込む

反対にセルの内容を取得する際はrow_values() col_values() get_all_values()を用います

# シートの2行目のデータをリストで取得する
row_list = wks.row_values(2)
print(row_list)

# シートの2列目をリストで取得
col_list = wks.col_values(2)
print(col_list)

# セルの全てのデータを取得する
data_list = wks.get_all_values()
print(data_list) # 戻り値は1行ずつ二次元配列に格納されている

参照：https://qiita.com/AAkira/items/22719cbbd41b26dbd0d1

参照：https://tanuhack.com/library-gspread/