【python】Googleスプレッドシートを操作する（GCPのAPI利用）

!pip install gspread
!pip install google-auth
import gspread

#認証（スコープを定義し、認証キーを呼び込む）
from google.oauth2.service_account import Credentials

scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]

credentials = Credentials.from_service_account_file(
'secret.json',
scopes=scopes
)

gc = gspread.authorize(credentials)

gc

#スプレッドシートの取得（スプレッドシートのIDを登録）
SP_SHEET_KEY = "****スプレッドシートのID****"
#スプレッドシートのシートの取得
SP_SHEET = "**シート名**"
#スプレッドシートを開く
sh = gc.open_by_key(SP_SHEET_KEY)
#シートを指定
worksheet = sh.worksheet(SP_SHEET)
♯シートの全ての値を取得
data = worksheet.get_all_values()
data(SP_SHEET)

import pandas as pd
df = pd.DataFrame(data[2:],columns=data[1])
df

#縦方向（axis=1)に0列目を削除する ※横方向は、「axis=0」
df=df.drop(df.columns[[0]],axis=1) 
df

#データ型を調べる
df.dtypes 

#データ型を文字列から数値型に変換
df=df.astype({'年齢':int,'社員ID':int}) 
df.dtypes

#所属ごとの年齢の平均値を取得
pvt_table=df.pivot_table(index=['所属'],values=['年齢'],aggfunc='mean') 
#数値の小数点以下を丸める
pvt_table['年齢']=pvt_table['年齢'].round(0)
pvt_table

#新しいシートを追加する（シート名：new、100行100列）
new_worksheet=sh.add_worksheet(title='new',rows=100,cols=100)
#gspreadとDataFrameの連携をスムーズにするライブラリをインストール
!pip install gspread-dataframe
from gspread_dataframe import set_with_dataframe
#データの書き出しの位置を決める（左上のセルの位置）
first_row=2
first_col=2
#１列目がインデックスと認識されるので、これをリセットして、インデックスもデータ値としてスプレッドシートに書き込む）
set_with_dataframe(new_worksheet,pvt_table.reset_index(),row=first_row,col=first_col)

#セルの書式設定をするためのライブラリをインストール
!pip install gspread-formatting
from gspread_formatting import*

header_fmt = cellFormat(
　　　　backgroundColor = color(38/255,166/255,154/255),
　　　　textFormat = textFormat(bold=True,foregroundColor = color(255/255,255/255,255/255)),
　　　　horizontalAlignment = "CENTER"
)

#範囲をインスタンス化
header_range='b2:c2'
index_range='B3:b8'
value_range='c3:c8'

#書式を指定した範囲に適用する
format_cell_range(new_worksheet,header_range,header_fmt)

#枠線を引く
border=Border('SOLID',Color(0,0,0,0))
fmt=cellFormat(borders=Borders(top=border,bottom=border,left=border,right=border))
format_cell_range(new_worksheet,header_range,fmt)
format_cell_range(new_worksheet,index_range,fmt)
format_cell_range(new_worksheet,value_range,fmt)