gspread

date: 2021-06-01 excerpt: gspreadの使い方

gspreadとは

概要

pythonでspreadsheetを操作できるようにするモジュール
local notebookとconsoleで使う場合とcolabで使う場合で大きく違う
- local notebook
  - pydata_google_authでローカルブラウザで認証しテンポラリなクレデンシャルを発行
- colab
  - colabのデフォルトの認証機能で認証を通す(同じアカウントのsheetsへのアクセスに限定される)
- console
  - GCPのプロジェクトからサービスアカウントを作る(サービスアカウント経由でのアクセスにする)

local notebookで使用する場合

import pydata_google_auth
SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets',
]
credentials = pydata_google_auth.get_user_credentials(
    SCOPES,
    auth_local_webserver=True,
    credentials_cache=pydata_google_auth.cache.NOOP, # 有効化すると、明示的にキャッシュを使用しない
)

def save_df(df, worksheet_name = ""):
  url = "https://docs.google.com/spreadsheets/d/*********"
  client = gspread.authorize(credentials)
  sheet = client.open_by_url(url)
  worksheet = sheet.worksheet(worksheet_name)
  worksheet.update([df.columns.values.tolist()] + df.values.tolist())

consoleで使う場合

GCPのAPIサービスから有効化する

https://console.cloud.google.com/apis/dashboardからspreadsheetを探して有効化する

GCPのプロジェクトからサービスアカウントを作成する

GCPの登録が個人でも必要であり、登録の後、[APIとサービス] -> [認証情報] -> [認証情報を作成] -> [サービスアカウント] -> (わかりやすい名前を入力) して作成する。
作成後、サービスアカウント名をクリックして詳細画面を表示して、[鍵を追加] から鍵を追加する
その情報を service_account.json 等の名前で ~/.config/gspread/service_account.json とパスを編集して保存する
あるアカウント（以下の例では gspread@starry-lattice-256603.iam.gserviceaccount.com )で保存できる。
google spreadsheetsを開いて共同編集者 gspread@starry-lattice-256603.iam.gserviceaccount.com を追加すると編集できるようになる

import pandas as pd
import numpy as np
import gspread
from pathlib import Path
from typing import List

class PandasGss:
    def __init__(self, link, service_account_filename):
        self.link = link
        gc = gspread.service_account(filename=Path(service_account_filename).expanduser())
        self.sh = gc.open_by_url(link)
    
    def get_meta(self) -> List:
        """開いているspreadsheetのすべてのシート名とそのIDを得る
        """
        worksheets = self.sh.worksheets()
        rets = []
        for worksheet in worksheets:
            title, id = worksheet.title, worksheet.id
            rets.append(dict(title=title, id=id))
        return rets

    def add_worksheet(self, name):
        """新規にワークシートを作成する
        """
        self.sh.add_worksheet(title=name)

    def get_df(self, name: str) -> pd.DataFrame:
        """シート名からデータフレームを得る

        Args: 
            name (str): 開きたいシート名
        Returns:
            df (pd.DataFrame): パースしたデータフレーム
        """
        arrs: List[List] = self.sh.worksheet(name).get_all_values()
        arrs = np.array(arrs)
        if len(arrs) == 0:
            raise Exception("パースできるデータがありません")
        header = arrs[0,:]
        values = arrs[1:, :]
        df = pd.DataFrame(values)
        df.columns = header
        return df

    def nullfy(self, name: str):
        """指定のシートの内容をすべて消す

        Args: 
            name (str): 内容をクリアしたいシート名
        """
        arrs: List[List] = self.sh.worksheet(name).get_all_values()
        for i in range(len(arrs)):
            for j in range(len(arrs[i])):
                arrs[i][j] = ''
        self.sh.worksheet(name).update(arrs)

    def save_df(self, name: str, df: pd.DataFrame):
        self.sh.worksheet(name).update([df.columns.values.tolist()] + df.values.tolist())

pg = PandasGss(link="https://docs.google.com/spreadsheets/d/1C1rKNIaSjloUkFe7QYvhrR6YdqJGNbSDcvGJ5LDy3Vs", 
        service_account_filename="~/.config/gspread/service_account.json")
print(pg.get_df(name="シート1++"))
print(pg.get_meta())

df = pd.DataFrame({"b": ['1', '3'] })
pg.nullfy(name="シート2++")
pg.save_df(name="シート2++", df=df)
print(pg.get_df(name="シート2++"))

colabで使う場合

pip install --upgrade gspread moduleが無い際は適宜インストールする

データの読み込み

import pandas as pd
import numpy as np
from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials
gc = gspread.authorize(GoogleCredentials.get_application_default())

wb = gc.open_by_url('https://docs.google.com/spreadsheets/d/1Y4Gi7QBAiQESKL_qtV71u8ToiGqu8IDwhlT--gKeDuo/edit#gid=1578443245')
a = pd.DataFrame(wb.worksheet('{SHEET_NAME}').get_all_values())
a.columns = a.iloc[0].tolist()
a = a[1:]

データの書き込み

wb.values_update("{SHEET_NAME}", 
                params={'valueInputOption': 'USER_ENTERED'}, 
                body={"values":  np.vstack([df.columns, df.values]).tolist()})