How to Read Google Sheets with PHP

by Vincy. Last modified on May 13th, 2024.

This tutorial guides how to read Google Sheets records using PHP. It has an example code that uses PHP Google client SDK to access the Google Sheets API via PHP script.

This example reads the Google Sheets records into a PHP array. Then, it iterates the array to store the records in the database and display them on the browser.

In a previous tutorial, we saw how to read Google Sheets via JavaScript API.

Steps to read Google Sheets using PHP

  1. Create a Google console project and enable Google Sheets API.
  2. Create a Service Account and download the credential JSON.
  3. Integrate Google client SDK into the PHP project.
  4. Set credentials and Google Sheet ID.
  5. Read Google Sheets records into the PHP array.
  6. Store Google Sheets records in the database.

PHP Read Google Sheet

Create a Google console project and enable Google Sheets API

Log in to the Google developers’ console and create a new project or select an existing one. Click “Enable API and services” and search for Google Sheets API to enable it.
Enable Google Sheet API

Create a Service Account and download the credential JSON

Navigate through Choose Credentials -> Create Credentials – >Create service account and give service account details. Then, grant permissions to the service account to access the cloud project resources.

Google API Service Account

Go to Manage keys -> Keys tab -> Add key. It provides a supported format in which the credentials can be downloaded.

The below screenshots show the recommended JSON format selected.

Integrate Google client SDK into the PHP project

In a previous tutorial, we came across this step to integrate a PHP Google client into an application. It provides functions to access Google Services easily via API.

For example, when creating a Google OAuth login, we have run this composer command. It installs all the dependencies to the PHP project.

composer require google/apiclient:^2.0

Create Service Account Keys

Set credentials and Google Sheet ID

The JSON file is downloaded from the Google developers console. This example contains these credentials in its root path.

credentials.json

{
  "type": "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 be set into the application server environment.

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

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

Read Google Sheets records into the PHP array

The following steps are taken to read the Google Sheets records into a PHP array.

  • Instantiate Google_Service_Sheets.
  • Sets the read-only scope.
  • Set the Google sheet ID and the column range
  • Call the API get() method with respect to the service instance.

Note: Permit the client_email that is the above JSON to access a Google Sheet.

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

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

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

// 5. Read Google spreadsheet data
$responseData = $service->spreadsheets_values->get($spreadsheetId, $range);
$dataArray = $responseData->getValues();
?>

The Google services provide features to write rows to Google Sheets. Want to learn how to post form data to store the records to Google Sheets? We have examples to achieve it easily.

Store Google Sheets records in the database

This code is on the landing page of this example. Initially, it displays a button to trigger reading Google Sheets from PHP.

In PHP it sends the read request to the Google API. It defines the following to perform the sheet reading successfully.

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

It generates the API client instance and bundles it with the above details. With reference to these instances, it instantiates the Google Sheets service and reads the row of data.

The data read from the Google Sheets are iterated with a PHP for loop. Then, it executes database insert on each iteration to import into a MySQL database.

This example program builds an INSERT query by using the Google Sheets API response data.

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. Include Google API PHP Client library
        require_once 'vendor/autoload.php';

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

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

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

        // 5. Read Google spreadsheet data
        $responseData = $service->spreadsheets_values->get($spreadsheetId, $range);
        $dataArray = $responseData->getValues();

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

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

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP Read Google Sheets</title>
    <link href="css/style.css" rel="stylesheet" type="text/css" />
    <link href="css/form.css" rel="stylesheet" type="text/css" />
    <link href="css/table.css" rel="stylesheet" type="text/css" />
    <style>
        .table-container {
            margin: 20px 0px;
        }
    </style>
</head>

<body>
    <div class="phppot-container">
        <h1 class="text-center">PHP Read Google Sheets</h1>
        <form method="POST">
            <button type="submit" name="read_google_sheets" class="">Read Google Sheets</button>

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

        </form>
        <?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>
        document.querySelector('form').addEventListener('submit', function() {
            document.getElementById('loading').style.display = 'inline-block';
        });
    </script>
</body>

</html>

Displaying Google Sheets data to the browser

This HTML code displays a table of Google Sheets data to the user interface. The data is from the MySQL database to which the Google sheet’s row data is inserted.

When iterating Google Sheets API response, this table HTML can be used to see the records. These table columns are designed to be synced with the Google Sheets column.

The HTML table, database table, and Google Sheet should have unified columns for getting better output.

google-sheet-table-data.php

<div class="table-container">
    <h3>Records read from Google Sheets</h3>
    <table id="data-table">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Company</th>
            </tr>
        </thead>
        <tbody>
            <?php
            // Display data from the database table
            $sql = "SELECT * FROM tbl_google_sheet";
            $result = $conn->query($sql);

            if ($result->num_rows > 0) {
                while ($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 results found.</td>
                </tr>
            <?php } ?>
        </tbody>
    </table>
</div>

Download

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Tutorials

↑ Back to Top

Share this page