前回の記事では、GoogleスプレッドシートをPythonで操作する基本的な方法を紹介しました。
今回は、さらに実用的なスクリプトを使って、Googleスプレッドシートを効果的に操作する方法を紹介します。
1. データの一括読み込みと加工
スプレッドシートからデータを一括で読み込み、Pythonで加工する方法を紹介します。
例えば、特定の列のデータを集計するケースです。
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
# 認証情報の設定
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/json/file.json', scope)
# クライアントの作成
client = gspread.authorize(creds)
# スプレッドシートの取得
spreadsheet = client.open('Your Spreadsheet Name')
# ワークシートの取得
worksheet = spreadsheet.sheet1
# 全データの取得
data = worksheet.get_all_records()
# データフレームに変換
df = pd.DataFrame(data)
# 特定の列を集計(例: 'Sales'列の合計)
total_sales = df['Sales'].sum()
print(f"総売上: {total_sales}")
2. スプレッドシートへのデータの自動更新
Pythonを使って、スプレッドシートのデータを定期的に更新する方法を紹介します。
例えば、外部のAPIから取得したデータをスプレッドシートに書き込むケースです。
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import requests
# 認証情報の設定
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/json/file.json', scope)
# クライアントの作成
client = gspread.authorize(creds)
# スプレッドシートの取得
spreadsheet = client.open('Your Spreadsheet Name')
# ワークシートの取得
worksheet = spreadsheet.sheet1
# 外部APIからデータを取得(例: ダミーAPIを使用)
response = requests.get('https://jsonplaceholder.typicode.com/todos')
todos = response.json()
# データを整形して書き込み
for todo in todos:
worksheet.append_row([todo['userId'], todo['id'], todo['title'], todo['completed']])
3. データのフィルタリングと抽出
特定の条件に基づいてスプレッドシートのデータをフィルタリングし、抽出する方法を紹介します。
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# 認証情報の設定
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/json/file.json', scope)
# クライアントの作成
client = gspread.authorize(creds)
# スプレッドシートの取得
spreadsheet = client.open('Your Spreadsheet Name')
# ワークシートの取得
worksheet = spreadsheet.sheet1
# 全データの取得
data = worksheet.get_all_records()
# 特定の条件でフィルタリング(例: 'Completed'列がTrueの行を抽出)
filtered_data = [row for row in data if row['Completed'] == True]
# 抽出結果の表示
for row in filtered_data:
print(row)
4. スプレッドシートのバックアップ
スプレッドシートのデータを定期的にバックアップするスクリプトを紹介します。
バックアップデータはCSVファイルとして保存します。
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
from datetime import datetime
# 認証情報の設定
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/json/file.json', scope)
# クライアントの作成
client = gspread.authorize(creds)
# スプレッドシートの取得
spreadsheet = client.open('Your Spreadsheet Name')
# ワークシートの取得
worksheet = spreadsheet.sheet1
# 全データの取得
data = worksheet.get_all_records()
# データフレームに変換
df = pd.DataFrame(data)
# バックアップファイル名の生成
backup_filename = f"backup_{datetime.now().strftime('%Y%m%d%H%M%S')}.csv"
# CSVファイルとして保存
df.to_csv(backup_filename, index=False)
print(f"バックアップファイルを保存しました: {backup_filename}")
5. Google Sheets APIの高度な設定
Google Sheets APIを使用して特定のセルのフォーマットを変更する方法を紹介します。
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build
# 認証情報の設定
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/json/file.json', scope)
# クライアントの作成
client = gspread.authorize(creds)
# スプレッドシートのIDを取得
spreadsheet = client.open('Your Spreadsheet Name')
spreadsheet_id = spreadsheet.id
# Google Sheets APIの設定
service = build('sheets', 'v4', credentials=creds)
# フォーマットのリクエストを作成
requests = [{
'repeatCell': {
'range': {
'sheetId': 0,
'startRowIndex': 1,
'endRowIndex': 2,
'startColumnIndex': 0,
'endColumnIndex': 1,
},
'cell': {
'userEnteredFormat': {
'backgroundColor': {
'red': 1.0,
'green': 0.0,
'blue': 0.0
},
'horizontalAlignment': 'CENTER',
'textFormat': {
'foregroundColor': {
'red': 1.0,
'green': 1.0,
'blue': 1.0
},
'fontSize': 12,
'bold': True
}
}
},
'fields': 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)'
}
}]
# フォーマットの適用
body = {'requests': requests}
response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
print(f"フォーマットを適用しました: {response}")
以上