PHP Google Spreadsheet Integration via CLI

README.md

Usage

• Run composer install
• Get crednetials: go to https://console.developers.google.com/apis/credentials and get "OAuth ClientId" credentials.
• Follow instructions below
• Change code as you desire

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:

composer.json

{
  "require": {
    "google/apiclient": "^2.0" 
  }
}

example.php

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