How to Use Google Sheets API to Read and Write Data in PHP

Setup Your Project with Composer Dependencies

• To interact with Google Sheets using PHP, you'll need to manage HTTP requests and handle authentication. This can be done using Google's official client library for PHP, accessible through Composer.

composer require google/apiclient:^2.0

Authenticate with Google Sheets API

• The PHP application must authenticate against Google Sheets API using OAuth 2.0. Obtain a JSON credentials file from Google Cloud Console and place it in your project directory.
• Use the credentials file to create a Google Client and authenticate with desired scopes, namely `spreadsheets` and possibly `drive` if you're accessing Google Drive files.

require 'vendor/autoload.php';

$client = new \Google_Client();
$client->setApplicationName('Google Sheets API PHP');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAuthConfig('/path/to/credentials.json');
$client->setAccessType('offline');
$client->setPrompt('select_account consent');

Initialize the Google Sheets Service

• Create an instance of `Google_Service_Sheets` to begin calling the API methods for reading and writing data.

$service = new \Google_Service_Sheets($client);

Read Data from a Google Sheet

• To read data, specify the spreadsheet ID and the range of cells you want to retrieve. The response will contain the requested data.

$spreadsheetId = 'your-spreadsheet-id';
$range = 'Sheet1!A1:D10';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

if (empty($values)) {
    echo "No data found.\n";
} else {
    foreach ($values as $row) {
        echo implode(", ", $row), "\n";
    }
}

Write Data to a Google Sheet

• To write data into a sheet, prepare the data in a 2D array and use the `ValueRange` object to structure input.
• The `update` operation can be performed using methods like `append`, `update`, or `batchUpdate` depending on your requirement.

$data = [
    ['Name', 'Age', 'Location'],
    ['Alice', '24', 'New York'],
    ['Bob', '30', 'Chicago']
];

$body = new \Google_Service_Sheets_ValueRange([
    'values' => $data
]);
$params = [
    'valueInputOption' => 'RAW'
];

$result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
printf("%d cells updated.", $result->getUpdatedCells());

Error Handling and Debugging

• Ensure proper error handling to catch exceptions during API calls. Use try-catch blocks around your API logic.
• Debugging can be aided by examining the error messages returned by the Google Client.

try {
    $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
} catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
}