3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

GoogleSpreadsheetの中身をAPIで取得

Last updated at Posted at 2017-10-28

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

image.png

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

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

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して、取得の実行部を作る

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

結果

image.png

取れてる~

ハマりポイント

curlでエラー出る

image.png

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

3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?