OOB禁用后如何通过Ruby认证访问Google Sheets？

步骤1：安装依赖 #

执行以下命令安装所需Ruby gem：

gem install google-cloud-sheets google-auth-library-ruby

步骤2：配置认证与API调用 #

将你已有的credentials.json放在项目目录下，使用以下代码完成认证与表格操作：

require "google/cloud/sheets"
require "googleauth"
require "googleauth/stores/file_token_store"
require "fileutils"

# 核心配置项
REDIRECT_URI = "http://localhost:8080"
CLIENT_SECRETS_PATH = "credentials.json" # 你的凭证文件路径
STORED_CREDENTIALS_PATH = File.join(Dir.home, ".credentials", "sheets-ruby-auth.yaml")
# 权限范围：只读用readonly，读写用spreadsheets
SCOPE = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

def authorize
  # 创建本地凭证存储目录
  FileUtils.mkdir_p(File.dirname(STORED_CREDENTIALS_PATH))

  # 加载客户端ID
  client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)
  # 本地持久化已授权凭证
  token_store = Google::Auth::Stores::FileTokenStore.new(file: STORED_CREDENTIALS_PATH)
  authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)

  user_id = "default"
  credentials = authorizer.get_credentials(user_id)

  # 首次授权流程
  if credentials.nil?
    auth_url = authorizer.get_authorization_url(base_url: REDIRECT_URI)
    puts "请打开以下链接完成授权："
    puts auth_url
    puts "授权后复制回调地址中的`code`参数，粘贴到下方并回车："
    auth_code = gets.chomp

    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: auth_code, base_url: REDIRECT_URI
    )
  end
  credentials
end

# 初始化Sheets服务实例
sheets_service = Google::Cloud::Sheets::V4::SheetsService.new
sheets_service.authorization = authorize

# 示例：读取指定表格的指定范围数据
spreadsheet_id = "你的Google表格ID" # 表格URL中的ID部分
range = "Sheet1!A1:C3" # 要读取的单元格范围
response = sheets_service.get_spreadsheet_values(spreadsheet_id, range)

puts "读取到的数据："
response.values.each do |row|
  puts row.join("\t")
end

关键说明 #

• 权限调整：如果需要修改表格内容，将SCOPE改为["https://www.googleapis.com/auth/spreadsheets"]
• 首次运行：程序会输出授权链接，完成OAuth授权后复制回调地址中的code参数粘贴回终端，凭证会自动保存在本地，后续运行无需重复授权
• 凭证类型：确保你的credentials.json是Google Cloud控制台创建的桌面应用类型OAuth客户端ID，而非其他类型

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