GOOGLE SHEETS PHP

What is Google Sheets API?

Google Sheets API with PHP allows developers to interact with Google Sheets programmatically. By utilizing PHP, a popular server-side scripting language, developers can create, read, update, and delete data in Google Sheets without manual input. This integration enables the automation of spreadsheet-related tasks, such as data synchronization, reporting, and analysis, making it easier to manage and manipulate data stored in Google Sheets through PHP-based applications. Developers can authenticate, access sheets, and perform various operations using the API, enhancing the functionality and efficiency of spreadsheet management in web applications.

Sheets API allows you to perform the following tasks:-

• Create Spreadsheet
• Read and write data
• Format text, number, and cell
• Adjust column size
• Create charts
• Delete and manage the sheet

How to enable & set up the Google Sheet API?

To implement the sheets API in your project you will need these steps:-

• Goto Google developer console by clicking this URL https://console.cloud.google.com
• Sign in and create a new project.

• Goto API & services -> Library -> Search the sheet API & enable it.
• After enabling the sheet API, click on create credentials -> Service account -> fill up the details & create.
• Select the role -> project -> owner.
• Goto service account & select the account which you have created -> keys -> Add Key -> Create a new key -> JSON -> Click on create.
• The secret key code will be automatically download on your device and save the key file in the project directory with the name credentials.json
• Go to google sheets, create a new sheet, and add some random data to the sheet.
• Copy the spreadsheet id and save it, see the example below.

• Open the credentials.json file copy the client _email and share the sheet with this email. Go to your Google sheet, click on the share button and paste the client email there, and click on done.

To Implement the sheet API in the PHP code project.

You will need the following:-

• PHP (ver > 7.0)
• Composer

Open the CMD terminal in the project root directory & run the command:-

composer require google/apiclient

• After installing the library, we need to load it into the project.

   require_once __DIR__. "/vendor/autoload.php";

    $client = new Google_Client();                              // Add a client
    $client->setApplicationName("My Spreadsheet");              // Set application name whatever you want
    $client->setScopes(Google\Service\Sheets::SPREADSHEETS);    // Add the scope which will be used to
    $client->setAccessType('offline');                          // it is used for accessing the offline credentials 
    $client->setAuthConfig(__DIR__. "/credentials.json");

    $service = new Google\Service\Sheets($client);                     // initiate the sheet object
    $spreadsheet_id = "YOUR-SPREADSHEET-ID";

1. Let’s read some data from the spreadsheet.

$range = "Customers!A1:D4";                                 //define the range how much data you want retrieve

$response = $service->spreadsheets_values->get(
    $spreadsheet_id,
    $range
);

$values = $response->getValues();
if (empty($values)) {
    print "No data found";
} else {
    echo "<pre>";
    print_r($values);
}

Now, You are ready to go, access the file in your web browser, or run the file in the command prompt.

php your-file-name.php

2. Let’s update the sheet columns

$range = "Customers!A2:E2";
$values = [
    [9999, 'Alexander', 'Male', 'Norway', 'U.S'],
];

$body = new Google\Service\Sheets\ValueRange([
    'values' => $values,
]);

$params = [
    'valueInputOption' => 'RAW',
];

$result = $service->spreadsheets_values->update(
    $spreadsheet_id,
    $range,
    $body,
    $params,
); 

3. Let’s insert a new row

$range = "Customers";
$values = [
    ['This', 'is', 'a', 'new', 'row'],
];

$body = new Google\Service\Sheets\ValueRange([
    'values' => $values,
]);

$params = [
    'valueInputOption' => 'RAW',
];


$result = $service->spreadsheets_values->append(
    $spreadsheet_id,
    $range,
    $body,
    $params,
);

If you have any questions, Leave a comment below