How one can Learn Google Sheets with PHP

This tutorial guides the way to learn Google Sheets information utilizing PHP. It has an instance code that makes use of PHP Google consumer SDK to entry the Google Sheets API through PHP script.

This instance reads the Google Sheets information right into a PHP array. Then, it iterates the array to retailer the information within the database and show them on the browser.

In a earlier tutorial, we noticed the way to learn Google Sheets through JavaScript API.

Steps to learn Google Sheets utilizing PHP

1. Create a Google console mission and allow Google Sheets API.
2. Create a Service Account and obtain the credential JSON.
3. Combine Google consumer SDK into the PHP mission.
4. Set credentials and Google Sheet ID.
5. Learn Google Sheets information into the PHP array.
6. Retailer Google Sheets information within the database.

Create a Google console mission and allow Google Sheets API

Log in to the Google builders’ console and create a brand new mission or choose an present one. Click on “Allow API and providers” and seek for Google Sheets API to allow it.

Create a Service Account and obtain the credential JSON

Navigate by Select Credentials -> Create Credentials – >Create service account and provides service account particulars. Then, grant permissions to the service account to entry the cloud mission sources.

Go to Handle keys -> Keys tab -> Add key. It gives a supported format wherein the credentials will be downloaded.

The beneath screenshots present the really useful JSON format chosen.

Combine Google consumer SDK into the PHP mission

In a earlier tutorial, we got here throughout this step to combine a PHP Google consumer into an software. It gives features to entry Google Providers simply through API.

For instance, when making a Google OAuth login, now we have run this composer command. It installs all of the dependencies to the PHP mission.

composer require google/apiclient:^2.0

Set credentials and Google Sheet ID

The JSON file is downloaded from the Google builders console. This instance comprises these credentials in its root path.

credentials.json

{
  "sort": "service_account",
  "project_id": "",
  "private_key_id": "",
  "private_key": "-----BEGIN PRIVATE KEY-----n-----END PRIVATE KEY-----n",
  "client_email": "",
  "client_id": "",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "",
  "universe_domain": "googleapis.com"
}

The credentials path will probably be set into the appliance server setting.

<?php
// 2. Configure Google Sheets API credentials JSON
putenv('GOOGLE_APPLICATION_CREDENTIALS=credentials.json');

// 3. Create the API consumer occasion
$consumer = new Google_Client();
$client->useApplicationDefaultCredentials();
?>

Learn Google Sheets information into the PHP array

The next steps are taken to learn the Google Sheets information right into a PHP array.

• Instantiate Google_Service_Sheets.
• Units the read-only scope.
• Set the Google sheet ID and the column vary
• Name the API get() methodology with respect to the service occasion.

Observe: Allow the client_email that’s the above JSON to entry a Google Sheet.

The response information returned by the Google API has the sheet information. The getValues() returns the response information as a PHP array.

<?php
$client->setScopes([Google_Service_Sheets::SPREADSHEETS_READONLY]);
$service = new Google_Service_Sheets($consumer);

// 4.Outline Google sheet ID and column vary to import
$spreadsheetId = 'GOOGLE SPREAD SHEET ID HERE';
$vary="Sheet1!A1:C";

// 5. Learn Google spreadsheet information
$responseData = $service->spreadsheets_values->get($spreadsheetId, $vary);
$dataArray = $responseData->getValues();
?>

The Google providers present options to jot down rows to Google Sheets. Need to discover ways to submit type information to retailer the information to Google Sheets? We now have examples to attain it simply.

Retailer Google Sheets information within the database

This code is on the touchdown web page of this instance. Initially, it shows a button to set off studying Google Sheets from PHP.

In PHP it sends the learn request to the Google API. It defines the next to carry out the sheet studying efficiently.

• Google Sheet id.
• Column vary.
• API credentials.

It generates the API consumer occasion and bundles it with the above particulars. On the subject of these situations, it instantiates the Google Sheets service and reads the row of information.

The info learn from the Google Sheets are iterated with a PHP for loop. Then, it executes database insert on every iteration to import right into a MySQL database.

This instance program builds an INSERT question through the use of the Google Sheets API response information.

index.php

<?php
$conn = new mysqli("localhost", "root", "", "google_sheet_data");

if ($_SERVER["REQUEST_METHOD"] == "POST" && !empty($_POST['read_google_sheets'])) {
    if (isset($_POST['read_google_sheets'])) {
        // 1. Embrace Google API PHP Consumer library
        require_once 'vendor/autoload.php';

        // 2. Configure Google Sheets API credentials JSON
        putenv('GOOGLE_APPLICATION_CREDENTIALS=credentials.json');

        // 3. Create the API consumer occasion
        $consumer = new Google_Client();
        $client->useApplicationDefaultCredentials();
        $client->setScopes([Google_Service_Sheets::SPREADSHEETS_READONLY]);
        $service = new Google_Service_Sheets($consumer);

        // 4.Outline Google sheet ID and column vary to import
        $spreadsheetId = 'GOOGLE SPREAD SHEET ID HERE';
        $vary="Sheet1!A1:C";

        // 5. Learn Google spreadsheet information
        $responseData = $service->spreadsheets_values->get($spreadsheetId, $vary);
        $dataArray = $responseData->getValues();

        if (empty($dataArray)) {
            $message = "No information discovered.";
            $message_type = "error";
        } else {
            // Insert Google sheets row into the database
            $sql = "INSERT INTO tbl_google_sheet (first_name, last_name, firm) VALUES (?, ?, ?)";
            $stmt = $conn->put together($sql);
            foreach ($dataArray as $row) {
                $stmt->bind_param("sss", $row[0], $row[1], $row[2]);
                $stmt->execute();
            }
            $message = "Google Sheets information imported efficiently.";
            $message_type = "success";

            $stmt->shut();
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta title="viewport" content material="width=device-width, initial-scale=1.0">
    <title>PHP Learn Google Sheets</title>
    <hyperlink href="https://phppot.com/php/php-read-google-sheet/css/type.css" rel="stylesheet" sort="textual content/css" />
    <hyperlink href="css/type.css" rel="stylesheet" sort="textual content/css" />
    <hyperlink href="css/desk.css" rel="stylesheet" sort="textual content/css" />
    <type>
        .table-container {
            margin: 20px 0px;
        }
    </type>
</head>

<physique>
    <div class="phppot-container">
        <h1 class="text-center">PHP Learn Google Sheets</h1>
        <type methodology="POST">
            <button sort="submit" title="read_google_sheets" class="">Learn Google Sheets</button>

            <span id="loading" class="display-none">Studying from Google Sheets...</span>

        </type>
        <?php
        require_once 'google-sheet-table-data.php';
        ?>
        <?php if (!empty($message)) { ?>
            <div class="phppot-message <?php echo $message_type; ?>"><?php echo $message; ?></div>
        <?php } ?>
    </div>
    <script>
        doc.querySelector('type').addEventListener('submit', operate() {
            doc.getElementById('loading').type.show = 'inline-block';
        });
    </script>
</physique>

</html>

Displaying Google Sheets information to the browser

This HTML code shows a desk of Google Sheets information to the person interface. The info is from the MySQL database to which the Google sheet’s row information is inserted.

When iterating Google Sheets API response, this desk HTML can be utilized to see the information. These desk columns are designed to be synced with the Google Sheets column.

The HTML desk, database desk, and Google Sheet ought to have unified columns for getting higher output.

google-sheet-table-data.php

<div class="table-container">
    <h3>Information learn from Google Sheets</h3>
    <desk id="data-table">
        <thead>
            <tr>
                <th>First Identify</th>
                <th>Final Identify</th>
                <th>Firm</th>
            </tr>
        </thead>
        <tbody>
            <?php
            // Show information from the database desk
            $sql = "SELECT * FROM tbl_google_sheet";
            $consequence = $conn->question($sql);

            if ($result->num_rows > 0) {
                whereas ($row = $result->fetch_assoc()) { ?>
                    <tr>
                        <td><?php echo $row["first_name"]; ?></td>
                        <td><?php echo $row["last_name"]; ?></td>
                        <td><?php echo $row["company"]; ?></td>
                    </tr>
                <?php }
            } else { ?>
                <tr>
                    <td colspan='3'>No outcomes discovered.</td>
                </tr>
            <?php } ?>
        </tbody>
    </desk>
</div>

Obtain

Written by Vincy, an internet developer with 15+ years of expertise and a Masters diploma in Pc Science. She makes a speciality of constructing fashionable, light-weight web sites utilizing PHP, JavaScript, React, and associated applied sciences. Phppot helps you in mastering internet improvement by over a decade of publishing high quality tutorials.

↑ Again to Prime