Create A "Hello World" SpreadSheet Using Google Sheets API PHP Client - Google Sheets API PHP Client

Another most common use of the Google Sheets API is to write SpreadSheet cell values. Since we have Created A Blank Spreadsheet Using Google Sheets API PHP Client Library from our previous tutorial, we can use that blank spreadsheet to write a "Hello World!" example data to a particular cell. Overall, this is the next thing we would like to do in a Google Sheet after creating a Google Spreadsheet.

Requirements:

The Google Sheets API provides the spreadsheets.values collection to enable our needed simple “Hello World” data writing. And to write data to a single cell, we will use the spreadsheets.values.update request, which will allow us to set values in a cell or range of cells of a spreadsheet.

To define a cell or range of cells with a string, the syntax used is called A1 notation. It contains the sheet name with the starting and ending cell coordinates using column letters and row numbers. Thus, the range is the A1 notation of the values to update. We will be using this range, along with the Spreadsheet ID, and ValueInputOption as parameters to set values in a range of a spreadsheet.

Parameters:

$spreadsheetId
$range
$valueInputOption
$values

Parameter	Type	Description	Required
$spreadsheetId	string	The ID of the spreadsheet to update.	Yes
$range	string	The A1 notation of the values to update.	Yes
$valueInputOption	enum	How the input data should be interpreted.	Yes
$values	array (ListValue format)	The input data to be written.	Yes

The $spreadsheetId parameter.

This is a unique value containing letters, numbers, hyphens, or underscores. You can find a spreadsheetId in a Google Sheets URL.

The $range parameter.

As stated above, the range is in A1 notation. And in this Google Sheet example, we will be adding the “Hello World!” data into the intersection of Column A and Row 1 of Sheet1, which is Sheet1!A1 or simply A1.

Note that the first sheet will be selected if there is none specified. Therefore, Sheet1!A1 is the same as A1.

There are other options where you can put data in any particular cell you want, below are a few more examples. All in Sheet1.

B2 – will involve only 1 cell .

A1:B2 – covers 4 cells.

C1:E2 – consist of 6 cells.

The $valueInputOption parameter.

You will have the option to decide what value you would like to put for this parameter, it should depend on how you would like the input data should be interpreted. Below are the options.

ValueInputOption	Description
RAW	The input is not parsed and is simply inserted as a string, so the input “=1+2” places the string “=1+2” in the cell, not a formula. (Non-string values like booleans or numbers are always handled as RAW.)
USER_ENTERED	The input is parsed exactly as if it were entered into the Google Sheets UI, so “Mar 1 2016” becomes a date, and “=1+2” becomes a formula. Formats may also be inferred, so “$100.15” becomes a number with currency formatting.

The $values parameter.

This is your input data, the data you want to write to the spreadsheet. This should be in a ListValue format, which is an array of arrays.

A ListValue is a wrapper around a repeated field of values. For example, [['Hello World!']]

All is set, we can start the writing “Hello World!” to our blank spreadsheet.

Step 1.

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

<?php

// Autoload Composer.
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\ValueRange;

/**
 * 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;
}

function updateValues($spreadsheetId, $range, $valueInputOption, $values)
{
    $client = getClient();
    $service = new Google_Service_Sheets($client);
    try{
        $body = new Google_Service_Sheets_ValueRange([
        'values' => $values
        ]);
        $params = [
        'valueInputOption' => $valueInputOption
        ];
        //executing the request
        $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
        printf("%d cells updated.", $result->getUpdatedCells());
        return $result;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
    }
}

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';
$range = 'A1';
$valueInputOption = 'USER_ENTERED';
$values = [['Hello World!']];

/**
 * Attempt to write to a sheet.
 */
updateValues($spreadsheetId, $range, $valueInputOption, $values);

Step 2.

Execute the following command to write “Hello World!” to a blank google sheet spreadsheet.

$ php create-a-hello-world-spreadsheet

command line

If successful, the response will be the total number of cells updated.

Done.

A “Hello World!” SpreadSheet example has been created from the command line in PHP using Google Sheets API PHP Client Library.