从github为Google Sheet加载JSON文件

Question

我试图使用谷歌服务帐户链接py脚本到谷歌表。我有一个工作的py脚本。但是，我想将脚本共享给我的客户端，因此我在github中创建了一个存储库，并在存储库中上传了凭据(JSON文件)。我需要在python脚本中直接从github加载JSON文件，以便每个使用脚本的人都可以通用访问JSON。

当前代码看起来像这样

import requests
import gspread
from gspread_formatting import *
gc = gspread.service_account(filename="C:\bin\myjson.json")

可以改成:

json_url = 'https://raw.githubusercontent.com/username/main/myjson.json'
f = requests.get(json_url)
json_name = json.loads(f.text)
gc = gspread.service_account(filename=json_name)

json的类型

{
"type": "service_account",
"project_id": "theta-*****-303113",
"private_key_id": "00ff078a8042c*********d8db217c39a5f5",
"private_key": "-----BEGIN PRIVATE KEY-----nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQClYkjapoOA7nByn1kiyK08i8J3zIRiScH784rMxik6nHJKFftT3pg3wfEvsN4UowZf73PL5gvm3ZJc8nocoXNnHPvvJd+pFdk7w7qmwBgGeDT4PSN7Clu9YW5OPfr4Wj27eMAhQzt+/CpLRKnroU6P7I82yhkh4ihIDzS6xh6c40nEB2cW***************nOCUtu1u2rb7oSE6cftXazSJJZyYidFgYZPli56Jex/aURaNsj9/omWCjqDKU228Nn+IIrlCcPhrkOMUR1JKiBs4lDQYJXtoC7i5H7Kf7ov86nRR3i5ra5/0NM+HjH54+Hnfdhsc5zXAgMBAAECggEAFm57RTTal00/8UOBPrFDHZEXS1fM1mftwW0aMvj4bBAjnC3pFdoWOVHT3c0WgiQOhEqdISXFrX2WEYdPr7jQTQcM7amXG+7NDD7Cwz49T+up5nDbVB/2i3xAktozRSYwQ1JAdQKCelnHfsgEilh14GVdy/4MrFTF1sAouimVE87vQ3nrwyaRJlFgBhmKQBX/19UubIxd8HKDxlJvsnhgHfxRdXDRbhgMHJUt4lk5e4xlqC8naqZUUaOxlj0hUeAIxcyhD/jmua24dj1JzNjMlkQXmLq8C7j4Cn89OqAAvo8DOB4Bn3utCEoXwIH0bnuIDkWi0PqGtXz97Mcb2/iZ2PnsKgQKBgQDb62vKlIvoT09XQhevnjlwOlwJkf02nbpgI4ue6olWlySVQj6T1iU7LO8dO6eHGKUMCplTyjcUkwhxGyL4UnFrJ1VUF8P95A5yRCFb2NT6/ue7Q9eJdtZg0WXwWLmUG/LZJZvDPsvc9qvdm87QdSns2pxzfHbEPO16qZkBkraDXmlwQKBgQDAhGAtOgWATwTebEy9r0jcSQjJHbCXMdvHnfTrzvGbIoA+VrDIrACo4IOCL4hEupQ9DNEtw9csMZw3Kff2eqV9OgeRMkfh68FYXnR5W4b3hXbU67GmypBhHY/IrQ3UxY1SWfGBPZ8wWLJBhhCr80z3WnwLYOyPCAlr+bnY1Iku9jYlwKBgQCjtKajwVo4Tzl/efqJu445ah3k04LpvQC9tLLNEmRxKb92Lcecn415dGR2vV5iyWyq/wTOA7xmu2DQWWP5tk8u6XgGGTUDTzXwHGYnltEkBXOJ509fdnHtdlLMzC8w2nE3RbCicY9t581eBMq6EE5fu5xZVg/kh8xbSgxrJXiSphwQKBgQCfn3o3boN4mofgCGN9VebVAsSmpkY0u0UmYjMci1qZoHnsyqK90hAuLVsoBf7cTDCDznhv7LDGIUp5ONPzkYw8AQes8yQtmche6IIHsVkQPXXj9OIvBvIEo77eP89HjNIBvAn1QbPK//DfzL7iygjW9BxgRfuiTEnJ4hwKndxZ8j4ewKBgGiNvFKC3SypYTnaV/0knyJlug9wMdc22f5ZDSXGx4EXOL3dT1zezyJIOtBJhPMjorbn/qcD9SKX/iGvCBkPlnXjAdXDteQAGInxOWdRKrD39OdHyTU51HDiqMFCJc4+CucVQmso0mkm1xeBKUkwHHnCn2ciEfhwJZyN+u/Fgm4nYa+n-----END PRIVATE KEY-----n",
"client_email": "g-service-acc@theta-*****-303113.iam.gserviceaccount.com",
"client_id": "10767932****790042565",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/g-service-acc%40theta-trees-303113.iam.gserviceaccount.com"
}

Answer 1

您应该将json数据保存到如下文件中:

json_url = 'https://someurl.com'
filename = 'credentials.json'
res = requests.get(json_url)
with open(filename, 'w') as f:
f.write(res.text)  # save the data into a file
gc = gspread.service_account(filename=filename)

如果你不想把文件保存到本地，你可以这样做:

json_url = 'https://someurl.com'
filename = 'credentials.json'
res = requests.get(json_url)
credentials = res.json()
gc = gspread.service_account_from_dict(credentials)