2017 © Pedro Peláez
 

library laravel-google-sheets

Google Sheets API v4

image

revolution/laravel-google-sheets

Google Sheets API v4

  • Thursday, June 28, 2018
  • by revolution
  • Repository
  • 3 Watchers
  • 55 Stars
  • 37,345 Installations
  • PHP
  • 0 Dependents
  • 0 Suggesters
  • 13 Forks
  • 10 Open issues
  • 23 Versions
  • 23 % Grown

The README.md

Google Sheets API v4 for Laravel

packagist Maintainability Test Coverage, (*1)

Requirements

  • PHP >= 8.2
  • Laravel >= 11.0

Versioning

  • Basic : semver
  • Drop old PHP or Laravel version : +0.1. composer should handle it well.
  • Support only latest major version (master branch), but you can PR to old branches.

Installation

Composer

composer require revolution/laravel-google-sheets

Laravel

  1. Run php artisan vendor:publish --tag="google-config" to publish the google config file, (*2)

    // 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. Get API Credentials from https://developers.google.com/console
    Enable Google Sheets API, Google Drive API., (*3)

  3. Configure .env as needed, (*4)

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

Demo

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

Another Google API Series. - https://github.com/kawax/laravel-google-photos - https://github.com/kawax/laravel-google-searchconsole, (*5)

Select auth type

You must select an authentication type and configure it appropriately., (*6)

  • Service Account : Access to only your own spreadsheets.
  • OAuth : Access to user's spreadsheets.
  • API key: Access to public spreadsheets.

Usage

id name mail
1 name1 mail1
2 name2 mail2

https://docs.google.com/spreadsheets/d/{spreadsheetID}/..., (*7)

Basic Laravel Usage

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']
// ]

Basic Non-Laravel Usage

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();

Get a sheet's values with the header as the key

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, (*8)

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

Using A1 Notation

use Revolution\Google\Sheets\Facades\Sheets;

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

Updating a specific range

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']
// ]

Append a set of values to a sheet

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']
// ]

Append a set of values with keys

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'],
// ]

Add a new sheet

use Revolution\Google\Sheets\Facades\Sheets;

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

Deleting a sheet

use Revolution\Google\Sheets\Facades\Sheets;

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

Specifying query parameters

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, (*9)

Use original Google_Service_Sheets

use Revolution\Google\Sheets\Facades\Sheets;

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

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

see https://github.com/google/google-api-php-client-services/blob/master/src/Google/Service/Sheets.php, (*10)

LICENSE

MIT
Copyright kawax, (*11)

The Versions

28/06 2018

dev-master

9999999-dev

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

24/06 2018

3.2.7

3.2.7.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

17/05 2018

3.2.6

3.2.6.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

15/05 2018

3.2.5

3.2.5.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

13/05 2018

3.2.4

3.2.4.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

13/05 2018

3.2.3

3.2.3.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

12/05 2018

3.2.2

3.2.2.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

12/05 2018

3.2.1

3.2.1.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

11/05 2018

3.2.0

3.2.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

27/01 2018

3.1.1

3.1.1.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

12/11 2017

3.1.0

3.1.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

05/09 2017

3.0.0

3.0.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

05/09 2017

2.3.4

2.3.4.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

04/08 2017

2.3.3

2.3.3.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

30/05 2017

2.3.2

2.3.2.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

27/03 2017

2.3.1

2.3.1.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

23/02 2017

2.3.0

2.3.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

04/10 2016

2.1.0

2.1.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

14/08 2016

2.0.0

2.0.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

01/07 2016

1.0.3

1.0.3.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

26/06 2016

1.0.2

1.0.2.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

26/06 2016

1.0.1

1.0.1.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets

26/06 2016

1.0.0

1.0.0.0

Google Sheets API v4

  Sources   Download

MIT

The Requires

 

The Development Requires

by kawax

laravel google sheets