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并创建一个新项目:

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

 

点击Enable APIs and Service,搜索Sheets API,启用服务:

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

 

启用后,我们可以配置服务:

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

 

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

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

 

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

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

 

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

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

 

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

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

 

创建和共享 Google表格

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

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

 

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

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

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

 

安装和配置 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

 

 

 

 

Add a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.