Change Google Sheets Border Using Google Sheets API PHP Client

With the use of the method spreadsheets.batchUpdate together with the request UpdateBordersRequest , Google Sheets Spreadsheet borders can be changed. Different styles can be set like DOTTED, DASHED, SOLID, SOLID_MEDIUM, SOLID_THICK, DOUBLE. And if the border needs to be removed, just set the style to NONE.

In this tutorial, the sample spreadsheet that we will be using is the Write Google Sheets Data To A Multiple Range Horizontally Using Google Sheets API PHP Client

The goal of this tutorial is to add border styles to an existing Google Sheets Spreadsheet.

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 needed parameters.

• $spreadsheetId
• $requests

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 $requests parameter.

This contains the border style and colors you would want to put into your existing Google Sheets Spreadsheet.

The objective is to set the borders of A1:D3 to color red with a DOTTED style.

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

$requests = [
    new Google_Service_Sheets_Request([
        'updateBorders' => [
            'range'    => ['startRowIndex'=> 0, 'endRowIndex'=> 3, 'startColumnIndex'=> 0, 'endColumnIndex'=> 4], // A1:D3
            'top'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'left'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'right'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'bottom'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'innerHorizontal'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'innerVertical'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
        ]
    ])
];

php

Step 2.

Inside your working directory, create a file (change-google-sheets-border.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 updateBorder($spreadsheetId, $requests)
{
    $client = getClient();
    $service = new Google_Service_Sheets($client);
    try{
        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
            'requests' => $requests
        ]);

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

        echo 'The borders 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.';

$requests = [
    new Google_Service_Sheets_Request([
        'updateBorders' => [
            'range'    => ['startRowIndex'=> 0, 'endRowIndex'=> 3, 'startColumnIndex'=> 0, 'endColumnIndex'=> 4], // A1:D3
            'top'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'left'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'right'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'bottom'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'innerHorizontal'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
            'innerVertical'=> ['style'=>'DOTTED', 'colorStyle'=>['rgbColor'=>['red'=>1.0]]],
        ]
    ])
];

/**
 * Attempt to update spreadsheet.
 */
updateBorder($spreadsheetId, $requests);

change-google-sheets-border.php

Take note that the spreadsheets.batchUpdate method and UpdateBordersRequest request will allow an update to the border of the Google Sheets Spreadsheet. The old properties will be the same, while the new set of requests will overwrite the existing data, which will allow the border to be updated.

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-sheets-border.php

command line

If successful, the response will be like below.

Done.

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

Result.

Other samples.

References:

• Google Sheets API
• REST Resource: spreadsheets.values
• Method: spreadsheets.values.update
• Reading & Writing Cell Values
• Google Sheets API Overview
• Method: spreadsheets.values.batchUpdate
• Write Google Sheets Data To A Single Range Horizontally Using Google Sheets API PHP Client
• Write Google Sheets Data To A Multiple Range Horizontally Using Google Sheets API PHP Client