Create A Blank SpreadSheet Using Google Sheets API PHP Client Library

The next thing to do after all the set up and configuration, from Creating a Google Cloud Project up to Obtaining Google API Access Token Through Command Line Using PHP, is to finally test the Google Sheets API. And I know it’s getting a little more exciting from here. Let’s create our first blank Google Sheet SpreadSheet.

Requirements:

Step 1.

Inside your working directory, create a file (create-a-blank-google-sheet-spreadsheet.php), and copy/paste the following codes.

<?php

require __DIR__ . '/vendor/autoload.php';

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

use Google\Client;
use Google\Service\Sheets\SpreadSheet;

/**
 * Returns an authorized API client.
 * @return Client the authorized client object.
 */
function getClient()
{
    $client = new Google\Client();
    $client--->setApplicationName('Google Sheets API PHP Quickstart');
    $client->setScopes('https://www.googleapis.com/auth/spreadsheets');
    $client->setAuthConfig('credentials.json');
    $client->setAccessType('offline');
    $client->setPrompt('select_account consent');

    // Load previously authorized token from a file, if it exists.
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    $tokenPath = 'token.json';
    if (file_exists($tokenPath)) {
        $accessToken = json_decode(file_get_contents($tokenPath), true);
        $client->setAccessToken($accessToken);
    }

    // If there is no previous token or it's expired.
    if ($client->isAccessTokenExpired()) {
        // Refresh the token if possible, else fetch a new one.
        if ($client->getRefreshToken()) {
            $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        } 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);
            $client->setAccessToken($accessToken);

            // Check to see if there was an error.
            if (array_key_exists('error', $accessToken)) {
                throw new Exception(join(', ', $accessToken));
            }
        }
        // Save the token to a file.
        if (!file_exists(dirname($tokenPath))) {
            mkdir(dirname($tokenPath), 0700, true);
        }
        file_put_contents($tokenPath, json_encode($client->getAccessToken()));
    }
    return $client;
}

/**
 * Create an empty spreadsheet.
 * 
 * Returns the created spreadsheet id.
 * @return Spreadsheet ID.
 */
 function create($title)
{   
    $client = getClient();
    $service = new Google_Service_Sheets($client);
    try{

        $spreadsheet = new Google_Service_Sheets_Spreadsheet([
            'properties' => [
                'title' => $title
                ]
            ]);
            $spreadsheet = $service->spreadsheets->create($spreadsheet, [
                'fields' => 'spreadsheetId'
            ]);
            printf("Spreadsheet ID: %s\n", $spreadsheet->spreadsheetId);
            return $spreadsheet->spreadsheetId;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
      }
}

/**
 * Create a blank google sheet spreadsheet with a particular title.
 */
create('My Test Google Sheet SpreadSheet');

Step 2.

Execute the following command to create a blank google sheet spreadsheet.

$ php create-a-blank-google-sheet-spreadsheet.php

command line

It will not take long before it return the SpreadSheet ID of the newly created blank google sheet spreadsheet, and if there are no errors presented, then that’s it, an empty Google Sheet SpreadSheet was created.

Done.

An empty or blank Google Sheets SpreadSheet has been created from the command line using PHP.

Result

Here are several ways to open the Google Sheet SpreadSheet you have created to check the result of your programming with Google Sheets API.

- 1. Double click the file.
- 2. Right click on the file, and click “Open with” -> “Google Sheets“.
- 3. Open this link on a browser – https://docs.google.com/spreadsheets/d/PUT-THE-SPREADSHEET-ID-HERE.