Change Google Spreadsheet Title Using Google Sheets API PHP Client

| July 23, 2022 | 1,049 views | Google Sheets API PHP Client

Rename Google Spreadsheets using the method spreadsheets.batchUpdate with request UpdateSpreadsheetPropertiesRequest by assigning a new value to it’s title property. Having a clearly defined Google Spreadsheet title will help identify your speadsheets more quickly, thus leaving them more organized and easier to manage.

Several prerequisites are listed below, and one of them is we will be using the created Google Spreadsheet on our tutotial Create A Blank SpreadSheet Using Google Sheets API PHP Client Library as the sample Google Spreadsheet that we will be renaming.

The goal of this tutorial is to change the current spreadsheet title My Test Google Sheet SpreadSheet to My Hello World Spreadsheet.

Requirements:

Step 1.

Prepare the needed parameters.

  • $spreadsheetId
  • $title

The $spreadsheetId parameter.

This is a unique value containing letters, numbers, hyphens, or underscores. If you have not saved it somewhere, you can find a spreadsheetId in a Google Sheets URL.

The $title parameter.

This represents the new title you would want to put into your existing Google Spreadsheet.

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';
$title = 'My Hello World Spreadsheet';

Step 2.

Inside your working directory, create a file (change-google-spreadsheet-title.php), and copy/paste the following codes. Also, input below your prepared paramenters from Step 1.

<?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 updateTitle($spreadsheetId, $title)
{
    $client = getClient();
    $service = new Google_Service_Sheets($client);
    try{
        $requests = [
            new Google_Service_Sheets_Request([
                'updateSpreadsheetProperties' => [
                    'properties'=> [
                        'title' => $title
                    ],
                    'fields'    => 'title'
                ]
            ])
        ];

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

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

        echo 'The title of Spreadsheet ID ' . $response->getSpreadsheetId() . ' was updated.';

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

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';
$title = 'My Hello World Spreadsheet';

/**
 * Attempt to update spreadsheet.
 */
updateTitle($spreadsheetId, $title);

It is worth paying attention to that the spreadsheets.batchUpdate method and UpdateSpreadsheetPropertiesRequest request will let us update the Title of the Google Spreadsheet. This will generally leave the old properties alone and the new set requests will overwrite the existing data, technically allowing us to update the title property.

Each request is validated before being applied. If any request is not valid then the entire request will fail and nothing will be applied.

Step 3.

Execute the following command to change the title of the Google Spreadsheet.

$ php change-google-spreadsheet-title.php

If successful, the response will be like below.

Done.

We have changed the title of the Google Spreadsheet from My Test Google Sheet SpreadSheet to My Hello World Spreadsheet from the command line in PHP using Google Sheets API PHP Client Library.

Result.

References:

0 Comments

Leave a Reply

Your email address will not be published.