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.
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.
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.
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.
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
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();
?>
The following steps are taken to read the Google Sheets records into a PHP array.
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.
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.
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>
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>