Update Google Sheets Cells Using Google Sheets API PHP Client

Update specific google sheets cells using the method spreadsheets.batchUpdate with request UpdateCellsRequest. Simply choose a precise cell position and update the value inside it.

We will be using the result of the tutorial Unmerge Google Sheets Cells Using Google Sheets API PHP Client for this example.

The goal of this tutorial is to update the value of cell B3 of Google Spreadsheets using Google Sheets API PHP Client.

Requirements:

Step 1.

Prepare the needed parameters.

$spreadsheetId

The $spreadsheetId parameter.

A unique value that contains letters, numbers, hyphens, or underscores that can be found in a Google Sheets URL.

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';

php

The $updateCellsRequest parameter.

These are the values of the request that will update a google sheets cell.

/**
 * Set up parameters.
 */
$updateCellsRequest = [
    'updateCells'   => [
        'rows'  => [
            'values'    => [
                'userEnteredValue'  => ['stringValue'=>'Updated B3']
            ],
        ],
        'fields'=> 'userEnteredValue',
        'start' => ['rowIndex'=>2, 'columnIndex'=>1]
    ],
];

Step 2.

Inside your working directory, create a file (update-google-sheets-cells.php), and copy/paste the following codes. The paremeters from Step 1 are already included below.

<?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 updateCells($spreadsheetId, $updateCellsRequest)
{
    $client = getClient();
    $service = new Google_Service_Sheets($client);
    try{
        $requests = [
            new Google_Service_Sheets_Request($updateCellsRequest)
        ];

        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
            'requests' => $requests
        ]);

        $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

        echo 'Updating of cells of Spreadsheet ID ' . $response->getSpreadsheetId() . ' was successful.';

        return $response;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
    }
}

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';

/**
 * Set up parameters.
 */
$updateCellsRequest = [
    'updateCells'   => [
        'rows'  => [
            'values'    => [
                'userEnteredValue'  => ['stringValue'=>'Updated B3']
            ],
        ],
        'fields'=> 'userEnteredValue',
        'start' => ['rowIndex'=>2, 'columnIndex'=>1]
    ],
];

/**
 * Attempt to update spreadsheet.
 */
updateCells($spreadsheetId, $updateCellsRequest);

update-google-sheets-cells.php

If any request is not valid then the entire request will fail and nothing will be applied. This is a note that should be taken because each request is validated before being applied.

Step 3.

Execute the following command to perform the updating of cells of the Google Spreadsheet.

$ php update-google-sheets-cells.php

command line

If successful, the response will be like below.

$ php update-google-sheets-cells.php
Updating of cells of Spreadsheet ID 'Your spreadsheetId here.' was successful.

Done.

We have updated the cell of the Google Spreadsheet from the command line in PHP using Google Sheets API PHP Client Library.

Result.

Before.

After.