修改OAuth范围后现有用户Google Sheet追加行报无效凭证错误

我们在PHP(v8)、Laravel(v9)应用中集成Google Sheets，原本运行正常，将OAuth范围从https://www.googleapis.com/auth/gmail.metadata修改为https://www.googleapis.com/auth/userinfo.email后出现问题：

目前追加行和表头时失败，抛出401错误，使用的包为google-api-php-client。

错误日志：

[2024-02-27 16:44:50] local.EMERGENCY: File:/var/www/html/projects/ProjectName/vendor/google/apiclient/src/Http/REST.phpLine:134Message:{
  "error": {
    "code": 401,
    "message": "Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
    "errors": [
      {
        "message": "Invalid Credentials",
        "domain": "global",
        "reason": "authError",
        "location": "Authorization",
        "locationType": "header"
      }
    ],
    "status": "UNAUTHENTICATED"
  }
}

已在Google开发者控制台和本地配置中更新了范围与凭证，新用户可正常完成认证、创建表格并通过API追加行和表头；但现有用户操作现有表格追加行时，会抛出上述错误。

相关代码 #

1. 用户认证代码

/*
* authenticating user using oauth for creating spreadsheet
*/
public function authorizeService(Request $request)
{
    try {

        $client = new Client();
        $client->setClientId(config('constants.GOOGLE_CLIENT_ID'));
        $client->setClientSecret(config('constants.GOOGLE_CLIENT_SECRET'));
        $client->setRedirectUri(config('constants.GOOGLE_SERVICE_INTEGRATION_CALLBACK'));
        $client->setAccessType('offline');
        $client->setApprovalPrompt("force");
        $client->setScopes([
            'https://www.googleapis.com/auth/spreadsheets',
            'https://www.googleapis.com/auth/userinfo.email',
        ]);
        $client->setState(base64_encode(json_encode([
            'form_name' => $request->get('form_name'),
            'form_id' => $request->get('form_id'),
            'service' => $request->get('service')
        ])));

        $authUrl = $client->createAuthUrl();

        return $this->respondSuccess(__('form.success'), ['authUrl' => $authUrl]);

    } catch (Exception $e) {
        \Log::emergency("File:" . $e->getFile(). "Line:" . $e->getLine(). "Message:" . $e->getMessage());
        return $this->respondWentWrong($e);
    }
}

2. 客户端初始化代码

/*
* initializing client
*/
public function initializeClient($integration)
{
    $accessToken = trim($integration->info['token']['access_token'] ?? '');
    $refresh_token = trim($integration->gmailAccount->refresh_token ?? '');

    $this->client = new Client();
    $this->client->setClientId(config('constants.GOOGLE_CLIENT_ID'));
    $this->client->setClientSecret(config('constants.GOOGLE_CLIENT_SECRET'));
    $this->client->setAccessType('offline');
    $this->client->setApprovalPrompt("force");
    $this->client->setAccessToken($accessToken);

    if ($this->client->isAccessTokenExpired()) {

        $this->client->setAccessToken($refresh_token);
        $this->client->fetchAccessTokenWithRefreshToken($refresh_token);
        $accessTokenUpdated = $this->client->getAccessToken();
        $this->client->setAccessToken($accessTokenUpdated);

        /*
        * update token
        */
        $info = $integration->info;
        $info['token'] = $accessTokenUpdated;
        $integration->info = $info;
        $integration->save();
    }
}

3. 追加行代码

/*
* appending rows
*/
public function appendRowsOnSpreadsheet($integration, $submission)
{
    try {

        $this->initializeClient($integration);
        $spreadsheetId = $integration->info['spreadsheetId'] ?? '';
        $accessToken = trim($integration->info['token']['access_token'] ?? '');

        /*
        * Define the range where you want to append data
        * (A2:C appends to columns A, B, C, starting from row 2)
        */
        $range = 'Sheet1';

        /*
        * Create the request body
        */
        $requestBody = [
            'values' => [
                $submission
            ]
        ];

        /*
        * Create the Guzzle HTTP client
        */
        $guzzleClient = new GuzzleClient();

        /*
        * Prepare the URL for appending data
        */
        $url = "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append";

        /*
        * Prepare the headers
        */
        $headers = [
            'Authorization' => 'Bearer ' . $accessToken,
            'Content-Type' => 'application/json',
        ];

        /*
        * Make the API request to append data
        */
        $response = $guzzleClient->post($url, [
            'headers' => $headers,
            'json' => $requestBody,
            'query' => [
                'valueInputOption' => 'RAW'
            ],
        ]);

        $responseData = json_decode($response->getBody(), true);

        $output = [
            'success' => true,
            'msg' => __('form.success'),
            'response' => $responseData
        ];
    } catch (Exception $e) {
        \Log::emergency("File:" . $e->getFile(). "Line:" . $e->getLine(). "Message:" . $e->getMessage());
        $output = [
            'success' => false,
            'msg' => $e->getMessage()
        ];
    }
    return $output;
}

• 强制现有用户重新授权：现有用户的旧token基于原OAuth范围生成，修改范围后权限不匹配。需要引导用户重新走OAuth授权流程，获取包含新范围的token和refresh token。可在用户操作时检测token权限，若不包含当前所需的spreadsheets和userinfo.email，则跳转授权页面。

• 修复客户端初始化的token逻辑：initializeClient方法中，token过期时错误地将refresh token直接设为access token，导致请求用无效token调用API。修改后的代码：

  if ($this->client->isAccessTokenExpired()) {
      $this->client->setRefreshToken($refresh_token);
      $accessTokenUpdated = $this->client->fetchAccessTokenWithRefreshToken();
      $this->client->setAccessToken($accessTokenUpdated);
  
      // 更新数据库中的token
      $info = $integration->info;
      $info['token'] = $accessTokenUpdated;
      // 若返回新的refresh token，同步更新
      if (isset($accessTokenUpdated['refresh_token'])) {
          $integration->gmailAccount->refresh_token = $accessTokenUpdated['refresh_token'];
          $integration->gmailAccount->save();
      }
      $integration->info = $info;
      $integration->save();
  }
  

• 改用Google Client内置服务调用API：appendRowsOnSpreadsheet中已初始化Google Client，却用Guzzle直接发请求，绕开了Client的token管理逻辑。建议直接使用Sheets服务类：

  // 替换原Guzzle请求部分
  $service = new \Google\Service\Sheets($this->client);
  $response = $service->spreadsheets_values->append(
      $spreadsheetId,
      $range,
      new \Google\Service\Sheets\ValueRange(['values' => $requestBody['values']]),
      ['valueInputOption' => 'RAW']
  );
  $responseData = $response->toSimpleObject();
  

• 检查现有用户token有效性：对于现有用户，检查数据库中保存的access token和refresh token是否有效。若refresh token已失效（如用户撤销权限），则必须引导重新授权。可在初始化客户端时捕获Google_Service_Exception，判断是否为token无效错误，触发重新授权流程。

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