通过API访问受组织限制的Google表格 [英] Accessing an organization-restriced Google Sheet via API

问题描述

我正在编写一个Python 3.7脚本，该脚本需要从Google Spreadsheet中读取数据.

I am writing a Python 3.7 script that needs to read data from a Google Spreadsheet.

有问题的电子表格属于我的工作Google帐户所属的组织:让我们将其称为组织".电子表格的权限设置为具有链接的任何组织人员都可以查看".此细节已阻止我的应用程序正常工作.

The spreadsheet in question belongs to an Organization that my work Google Account is part of: let's just call it "Organization". The spreadsheet permissions are set as "Anyone at Organization with the link can view". This detail has been preventing my application from working.

我转到了 https://console.cloud.google.com/上的凭据仪表板apis/凭据，同时使用我在Organization中的帐户进行身份验证，并创建了服务帐户密钥.根据通过使用新的Google API控制台项目getauthorized_client，客户端可以使用此方法未经授权来检索访问令牌.我将JSON密钥文件下载到/path/to/service_account_key.json.

I went to the credentials dashboard at https://console.cloud.google.com/apis/credentials while being authenticated with my account in Organization, and created a Service Account Key. Domain Wide Delegation is allowed, as per Using New Google API Console project getting unauthorized_client ,Client is unauthorized to retrieve access tokens using this method. I downloaded the JSON keyfile to /path/to/service_account_key.json.

下面，我用gspread客户端库记录了我的尝试- https://github.com/burnash/gspread -但是使用google-api-python-client 1.7.4时，我遇到了完全相同的问题:

Below I document my attempt with the gspread client library - https://github.com/burnash/gspread - however I had the exact same problem using google-api-python-client 1.7.4:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']
credentials = ServiceAccountCredentials.from_json_keyfile_name('/path/to/service_account_key.json', scopes)
gc = gspread.authorize(credentials)
# spreadhseet ID below obfuscated, it's actually the one you get from its URL
sheet = gc.open_by_key('12345-abcdef')

gspread的响应具有与普通Google API v4相同的HTTP代码和消息:

gspread's response has the same HTTP code and message as the plain Google API v4:

gspread.exceptions.APIError: {
  "error": {
    "code": 403,
    "message": "The caller does not have permission",
    "status": "PERMISSION_DENIED"
  }
}

但是，如果我将电子表格上的权限(不允许在实际的电子表格上进行更改)更改为任何具有链接的人都可以查看"，从而删除了组织"，则一切有效！

However if I change the permissions on the spreadsheet (which I won't be allowed to do on the actual one) to "Anyone with the link can view", thus removing Organization", everything works!

<Spreadsheet 'Your spreadsheet' id:12345-abcdef>

我的粗略猜测是，我创建的服务帐户未从我那里继承组织的成员身份.但是，我没有选择确保这一点.我什至要求域管理员从他的管理员帐户(也启用了域范围委派)为我创建服务帐户:什么也没有.

My rough guess was that the service account I created did not inherit from me the membership in Organization. However I found no option for ensuring that. I even asked the domain Administrator to create the service account for me from his Admin accounts (also with Domain-wide Delegation on): nothing.

我曾在 Google服务帐户和工作表权限中说过必须与服务帐户的电子邮件地址明确共享工作表.当我这样做时，它起作用了，但是我还收到一条警告消息，声称该帐户不在Organization的G Suite域中(同样，怎么可能不呢?).

It has been said at Google service account and sheets permissions that I must explicitly share the sheet with the email address of the service account. When I did that it worked, but I also got a warning message claiming that account was not in the G Suite domain of Organization (again, how could it not be?).

非常感谢

推荐答案

尝试将该帐户委派给您组织的特定用户.

Try delegating that account to a specific user of your organization.

按原样

from oauth2client.service_account import ServiceAccountCredentials
from httplib2 import Http

scope = [
            'https://www.googleapis.com/auth/drive',
            'https://www.googleapis.com/auth/spreadsheets.readonly',
            'https://www.googleapis.com/auth/spreadsheets'
        ]

creds = ServiceAccountCredentials.from_json_keyfile_dict('/path_to_service_account_json_key', scope)

delegated = creds.create_delegated('anyuser@org_domain.com')
delegated_http = delegated.authorize(Http())
spreadsheetservice = gspread.authorize(delegated)

这将使服务帐户委派为用户. 现在，您可以将表格直接分享给上述用户(anyuser@org_domain.com)或动态传递表格所有者的电子邮件.

This would make the service account delegated as the user. now you may simply share that very sheet to the above mentioned user (anyuser@org_domain.com) or pass the sheet's owner email dynamically.

这篇关于通过API访问受组织限制的Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！