Lecture 10 - Firestore 與 Google Sheets 連結

tags: GCP Firestore Python Google Sheets

• Lecture 10 - Firestore 與 Google Sheets 連結
  • 一、前言
  • 二、事前準備
    • （一）取得權限（若在使用firestore時已經有Key，則可跳過此步驟）
    • （二）套件下載
  • 三、Python 讀寫 Google Sheets
    • （一）使用憑證開啟 Google Sheets
    • （二）指定資料表
    • （三）讀取 資料與轉型為 Dataframe
    • （四）新增/更改 資料
  • 四、進階課程
    • （一）以 Cloud Function 連接 Google Sheets
    • （二）Dataframe 輸出 CSV 檔

---

一、前言

在前面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做資料的處理，不過處理後應該如何寫入資料呢？可分為兩種方式：

1. update_value('儲存格位置', '更改值')：更改單一值，例如更改A1儲存格為test
   ​​​​ws.update_value('A1', 'test')
   

2. 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

小提醒：

1. json.dumps 是用來將Python對象編碼成JSON字符串，由於字符串會有編碼問題，所以務必在後頭補上.encode('utf8')來指定編碼
2. 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 再讀進來。

1. 輸出與讀取

   ​​​​# 輸出
   ​​​​ws.export(filename='df')
   
   ​​​​# 讀取
   ​​​​df3 = pd.read_csv('df.csv')
   

2. 寫成 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
   

3. 使用範例

   ​​​​df4 = ws.ws_to_df()
   

參考資料：

1. 用Python連接google sheet https://hackmd.io/@rpyapp/GoogleSheets

2. 使用Python上傳資料到Google試算表 https://sites.google.com/site/zsgititit/home/python-cheng-shi-she-ji/shi-yongpython-shang-chuan-zi-liao-daogoogle-shi-suan-biao?tmpl=%2Fsystem%2Fapp%2Ftemplates%2Fprint%2F&showPrintDialog=1

3. 認證問題 https://stackoverflow.com/questions/56817195/pygsheets-load-credentials-from-dict-instead-of-file

4. 編碼問題 https://stackoverflow.com/questions/18337407/saving-utf-8-texts-in-json-dumps-as-utf8-not-as-u-escape-sequence

5. pygsheets官方文檔 https://pygsheets.readthedocs.io/en/stable/index.html

6. Google Sheets API returns “The caller does not have permission” when using server key https://stackoverflow.com/questions/38949318/google-sheets-api-returns-the-caller-does-not-have-permission-when-using-serve