google APIをOAuth2.0越しにofflineで使う簡単な方法

CLI(コマンドライン)から行うgoogle APIのOAuth2.0認証及びグーグルスプレッドシートの読み込み・書き込みは次のようにすれば良い。言語はPHP。



Here’s how to do a simple PHP OAuth2.0 certification for google API and how to read and edit your Google Spreadsheet via CLI(Command Line Interface).
The sample code shown in QuickStart is not working correctly for now (7/17/2016), so after performing Step.2 in the QuickStart, use the code below instead of the code written in Step.3. Be sure to set your spread sheet ID and sheet name to $spreadsheetID and $sheetName.



define('APPLICATION_NAME', 'Google Sheets API PHP Quickstart');
define('CREDENTIALS_PATH', '~/.credentials/');
define('CLIENT_SECRET_PATH', __DIR__ . '/client_secret.json');
// Set scope readable and editable
define('SCOPES', implode(' ', array(

// Check if it's exeuted via CLI (command line interface).
// It also detects php-cgi cli.
function is_cli() {
    return (!isset($_SERVER['SERVER_SOFTWARE']) 
        && (php_sapi_name() == 'cli' 
            || (is_numeric($_SERVER['argc']) && $_SERVER['argc'] > 0))

if (!is_cli()) {
    throw new Exception('This application must be run on the command line.');

function getClient() {
    $client = new Google_Client();

    // Load previously authorized credentials from a file.
    $credentialsPath = expandHomeDirectory(CREDENTIALS_PATH);
    if (file_exists($credentialsPath)) {
        $accessToken = file_get_contents($credentialsPath);
    } 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)); is not working on php-cgi, so use this instead.
        $stdin = '';
        $fh = fopen('php://stdin','r');
        if($fh) {
            // read *one* line from stdin upto "\r\n"
            $stdin = fgets($fh);
        $authCode = trim($stdin);

        // Exchange authorization code for an access token.
        $accessToken = $client->authenticate($authCode);
        // Store the credentials to disk.
        if(!file_exists(dirname($credentialsPath))) {
            mkdir(dirname($credentialsPath), 0700, true);
        file_put_contents($credentialsPath, json_encode($accessToken)); //need json_encode here
        printf("Credentials saved to %s\n", $credentialsPath);

    // Refresh the token if it's expired.
    // RefreshToken is not saved to the file when using the original code.
    if ($client->isAccessTokenExpired()) {
        $refreshToken = $client->getRefreshToken();
        $newAccessToken = $client->getAccessToken();
        $newAccessToken['refresh_token'] = $refreshToken;
        file_put_contents($credentialsPath, json_encode($newAccessToken));
    return $client;

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

// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);

// Prints the names and majors of students in a sample spreadsheet:
$spreadsheetId = 'put your spreadsheet id here';
$sheetName = 'put your sheet name here';
$range = $sheetName . '!A1:A';

// Read values and show
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
foreach ($values as $row) {
    printf("a: %s\n", $row[0]);

// Get last row and last value
$lastRow = count($values); //start from 1
$lastValue = '';
if ($lastRow > 0) {
    $lastValue = $values[$lastRow-1][0];
printf("lastrow:%s lastvalue:%s\n", $lastRow, $lastValue);

// Set new value
$newRow = $lastRow + 1;
$range = "{$sheetName}!A{$newRow}:A{$newRow}";
$valueRange= new Google_Service_Sheets_ValueRange();
$valueRange->setValues(['values' => ["test:{$newRow}"]]);
$conf = ['valueInputOption' => 'RAW'];
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $conf);


google APIにアクセスする際はアクセストークンが必要になる。アクセストークンはデフォルトで3600秒の有効期限を持つため、有効期限が切れたら再度アカウント認証をしなければならない。しかしこれでは毎回ユーザーの認証操作が必要になるため、定期的にcronなどで実行するプログラムには不向きだ。そこで、リフレッシュトークンというものを発行し、アクセストークンの有効期限が切れたらそのリフレッシュトークンを使って新しいアクセストークンを取得するという方法がある。これをオフライン・アクセスという。



  • ユーザーがアクセス権限を取り消した場合
  • リフレッシュトークンが6か月間一度も使用されなかった場合
  • トークンがGmail, Calendar, Contacts, or Hangoutsのスコープを持ち、ユーザーがパスワードを変更した場合
  • 一人のユーザーが特定のトークン発行数上限を超えた場合


When you run the code (on CLI) for the first time, it shows URI for account certification. Access the URI from your browser and certificate your google account. When the certification succeeds, you see a certification code on the browser. Copy & paste it into CLI. Then the tokens (An access token and a refresh token) are automatically obtained and saved to a json file in .credential folder. From next time, the program uses the saved tokens so you don’t need to certificate your google account any more.