Write Google Sheets Data To A Single Range Horizontally Using Google Sheets API PHP Client

Very similar with the Create A “Hello World” SpreadSheet Using Google Sheets API PHP Client tutorial that was previously published, we will be attempting to further demonstrate the ability of the Google Sheets API and its PHP Client Library to write to a single range of a given Google Sheet. This article will focus on writing a data to a Google Sheet cells from left to right. We will distribute several words on each cells arranged sideways.

Requirements:

• Create A Google Cloud Project
• Create A Google OAuth Consent Screen
• Create A Google Credential OAuth Client ID
• Enable Google Sheets API Library
• Install The Google Client Library Specifying Google Sheets
• Obtain Google API Access Token Through Command Line Using PHP
• Create A Blank SpreadSheet Using Google Sheets API PHP Client Library

Step 1.

Prepare the parameters.

Get ready your spreadsheetId.

We will try to write data to the following cells.

• A1
• B1
• C1
• D1

We just need to write plain text cell data, therefore we will be using RAW value for the valueInputOption.

For each cells we inteded to write on, we will be including the cells specific row and column coordinates to determine if we have put the exact data where we want them to be.

• Hello A1 for Cell A1
• Hello B1 for Cell B1
• Hello C1 for Cell C1
• Hello D1 for Cell D1

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';
$range = 'A1:D1'; // Horizontally
$valueInputOption = 'RAW';
$values = [['Hello A1', 'Hello B1', 'Hello C1', 'Hello D1']];

Step 2.

Inside your working directory, create a file (write-google-sheets-data-to-a-single-range-horizontally.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:D1'; // Horizontally
$valueInputOption = 'RAW';
$values = [['Hello A1', 'Hello B1', 'Hello C1', 'Hello D1']];

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

Step 3.

Execute the following command to write data to a single range horizontally in your Google Sheet.

$ php write-google-sheets-data-to-a-single-range-horizontally.php

command line

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

Done.

A single range data has been written across our calculated cells from the command line in PHP using Google Sheets API PHP Client Library.

Result.

References:

• Google Sheets API
• REST Resource: spreadsheets.values
• Method: spreadsheets.values.update
• Reading & Writing Cell Values
• Google Sheets API Overview