Count Google SpreadSheets Cells With Text In PHP Using Google Sheets API PHP Client

Learn how to count Google SpreadSheets cells with text in PHP using Google Sheets API PHP Client.

Using the REST Resource spreadsheets.values with method update you can specifically set the COUNTA formula that will count cells with text in Google SpreadSheets. The COUNTA formula will return the number of values in a dataset.

This article will discuss the basic process on how to set a formula that will count cells with text in a given range of a given spreadsheet ID. And since we are adding a formula, the input data should be interpreted like a user-entered value directly into the user-interface, thus we will use USER_ENTERED as the valueInputOption.

The sample spreadsheet resource that we will be using for this tutorial is the result of the tutorial Update Google Sheets Cells Using Google Sheets API PHP Client.

We will set the following goals for this tutorial.

1. Count cells with text within cells A1 up to D1 and input the total on cell E1.

2. Count cells with text within cells A2 up to D2 and input the total on cell E2.

3. Count cells with text within cells A3 up to D3 and input the total on cell E3.

Requirements:

Step 1.

Prepare the needed parameters.

$spreadsheetId

The $spreadsheetId parameter.

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

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

php

The $range parameter.

The Cells where total counts will be stored.

Total counts per row will be written on Cells E1, E2, and E3 respectively.

$range = 'E1:E3';

The $valueInputOption parameter.

The value on how the input data should be interpreted.

We will be inputting formulas, therefore, we will put the value as USER_ENTERED.

$valueInputOption = 'USER_ENTERED';

The $values parameter.

These will be the formulas that will count cells with text.

$values = [
    ['=COUNTA(A1:D1)'], // Will be stored at Cell E1
    ['=COUNTA(A2:D2)'], // Will be stored at Cell E2
    ['=COUNTA(A3:D3)'], // Will be stored at Cell E3
];

Step 2.

Inside your working directory, create a file (count-cells-with-text.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 countCellsWithText($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 = 'E1:E3'; // The Cells where total counts will be stored
$valueInputOption = 'USER_ENTERED'; // The values will be parsed as if the user typed them into the UI.
$values = [
    ['=COUNTA(A1:D1)'], // Will be stored at Cell E1
    ['=COUNTA(A2:D2)'], // Will be stored at Cell E2
    ['=COUNTA(A3:D3)'], // Will be stored at Cell E3
];

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

count-cells-with-text.php

Note. Each request is validated before being applied. Therefore, if any request is not valid then the entire request will fail and nothing will be applied

Step 3.

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

$ php count-cells-with-text.php

command line

If successful, the response will be like below.

$ php count-cells-with-text.php
3 cells updated.

Done.

We have counted Google Spreadsheet cells with text from the command line in PHP using Google Sheets API PHP Client Library.

Result.

Before.

After.