ksenonda/my-g-s

Google spreadsheet client

dev-main 2021-06-01 08:51 UTC

Requires

• google/apiclient: 2.*

MIT

• ksenonda

README

Google Spreadsheet Client for PHP. This requires 'google/apiclient' package.

Get started

1. Get key file

1. Log in Google Developer Console
2. Create new project
3. Create Service Account credentials in the project
4. Download key file as JSON

2. Create spreadsheet

1. Create a new spreadsheet in Google Drive
2. Authorize the email address, which is found as 'client_email' in key file, to read and edit.
3. Save the file ID from address bar.

3. Access by PHP

$client = Google_Spreadsheet::getClient('the/path/to/credential.json');
// Get the sheet instance by sheets_id and sheet name
$sheet = $client->file('XXXxxxXXXXxxxXXXX')->sheet('Sheet1');
// Fetch data from remote (or cache)
$sheet->fetch();
// Flush all rows in the sheet
var_dump($sheet->items);

Usage

Initialize sheet

The target sheet must be empty

$sheet->init(array(
  'id',
  'name',
  'age',
  'email',
  'note'
));

Select rows

// Array
$items = $sheet->select(array(
  'id' => '1'
));
// Closure
$items = $sheet->select(function($row){
  return (int) $row['age'] < 30;
});

Insert a new row

// Insert a new row
$sheet->insert(array(
  'name' => 'John',
  'age' => 23,
  'email' => '[e-mail]'
));

// Get up-to-date items
$items = $sheet->fetch(true)->items;

Update rows

// Update rows selected by array
$sheet->update(
  array(
    'email' => '[e-mail]'
  ),
  array(
    'name' => 'Tom'
  )
);

// Update rows selected by closure
$sheet->update(
  array(
    'email' => '[e-mail]'
  ),
  function($row){
    return $row['name'] === 'Tom';
  }
);

// Get up-to-date items
$items = $sheet->fetch(true)->items;

Delete rows

// delete rows selected by array
$sheet->delete(
  array(
    'name' => 'Tom'
  )
);
//delete rows selected by closure
$sheet->delete(
  function($row){
    return $row['name'] === 'Tom';
  }
);

// Get up-to-date items
$items = $sheet->fetch(true)->items;

Update cells

edit method let you to update cells' value manually

// Update `B2` cell
$sheet->edit(2, 2, 'Tom');

// Update `C1:C4` cells
$sheet->edit(3, 1, array(1, 'John', 23, '[e-mail]'));

Get up-to-date table data

// Pass `true` to ignore cache
$items = $sheet->fetch(true)->items;

Save cache option

$sheet->config(array(
  'cache' => true,
  'cache_dir' => __DIR__ . '/cache',
  'cache_expires' => 360
));

Requirement

• google/apiclient (Apache License v2.0)