Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What is going on with this article?
@rev84

GoogleSpreadsheetの中身をAPIで取得

More than 3 years have passed since last update.

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

image.png

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

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

SpreadsheetClient.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に織り込み済み

2
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
rev84
ニコニコ動画でゲーム実況をしていた者 エントリはメモ代わりに使用することが多いため、公益性は微妙かもしれません。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
2
Help us understand the problem. What is going on with this article?