Lecture 10 - Firestore 與 Google Sheets 連結

tags: `GCP` `Firestore` `Python` `Google Sheets`

Lecture 10 - Firestore 與 Google Sheets 連結

一、前言

在前面Cloud Function的延伸應用提到Google Sheets是個很方便的工具
此章專注於教學應該如何使用 Firestore 來與 Google Sheets 做連結

二、事前準備

（一）取得權限（若在使用firestore時已經有Key，則可跳過此步驟）

建立Google API
https://console.developers.google.com/

（二）套件下載

pip install pygsheets：在python上上傳資料到google試算表
pip install pandas：方便進行資料處理

三、Python 讀寫 Google Sheets

在取得 Google Sheets 的編輯權限與下載套件後，我們就能正式學習如何以 Python 讀寫 Google Sheets 了

小提醒

記得前往此處或打開憑證的json檔，複製email並且於google sheets共用該email。

（一）使用憑證開啟 Google Sheets

取得憑證後只需要用 Google Sheets 的 ID 就可以連接，不過用 url 可以直接點過去看一下也是滿方便的。









import pygsheets
# 匯入憑證碼的json檔
gc = pygsheets.authorize(service_account_file='path/to/key.json')

# 輸入要更改的Googles Sheets網址（也可直接用 Google Sheets 的 ID ）
gs_url = 'https://docs.google.com/spreadsheets/d/1sefxKcrFGhfJztdeBOwx9l58HR1kAruzEO3JN5zMKKk/'

# 開啟該Google sheets
sh = gc.open_by_url(gs_url)

（二）指定資料表

承接前段，sh 變數如果執行會得到 <Spreadsheet 'firebase_test' Sheets:1> ，也就是「專案名稱」與「共有幾個資料表」，所以還需要指定資料表的名稱，才能讀取資料。


# 指定工作表
ws = sh.worksheet_by_title('工作表1')

（三）讀取資料與轉型為 Dataframe





# 讀取單一儲存格值
val = ws.get_value('A1')

# 以dataframe形式讀取資料
user_df = ws.get_as_df(start='A2', index_colum=0, empty_value='', include_tailing_empty=False) # index 從 0 開始算

小提醒：這個套件在 get_as_df() 預設會自動轉換可能是數字的資料，所以即使資料內容是文字的 ‘0001’ 也是會被轉成數字 1 ，只要多加入參數 numerize=False 就能避免這個問題。

如果想避免用 get_as_df() 設定上的麻煩，最快的方法就是先輸出 csv 再讀進來。（下方四、進階課程會教學）

（四）新增/更改資料

學會了（一）、（二）之後就可以直接把資料轉為dataframe再用pandas做資料的處理，不過處理後應該如何寫入資料呢？可分為兩種方式：

update_value('儲存格位置', '更改值')：更改單一值，例如更改A1儲存格為test
```
ws.update_value('A1', 'test')
```
set_dataframe(插入的df, '起始位子', copy_index=True, nan='')：直接插入dataframe
```
df1 = pd.DataFrame({'a': [1, 2], 'b': [3, 4]})
ws.set_dataframe(df1, 'A2', copy_index=True, nan='')
```
小提醒：
1. copy_index=True 將dataframe的index也填入Google Sheets裡
2. nan="" 將資料空值以空字串填入

我們也可以直接將前三部份統整為自定義函數，直接匯出dataframe格式的資訊








# TODO 獲取google sheet上的user資料，並轉為user_df
def get_gs_to_df():
    gc = pygsheets.authorize(service_account_file='path/to/key.json')
    survey_url = 'https://docs.google.com/spreadsheets/d/1sefxKcrFGhfJztdeBOwx9l58HR1kAruzEO3JN5zMKKk/'
    sh = gc.open_by_url(survey_url)
    ws = sh.worksheet_by_title('工作表1')  # 指定工作表
    user_df = ws.get_as_df(start='A2', index_colum=0, empty_value='', include_tailing_empty=False)
    return user_df

四、進階課程

（一）以 Cloud Function 連接 Google Sheets

從第三部份我們可以知道「用Python連接Google Sheets」其實是一件很簡單的事情，不過其實認證問題卻很麻煩，上面的教學我們是以認證json檔的路徑來獲得金鑰內容，那如果我們使用 Cloud Function 這種雲端伺服器架構我們要如何使用「以Python連接Google Sheets」這個功能呢？

主要就是先將json檔用記事本打開，再使用ctrl+A將所有內容複製自
temp.write(json.dumps("json中的key").encode('utf8')) 中即可









import json, tempfile

# 認證
def _google_creds_as_file():
    temp = tempfile.NamedTemporaryFile()
    temp.write(
        json.dumps({Your_json_file}).encode('utf8'))
    temp.flush()
    return temp

小提醒：

json.dumps 是用來將Python對象編碼成JSON字符串，由於字符串會有編碼問題，所以務必在後頭補上.encode('utf8')來指定編碼
Python 的 tempfile套件是用來創建系統臨時文件（用完後系統會自動刪除)，也就是在系統中創建一個有utf8編碼的json暫存檔的概念

接著呼叫認證的function
以往的gc = pygsheets.authorize(service_account_file='path/to/key.json')直接取代為下方3、4列程式碼的部分即可










# TODO 獲取google sheet上的user資料，並轉為user_df
def get_gs_to_df():
    creds_file = _google_creds_as_file()
    gc = pygsheets.authorize(service_account_file=creds_file.name)
    # 可直接拿firebase的金鑰
    survey_url = 'https://docs.google.com/spreadsheets/d/1sefxKcrFGhfJztdeBOwx9l58HR1kAruzEO3JN5zMKKk/'
    sh = gc.open_by_url(survey_url)
    ws = sh.worksheet_by_title('工作表1')  # 指定工作表
    user_df = ws.get_as_df(start='A2', index_colum=0, empty_value='', include_tailing_empty=False)
    return user_df

（二）Dataframe 輸出 CSV 檔

前面有提到，如果想避免用get_as_df設定上的麻煩，最快的方法就是先輸出 csv 再讀進來。

輸出與讀取





# 輸出
ws.export(filename='df')

# 讀取
df3 = pd.read_csv('df.csv')

寫成 functions







def ws_to_df(self, **kwargs):
    worksheet_title = self.title
    self.export(filename=worksheet_title + '_df')
    df = pd.read_csv(worksheet_title + '_df.csv', **kwargs)
    return df

pygsheets.worksheet.Worksheet.ws_to_df = ws_to_df