{%hackmd @wh91008/theme %}

# Lecture 10 - Firestore 與 Google Sheets 連結

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

[TOC]

---
## ==一、前言==
在前面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 了

:::warning
:bulb: **小提醒**

記得前往[此處](https://console.developers.google.com/iam-admin/serviceaccounts/)或打開憑證的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 開始算

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

如果想避免用 ```get_as_df()``` 設定上的麻煩，最快的方法就是先輸出 csv 再讀進來。（下方 [四、進階課程](https://hackmd.io/aczAPC1_R0esFZNENrgFkg?both#%EF%BC%88%E4%BA%8C%EF%BC%89Dataframe-%E8%BC%B8%E5%87%BA-CSV-%E6%AA%94) 會教學）
:::
### （四）新增/更改 資料
學會了（一）、（二）之後就可以直接把資料轉為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='')

:::warning
:bulb: 小提醒：
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將所有內容複製自

import json, tempfile

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

:::warning
:bulb: 小提醒：
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()

###### :book: 參考資料：
###### 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