README

要求

• PHP >= 8.2
• Laravel >= 11.0

版本控制

• 基本: semver
• 删除旧PHP或Laravel版本: +0.1. composer 应能很好地处理。
• 仅支持最新主要版本（master分支），但您可以向旧分支提交PR。

安装

Composer

composer require revolution/laravel-google-sheets

Laravel

1. 运行 php artisan vendor:publish --tag="google-config" 以发布Google配置文件

    // config/google.php
   
    // OAuth
    'client_id'        => env('GOOGLE_CLIENT_ID', ''),
    'client_secret'    => env('GOOGLE_CLIENT_SECRET', ''),
    'redirect_uri'     => env('GOOGLE_REDIRECT', ''),
    'scopes'           => [\Google\Service\Sheets::DRIVE, \Google\Service\Sheets::SPREADSHEETS],
    'access_type'      => 'online',
    'approval_prompt'  => 'auto',
    'prompt'           => 'consent', //"none", "consent", "select_account" default:none
   
    // or Service Account
    'file'    => storage_path('credentials.json'),
    'enable'  => env('GOOGLE_SERVICE_ENABLED', true),
   

2. 从 https://developers.google.com/console 获取API凭证
   启用 Google Sheets API、Google Drive API。

3. 根据需要配置 .env

    GOOGLE_APPLICATION_NAME=
    GOOGLE_CLIENT_ID=
    GOOGLE_CLIENT_SECRET=
    GOOGLE_REDIRECT=
    GOOGLE_DEVELOPER_KEY=
    GOOGLE_SERVICE_ENABLED=
    GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=
   

演示

• https://github.com/kawax/google-sheets-project
• https://sheets.kawax.biz/

另一个Google API系列。

• https://github.com/kawax/laravel-google-photos
• https://github.com/kawax/laravel-google-searchconsole

选择身份验证类型

您必须选择一种身份验证类型并相应地配置它。

• 服务账户: 仅访问您自己的电子表格。
• OAuth: 访问用户的电子表格。
• API密钥: 访问公开电子表格。

使用方法

https://docs.google.com/spreadsheets/d/{spreadsheetID}/...

基本Laravel使用方法

use Revolution\Google\Sheets\Facades\Sheets;

$user = $request->user();

$token = [
      'access_token'  => $user->access_token,
      'refresh_token' => $user->refresh_token,
      'expires_in'    => $user->expires_in,
      'created'       => $user->updated_at->getTimestamp(),
];

// all() returns array
$values = Sheets::setAccessToken($token)->spreadsheet('spreadsheetId')->sheet('Sheet 1')->all();
// [
//   ['id', 'name', 'mail'],
//   ['1', 'name1', 'mail1'],
//   ['2', 'name1', 'mail2']
// ]

基本非Laravel使用方法

use Google\Client;
use Revolution\Google\Sheets\SheetsClient;

$client = new Client();
$client->setScopes([Google\Service\Sheets::DRIVE, Google\Service\Sheets::SPREADSHEETS]);
// setup Google Client
// ...

$service = new \Google\Service\Sheets($client);

$sheets = new SheetsClient();
$sheets->setService($service);

$values = $sheets->spreadsheet('spreadsheetID')->sheet('Sheet 1')->all();

使用标题作为键获取工作表的值

use Revolution\Google\Sheets\Facades\Sheets;

// get() returns Laravel Collection
$rows = Sheets::sheet('Sheet 1')->get();

$header = $rows->pull(0);
$values = Sheets::collection(header: $header, rows: $rows);
$values->toArray()
// [
//   ['id' => '1', 'name' => 'name1', 'mail' => 'mail1'],
//   ['id' => '2', 'name' => 'name2', 'mail' => 'mail2']
// ]

Blade

@foreach($values as $value)
  {{ data_get($value, 'name') }}
@endforeach

使用A1表示法

use Revolution\Google\Sheets\Facades\Sheets;

$values = Sheets::sheet('Sheet 1')->range('A1:B2')->all();
// [
//   ['id', 'name'],
//   ['1', 'name1'],
// ]

更新特定范围

use Revolution\Google\Sheets\Facades\Sheets;

Sheets::sheet('Sheet 1')->range('A4')->update([['3', 'name3', 'mail3']]);
$values = Sheets::range('')->all();
// [
//   ['id', 'name', 'mail'],
//   ['1', 'name1', 'mail1'],
//   ['2', 'name1', 'mail2'],
//   ['3', 'name3', 'mail3']
// ]

将一组值追加到工作表

use Revolution\Google\Sheets\Facades\Sheets;

// When we don't provide a specific range, the sheet becomes the default range
Sheets::sheet('Sheet 1')->append([['3', 'name3', 'mail3']]);
$values = Sheets::all();
// [
//   ['id', 'name', 'mail'],
//   ['1', 'name1', 'mail1'],
//   ['2', 'name1', 'mail2'],
//   ['3', 'name3', 'mail3']
// ]

带键的值追加到工作表

use Revolution\Google\Sheets\Facades\Sheets;

// When providing an associative array, values get matched up to the headers in the provided sheet
Sheets::sheet('Sheet 1')->append([['name' => 'name4', 'mail' => 'mail4', 'id' => 4]]);
$values = Sheets::all();
// [
//   ['id', 'name', 'mail'],
//   ['1', 'name1', 'mail1'],
//   ['2', 'name1', 'mail2'],
//   ['3', 'name3', 'mail3'],
//   ['4', 'name4', 'mail4'],
// ]

添加新工作表

use Revolution\Google\Sheets\Facades\Sheets;

Sheets::spreadsheetByTitle($title)->addSheet('New Sheet Title');

删除工作表

use Revolution\Google\Sheets\Facades\Sheets;

Sheets::spreadsheetByTitle($title)->deleteSheet('Old Sheet Title');

指定查询参数

use Revolution\Google\Sheets\Facades\Sheets;

$values = Sheets::sheet('Sheet 1')->majorDimension('DIMENSION_UNSPECIFIED')
                                  ->valueRenderOption('FORMATTED_VALUE')
                                  ->dateTimeRenderOption('SERIAL_NUMBER')
                                  ->all();

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get#query-parameters

使用原始Google_Service_Sheets

use Revolution\Google\Sheets\Facades\Sheets;

$sheets->spreadsheets->...
$sheets->spreadsheets_sheets->...
$sheets->spreadsheets_values->...

Sheets::getService()->spreadsheets->...

查看 https://github.com/google/google-api-php-client-services/blob/master/src/Google/Service/Sheets.php

许可证

MIT
版权所有 kawax