一起學 Python 102 : 使用 Google API 上傳資料至 Google sheet

1. 打開 CMD

2. 安裝需要的模組
pip install PyOpenSSL
pip install gspread
pip install oauth2client

hint : 若你電腦中同時擁有 python2 及 python3
則可以使用 pip3 install gspread 來指定安裝到 python3

3. 申請 Google API (連結)

點選建立專案 -> 輸入專案名稱

4. 點選 Google Drive API -> 啟用

5. 進入專案底下，點選 "憑證"

6. 選擇 "建立憑證" -> 選擇服務帳戶金鑰

7. 輸入服務帳戶名稱如 PyUpLoadTest ，金鑰類型選擇 Json

8. 點選建立 -> 會得到一個 *.json 檔 -> 將其更名為 PyUpLoadTest.json

記下該 json 檔案在電腦中的路徑例如 D:\Python_program\PyUpLoadTest.json

9. 回到專案底下，點選 API和服務 -> 資料庫

10. 找到 Google Sheets API 並將它啟用

11. 到 Google drive 裡面新增一個新的工作表(google sheet)

12. 命名該工作表為 P_UpLoadTest (並注意，下方的分頁名稱為 sheet1 )

11. 打開 PyUpLoadTest.json 找到文件裡面的 "client_email": 字樣
複製雙引號後面的文字如 pyuploadtest@earthquakeupload.iam.gserviceaccount.com

12. 回到 P_UpLoadTest 工作表並點選右上角的共用

13. 將 pyuploadtest@earthquakeupload.iam.gserviceaccount.com 設置為共用對象並且權限為"可編輯"

Python 程式碼

import json
import sys
import time
import datetime


import gspread
from oauth2client.service_account import ServiceAccountCredentials

GDOCS_OAUTH_JSON = 'D:\Python_program\PyUpLoadTest.json' 
GDOCS_SPREADSHEET_NAME = "P_UpLoadTest"
FREQUENCY_SECONDS      = 2

print('將資料記錄在試算表',GDOCS_SPREADSHEET_NAME,'每',1/FREQUENCY_SECONDS,'秒')

def login_open_sheet(oauth_key_file, spreadsheet):
    """Connect to Google Docs spreadsheet and return the first worksheet."""
    try:
        scope =  ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
        credentials = ServiceAccountCredentials.from_json_keyfile_name(oauth_key_file, scope)
        gc = gspread.authorize(credentials)
        worksheet = gc.open(spreadsheet).sheet1
        return worksheet
    except Exception as ex:
        print('Unable to login and get spreadsheet.  Check OAuth credentials, spreadsheet name, and make sure spreadsheet is shared to the client_email address in the OAuth .json file!')
        print('Google sheet login failed with error:', ex)
        sys.exit(1)
        
print('Logging sensor measurements to {0} every {1} seconds.'.format(GDOCS_SPREADSHEET_NAME, FREQUENCY_SECONDS))
print('Press Ctrl-C to quit.')
worksheet = None

while True:
    # Login if necessary.
    if worksheet is None:
        worksheet = login_open_sheet(GDOCS_OAUTH_JSON, GDOCS_SPREADSHEET_NAME)

    # Append the data in the spreadsheet, including a timestamp
    try:
        worksheet.update_acell('A1','Bingo!') #更新某個儲存格
        #value = 10
        #worksheet.append_row((datetime.datetime.now(), value)) #將資料加在最下方
    except:
        # Error appending data, most likely because credentials are stale.
        # Null out the worksheet so a login is performed at the top of the loop.
        print('Error, logging in again')
        worksheet = None
        time.sleep(FREQUENCY_SECONDS)
        continue

    # Wait 30 seconds before continuing
    print('Wrote a row to {0}'.format(GDOCS_SPREADSHEET_NAME))
    time.sleep(FREQUENCY_SECONDS)

參考連結

使用Python的上傳資料到谷歌試算表
Python 使用 Google 雲端硬碟 API 自動進行文字辨識教學
Github
價格追蹤：使用 gspread 自動更新 Google Sheets
Using OAuth2 for Authorization