前回の記事では、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}")

以上