使用Apps Script API调用Google Sheets函数时遇401未授权错误求助

问题背景 #

尝试在Ruby on Rails应用中调用Google Apps Script API执行Google Sheets函数，因API不支持服务账号，改用OAuth 2.0认证，但调用run_script时始终返回401未授权错误。

现有代码 #

require 'google/apis/drive_v3'
require 'sinatra'
require 'googleauth'
require 'googleauth/stores/redis_token_store'

client_id = Google::Auth::ClientId.from_file('lib/get_property_data/client_secret.json')
scope = ['https://www.googleapis.com/auth/spreadsheets']
token_store=  Google::Auth::Stores::RedisTokenStore.new(redis: Redis.new)
authorizer = Google::Auth::WebUserAuthorizer.new(client_id, scope, token_store, '/oauth2callback')

service = Google::Apis::ScriptV1::ScriptService.new
service.client_options.application_name = 'Application name'
service.authorization = authorizer

request = Google::Apis::ScriptV1::ExecutionRequest.new(function: 'testFunction', parameters: ['sheet_id'])
script_id = 'xxxxxxx'

response  = service.run_script(script_id, request)

错误信息 #

Caught error Unauthorized
Error - #<Google::Apis::AuthorizationError: Unauthorized status_code: 401 header: #<HTTP::Message::Headers:0x00007f806c517978 @http_version="1.1", @body_size=0, @chunked=false, @request_method="POST", @request_uri=#<Addressable::URI:0x3fc03926f794 URI:https://script.googleapis.com/v1/scripts/1BrWLyFp4L0Q-00sZekntFTsZzed_4TLesPQYUSDWx-AbRe4EVEGd5-DW:run?>, @request_query=nil, @request_absolute_uri=nil, @status_code=401, @reason_phrase="Unauthorized", @body_type=nil, @body_charset=nil, @body_date=nil, @body_encoding=#<Encoding:UTF-8>, @is_request=false, @header_item=[["WWW-Authenticate", "Bearer realm=\"https://accounts.google.com/\""], ["Vary", "Origin"], ["Vary", "X-Origin"], ["Vary", "Referer"], ["Content-Type", "application/json; charset=UTF-8"], ["Content-Encoding", "gzip"], ["Date", "Wed, 20 Sep 2023 12:27:41 GMT"], ["Server", "ESF"], ["Cache-Control", "private"], ["X-XSS-Protection", "0"], ["X-Frame-Options", "SAMEORIGIN"], ["X-Content-Type-Options", "nosniff"], ["Alt-Svc", "h3=\":443\"; ma=2592000,h3-29=\":443\"; ma=2592000"], ["Transfer-Encoding", "chunked"]], @dumped=false> body: "{
  \"error\": {
    \"code\": 401,
    \"message\": \"Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential.\",
    \"errors\": [
      {
        \"message\": \"Login Required.\",
        \"domain\": \"global\",
        \"reason\": \"required\",
        \"location\": \"Authorization\",
        \"locationType\": \"header\"
      }
    ],
    \"status\": \"UNAUTHENTICATED\",
    \"details\": [
      {
        \"@type\": \"type.googleapis.com/google.rpc.ErrorInfo\",
        \"reason\": \"CREDENTIALS_MISSING\",
        \"domain\": \"googleapis.com\",
        \"metadata\": {
          \"service\": \"script.googleapis.com\",
          \"method\": \"google.apps.script.v1.ScriptExecution.Execute\"
        }
      }
    ]
  }
}\n">

Retrying after authentication failure
Traceback (most recent call last):
        1: from (irb):37
Google::Apis::AuthorizationError (Unauthorized)

解决建议 #

1. 修正OAuth权限范围 #

当前仅请求了spreadsheets权限，但调用Apps Script API需要额外添加脚本相关权限，替换scope为：

scope = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/script.projects',
  'https://www.googleapis.com/auth/script.execution'
]

2. 完整触发OAuth授权流程 #

WebUserAuthorizer不能直接绑定到service后调用接口，必须先引导用户完成授权获取有效令牌：

• 添加授权回调路由：

# 引导用户授权的路由
get '/authorize' do
  user_id = session[:user_id] # 替换为实际用户标识，比如当前登录用户ID
  redirect authorizer.get_authorization_url(user_id: user_id, request: request)
end

# OAuth回调处理路由
get '/oauth2callback' do
  user_id = session[:user_id]
  authorizer.get_and_store_credentials_from_code(
    user_id: user_id, code: params[:code], request: request
  )
  redirect '/' # 授权完成后跳转至业务页面
end

• 调用API前验证令牌：

user_id = session[:user_id]
credentials = authorizer.get_credentials(user_id, request)

if credentials.nil?
  redirect '/authorize' # 无有效令牌则引导授权
else
  # 检查令牌是否过期，过期则刷新
  credentials.refresh! if credentials.expired?
  service.authorization = credentials
  # 执行API调用
  response = service.run_script(script_id, request)
end

3. 检查Google Cloud项目配置 #

• 确认项目中已启用Apps Script API和Google Sheets API
• 验证OAuth客户端ID类型为「Web应用」，且回调地址与代码中的/oauth2callback完全一致
• 若为外部应用，需在OAuth同意屏幕中添加测试用户

4. 验证令牌有效性 #

可以通过以下代码检查令牌状态：

puts "Access Token: #{credentials.access_token}"
puts "Token Expired: #{credentials.expired?}"

内容的提问来源于stack exchange，提问作者sajid nawaz