R Cron任务无法编辑Google Sheet：授权权限不足问题求助

问题背景 #

使用googlesheets4包的range_clear和sheet_write函数编写Cron任务，用于每日编辑Google Sheet。此前按配置步骤完成权限设置后正常运行约一个月，之后任务失效。重装开发版googlesheets4（devtools::install_github("tidyverse/googlesheets4")）并重新配置权限后，本地运行正常，但Cron任务持续失败，抛出令牌权限范围不足的403错误。

此前的权限配置步骤 #

library(googlesheets4)

# Set authentication token to be stored in a folder called `.secrets`
options(gargle_oauth_cache = ".secrets")

# Authenticate manually
gs4_auth()

# If successful, the previous step stores a token file.
# Check that a file has been created with:
list.files(".secrets/")

# Check that the non-interactive authentication works by first deauthorizing:
gs4_deauth()

# Authenticate using token. If no browser opens, the authentication works.
gs4_auth(cache = ".secrets", email = "my.email@gmail.com")

错误信息 #

i The googlesheets4 package is using a cached token for
  'my.email@gmail.com'.
Error in `gs4_get_impl_()`:
! Client error: (403) PERMISSION_DENIED
* Client does not have sufficient permission. This can happen because the OAuth
  token does not have the right scopes, the client doesn't have permission, or
  the API has not been enabled for the client project.
* Request had insufficient authentication scopes.

Error details:
* reason: ACCESS_TOKEN_SCOPE_INSUFFICIENT

解决方案 #

• 清除旧缓存令牌：删除本地.secrets目录下的所有文件，彻底移除旧的、权限范围不足的认证令牌。
• 重新生成完整权限令牌：在本地交互式环境中执行认证时，明确指定编辑Google Sheet所需的权限范围，确保令牌包含足够权限：

library(googlesheets4)
options(gargle_oauth_cache = ".secrets")

# 明确指定读写表格及关联Drive的权限
gs4_auth(
  email = "my.email@gmail.com",
  scopes = c(
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file"
  ),
  cache = ".secrets"
)

• 同步令牌到Cron运行环境：将更新后的.secrets文件夹复制到Cron任务运行的服务器或环境中，确保脚本执行时能访问到新令牌。建议在Cron脚本中使用绝对路径指定缓存目录，避免工作路径不一致导致的问题：

# Cron脚本中使用绝对路径指定缓存
gs4_auth(
  email = "my.email@gmail.com",
  cache = "/home/your-user/.secrets",  # 替换为实际绝对路径
  scopes = c("https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive.file")
)

• 验证Cron环境认证：通过SSH登录服务器，直接执行脚本，确认无需浏览器交互即可完成认证，且能正常执行range_clear和sheet_write操作。
• 检查Google API状态：登录Google Cloud控制台，确认Google Sheets API和Google Drive API已启用，且对应的OAuth客户端未被限制访问。

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