时间戳

首页 > 解决方案 > 没有密钥文件的 Python 到 Google 表格连接器

python - 没有密钥文件的 Python 到 Google 表格连接器

问题描述

我设法让 Python 从 Google Sheet 中读取。但是，因为我使用 PowerBI 中的脚本从 Google 表格中读取数据，所以我无法在我的计算机上使用本地秘密 JSON 密钥文件，因为 PowerBI 无法访问此文件。我目前连接到工作表如下：

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json(r'C:\Users\Laila\Documents\google_spreadsheet_secret_key.json', scope)
gc = gspread.authorize(credentials)
return gc,credentials

我想做这样的事情：

secret_key={xxx:xxx}
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json(secret_key)
gc = gspread.authorize(credentials)
return gc,credentials

这可能吗？

我已经尝试使用 JSON 转储添加密钥并像这样添加它，这会产生以下错误：

        ---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-85-0b15fbe84ead> in <module>

----> 8 onboarding_yearmonth=get_onboarding_year_month()

<ipython-input-30-ff93a4b40320> in get_onboarding_year_month()
      1 def get_onboarding_year_month():
----> 2     onboarding_sheet = read_onboarding_sheet()
      3     onboarding_row=onboarding_sheet.loc[onboarding_sheet['ID'] == str(client_id)]
      4     onboarding_row = onboarding_row.iloc[0]['Live date']
      5     onboarding_dt = parse(onboarding_row)

<ipython-input-28-d0961c0ce7d1> in read_onboarding_sheet()
      1 def read_onboarding_sheet():
----> 2     gc,credentials = connect_to_gsheets()
      3     spreadsheet_key = '1zeUiWGMWp-xxx'
      4     spreadsheet = gc.open_by_key(spreadsheet_key)
      5     worksheet = spreadsheet.worksheet("Onboarding Dates")

<ipython-input-82-7b436cef1e5e> in connect_to_gsheets()
      1 def connect_to_gsheets():
      2     scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
----> 3     credentials = ServiceAccountCredentials.from_json(secret_key)
      4     gc = gspread.authorize(credentials)
      5     return gc,credentials

c:\users\laila\appdata\local\programs\python\python36-32\lib\site-packages\oauth2client\service_account.py in from_json(cls, json_data)
    440         password = None
    441         if pkcs12_val is None:
--> 442             private_key_pkcs8_pem = json_data['_private_key_pkcs8_pem']
    443             signer = crypt.Signer.from_string(private_key_pkcs8_pem)
    444         else:

KeyError: '_private_key_pkcs8_pem'

标签： pythonjsongoogle-sheetsoauthpowerbi

解决方案

根据ServiceAccountCredentials.from_json 文档，您可以传递已解析的字典。

您可以将文件内容复制为变量中的字符串，然后使用json.loads().

import json


# store contents of 'C:\Users\Laila\Documents\google_spreadsheet_secret_key.json' in secrets
secrets = 'STORE_FILE_CONTENT_HERE' # Use single quotation to wrap the content
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json(json.loads(secrets), scope)
gc = gspread.authorize(credentials)
return gc,credentials

免责声明：我没有测试过。

参考

推荐阅读