如何将PHP端Google Sheets API v3迁移至最新V4版本

前置准备 #

• 前往Google Cloud控制台，找到原有项目，开启Google Sheets API v4，如果需要拉取用户名下所有表格清单，还需要同时开启Google Drive API
• 原有OAuth授权范围可直接复用，确保包含https://www.googleapis.com/auth/spreadsheets即可，已授权的用户不需要重新授权
• 安装官方PHP客户端：执行命令composer require google/apiclient:^2.0

核心代码迁移 #

你原有代码中token生成、过期刷新的逻辑可以完全保留，只需要替换v3专属的Google\Spreadsheet系列类的调用逻辑即可，以下是对应功能的重构实现：

if(!$accessToken){
    $auth_url = $this->client->createAuthUrl();
} else {
    try{
        $this->client->setAccessToken($accessToken);
        $token = json_decode($accessToken);

        if ($this->client->isAccessTokenExpired()) {
            $this->client->refreshToken($token->refresh_token);
            $tok = json_encode($this->client->getAccessToken());
            $token = json_decode($tok);
            $db->setQuery("Update #__breezingforms_addons_gdata set password = " . $db->quote($tok) . " Where form_id = " . intval($form_id));
            $db->execute();
        }

        // 初始化v4版本的Sheets服务、Drive服务
        $sheetsService = new Google\Service\Sheets($this->client);
        $driveService = new Google\Service\Drive($this->client);
        
        // 1. 替代原getSpreadsheets()获取用户所有电子表格列表
        $gdata_spreadsheets = [];
        $optParams = [
            'q' => "mimeType='application/vnd.google-apps.spreadsheet'",
            'fields' => 'files(id, name)',
            'pageSize' => 1000
        ];
        $files = $driveService->files->listFiles($optParams);
        foreach ($files->getFiles() as $file) {
            $gdata_spreadsheets[$file->id] = $file->name;
        }
        $spreadsheetFeed = !empty($gdata_spreadsheets);
        
    }catch(Exception $ee){
        $error=$ee->getMessage();
    }
}

if($gdata->spreadsheet_id != '' && isset( $gdata_spreadsheets[$gdata->spreadsheet_id] ) && $spreadsheetFeed !== null){
    // 2. 替代原getWorksheets()获取指定表格的所有工作表
    $spreadsheet = $sheetsService->spreadsheets->get($gdata->spreadsheet_id);
    $worksheetFeed = $spreadsheet->getSheets();
    $gdata_worksheets = [];
    foreach ($worksheetFeed as $sheet) {
        $gdata_worksheets[$sheet->properties->sheetId] = $sheet->properties->title;
    }
    
    if($gdata->worksheet_id != '' && isset( $gdata_worksheets[$gdata->worksheet_id] )){
        $worksheetTitle = $gdata_worksheets[$gdata->worksheet_id];
        // 3. 替代原getCellFeed()获取第一行表头数据
        $range = $worksheetTitle . '!1:1';
        $response = $sheetsService->spreadsheets_values->get($gdata->spreadsheet_id, $range);
        $gdata_columns = $response->getValues()[0] ?? [];
    }
}
} catch(Exception $e){
    $error = $e->getMessage();
}

原有方法对应替换说明 #

• 原getSpreadsheetById($id)：直接调用$sheetsService->spreadsheets->get($id)即可获取对应表格实例
• 原getCellFeed($worksheetId)：拼接对应工作表的单元格范围作为参数，调用$sheetsService->spreadsheets_values->get(表格ID, 范围)即可返回指定区域的所有单元格内容
• 原getListFeed($worksheetId)：调用spreadsheets_values->get时指定范围为整张工作表，返回的values数组就是按行排列的列表数据，和原ListFeed功能一致

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