Google Sheets API PERMISSION_DENIED错误求助：动态创建表格时权限不足问题排查

问题描述 #

我尝试通过Ruby代码动态创建Google表格，但运行代码时触发了PERMISSION_DENIED错误。

代码示例 #

require "google/apis/sheets_v4"
require "googleauth"
require "googleauth/stores/file_token_store"
require "fileutils"

OOB_URI = "urn:ietf:wg:oauth:2.0:oob".freeze
APPLICATION_NAME = "Google Sheets API Ruby Quickstart".freeze
CREDENTIALS_PATH = "new_credential.json".freeze
# The file token.yaml stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
TOKEN_PATH = "token.yaml".freeze
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

##
# Ensure valid credentials, either by restoring from the saved credentials
# files or intitiating an OAuth2 authorization. If authorization is required,
# the user's default browser will be launched to approve the request.
#
# @return [Google::Auth::UserRefreshCredentials] OAuth2 credentials
def authorize
  client_id = Google::Auth::ClientId.from_file CREDENTIALS_PATH
  token_store = Google::Auth::Stores::FileTokenStore.new file: TOKEN_PATH
  authorizer = Google::Auth::UserAuthorizer.new client_id, SCOPE, token_store
  user_id = "default"
  credentials = authorizer.get_credentials user_id
  if credentials.nil?
    url = authorizer.get_authorization_url base_url: OOB_URI
    puts "Open the following URL in the browser and enter the " \
         "resulting code after authorization:\n" + url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI
    )
  end
  credentials
end

# Initialize the API
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

spreadsheet = {
  properties: {
    title: 'Sales Report'
  }
}
spreadsheet = service.create_spreadsheet(spreadsheet, fields: 'spreadsheetId')
puts "Spreadsheet ID: #{spreadsheet.spreadsheet_id}"

错误信息 #

Traceback (most recent call last):
15: from quickstart.rb:49:in `<main>'
14: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-api-client-0.53.0/generated/google/apis/sheets_v4/service.rb:121:in `create_spreadsheet'
13: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/base_service.rb:377:in `execute_or_queue_command'
12: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:102:in `execute'
11: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
10: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
9: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
8: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:111:in `block in execute'
7: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
6: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
5: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
4: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:114:in `block (2 levels) in execute'
3: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:311:in `execute_once'
2: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:195:in `process_response'
1: from /home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/api_command.rb:134:in `check_status'
/home/vagrant/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:229:in `check_status': PERMISSION_DENIED: Request had insufficient authentication scopes. (Google::Apis::ClientError)

我当前使用OAuth认证方式，生成认证JSON文件时已选择所有可用权限范围，且重新创建了认证JSON，但问题仍未解决。请问该如何处理此权限错误？

解决方案 #

这个问题的核心原因很明确——你的代码里用了只读权限范围，但创建表格需要的是读写权限，而且之前生成的token文件会保留旧的权限配置，所以光替换认证JSON文件没用。按下面的步骤来修复：

1. 修改代码中的权限范围常量 #

找到代码里的SCOPE定义行，把只读权限替换成完整的读写权限：

# 替换原来的只读范围为读写范围
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

AUTH_SPREADSHEETS对应的是Google Sheets的完整读写权限，足够支持创建表格的操作；而原来的AUTH_SPREADSHEETS_READONLY仅能读取数据，没有创建、修改表格的权限。

2. 删除旧的token文件 #

之前授权时生成的token.yaml文件里保存的是带有只读权限的令牌，即使你修改了代码里的权限范围，程序还是会优先加载这个旧token。所以必须删除它：

rm token.yaml

3. 重新运行代码并完成授权 #

删除旧token后，再次运行你的Ruby代码，程序会重新生成授权链接。打开链接完成授权流程——这次授权会使用新的读写权限范围，生成的新token就会带有创建表格的权限了。

额外提醒 #

即使你在Google Cloud控制台给项目开启了所有权限，最终生效的权限是代码里指定的范围。API客户端只会请求代码中定义的权限，控制台的设置只是允许项目使用哪些权限，具体请求哪些权限还是由代码配置决定。所以一定要确保代码里的SCOPE和你需要执行的操作匹配。

内容的提问来源于stack exchange，提问作者Felipe Cristiano