PHP操作Google Sheets, PHP 读写 Google 表格, Google Sheets API, google/apiclient, Reading and Writing Google Sheets in PHP

Google Sheets 是一个非常常用的在线电子表格系统，允许对数据进行实时协作。让我们学习如何使用 PHP API 客户端操作 Google 表格。

整个代码和示例可在 GitHub 存储库中获得；它被打包为一个简单的 Symfony 项目，并提供了一个 Docker 镜像。

创建 Google 项目并配置 Sheets API

首先，让我们配置一个新的 Google Console 项目以启用 Sheets API。

打开Google Cloud Console并创建一个新项目：

点击Enable APIs and Service，搜索Sheets API，启用服务：

启用后，我们可以配置服务：

我们现在创建凭据，在本教程中，我们将使用Application data访问权限：

我们需要创建一个服务帐户（生成的电子邮件稍后会有用💡）：

我们使用以下类型编辑服务帐户以创建新密钥json：

我们现在可以在本地下载密钥文件并将其重命名为credentials.json.

有点繁琐的配置部分就结束了！

创建和共享 Google表格

我们可以创建一个 Google Sheets 文档。在本教程中，我使用了一系列电影：

一个电子表格可以包含多个工作表；这个有一张名为Sheet1.

我们必须与之前生成的服务帐户电子邮件共享此文档（我们设置Editor权限以允许更新工作表）：

安装和配置 PHP客户端

让我们安装 Google PHP API 客户端：

composer require google/apiclient

客户端配置有credentials.json：

// configure the Google Client
$client = new \Google_Client();
$client->setApplicationName('Google Sheets API');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
// credentials.json is the key file we downloaded while setting up our Google Sheets API
$path = 'data/credentials.json';
$client->setAuthConfig($path);

// configure the Sheets Service
$service = new \Google_Service_Sheets($client);

获取电子表格 Get the Spreadsheet

我们使用 Sheets 服务来检索Spreadsheet对象：

// the spreadsheet id can be found in the url https://docs.google.com/spreadsheets/d/143xVs9lPopFSF4eJQWloDYAndMor/edit
$spreadsheetId = '143xVs9lPopFSF4eJQWloDYAndMor';
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
var_dump($spreadsheet);

获取工作表 Fetch All the Rows of a Sheet

我们读取给定工作表的所有行：

// get all the rows of a sheet
$range = 'Sheet1'; // here we use the name of the Sheet to get all the rows
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);

结果：

array(10) {
  [0]=>
  array(6) {
    [0]=>
    string(2) "id"
    [1]=>
    string(5) "title"
    [2]=>
    string(6) "poster"
    [3]=>
    string(8) "overview"
    [4]=>
    string(12) "release_date"
    [5]=>
    string(6) "genres"
  }
  [1]=>
  array(6) {
    [0]=>
    string(6) "287947"
    [1]=>
    string(7) "Shazam!"
    [2]=>
    string(63) "https://image.tmdb.org/t/p/w500/xnopI5Xtky18MPhK40cZAGAOVeV.jpg"
    [3]=>
    string(98) "A boy is given the ability to become an adult superhero in times of need with a single magic word."
    [4]=>
    string(10) "1553299200"
    [5]=>
    string(23) "Action, Comedy, Fantasy"
  }
  ... more lines
}

获取表格区间 Fetch a Few Rows by Using a Range

我们阅读了 Google 表格的前十行：

// we define here the expected range, columns from A to F and lines from 1 to 10
$range = 'Sheet1!A1:F10';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);

仅获取指定列 Fetch Only Cells of a Given Column

我们读取给定列的单元格以避免获取所有内容：

$range = 'Sheet1!B1:B21'; // the column containing the movie title
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);

将行转换为 JSON对象 Convert Rows into JSON Objects

在许多情况下，将每一行作为一个独立的对象来操作会更容易。让我们将每一行转换为一个关联数组。

// Fetch the rows
$range = 'Sheet1';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$rows = $response->getValues();
// Remove the first one that contains headers
$headers = array_shift($rows);
// Combine the headers with each following row
$array = [];
foreach ($rows as $row) {
    $array[] = array_combine($headers, $row);
}
var_dump($array);

结果是：

array(21) {
  [0]=>
  array(6) {
    ["id"]=>
    string(6) "287947"
    ["title"]=>
    string(7) "Shazam!"
    ["poster"]=>
    string(63) "https://image.tmdb.org/t/p/w500/xnopI5Xtky18MPhK40cZAGAOVeV.jpg"
    ["overview"]=>
    string(98) "A boy is given the ability to become an adult superhero in times of need with a single magic word."
    ["release_date"]=>
    string(10) "1553299200"
    ["genres"]=>
    string(23) "Action, Comedy, Fantasy"
  }
  [1]=>
  array(6) {
    ["id"]=>
    string(6) "299537"
    ["title"]=>
    string(14) "Captain Marvel"
    ["poster"]=>
    string(63) "https://image.tmdb.org/t/p/w500/AtsgWhDnHTq68L0lLsUrCnM7TjG.jpg"
    ["overview"]=>
    string(307) "The story follows Carol Danvers as she becomes one of the universe’s most powerful heroes when Earth is caught in the middle of a galactic war between two alien races. Set in the 1990s, Captain Marvel is an all-new adventure from a previously unseen period in the history of the Marvel Cinematic Universe."
    ["release_date"]=>
    string(10) "1551830400"
    ["genres"]=>
    string(34) "Action, Adventure, Science Fiction"
  }
  // ... more rows
}

这种新结构允许我们将每一行作为特定的 JSON 对象进行操作，应用转换或流式处理。

我们还可以通过一行代码将其转换为 JSON 字符串。

$jsonString = json_encode($array, JSON_PRETTY_PRINT);
print($jsonString);

[
    {
        "id": "287947",
        "title": "Shazam!",
        "poster": "https:\/\/image.tmdb.org\/t\/p\/w500\/xnopI5Xtky18MPhK40cZAGAOVeV.jpg",
        "overview": "A boy is given the ability to become an adult superhero in times of need with a single magic word.",
        "release_date": "1553299200",
        "genres": "Action, Comedy, Fantasy"
    },
    {
        "id": "299537",
        "title": "Captain Marvel",
        "poster": "https:\/\/image.tmdb.org\/t\/p\/w500\/AtsgWhDnHTq68L0lLsUrCnM7TjG.jpg",
        "overview": "The story follows Carol Danvers as she becomes one of the universe\u2019s most powerful heroes when Earth is caught in the middle of a galactic war between two alien races. Set in the 1990s, Captain Marvel is an all-new adventure from a previously unseen period in the history of the Marvel Cinematic Universe.",
        "release_date": "1551830400",
        "genres": "Action, Adventure, Science Fiction"
    },
...

追加新行 Append a New Row

我们在工作表的末尾写一个新行：

$newRow = [
    '456740',
    'Hellboy',
    'https://image.tmdb.org/t/p/w500/bk8LyaMqUtaQ9hUShuvFznQYQKR.jpg',
    "Hellboy comes to England, where he must defeat Nimue, Merlin's consort and the Blood Queen. But their battle will bring about the end of the world, a fate he desperately tries to turn away.",
    '1554944400',
    'Fantasy, Action'
];
$rows = [$newRow]; // you can append several rows at once
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1'; // the service will detect the last row of this sheet
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options);

使用USER_ENTEREDasvalueInputOption服务时，解析数据的方式与您直接在 Google 表格 UI 中输入数据时的方式相同。这意味着字符串将根据其格式转换为日期、链接等。

我们还可以使用RAW输入选项来保持数据不变。

更新现有行 Update an Existing Row

我们将现有行替换为其单元格的新值：

$updateRow = [
    '456740',
    'Hellboy Updated Row',
    'https://image.tmdb.org/t/p/w500/bk8LyaMqUtaQ9hUShuvFznQYQKR.jpg',
    "Hellboy comes to England, where he must defeat Nimue, Merlin's consort and the Blood Queen. But their battle will bring about the end of the world, a fate he desperately tries to turn away.",
    '1554944400',
    'Fantasy, Action'
];
$rows = [$updateRow];
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1!A2'; // where the replacement will start, here, first column and second line
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $options);

同时更新多行

$updateRow = [
    ['11', '12', '13'],
    ['21', '22', '23']
];
$valueRange = new \Google_Service_Sheets_ValueRange(['values' => $updateRow]);
$range = 'Sheet1!A2'; // where the replacement will start, here, first column and second line
$params = ['valueInputOption' => 'RAW'];
$service->spreadsheets_values->update($spreadSheetId, $range, $valueRange, $params);

删除一些行 Delete Some Rows

我们通过指定要清除的单元格范围来删除一些行：

$range = 'Sheet1!A23:F24'; // the range to clear, the 23th and 24th lines
$clear = new \Google_Service_Sheets_ClearValuesRequest();
$service->spreadsheets_values->clear($spreadsheetId, $range, $clear);

下载代码和示例

您可以在此GitHub 存储库中找到所有代码和示例。

它被打包为一个简单的 Symfony 项目，一组命令，它还附带一个 Docker 映像。🐋

更多参考

本文：PHP操作Google Sheets, PHP 读写 Google 表格, Google Sheets API, google/apiclient, Reading and Writing Google Sheets in PHP