Gspread の使い方 (シートのCreate)

プログラム

create_spread.py

#! /usr/bin/python
#
#	create_spread.py
#
#					Nov/26/2024
import sys
import os

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread/lib/python3.12/site-packages')

sys.path.append(package_path)

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread-dataframe/lib/python3.12/site-packages')

sys.path.append(package_path)

import pandas as pd
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

# サービス アカウントの認証情報ファイルのパスを指定
credentials_file = './service_account.json'

credentials = Credentials.from_service_account_file(credentials_file)

# Google Sheets API クライアントの作成
service = build('sheets', 'v4', credentials=credentials)

spreadsheet_id = '14MdjJ3Hvna9ynheAphHozcW_k1JNQjjtkOtvuCrn9eM'
sheet_name = 'Sheet1'

data = {
    'ID': ['t1301', 't1302', 't1304'],
    'Name': ['宇都宮', '小山', '栃木'],
    'Population': [25836, 31945, 21837],
    'Mod_date': ['2020-3-6', '2020-5-12', '2020-8-11']
}
df = pd.DataFrame(data)

service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id,
    range=sheet_name + '!A1',
    valueInputOption='RAW',
    body={'values': df.values.tolist()}
).execute()

service_account.json

{
  "type": "service_account",
  "project_id": "project-***",
  "private_key_id": "eb863487cf9a69df04cc2706f3b94a47513e9cd7",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANB",
  "client_email": "sss-aaa@project-***.iam.gserviceaccount.com",
  "client_id": "11643590661909****",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/sss-aaa%40project-***.iam.gserviceaccount.com",
  "universe_domain": "googleapis.com"
}

スプレッドシートに共有の設定をする

"client_email": で定義されている次のメールアドレスを加える
"sss-aaa@project-***.iam.gserviceaccount.com",

実行

./create_spread.py