Discord Bot操作Google Sheets遇认证权限不足问题及优化咨询

我正在开发一款Discord Bot，目标是接收指令后编辑Google Sheets中的指定行列。已配置Google Cloud应用拥有该Google Sheets文件的所有者权限，但运行Bot时触发「Request had insufficient authentication scopes」错误。请问需修改哪些设置？同时是否有更优的Bot编码方案？

当前代码：

import discord
from discord.ext import commands
import gspread
from oauth2client.service_account import ServiceAccountCredentials

intents = discord.Intents.default()
intents.members = True

bot = commands.Bot(command_prefix='/', intents=intents)
@bot.event
async def on_ready():
    print('Logged in as {0.user}'.format(bot))

scope = ['https://www.googleapis.com/auth/spreadsheets']
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)

client = gspread.authorize(creds)
sheet = client.open('Kopia The LOST MC').sheet1


@bot.command()
async def skladka(ctx, user: discord.Member):
    discord_id = str(user.id)
    cell_with_discord_id = sheet.find(discord_id)

    value_to_copy = sheet.cell(116, 2).value

    sheet.update_cell(cell_with_discord_id.row, 4, value_to_copy)

bot.run('token')

报错信息：

Traceback (most recent call last):
  File "c:\Users\szymo\Desktop\discord-lost\bot.py", line 18, in <module>
    sheet = client.open('Kopia The LOST MC').sheet1
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\szymo\AppData\Local\Programs\Python\Python312\Lib\site-packages\gspread\client.py", line 123, in open
    spreadsheet_files, response = self._list_spreadsheet_files(title, folder_id)
                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\szymo\AppData\Local\Programs\Python\Python312\Lib\site-packages\gspread\client.py", line 96, in _list_spreadsheet_files
    response = self.http_client.request("get", url, params=params)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\szymo\AppData\Local\Programs\Python\Python312\Lib\site-packages\gspread\http_client.py", line 123, in request
    raise APIError(response)
gspread.exceptions.APIError: {'code': 403, 'message': 'Request had insufficient authentication scopes.', 'errors': [{'message': 'Insufficient Permission', 'domain': 'global', 'reason': 'insufficientPermissions'}], 'status': 'PERMISSION_DENIED', 'details': [{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'ACCESS_TOKEN_SCOPE_INSUFFICIENT', 'domain': 'googleapis.com', 'metadata': {'service': 'drive.googleapis.com', 'method': 'google.apps.drive.v3.DriveFiles.List'}}]}

一、权限问题修复 #

从报错详情能看出，调用drive.googleapis.com的DriveFiles.List方法时权限不足。当前代码仅申请了Google Sheets的操作权限，但client.open()方法需要调用Google Drive API来查找指定名称的表格文件，因此必须补充Drive的权限：

1. 修改代码中的scope配置，添加Drive只读权限（足够满足文件查找需求，且比全权限更安全）：

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

若后续需要操作Drive的其他功能（如创建/删除文件），可替换为https://www.googleapis.com/auth/drive，但优先使用最小必要权限。

2. 无需重新生成凭证文件，修改代码中的scope后，Bot下次运行会自动获取包含新权限的访问令牌。

二、编码优化方案 #

1. 延迟初始化Google Sheets客户端 #

原代码在Bot启动前就初始化Sheets客户端，若网络或权限异常会导致Bot直接崩溃。建议将初始化逻辑移至on_ready事件中，确保Bot登录完成后再建立Sheets连接：

import discord
from discord.ext import commands
import gspread
from oauth2client.service_account import ServiceAccountCredentials

intents = discord.Intents.default()
intents.members = True

bot = commands.Bot(command_prefix='/', intents=intents)
sheet = None  # 全局声明表格对象

@bot.event
async def on_ready():
    global sheet
    print('Logged in as {0.user}'.format(bot))
    # 初始化Sheets客户端
    scope = [
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive.readonly'
    ]
    creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
    client = gspread.authorize(creds)
    sheet = client.open('Kopia The LOST MC').sheet1

2. 完善错误处理 #

原代码无任何异常捕获，遇到找不到用户ID、表格访问失败等情况会直接崩溃。优化后添加多层错误处理，返回友好提示到Discord频道：

@bot.command()
async def skladka(ctx, user: discord.Member):
    if not sheet:
        await ctx.send("服务未准备好，请稍后再试")
        return
    try:
        discord_id = str(user.id)
        cell_with_discord_id = sheet.find(discord_id)
        if not cell_with_discord_id:
            await ctx.send(f"未找到用户 {user.mention} 的记录")
            return
        
        value_to_copy = sheet.cell(116, 2).value
        sheet.update_cell(cell_with_discord_id.row, 4, value_to_copy)
        await ctx.send(f"已成功更新用户 {user.mention} 的记录")
    except Exception as e:
        await ctx.send(f"操作失败：{str(e)}")
        print(f"错误详情：{e}")

3. 替换弃用的认证库 #

oauth2client已被官方弃用，建议换成维护更活跃的google-auth系列库：
先安装依赖：

pip install google-auth google-auth-oauthlib google-auth-httplib2 gspread discord.py

然后修改认证逻辑：

from google.oauth2.service_account import Credentials

# 在on_ready事件中替换原认证代码
creds = Credentials.from_service_account_file('credentials.json', scopes=scope)
client = gspread.authorize(creds)

4. 提取硬编码常量 #

将表格名称、目标行列等固定值抽为常量，方便后续维护修改：

# 配置常量
SPREADSHEET_NAME = 'Kopia The LOST MC'
SOURCE_ROW = 116
SOURCE_COL = 2
UPDATE_TARGET_COL = 4

之后在代码中用这些变量代替硬编码数值即可。

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