Google Sheets PHP integration: Add form data to Sheets

This tutorial gives a PHP example for creating Google Sheets and inserting form data as records via a program. It calls Google Sheets API to create a new spreadsheet. Then, we have a script to render a form. On submission, it adds the record to the created Google Sheets.

This article describes the implementation procedure with detailed step-by-step narration. This will help you to integrate Google Sheets API into a PHP application.

In a previous tutorial, we have seen how to read Google Sheets using Google JavaScript API services to display the data table to the browser.

Steps to Google Sheets integration and add PHP form data

1. Generate Google Sheets API keys.
2. Install the Google PHP client library.
3. Retrieve access token to connect Google sheet service.
4. Create a new sheet with a header from PHP.
5. Store spreadsheet ID in the database.
6. Create a PHP form to post data to Google Sheets.

1. Generate Google Sheets API keys

Previously, we have seen PHP examples for using different Google API services. It needs to create Google API keys to access the services.

For example, we have integrated the Google reCaptcha service using PHP. Also, we used the Google PHP client to implement OAuth login for a PHP application.

Open the Google Cloud console and perform the below steps to get the keys and configure it into the PHP application.

a) Create a Google Cloud project

Create a new project or select an existing one and navigate through the left menu to set the client details.

b) Enable Google Sheets API

Click the “APIs & services” and search for the Google Sheets API. Click “Enable” to see the below screen.

c) Setup OAuth consent screen

The Google OAuth consent screen collects details in a wizard flow. It gets the app details, scope, or permits for the API access limitations, test user restrictions and more.

d) Create a web client and get the client ID and client secret

The next step is to create the web client to get the keys. This step allows you to see the client ID and client secret key. It also allows to download the client credentials in a JSON format.

e) Download Google client credentials JSON

JSON code below has the client keys and authentication URI. This JSON is used at the time of creating the Google client instance.

credentials.json

{
    "web":{
        "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. Install the Google PHP client library

Run the below composer command to have the Google API client dependencies into the PHP project.

composer require google/apiclient:^2.0

3. Retrieve the access token to connect the Google Sheets service

After integrating the Google Sheet API client, the first step is to retrieve the access token.

When reading or writing into the Google sheet, the below code will be included to get the access token before requesting services. In the Google Drive file upload example also, we call this script to get the access token.

It uses the credentials JSON downloaded to create the Google client instance.

Then, it sends the OAuth token request to the Google API via getOauthTokenFromGoogle().

This function stores the retrieved OAuth token in the PHP session and redirects the user to the redirect_uri set with the request.

In 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 web client credentials JSON path
$credentialsPath = './credentials.json';
$googleSpreadSheetService = new GoogleSpreadSheetService();

// Initializing Google client to access GoogleSpreadSheet service with credentials JSON
$client = $googleSpreadSheetService->intializeGoogleClient($credentialsPath);
$googleSpreadSheetService->getOauthTokenFromGoogle($client);

// Get OAuth 2.0 token
$accessToken = $googleSpreadSheetService->getOauthToken();

?>

Request to connect to Google Sheets

The below code is for showing a landing page that displays the “Connect to Google sheet” checkbox.

On clicking the checkbox, the client 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>
    <link href="css/form.css" rel="stylesheet" type="text/css" />
    <link href="css/style.css" rel="stylesheet" type="text/css" />
    <title>Google Sheet PHP</title>
    <style>
        .success {
            display: inline-block;
            padding: 4px 20px;
        }

        .phppot-container {
            margin-top: 100px;
        }
    </style>
</head>

<body>
    <div class="phppot-container text-center">
        <?php
        if (!empty($accessToken)) {
            $googleSpreadSheetService->refreshToken($client, $accessToken);

            $spreadsheetId = $googleSpreadSheetService->createSpreadSheet($client);

            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($client, $spreadsheetId, $values);
        ?>

            <img src="tick.png">
            <h1>Google Sheet connected!</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; ?>" target="_blank">View created spreadsheet</a></p>
            <?php
            if (!empty($headingResult)) {
            ?>
                <div class="phppot-message success">Sheet header added. <a href="form.php">Go to form</a> to insert data.</div>
            <?php
            }
            ?>
        <?php
        } else {
            $authUrl = $client->createAuthUrl();
        ?>
            <p>Click the below button to connect to Google Sheets API.</p>
            <button><input type="checkbox" id="autoConnectCheckbox" onchange="connectToGoogleSheets()"> Connect to Google Sheets</button>
            <script>
                function connectToGoogleSheets() {
                    var checkBox = document.getElementById("autoConnectCheckbox");
                    if (checkBox.checked === true) {
                        window.location.href = "<?php echo $authUrl; ?>";
                    }
                }
            </script>
        <?php
            exit();
        }
        ?>
    </div>
</body>

</html>

4. Create a new sheet with a header from PHP

Once the user is allowed to access the sheet, the code creates a new Google sheet via PHP code. A column header is also inserted into the sheet at the time of creation.

This example allows loading sheet data from an HTML form. After requesting createSpreadSheet, it links the form to enter data to be loaded to the Google sheet created.

This PHP class contains functions to request the Google client to create a new sheet and load data. It also verifies the access token expiry and calls refreshToken() request if expired.

GoogleSpreadSheetService.php

<?php
class GoogleSpreadSheetService
{
    function intializeGoogleClient($credentialsPath)
    {
        $client = 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 $client;
    }

    function storeOauthToken($accessToken)
    {

        $_SESSION['access_token'] = $accessToken;
    }

    function getOauthTokenFromGoogle($client)
    {
        // Handle 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 remove the code from URL
            header('Location: ' . filter_var($client->getRedirectUri(), FILTER_SANITIZE_URL));
            exit();
        }
    }
    function getOauthToken()
    {
        if (!empty($_SESSION['access_token'])) {

            $accessToken = $_SESSION['access_token'];
            return $accessToken;
        }
    }

    function refreshToken($client, $accessToken)
    {
        $client->setAccessToken($accessToken);

        // Refresh the token if it's expired
        if ($client->isAccessTokenExpired()) {
            if ($client->getRefreshToken()) {
                $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
            } else {
                $authUrl = $client->createAuthUrl();
                header('Location: ' . filter_var($authUrl, FILTER_SANITIZE_URL));
                exit();
            }
        }
    }

    function createSpreadSheet($client)
    {
        $spreadsheet = new Google_Service_Sheets_Spreadsheet([
            'properties' => ['title' => 'New spreadsheet'] // Give the title for the spreadsheet
        ]);
        $service = new Google_Service_Sheets($client);
        try {
            $spreadsheet = $service->spreadsheets->create($spreadsheet, ['fields' => 'spreadsheetId']);
            $spreadsheetId = $spreadsheet->spreadsheetId;
            return $spreadsheetId;
            // Get the spreadsheet ID of new file created
        } catch (Exception $e) {
            echo 'An error occurred while creating the spreadsheet: ' . $e->getMessage();
        }
    }

    function insertHeading($client, $spreadsheetId, $values)
    {
        $service = new Google_Service_Sheets($client);
        $range = 'Sheet1'; // Google spreadsheet range
        // Build body with record set
        $body = new Google_Service_Sheets_ValueRange(['values' => $values]);
        $params = ['valueInputOption' => 'RAW'];

        $result = "";
        try {
            //Append data with respect to the spreadsheet id
            $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
        } catch (Exception $e) {
            echo 'An error occurred: ' . $e->getMessage();
        }
        return $result;
    }
}

It redirects to “Sign in with Google” window and asks permission to allow the Google console app to access Google Sheets. The below screenshots show the interim steps required to permit the app for the first time only.

5. Store spreadsheet ID in the database

When a new spreadsheet is created the Google API callback can get the spreadsheet id.

In this PHP example, the spreadsheet ID is for future reference.

This ID is useful to refer to load the data to the created sheet when posting the form.

This PHP model class contains two functions. One is to insert the spreadsheet ID when a new sheet is created. The other is to read the sheet ID as a reference to load the form data to the Google sheet rows.

SpreadsheetModel.php

<?php
class SpreadsheetModel {
    private $ds = "";

    function __construct()
    {
        require_once __DIR__ . "/DataSource.php";
        $this->ds = new DataSource();
    }

    function insertSpreadSheetId($spreadsheetId)
    {
        $query = "INSERT INTO tbl_spreadsheet (spreadsheet_id) VALUES (?)";
        $paramType = "s";
        $paramArray = array($spreadsheetId);
        $this->ds->insert($query, $paramType, $paramArray);
    }

    function getSpreadSheetId()
    {
        $query = "SELECT spreadsheet_id FROM tbl_spreadsheet ORDER BY id DESC LIMIT 1";
        $row = $this->ds->select($query);
        $spreadsheetId = $row[0]['spreadsheet_id'];
        return $spreadsheetId;
    }
}

Import this database script to have the tbl_spreadsheet table before running the program.

database.sql

--
-- Table structure for table `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 table `tbl_spreadsheet`
--
ALTER TABLE `tbl_spreadsheet`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_spreadsheet`
--
ALTER TABLE `tbl_spreadsheet`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

6. Create a PHP form to post data to Google Sheets

Posting dynamic data to the Google sheet from an HTML form will be a useful feature.

This form collects the Name and Email data from the user. When these data are posted, the PHP script builds a value range object array to the Google Sheets service.

This range is set with a data body to load the record into the Google sheet created.

After loading the data, the PHP success message will show a link to view the sheet with the result.

form.php

<?php
session_start();

require_once __DIR__ . '/init-google-client.php';

if (!empty($accessToken)) {
    // Check token expiry and refresh if expired
    $googleSpreadSheetService->refreshToken($client, $accessToken);
} else {
    $authUrl = $client->createAuthUrl();
    // Redirect to auth URL to get enduser concent to allow the App to access the Google sheet
    header('Location: ' . filter_var($authUrl, FILTER_SANITIZE_URL));
    exit();
}
?>
<html>

<head>
    <link href="css/form.css" rel="stylesheet" type="text/css" />
    <link href="css/style.css" rel="stylesheet" type="text/css" />
    <title>Inser data from PHP Form to Google Sheet</title>
    <style>
        .phppot-container {
            margin-top: 100px;
        }

        .tile-container {
            margin: 0 auto;
        }
    </style>
</head>

<body>
    <div class="phppot-container">
        <?php
        if ($_SERVER['REQUEST_METHOD'] === 'POST') {
            $name = $_POST['name'];
            $email = $_POST['email'];

            $range = 'Sheet1'; // Adjust range accordingly
            $values = [[$name, $email]]; // Data to be written

            $body = new Google_Service_Sheets_ValueRange(['values' => $values]);
            $params = ['valueInputOption' => 'RAW'];
            $service = new Google_Service_Sheets($client);

            try {
                require_once __DIR__ . '/SpreadsheetModel.php';
                $spreadsheetModel = new SpreadsheetModel();
                $spreadsheetId = $spreadsheetModel->getSpreadSheetId();

                $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
                $output = "<div class='phppot-message success'>Data inserted into the Google sheet. ";
                $output .= "<a href='https://docs.google.com/spreadsheets/d/" . $spreadsheetId . "'
                 target='_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">
                <form name="frmSheetData" method="post">
                    <div class="row">
                        <label for="name">Name:</label>
                        <input type="text" name="name" class="full-width" required>
                    </div>
                    <div class="row">
                        <label for="email">Email:</label>
                        <input type="email" name="email" class="full-width" required>
                    </div>
                    <div class="row">
                        <input type="submit" value="Submit" class="full-width">
                    </div>
                </form>
            </div>
        <?php
        }
        ?>
    </div>
</body>

</html>

Download

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.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
    • PHP Cache for Dynamic Web Pages
    • How to Get Google reCaptcha Site and Secret key
    • How to Enable and Customize Google Invisible reCAPTCHA in a Webpage
    • Add Google reCaptcha V3 Example with PHP
    • How to Upload Files to Google Drive with API using PHP
    • How to embed Google Reviews on website
    • How to Read Google Sheets with PHP
    • How to Create a Personal Online PHP Editor
    • Read Facebook Data via Graph API using PHP
    • How to Read Facebook Feed Posts using PHP SDK
    • How to Get Current Location using Google Map Javascript API
    • Add Markers to Show Locations on Google Maps
    • Get Geolocation with Country by IP Address in PHP
    • Adding Custom Markers on Map using Google Maps Javascript API
    • Google Sheets PHP integration: Add form data to Sheets
    • Draw Path using Google Maps Javascript API
    • How to Draw Path on Map using Google Maps Direction API
    • How to Upload and Read Facebook User Photos using PHP SDK
    • Publish Multi-Photo Stories using Facebook API with PHP SDK
    • Trello Like Drag and Drop Cards for Project Management Software
    • PHP EvTimer Class - Usage and Examples
    • Track Individual Logged in Users using Google Analytics
    • How to Enable Multi-language Support to Website using PHP
    • PHP Poll Script
    • Getting Started with IoT using Raspberry Pi and PHP
    • Guide to Setup Raspberry Pi with LCD Display using I2C Backpack
    • Login with Twitter using OAuth1.0a Protocol via API in PHP
    • How to get metadata from webpage link to show preview in PHP
    • How to Text Share on LinkedIn using API in PHP
    • PHP Google OAuth Login
    • Simple PHP LinkedIn OAuth Login Integration
    • Simple PHP Chat using WebSocket
    • Draw Responsive Charts (Pie Bar Column) using Google Charts
    • How to Render Attendance Graph using Google Charts
    • How to Migrate Website from PHP 5.6 to PHP 7
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js