Skip to content

Instantly share code, notes, and snippets.

@Shaked Shaked/README.md
Last active Feb 3, 2018

Embed
What would you like to do?
PHP Google Spreadsheet Integration via CLI

Usage

Instructions

Example URL: https://spreadsheets.google.com/feeds/cells/1jMe4AJIBBBowXv3WWgLTNcRaPKmAkFF3pc1C7_sib9WAAA/2/public/basic?alt=json spreadsheetId is taken from the link ^, e.g: $spreadsheetId = '1jMe4AJIBBBowXv3WWgLTNcRaPKmAkFF3pc1C7_sib9WAAA'

Run with:

$ php example.php --spreadsheetId="13U34uMCFA6VGaZEVAjA2FFFFetdNc-AAAAftn5d7I0Eg" --clientSecretPath="/tmp/clientSecret"

First run from cli will require to open the browser. See message:

$ php example.php --spreadsheetId="13U34uMCFA6VGaZEVAjA2FFFFetdNc-AAAAftn5d7I0Eg" --clientSecretPath="/tmp/clientSecret";
Open the following link in your browser:
https://accounts.google.com/o/oauth2/auth?response_type=code&.......
Enter verification code:
{
"require": {
"google/apiclient": "^2.0"
}
}
<?php
require_once 'vendor/autoload.php';
/**
* @param $spreadsheetId
* @return mixed
*/
function getWorksheets($config, $spreadsheetId) {
$client = getClient($config);
$service = new \Google_Service_Sheets($client);
$response = $service->spreadsheets->get($spreadsheetId);
$worksheets = [];
foreach ($response['sheets'] as $sheet) {
$worksheets[] = $sheet->getProperties()->getTitle();
}
return $worksheets;
}
/**
* @return mixed
*/
function getClient($config) {
$scopes = implode(' ', [
\Google_Service_Sheets::SPREADSHEETS_READONLY]
);
$client = new \Google_Client();
$client->setApplicationName($config['appName']);
$client->setScopes($scopes);
$client->setAuthConfig($config['clientSecretPath']);
$client->setAccessType('offline');
// Load previously authorized credentials from a file.
$credentialsPath = $config['credentialsPath'];
if (file_exists($credentialsPath)) {
$accessToken = json_decode(file_get_contents($credentialsPath), true);
} else {
// Request authorization from the user.
$authUrl = $client->createAuthUrl();
printf("Open the following link in your browser:\n%s\n", $authUrl);
print 'Enter verification code: ';
$authCode = trim(fgets(STDIN));
// Exchange authorization code for an access token.
$accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
// Store the credentials to disk.
if (!file_exists(dirname($credentialsPath))) {
mkdir(dirname($credentialsPath), 0700, true);
}
file_put_contents($credentialsPath, json_encode($accessToken));
printf("Credentials saved to %s\n", $credentialsPath);
}
$client->setAccessToken($accessToken);
// Refresh the token if it's expired.
if ($client->isAccessTokenExpired()) {
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
file_put_contents($credentialsPath, json_encode($client->getAccessToken()));
}
return $client;
}
/**
* @param $spreadsheetId
* @param $worksheetName
* @param $range
* @param array $options
* @return mixed
*/
function getDataBy($config, $spreadsheetId, $worksheetName, $range, $options = []) {
$client = getClient($config);
$service = new \Google_Service_Sheets($client);
$range = $worksheetName . '!' . $range;
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
$r = [];
if ($values) {
$headers = array_shift($values);
foreach ($values as $key => $value) {
$v = [];
foreach ($headers as $hk => $hv) {
if (isset($options['removeSpaces'])) {
$hv = str_replace(' ', '', $hv);
}
if (isset($options['lcfirst'])) {
$hv = lcfirst($hv);
}
$v[$hv] = $value[$hk] ?? '';
}
$r[] = $v;
}
}
return $r;
}
$config = [
'appName' => 'Google Spreadsheet',
'credentialsPath' => '/tmp/sheets.googleapis.com-php-quickstart.json',
];
$o = getopt('', ['spreadsheetId:', 'clientSecretPath:']);
$config['clientSecretPath'] = $o['clientSecretPath'];
$spreadsheetId = $o['spreadsheetId'];
$worksheets = getWorksheets($config, $spreadsheetId);
$data = [];
$range = 'A:Z';
foreach ($worksheets as $key => $worksheetName) {
$data[] = getDataBy($config, $spreadsheetId, $worksheetName, $range);
}
var_dump($data);
die(PHP_EOL);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.