使用PHP调用Google Sheets API无法获取最新数据的求助

核心问题分析 #

• 认证方式冲突：代码同时混用服务账号环境变量和OAuth2凭证文件，导致认证逻辑混乱，可能无法正确获取最新权限。
• API缓存未彻底禁用：仅设置Cache-Control: no-cache不足以绕过Google Sheets API的服务器端缓存，公式计算结果可能被缓存。
• 异常处理逻辑错误：$sheetsService变量未定义，异常触发后无法重新获取数据。
• 缺少MySQL写入逻辑：现有代码仅获取表格数据，没有将数据插入/更新到MySQL的步骤。
• 依赖sleep等待公式更新不可靠：固定延迟无法适配公式计算的实际耗时，可能导致获取到未更新的数据。

分步解决方案 #

1. 统一认证方式（使用服务账号适配Cron无交互场景） #

删除OAuth2相关的token.json逻辑，改用服务账号认证，避免交互步骤：

• 确保服务账号已被共享到目标Google表格（赋予编辑权限）。
• 只保留服务账号认证代码，移除OAuth2的token获取和刷新逻辑。

2. 强制禁用API缓存 #

调用spreadsheets_values->get时添加专属参数，同时在请求头中强化缓存禁用规则：

$response = $service->spreadsheets_values->get($spreadsheetId, $range, [
    'valueRenderOption' => 'UNFORMATTED_VALUE',
    'includeGridData' => true
]);

3. 修复异常处理逻辑 #

将未定义的$sheetsService改为$service，优化异常重试逻辑：

try {
    $response = $service->spreadsheets_values->get($spreadsheetId, $range, $params);
} catch (\Google_Service_Exception $e) {
    $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
    $response = $service->spreadsheets_values->get($spreadsheetId, $range, $params);
}

4. 添加MySQL数据同步逻辑 #

获取到$values后，编写插入/更新MySQL的代码，处理空值和批量操作：

// 连接MySQL
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 清空旧数据（或根据主键做增量更新）
$pdo->exec("TRUNCATE TABLE your_table");

// 批量插入新数据
$stmt = $pdo->prepare("INSERT INTO your_table (col1, col2, col3) VALUES (?, ?, ?)");
foreach ($values as $row) {
    // 补全空值，确保列数匹配表结构
    $row = array_pad($row, 3, null);
    $stmt->execute([$row[0], $row[1], $row[2]]);
}

5. 替换sleep为API强制刷新（可选） #

如果公式更新延迟较高，先调用API触发表格计算刷新，再获取数据：

// 触发表格刷新（需Drive权限）
$driveService = new \Google\Service\Drive($client);
$driveService->files->touch($spreadsheetId);
usleep(2000000); // 等待2秒，根据实际调整

修改后的完整代码 #

require '../vendor/autoload.php';

// 服务账号认证
putenv('GOOGLE_APPLICATION_CREDENTIALS=myserviceaccount.json');
$client = new \Google\Client();
$client->setApplicationName('Application Name');
$client->setScopes([
    \Google\Service\Sheets::SPREADSHEETS,
    \Google\Service\Drive::DRIVE // 用于触发表格刷新（可选）
]);
$client->useApplicationDefaultCredentials();
$client->setAccessType('offline');

// 配置禁用缓存的HttpClient
$client->setHttpClient(new \GuzzleHttp\Client([
    'headers' => ['Cache-Control' => 'no-cache, max-age=0']
]));

$sheetsService = new \Google\Service\Sheets($client);
$spreadsheetId = 'your_spreadsheet_id';
$range = 'Sheet1!A2:Z6';

// 可选：触发表格刷新，确保公式计算完成
$driveService = new \Google\Service\Drive($client);
$driveService->files->touch($spreadsheetId);
usleep(2000000);

// 获取最新数据
$params = [
    'valueRenderOption' => 'UNFORMATTED_VALUE',
    'includeGridData' => true
];

try {
    $response = $sheetsService->spreadsheets_values->get($spreadsheetId, $range, $params);
} catch (\Google_Service_Exception $e) {
    $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
    $response = $sheetsService->spreadsheets_values->get($spreadsheetId, $range, $params);
}

$values = $response->getValues();

// 同步到MySQL
try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'db_user', 'db_password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $pdo->exec("TRUNCATE TABLE your_table_name");

    $stmt = $pdo->prepare("INSERT INTO your_table_name (col1, col2, col3) VALUES (?, ?, ?)");
    foreach ($values as $row) {
        $row = array_pad($row, 3, null);
        $stmt->execute($row);
    }
} catch (PDOException $e) {
    error_log("MySQL同步失败: " . $e->getMessage());
    exit(1);
}

error_log("数据同步完成，共同步 " . count($values) . " 行数据");

额外注意事项 #

• 服务账号权限：确保服务账号已被添加为Google表格的协作者（编辑权限），否则无法获取最新数据。
• Cron任务环境：确保Cron执行时的工作目录正确，myserviceaccount.json路径可访问，依赖包已安装。
• 数据一致性：如果需要增量同步，建议在表格中添加“更新时间”列，仅同步上次同步后更新的数据，避免全量清空。

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