This tutorial provides a PHP instance for creating Google Sheets and inserting kind knowledge as information through a program. It calls Google Sheets API to create a brand new spreadsheet. Then, we’ve got a script to render a kind. On submission, it provides the report to the created Google Sheets.
This text describes the implementation process with detailed step-by-step narration. This may provide help to to combine Google Sheets API right into a PHP software.
In a earlier tutorial, we’ve got seen tips on how to learn Google Sheets utilizing Google JavaScript API providers to show the information desk to the browser.
Steps to Google Sheets integration and add PHP kind knowledge
- Generate Google Sheets API keys.
- Set up the Google PHP consumer library.
- Retrieve entry token to attach Google sheet service.
- Create a brand new sheet with a header from PHP.
- Retailer spreadsheet ID within the database.
- Create a PHP kind to put up knowledge to Google Sheets.
1. Generate Google Sheets API keys
Beforehand, we’ve got seen PHP examples for utilizing totally different Google API providers. It must create Google API keys to entry the providers.
For instance, we’ve got built-in the Google reCaptcha service utilizing PHP. Additionally, we used the Google PHP consumer to implement OAuth login for a PHP software.
Open the Google Cloud console and carry out the under steps to get the keys and configure it into the PHP software.
a) Create a Google Cloud mission
Create a brand new mission or choose an present one and navigate by way of the left menu to set the consumer particulars.
b) Allow Google Sheets API
Click on the “APIs & providers” and seek for the Google Sheets API. Click on “Allow” to see the under display screen.
c) Setup OAuth consent display screen
The Google OAuth consent display screen collects particulars in a wizard movement. It will get the app particulars, scope, or permits for the API entry limitations, take a look at person restrictions and extra.
d) Create an online consumer and get the consumer ID and consumer secret
The subsequent step is to create the net consumer to get the keys. This step lets you see the consumer ID and consumer secret key. It additionally permits to obtain the consumer credentials in a JSON format.
e) Obtain Google consumer credentials JSON
JSON code under has the consumer keys and authentication URI. This JSON is used on the time of making the Google consumer occasion.
credentials.json
{
"net":{
"client_id":"YOUR_CLIENT_ID",
"project_id":"GOOGLE_CLOUD_PROJECT_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_secret":"YOUR_CLIENT_SECRET",
"redirect_uris":["http://localhost/PATH_TO_PHP_PROJECT/google-sheet-php/index.php"],
"javascript_origins":["http://localhost"]
}
}
2. Set up the Google PHP consumer library
Run the under composer command to have the Google API consumer dependencies into the PHP mission.
composer require google/apiclient:^2.0
3. Retrieve the entry token to attach the Google Sheets service
After integrating the Google Sheet API consumer, step one is to retrieve the entry token.
When studying or writing into the Google sheet, the under code shall be included to get the entry token earlier than requesting providers. Within the Google Drive file add instance additionally, we name this script to get the entry token.
It makes use of the credentials JSON downloaded to create the Google consumer occasion.
Then, it sends the OAuth token request to the Google API through getOauthTokenFromGoogle().
This perform shops the retrieved OAuth token within the PHP session and redirects the person to the redirect_uri set with the request.
Within the getOauthToken(), it reads the token from the PHP session after redirect.
init-google-client.php
<?php
require_once __DIR__ . '/vendor/autoload.php';
require_once __DIR__ . '/GoogleSpreadSheetService.php';
// Google net consumer credentials JSON path
$credentialsPath="./credentials.json";
$googleSpreadSheetService = new GoogleSpreadSheetService();
// Initializing Google consumer to entry GoogleSpreadSheet service with credentials JSON
$consumer = $googleSpreadSheetService->intializeGoogleClient($credentialsPath);
$googleSpreadSheetService->getOauthTokenFromGoogle($consumer);
// Get OAuth 2.0 token
$accessToken = $googleSpreadSheetService->getOauthToken();
?>
Request to hook up with Google Sheets
The under code is for displaying a touchdown web page that shows the “Hook up with Google sheet” checkbox.
On clicking the checkbox, the consumer script redirects to the auth URL to get end-user consent.
index.php
<?php
session_start();
require_once __DIR__ . '/init-google-client.php';
?>
<html>
<head>
<hyperlink href="https://phppot.com/php/google-sheet-php/css/kind.css" rel="stylesheet" sort="textual content/css" />
<hyperlink href="css/fashion.css" rel="stylesheet" sort="textual content/css" />
<title>Google Sheet PHP</title>
<fashion>
.success {
show: inline-block;
padding: 4px 20px;
}
.phppot-container {
margin-top: 100px;
}
</fashion>
</head>
<physique>
<div class="phppot-container text-center">
<?php
if (!empty($accessToken)) {
$googleSpreadSheetService->refreshToken($consumer, $accessToken);
$spreadsheetId = $googleSpreadSheetService->createSpreadSheet($consumer);
require_once __DIR__ . '/SpreadsheetModel.php';
$spreadsheetModel = new SpreadsheetModel();
$spreadsheetModel->insertSpreadSheetId($spreadsheetId);
// Two column names added to the sheet header
$values = [["Name", "Email"]];
$headingResult = $googleSpreadSheetService->insertHeading($consumer, $spreadsheetId, $values);
?>
<img src="tick.png">
<h1>Google Sheet linked!</h1>
<p>New spreadsheet created with ID: <b><?php echo $spreadsheetId; ?></b></p>
<p><a href="https://docs.google.com/spreadsheets/d/<?php echo $spreadsheetId; ?>" goal="_blank">View created spreadsheet</a></p>
<?php
if (!empty($headingResult)) {
?>
<div class="phppot-message success">Sheet header added. <a href="kind.php">Go to kind</a> to insert knowledge.</div>
<?php
}
?>
<?php
} else {
$authUrl = $client->createAuthUrl();
?>
<p>Click on the under button to hook up with Google Sheets API.</p>
<button><enter sort="checkbox" id="autoConnectCheckbox" onchange="connectToGoogleSheets()"> Hook up with Google Sheets</button>
<script>
perform connectToGoogleSheets() {
var checkBox = doc.getElementById("autoConnectCheckbox");
if (checkBox.checked === true) {
window.location.href = "<?php echo $authUrl; ?>";
}
}
</script>
<?php
exit();
}
?>
</div>
</physique>
</html>
4. Create a brand new sheet with a header from PHP
As soon as the person is allowed to entry the sheet, the code creates a brand new Google sheet through PHP code. A column header can also be inserted into the sheet on the time of creation.
This instance permits loading sheet knowledge from an HTML kind. After requesting createSpreadSheet, it hyperlinks the shape to enter knowledge to be loaded to the Google sheet created.
This PHP class accommodates capabilities to request the Google consumer to create a brand new sheet and cargo knowledge. It additionally verifies the entry token expiry and calls refreshToken() request if expired.
GoogleSpreadSheetService.php
<?php
class GoogleSpreadSheetService
{
perform intializeGoogleClient($credentialsPath)
{
$consumer = new Google_Client();
$client->setApplicationName('Google Sheets and PHP Integration');
$client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
$client->setAuthConfig($credentialsPath);
$client->setAccessType('offline');
$client->setPrompt('select_account consent');
$client->setState('abc');
$client->setRedirectUri('http://localhost/phppot/php/google-sheet-php/index.php');
return $consumer;
}
perform storeOauthToken($accessToken)
{
$_SESSION['access_token'] = $accessToken;
}
perform getOauthTokenFromGoogle($consumer)
{
// Deal with OAuth 2.0 server response
if (isset($_GET['code']) && !isset($_SESSION['access_token'])) {
$accessToken = $client->fetchAccessTokenWithAuthCode($_GET['code']);
$client->setAccessToken($accessToken);
$this->storeOauthToken($accessToken);
// Redirect to take away the code from URL
header('Location: ' . filter_var($client->getRedirectUri(), FILTER_SANITIZE_URL));
exit();
}
}
perform getOauthToken()
{
if (!empty($_SESSION['access_token'])) {
$accessToken = $_SESSION['access_token'];
return $accessToken;
}
}
perform refreshToken($consumer, $accessToken)
{
$client->setAccessToken($accessToken);
// Refresh the token if it is expired
if ($client->isAccessTokenExpired()) {
if ($client->getRefreshToken()) {
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
} else {
$authUrl = $client->createAuthUrl();
header('Location: ' . filter_var($authUrl, FILTER_SANITIZE_URL));
exit();
}
}
}
perform createSpreadSheet($consumer)
{
$spreadsheet = new Google_Service_Sheets_Spreadsheet([
'properties' => ['title' => 'New spreadsheet'] // Give the title for the spreadsheet
]);
$service = new Google_Service_Sheets($consumer);
attempt {
$spreadsheet = $service->spreadsheets->create($spreadsheet, ['fields' => 'spreadsheetId']);
$spreadsheetId = $spreadsheet->spreadsheetId;
return $spreadsheetId;
// Get the spreadsheet ID of recent file created
} catch (Exception $e) {
echo 'An error occurred whereas creating the spreadsheet: ' . $e->getMessage();
}
}
perform insertHeading($consumer, $spreadsheetId, $values)
{
$service = new Google_Service_Sheets($consumer);
$vary="Sheet1"; // Google spreadsheet vary
// Construct physique with report set
$physique = new Google_Service_Sheets_ValueRange(['values' => $values]);
$params = ['valueInputOption' => 'RAW'];
$consequence = "";
attempt {
//Append knowledge with respect to the spreadsheet id
$consequence = $service->spreadsheets_values->append($spreadsheetId, $vary, $physique, $params);
} catch (Exception $e) {
echo 'An error occurred: ' . $e->getMessage();
}
return $consequence;
}
}
It redirects to “Register with Google” window and asks permission to permit the Google console app to entry Google Sheets. The under screenshots present the interim steps required to allow the app for the primary time solely.
5. Retailer spreadsheet ID within the database
When a brand new spreadsheet is created the Google API callback can get the spreadsheet id.
On this PHP instance, the spreadsheet ID is for future reference.
This ID is beneficial to discuss with load the information to the created sheet when posting the shape.
This PHP mannequin class accommodates two capabilities. One is to insert the spreadsheet ID when a brand new sheet is created. The opposite is to learn the sheet ID as a reference to load the shape knowledge to the Google sheet rows.
SpreadsheetModel.php
<?php
class SpreadsheetModel {
non-public $ds = "";
perform __construct()
{
require_once __DIR__ . "/DataSource.php";
$this->ds = new DataSource();
}
perform insertSpreadSheetId($spreadsheetId)
{
$question = "INSERT INTO tbl_spreadsheet (spreadsheet_id) VALUES (?)";
$paramType = "s";
$paramArray = array($spreadsheetId);
$this->ds->insert($question, $paramType, $paramArray);
}
perform getSpreadSheetId()
{
$question = "SELECT spreadsheet_id FROM tbl_spreadsheet ORDER BY id DESC LIMIT 1";
$row = $this->ds->choose($question);
$spreadsheetId = $row[0]['spreadsheet_id'];
return $spreadsheetId;
}
}
Import this database script to have the tbl_spreadsheet desk earlier than working this system.
database.sql
--
-- Desk construction for desk `tbl_spreadsheet`
--
CREATE TABLE `tbl_spreadsheet` (
`id` int(11) NOT NULL,
`spreadsheet_id` varchar(255) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for desk `tbl_spreadsheet`
--
ALTER TABLE `tbl_spreadsheet`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for desk `tbl_spreadsheet`
--
ALTER TABLE `tbl_spreadsheet`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
6. Create a PHP kind to put up knowledge to Google Sheets
Posting dynamic knowledge to the Google sheet from an HTML kind shall be a helpful function.
This manner collects the Title and E-mail knowledge from the person. When these knowledge are posted, the PHP script builds a worth vary object array to the Google Sheets service.
This vary is ready with an information physique to load the report into the Google sheet created.
After loading the information, the PHP success message will present a hyperlink to view the sheet with the consequence.
kind.php
<?php
session_start();
require_once __DIR__ . '/init-google-client.php';
if (!empty($accessToken)) {
// Verify token expiry and refresh if expired
$googleSpreadSheetService->refreshToken($consumer, $accessToken);
} else {
$authUrl = $client->createAuthUrl();
// Redirect to auth URL to get enduser concent to permit the App to entry the Google sheet
header('Location: ' . filter_var($authUrl, FILTER_SANITIZE_URL));
exit();
}
?>
<html>
<head>
<hyperlink href="https://phppot.com/php/google-sheet-php/css/kind.css" rel="stylesheet" sort="textual content/css" />
<hyperlink href="css/fashion.css" rel="stylesheet" sort="textual content/css" />
<title>Inser knowledge from PHP Kind to Google Sheet</title>
<fashion>
.phppot-container {
margin-top: 100px;
}
.tile-container {
margin: 0 auto;
}
</fashion>
</head>
<physique>
<div class="phppot-container">
<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$title = $_POST['name'];
$electronic mail = $_POST['email'];
$vary="Sheet1"; // Regulate vary accordingly
$values = [[$name, $email]]; // Knowledge to be written
$physique = new Google_Service_Sheets_ValueRange(['values' => $values]);
$params = ['valueInputOption' => 'RAW'];
$service = new Google_Service_Sheets($consumer);
attempt {
require_once __DIR__ . '/SpreadsheetModel.php';
$spreadsheetModel = new SpreadsheetModel();
$spreadsheetId = $spreadsheetModel->getSpreadSheetId();
$consequence = $service->spreadsheets_values->append($spreadsheetId, $vary, $physique, $params);
$output = "<div class="phppot-message success">Knowledge inserted into the Google sheet. ";
$output .= "<a href="https://docs.google.com/spreadsheets/d/" . $spreadsheetId . ""
goal="_blank">View spreadsheet</a>.";
$output .= "</div>";
} catch (Exception $e) {
$output="<div class="phppot-message error">An error occurred: " . $e->getMessage() . "</div>";
}
echo $output;
?>
<?php
} else {
?>
<div class="tile-container">
<kind title="frmSheetData" methodology="put up">
<div class="row">
<label for="title">Title:</label>
<enter sort="textual content" title="title" class="full-width" required>
</div>
<div class="row">
<label for="electronic mail">E-mail:</label>
<enter sort="electronic mail" title="electronic mail" class="full-width" required>
</div>
<div class="row">
<enter sort="submit" worth="Submit" class="full-width">
</div>
</kind>
</div>
<?php
}
?>
</div>
</physique>
</html>
