GoogleSpreadsheetの中身をAPIで取得

# ここに書いてあることをStep2までやる

https://developers.google.com/sheets/quickstart/php

![image.png](https://qiita-image-store.s3.amazonaws.com/0/1642/ec85a29c-699e-8fd8-7f8d-561d6547a038.png)

# クライアントクラスを作る

`APPLICATION_NAME` は適宜変える（上のStep3の変奏）
`PEM_FILE`は以下から取得
http://curl.haxx.se/ca/cacert.pem

```php:SpreadsheetClient.php:php

<?php

require_once dirname(__FILE__).'/../vendor/autoload.php';

define('APPLICATION_NAME', 'rogue golf data');
define('SCOPES', implode(' ', [
  Google_Service_Sheets::SPREADSHEETS_READONLY
]));
define('CLIENT_SECRET_PATH', dirname(__FILE__).'/client_secret.json');
define('CREDENTIALS_PATH', dirname(__FILE__).'/credentials.json');
define('PEM_FILE', dirname(__FILE__).'/cacert.pem');


class SpreadsheetClient
{
    protected static $_client = null;

    public static function getClient($isForceCreate = false)
    {
        if (!$isForceCreate && !is_null(static::$_client)) return static::$_client;

        $http = new \GuzzleHttp\Client([
            'verify' => PEM_FILE,
        ]);
        $client = new Google_Client();
        $client->setHttpClient($http);

        $client->setApplicationName(APPLICATION_NAME);
        $client->setScopes(SCOPES);
        $client->setAuthConfig(CLIENT_SECRET_PATH);
        $client->setAccessType('offline');

        // Load previously authorized credentials from a file.
        $credentialsPath = static::expandHomeDirectory(CREDENTIALS_PATH);
        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()));
        }
        static::$_client = $client;

        return static::$_client;
    }

    /**
     * Expands the home directory alias '~' to the full path.
     * @param string $path the path to expand.
     * @return string the expanded path.
     */
    protected static function expandHomeDirectory($path)
    {
        $homeDirectory = getenv('HOME');
        if (empty($homeDirectory)) {
            $homeDirectory = getenv('HOMEDRIVE') . getenv('HOMEPATH');
        }
        return str_replace('~', realpath($homeDirectory), $path);
    }

}

```


# クライアントクラスをrequireして、取得の実行部を作る

```php:main.php

<?php

require_once dirname(__FILE__).'/SpreadsheetClient.php';

function updateCardClub()
{
    $c = SpreadsheetClient::getClient();

    $service = new Google_Service_Sheets($c);

    $spreadsheetId = '19lHIoq22c9vYTeGYCcKEf0b24rUFCZ-CewolEiMKI4c';
    $range = 'card_club!A1:G26';
    $response = $service->spreadsheets_values->get($spreadsheetId, $range);
    $values = $response->getValues();
    return $values;
}

var_dump(updateCardClub());


```


# シートはこんな感じ

![image.png](https://qiita-image-store.s3.amazonaws.com/0/1642/3e38ee1f-3313-7498-2cde-69a4564fbcd9.png)

# 結果

![image.png](https://qiita-image-store.s3.amazonaws.com/0/1642/e4ac5b4b-0510-94b2-df53-2315b5a9d941.png)

取れてる～

# ハマりポイント

## curlでエラー出る

![image.png](https://qiita-image-store.s3.amazonaws.com/0/1642/07b40998-4194-c386-7b76-90657f15ee98.png)

こちらの方法の「Google Client SDKを使っているときにこのエラーが出たときは…」で解決。
既にSpreadsheetClientに織り込み済み

http://nanoappli.com/blog/archives/7992