こんにちは。aliceです。
9月になりましたね。まだまだ暑いのに9月という響きだけで秋っぽく感じます。

前回のExcelに続き、今回はGoogleスプレッドシートの操作です。
認証がうまくできずに終わるかと思いましたが、なんとかできました。

本ではEZSheetsをインストールしましょうとあったのですが、ググったりChatGPTに聞いても出てこなかったのでgspreadライブラリをインストールしました。

ということで、今まで以上に本を無視した内容となっております🙆‍♀️

とりあえず、読み込みと書き込みができればいいかなーと思ったので、それをやってみて、さくっと次に進もうと思います。

前回のらくがき帳📝

では、以下らくがきです。

14章　英語版🔤

GitHub💖

認証

認証関係はだいたい本に書いてあるとおりにやりました。
最後にスプレッドシートの共有で、作成したサービスアカウントのメールアドレスを追加しました。

スプレッドシートの値を読み込む

このようなスプレッドシートを作りました。

potatoというスプレッドシートのシート1においしそうなさつまいもの名前と種類が書かれています🍠

セルの値をリストのリストとして読み込む

セルの値を読み込んでみます。
client_secret.jsonは認証のときに作成したJsonです。
同一ディレクトリに保存しています。

GASのgetValuesメソッドのイメージで取得しました。

import pprint

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# 認証情報のロード
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'client_secret.json', scope
)

# Google Sheetsに接続
gc = gspread.authorize(credentials)

# スプレッドシートの名前を指定して開く
sheet_name = 'potato'
worksheet = gc.open(sheet_name).sheet1


# ワークシートのデータを取得
# データはリストのリストとして取得
list_of_lists = worksheet.get_all_values()
pprint.pprint(list_of_lists)

出力結果

[['名前', '特徴'],
 ['安納芋', '甘味が強く、焼くと水分の多いねっとりとした食感で、「蜜イモ」とも呼ばれている。'],
 ['紅はるか', '甘味が強く、水分が多めで、蒸し芋や干し芋にすると美味しい'],
 ['シルクスイート', '絹のような滑らかな食感と強い甘さを持つ']]

単体のセルの値の取得など。

セルの値を辞書のリストとして読み込む

こちらは辞書バージョン。
GASでいうとオブジェクトとして取得するパターンですね。

import pprint

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# 認証情報のロード
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'client_secret.json', scope
)

# Google Sheetsに接続
gc = gspread.authorize(credentials)

# スプレッドシートの名前を指定して開く
sheet_name = 'potato'
worksheet = gc.open(sheet_name).sheet1

# ワークシートのデータを辞書のリストとして取得
list_of_dict = worksheet.get_all_records()
pprint.pprint(list_of_dict)

出力結果

[{'名前': '安納芋', '特徴': '甘味が強く、焼くと水分の多いねっとりとした食感で、「蜜イモ」とも呼ばれている。'},
 {'名前': '紅はるか', '特徴': '甘味が強く、水分が多めで、蒸し芋や干し芋にすると美味しい'},
 {'名前': 'シルクスイート', '特徴': '絹のような滑らかな食感と強い甘さを持つ'}]

スプレッドシートに値を書き込む（追加する）

スプレッドシートに値を追加します。
こちらのスプレッドシートのA列の最終行に「紅あずま」、B列の最終行に「繊維が比較的少なく甘味が強い」を追加します。

GASのappendRowメソッドのイメージです。
append_rowsメソッドなのは覚えやすい😀

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# 認証情報のロード
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'client_secret.json', scope
)

# Google Sheetsに接続
gc = gspread.authorize(credentials)

# スプレッドシートの名前を指定して開く
sheet_name = 'potato'
worksheet = gc.open(sheet_name).sheet1

# 追加するデータをリストで準備
new_row = ['紅あずま', '繊維が比較的少なく甘味が強い']

# データを最終行に追加
worksheet.append_rows([new_row])

print('データを追加しました🍠')

紅あずまが追加されました。

スプレッドシートに値を書き込む（転記する）

スプレッドシートの値を書き込んでみます。
またまた、こちらのスプレッドシートを使います。

名前に「芋」と入っているさつまいもだけシート2に出力してみます。
（安納芋の列だけ出力します）

リストを使ってみる

GASでいうとsetValuesメソッドのイメージです。
headerとデータを分割代入っぽくしてみました。

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint

# 認証情報のロード
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'client_secret.json', scope
)

# Google Sheetsに接続
gc = gspread.authorize(credentials)

# スプレッドシートの名前を指定して開く
sheet_name = 'potato'
worksheet = gc.open(sheet_name).sheet1

# ワークシートのデータを取得
list_of_lists = worksheet.get_all_values()

# ワークシートのヘッダーとデータを取得
header, *data = list_of_lists

# '芋' を含む行を抽出
imo_data = [row for row in data if '芋' in row[0]]

# ヘッダーと '芋' を含むデータを結合
imo_list = [header] + imo_data
pprint.pprint(imo_list)

# sheet2に書き込み
worksheet2 = gc.open(sheet_name).worksheet('シート2')
worksheet2.clear()
worksheet2.update('A1', imo_list)
print('書き込み完了🍠')

シート2はこちら。

pandasを使ってみる

せっかくなのでpandasでも。

import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials

# 認証情報のロード
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'client_secret.json', scope
)

# Google Sheetsに接続
gc = gspread.authorize(credentials)

# スプレッドシートの名前を指定して開く
sheet_name = 'potato'
worksheet = gc.open(sheet_name).sheet1

# ワークシートのデータを取得
list_of_lists = worksheet.get_all_values()

# ワークシートのデータをPandas DataFrameに変換
df = pd.DataFrame(list_of_lists[1:], columns=list_of_lists[0])

# '芋' を含む行を抽出
imo_df = df[df['名前'].str.contains('芋')]

# ヘッダー行を含めてデータを新しいワークシートに書き込む
worksheet2 = gc.open(sheet_name).worksheet('シート2')
worksheet2.clear()  # 既存のデータをクリア

# ヘッダー行とデータを結合してリスト化し、新しいワークシートに書き込む
header_and_data = [imo_df.columns.tolist()] + imo_df.values.tolist()
worksheet2.insert_rows(header_and_data, 1)
print('書き込み完了🍠')

最終的にリストにして書き込んでいるのか。

なるほどー。

職場ではGoogle Photosを使っているので、ダウンロードするコードを書いてみたいなぁ。

おまけ🍠

さつまいもの種類はWikipediaを参考にしました。
いろんな種類があるんですね。
これからさつまいものシーズンになるので、今年はたくさんの種類のさつまいもをいただきたいと思います😋

つづき。