Google API 2/4: Python 存取 Google Sheets？讀寫 pandas df

如果您使用 Python 編程，其中一個要考慮的地方就是如何儲存和存取數據。

其中，特別是新手，我們在 Python 編程的時候很希望我們能夠具現化我們的數據。那麼 Google Sheets 便是我們其中一個選擇。

您可能感興趣：

• 如何安裝 Google Cloud SDK 命令行工具？macOS 就是那麼簡單
• Google API 1/4：使用 Google API Python Client 存取谷歌服務

為何使用 Google Sheets 儲存數據？

Google Sheets 讓我們可以保留（persist）一些表格類型的數據（tabular data）。這樣，我們在 Python 便可以融合 pandas dataframe 等數據工具使用。

Google Sheets 同時亦可以把數據儲存在雲端（cloud）上，不用擔心數據在自己電腦的話會意外丟失。

Google Sheets 亦讓我們可以在網頁版裡任意更改數據，讓我們可以測試不同的情況。

最後，當然是因為 Google Sheets 是免費的咯！

為何不使用 Google Sheets 儲存數據？

當然，使用 Google Sheets 儲存和存取數據亦不是沒有問題。

首先，Google Sheets 是一個對數據格式嚴格（structured data）的儲存方法。我們許多時候只能使用 Google Sheets 儲存 2D 的數據，對於 3D 或其他的散亂的數據（unstructured data）不是最好。

另外，Google Sheets 亦有比較少的儲存空間，對於一些比較大型的數據分析專案就不是太適合。Google Sheets 存取數據的速度亦未能滿足對某一些要求效率/速度的專案。

那麼您的專案適合使用 Google Sheets 嗎？一個好的 Python 編程師會考慮自己專案的規模再做決定。如果您不清楚的話，不妨先試試 Google Sheets 再決定吧！

第 1 步：建立新的 Google Sheets

我們先到 Google Sheets 的主頁面，按一下右下角的加號新增新的 Google Sheets 試算表。

完成後，我們為新的試算表命名（按一下圖中紅框的位置，並輸入新的名稱），然後像 Excel 試算表一樣輸入一些數據。

第 2 步：啟用 Google Sheets API

我們有了所需的 Google Sheets 後，便需要為我們的 Google 帳戶啟用 Google Sheets API。

首先我們到 Google Cloud Console 的 Google Sheet API 的頁面，按一下同意使用條款並繼續。

看到 Google Sheets API 後，我們按一下啟用：

等了一會後，頁面應該自動跳轉到「已啟用的 API 和服務」。我們會見到 Google Sheets API 已經啟用：

第 3 步：設立 OAuth、設立專案

接下來，我們需要授權自己透過 Google Sheets API 存取 Google Sheets 的試算表資源。

請參考這裡的教學第 1 至 6 步：Google API 1/4：使用 Google API Python Client 存取谷歌服務

接下來我們假設您已完成教學，成功建立了 main.py 裡的 GoogleAPIClient，並把自己的 Google 授權儲存為 cred.json。

第 4 步：建立 Google Sheets API

首先我們在專案建立 googleSheet.py 並輸入以下 Python 代碼：

from main import GoogleAPIClient

class GoogleSheets(GoogleAPIClient):
    def __init__(self) -> None:
        # 呼叫 GoogleAPIClient.__init__()，並提供 serviceName, version, scope
        super().__init__(
            'sheets',
            'v4',
            ['https://www.googleapis.com/auth/spreadsheets'],
        )

if __name__ == '__main__':
    myWorksheet = GoogleSheets()
    print(myWorksheet.googleAPIService)   

在這裡我們使用 Google API 1/4: 使用 Google API Python Client 存取 Google 服務 main.py 的 GoogleAPIClient，並建立新的物件 GoogleSheets 作為它的承傳（inherited class）。

因此，在 GoogleSheets 的起始功能，我們使用 super().__init__() 呼叫 GoogleAPIClient 的起始功能。

如果我們我們在 Terminal 運行 python googleSheets.py，我們可以見到輸出了 <googleapiclient.discovery.Resource object at 0xXXXXXXXXX>。這代表我們可以透過在 GoogleSheets 物件裡存取 self.googleAPIService 的 Google API 服務了！

第 5 步：從 Google Sheets 獲取 Worksheet 數據

參考連結：Reading & Writing Cell Values

我們第一個任務就是從 Google Sheets 裡獲取數據，以供 Python 讀取。

from main import GoogleAPIClient
from pprint import pprint

class GoogleSheets(GoogleAPIClient):
    def __init__(self) -> None:
        # 呼叫 GoogleAPIClient.__init__()，並提供 serviceName, version, scope
        super().__init__(
            'sheets',
            'v4',
            ['https://www.googleapis.com/auth/spreadsheets'],
        )

    def getWorksheet(self, spreadsheetId: str, range: str):
        request = self.googleAPIService.spreadsheets().values().get(
            spreadsheetId=spreadsheetId,
            range=range,
        )
        response = request.execute()

        return response

if __name__ == '__main__':
    myWorksheet = GoogleSheets()
    pprint(myWorksheet.getWorksheet(
        spreadsheetId='<輸入您的 Google Sheets ID>',
        range='工作表1'
    ))

留意上圖有一個「spreadsheetId」的橘色框框。這是因為我們讓 Google 知道我們到底想要那個 Google Sheets 的數據。因此，我們的 getWorksheet 功能使用了 2 個函數：

1. spreadsheetId：Google Sheet 的識別碼，在網址「docs.google.com/spreadsheets/d/<spreadsheetId>/edit」裡可以找到
2. range: 類似 Excel 裡面的「範圍」。我們想要提取整張 Google Sheet 的工作表，可以直接指明工作表的名稱

下圖展示我們可以從哪裡找到這些資訊：

除此之外，留意我們的輸出是一個字典（dictionary），有「majorDimension」、「range」和「values」這 3 個元素。我們最感興趣的是「values」，因為可以看得出「values」是一個列表的列表（list of list），每一個子項都是 Google Sheets 裡其中一行。

小延伸：匯入 Google Sheets 至 pandas dataframe

許多時候，我們的 Google Sheets 是一個工整的 2D 數據表，可以直接讓 pandas 使用。那麼我們可以如何稍微更改以上的編程，使 Python 輸出 pandas dataframe？

from main import GoogleAPIClient
import pandas as pd

class GoogleSheets(GoogleAPIClient):
    def __init__(self) -> None:
        # 呼叫 GoogleAPIClient.__init__()，並提供 serviceName, version, scope
        super().__init__(
            'sheets',
            'v4',
            ['https://www.googleapis.com/auth/spreadsheets'],
        )

    def getWorksheet(self, spreadsheetId: str, range: str):
        request = self.googleAPIService.spreadsheets().values().get(
            spreadsheetId=spreadsheetId,
            range=range,
        )
        result = request.execute()['values']
        header = result[0]
        del result[0]
        return pd.DataFrame(result, columns=header)

if __name__ == '__main__':
    myWorksheet = GoogleSheets()
    print(myWorksheet.getWorksheet(
        spreadsheetId='<輸入您的 Google Sheets ID>',
        range='工作表1'
    ))

其實我們把 getWorksheet 的輸出稍微更改即可。我們首先從「values」裡抽取第一行作為我們的標頭（column header），然後把剩餘的行作為 pd.DataFrame() 的數據輸入。

完成後，我們便可以直接使用 getWorksheet 的輸出作為 pandas Dataframe 使用了！

第 6 步：把 pandas dataframe 匯出 Google Sheets

參考連結：Reading & Writing Cell Values、spreadsheets.values.clear

接下來，我們來看看如何把數據匯出至 Google Sheets。我們繼續以 pandas dataframe 作例子介紹。

from main import GoogleAPIClient
import pandas as pd

class GoogleSheets(GoogleAPIClient):
    def __init__(self) -> None:
        # 呼叫 GoogleAPIClient.__init__()，並提供 serviceName, version, scope
        super().__init__(
            'sheets',
            'v4',
            ['https://www.googleapis.com/auth/spreadsheets'],
        )

    def clearWorksheet(self, spreadsheetId: str, range: str):
        self.googleAPIService.spreadsheets().values().clear(
            spreadsheetId=spreadsheetId,
            range=range,
        ).execute()
        return 0

    def setWorksheet(self, spreadsheetId: str, range: str, df: pd.DataFrame):
        self.clearWorksheet(spreadsheetId, range)
        self.googleAPIService.spreadsheets().values().update(
            spreadsheetId=spreadsheetId,
            range=range,
            valueInputOption='USER_ENTERED',
            body={
                'majorDimension': 'ROWS',
                'values': df.T.reset_index().T.values.tolist()
            },
        ).execute()
        return 0

if __name__ == '__main__':
    myWorksheet = GoogleSheets()
    print(myWorksheet.setWorksheet(
        spreadsheetId='<輸入您的 Google Sheets ID>',
        range='工作表1',
        df=pd.DataFrame(
            {'姓名': ['Janice','Wade','Henry','Wendy'],
            '性別': ['F','M','M','F'],
            '體重': [54,67,82,68]}
        )
    ))

我們加入 2 個新的功能。第一個功能是 clearWorksheet，而第二個功能是 setWorksheet。

把 pandas dataframe 的數據匯出至 Google Sheets 前，我們需要首先把試算表裡原有的數據刪除，以免我們混淆了舊的與新的數據。

因此，我們在 setWorksheet 的第一行便呼叫 clearWorksheet，把原有的數據刪去。

接下來，我們使用 update 的語法把我們新的數據加入至 Google Sheets。這裡有 2 個重要的設定：

• valueInputOption：我們選擇了「USER_ENTERED」，因為這樣我們可以輸出 Google Sheets 公式（formula）。例如我們的數據裡有「=1+1」，Google Sheets 便會成立一條新的 Google Sheets 公式（formula），而不是寫出 2 的數值（value）
• values：還記得我們在第 5 步使用 getWorksheet 時，Google Sheets 原始的數據是列表的列表（list of list）嗎？同樣地，我們也需要把 pandas dataframe 變回列表的列表（list of list）去輸入。以上的語法可以把標頭和數據輸出成所需的格式供 Google Sheets 使用

完成後，我們可以見到 Google Sheets 上的資料已被更新，而且 Google 還會提醒我們「上次編輯是在數秒前」，即我們成功透過 Google API 更改 Google Sheets 的內容。

第 7 步：在 Google Sheets 的末端加上一行

參考連結：Reading & Writing Cell Values

最後，我們一起來看看如何在 Google Sheets 的末端加上一行。

from main import GoogleAPIClient
import pandas as pd

class GoogleSheets(GoogleAPIClient):
    def __init__(self) -> None:
        # 呼叫 GoogleAPIClient.__init__()，並提供 serviceName, version, scope
        super().__init__(
            'sheets',
            'v4',
            ['https://www.googleapis.com/auth/spreadsheets'],
        )

    def appendWorksheet(self, spreadsheetId: str, range: str, df: pd.DataFrame):
        self.googleAPIService.spreadsheets().values().append(
            spreadsheetId=spreadsheetId,
            range=range,
            valueInputOption='USER_ENTERED',
            body={
                'majorDimension': 'ROWS',
                'values': df.values.tolist()
            },
        ).execute()
        return 0

if __name__ == '__main__':
    myWorksheet = GoogleSheets()
    print(myWorksheet.appendWorksheet(
        spreadsheetId='<輸入您的 Google Sheets ID>',
        range='工作表1',
        df=pd.DataFrame(
            {'姓名': ['Brian'],
            '性別': ['M'],
            '體重': [65]}
        )
    ))

這次我們定義了 appendWorksheet 這個功能。這個功能與我們剛才的 setWorksheet 功能非常相似，除了我們把 values 轉成 df.values.tolist() 以外。

由於我們在試算表的最後一行加入新的數據，所以我們不需要 pandas dataframe 的標頭（column header），只需要把數據輸出成一個列表的列表（list of list）。

完成後，可見 Google Sheets 的數據多了一行：

教學完整代碼

最後送給大家這篇教學的 Python 完整代碼：

結語

希望您透過這篇教學了解到基本的 Google Sheets API 操作以及如何融合 pandas dataframe 使用吧！

• 如何安裝 Google Cloud SDK 命令行工具？macOS 就是那麼簡單
• Google API 1/4：使用 Google API Python Client 存取谷歌服務