「Googleスプレッドシート」タグアーカイブ

gspreadライブラリを使ってPythonでGoogleスプレッドシートの操作（値追加・値取得・行追加・行削除）する

コメントを残す

概要〜Pythonでgspreadライブラリを公式ページにならってやってみてAWS LambdaでGoogleスプレッドシートに定期的に記録する〜

皆さん日々記録を取りたい時どこに記録をしていますか？
私はGoogleスプレッドシートを多用しています。
なぜなら、、、

  • Googleアカウントをもっていれば誰でも無料で使える
  • Webブラウザがあれば携帯、PC問わずアクセスできる
  • ちょっと修正したいなと思った時の修正がExcelと同じ操作感なので容易

などの理由からです。
日々自動化して記録をしたいことがあるのですが、その都度DBを用意して記録するのはそれなりに手間がかかるので私はGoogleスプレッドシートを使って日々の自動化処理を実行しています。
今回はその時に役に立つ、PythonからGoogleスプレッドシートに簡単にアクセスできるgspreadライブラリを調べたのでその整理内容になります。

出来るようになること

  • Googleスプレッドシートを開く
  • Googleスプレッドシートのシートを指定する
  • Googleスプレッドシートの値を取得する
  • Googleスプレッドシートに値を書き込む
  • Googleスプレッドシートに行を追加する／行を削除する

最終的なコードは以下になります。
これだけのコードでGoogleスプレッドシートに対する最低限の操作が出来るのでかなり簡単だったと思います。

import gspread
import json

#認証処理
gc  = gspread.service_account_from_dict('認証情報のJSON')

# ファイル名指定で開く
sh = gc.open('ファイル名')

# シート名指定
worksheet = sh.worksheet("シート名")

# A1等のラベル指定して値取得
val = worksheet.acell('B1').value

# ラベル指定して値を更新
worksheet.update('B1', 'Bingo!')

# 最終行に引数のリストの内容を1行追加
worksheet.append_row(['A','B','C'])

公式ページ

gspreadの公式ページは以下になります。

https://docs.gspread.org/en/latest/

前提条件

前提条件としてGoogleスプレッドシートにアクセスするための認証情報（JSONファイル）をGCPで取得する必要があります。（GCPを利用するのでGCPアカウントが必要になります）
今回はgspreadライブラリの説明をメインとした記事なのでこの部分は割愛しますが、以下のようなコードで取得した認証情報を使って認証処理を実装します。
gspreadライブラリを使えばたった3行です。簡単ですね。

import gspread
import json

#認証処理
gc  = gspread.service_account_from_dict('認証情報のJSON')

認証処理の更新ページURLは以下になります。
実装方法は上のサンプル以外にもあるので必要なら別の方法を確認してみてください。

https://docs.gspread.org/en/latest/oauth2.html

ワークブックを開く

それでは実際のGoogleスプレッドシートの操作の説明に移ります。
まず最初はGoogleスプレッドシートの操作するスプレッドシートを指定して開きます。
指定方法にはファイル名を指定、キーを指定、URLの指定の３つの方法があるので、扱い易い方法で開けばいいと思います。個人的にはファイル名指定が一番直感的に扱えるかなと思っています。

# ファイル名指定で開く
sh = gc.open('ファイル名')

# スプレッドシートのキー指定で開く
sh = gc.open_by_key('スプレッドシートキー')

# URL指定で開く
sh = gc.open_by_url('URL')

# スプレッドシートを取得したらスプレッドシートの内容を表示してみる
print(sh.title) #スプレッドシートのタイトルを表示する
print(sh.id) # スプレッドシートキーを表示する

ファイルを開く部分の公式ページは以下になります。

https://docs.gspread.org/en/latest/user-guide.html#opening-a-spreadsheet

シート選択

スプレッドシートが開けたら次はシートを選択します。
この辺りの操作間はExcel操作と一緒ですね。
こちらも複数の方法がありますが、基本的にはインデックスを指定するか、シート名を指定する方法になります。個人的にはこちらもシート名を指定する方法が直感的かなと思います。

# シートのインデックスを指定（先頭は0になります）
worksheet = sh.get_worksheet(0)

# シート名指定
worksheet = sh.worksheet("シート名")

# 先頭のシート指定（先頭のシートだけです。sheet2にしたところで2番目のシートは指定できません）
worksheet = sh.sheet1

# すべてのシートをリストで取得する
worksheet_list = sh.worksheets()

# シートを取得したらシートの内容を表示してみる
print(worksheet.title)  # シート名表示
print(worksheet.id) # シートID表示

公式ページは以下になります。

https://docs.gspread.org/en/latest/user-guide.html#selecting-a-worksheet

値取得

シートまで取得できたので、それではここから値を取得していきます。
A1等のラベル表記と1,2といった行と列番号を指定して取得する方法があります。
こちらはどちらの取得方法でも良いと思うので好みですかね。。。

# A1等のラベル指定して値取得
val = worksheet.acell('B1').value

# 行と列番号を指定して値取得（左：行番号、右：列番号）
val = worksheet.cell(1, 2).value

# 行番号の指定して1行の値をリストとして取得
values_list = worksheet.row_values(1)

# 列番号の指定して1列の値をリストとして取得
values_list = worksheet.col_values(1)

# 値を取得したら値の内容を表示してみる
print(val) # 値表示
print(values_list) # 取得したリストの値を表示

公式ページは以下になります。

https://docs.gspread.org/en/latest/user-guide.html#getting-a-cell-value

他にもシート全て値を取得する方法や行ごとに取得する方法が公式ページには記載されているので参照してみてください。

https://docs.gspread.org/en/latest/user-guide.html#getting-all-values-from-a-worksheet-as-a-list-of-lists

https://docs.gspread.org/en/latest/user-guide.html#getting-all-values-from-a-worksheet-as-a-list-of-dictionaries

値を更新

続いて値を更新していきます。
方法は値取得と同一になり、A1等のラベル表記と1,2といった行と列番号を指定して取得する方法があります。

# ラベル指定して値を更新
worksheet.update('B1', 'Bingo!')

# 行と列番号を指定して値更新
worksheet.update_cell(1, 2, 'Bingo!')

# 範囲を指定して値をまとめて更新
worksheet.update('A1:B2', [[1, 2], [3, 4]])

# 更新したら値の内容を表示してみる
print(worksheet.acell('B2').value) # 値表示

公式ページは以下になります。

https://docs.gspread.org/en/latest/user-guide.html#updating-cells

行操作

最後に行ごとに操作する方法の紹介になります。
実際スプレッドシートの更新は行ごとに行うことが多いと思うので、その時の操作方法になります。
方法としては、最終行に追加、指定行に追加、指定行の削除になります。
行を指定しての更新方法が見つからず私は指定行を削除後、指定行を追加することで行の更新を行っています。

# 最終行に引数のリストの内容を1行追加
worksheet.append_row(['A','B','C'])

# 指定行に引数のリストの内容を1行追加
worksheet.insert_row(['A','B','C'], 1)

# 指定行を削除
worksheet.delete_rows(2)

# 行を更新したら値の内容を表示してみる
values_list = worksheet.row_values(1)
print(values_list)

公式ページは以下になります。

https://docs.gspread.org/en/latest/api/models/worksheet.html#gspread.worksheet.Worksheet.append_row

https://docs.gspread.org/en/latest/api/models/worksheet.html#gspread.worksheet.Worksheet.insert_row

https://docs.gspread.org/en/latest/api/models/worksheet.html#gspread.worksheet.Worksheet.delete_row

まとめ

いかがだったでしょうか。
私はgspreadライブラリを使うことでかなり簡単にGoogleスプレッドシートの操作ができたと感じています。
私はこれをAWS Lambdaに実装し日々の記録を自動化しています。
機会があればLambdaへの実装方法を記事にしようと思いますが、ソースコードだけ参考に載せておきます。
認証情報をパラメータストアから取得するところがAWS Lambda特有の部分ですが、他は同じようにGoogleスプレッドシートの操作が可能です。

import gspread
import json
import boto3
import os

# パラメータストアに保存している認証情報のキー名を環境変数から取得
# PARAM_KEY = os.environ.get('●●●パラメータストアを取得する環境変数名を指定●●●')
# 私は環境変数として「PARAM_KEY」として設定
PARAM_KEY = os.environ.get('PARAM_KEY')

def lambda_handler(event, context):

    # パラメータストアに保存している認証情報（JSONファイルを取得）
    ssm = boto3.client('ssm')
    response = ssm.get_parameters(
        Names=[
            PARAM_KEY,
        ],
        WithDecryption=True
    )
    service_account_info = json.loads(response['Parameters'][0]['Value'])

    #認証処理
    gc  = gspread.service_account_from_dict(service_account_info)

    # ファイル名指定で開く
    sh = gc.open('●●●Googleスプレッドシートファイル名を指定●●●')

    # シート名指定
    worksheet = sh.worksheet("●●●操作するシート名を指定●●●")

    # A1等のラベル指定して値取得
    val = worksheet.acell('B1').value

    # ラベル指定して値を更新
    worksheet.update('B1', 'Bingo!')

    # 最終行に引数のリストの内容を1行追加
    worksheet.append_row(['A','B','C'])

Lambdaの環境変数に以下を設定

SystemsManagerのパラメータストアに以下を設定

AWS Lambda関連の記事は以下などを投稿しているので興味がある方はぜひ読んでみてください(^^)

１．AWS Lambda 入門～おすすめ本5冊の紹介～

２．AWS Lambda で Python プログラミング～Boto3 サンプル集S3編～

３．AWS Lambda SAMとは？～Cloud9でSAM Sampleを使ってPythonのLambdaプログラムを簡単にデプロイする～

これで日々の単調な作業から脱却します！