How to Integrate Google Sheets with a PHP Website Form: Step-by-Step Guide

Storing form data directly in Google Sheets helps improve data management and collaboration. This integration enables various team members to view and analyse data in real time, without having to deal with sophisticated database queries.

1. Create a Google Sheet

Go to Google Sheets.
Create a new sheet and name it accordingly, e.g., "Form Submissions."
In the first row, create headers for the data you want to store. For example, if you are collecting name, email, and message, create columns titled Name, Email, and Message.

2. Create a Google Apps Script Web App

Go to Extensions > Apps Script.

3. Write the Google Apps Script to Accept Form Data

function doPost(e) {
  let ss = SpreadsheetApp.openById("123123asdasd"); // Change "SpreadsheetAppId" to your actual sheet id
  let sheet = ss.getSheetByName("Sheet1"); // Change "Sheet1" to your actual sheet name

  let data;
  try {
    data = JSON.parse(e.postData.contents);
  } catch (err) {
    data = e.parameter;
  }

  sheet.appendRow([data.name, data.email, data.message]);

  return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
}

Explanation:

function doPost(e)
- This is a special function in Google Apps Script that is triggered whenever your web app receives an HTTP POST request. The e parameter contains the information sent by the form.

let ss = SpreadsheetApp.openById("123123asdasd");
- This line opens the Google Spreadsheet by its unique ID. Replace the ID "123123asdasd" with your own Google Sheet's ID, which you can find in the sheet’s URL.

var sheet = ss.getSheetByName("Sheet1");
- This retrieves the specific sheet within the spreadsheet where you want to append the form data. If your sheet has a different name, replace "Sheet1" with your actual sheet name.

var data;
- This variable will store the incoming form data after it is parsed.

try { data = JSON.parse(e.postData.contents); } catch (err) { data = e.parameter; }
- Here, the code attempts to parse the incoming data as JSON, assuming the form sends data as JSON. If parsing fails (meaning the form used a URL-encoded format instead), it falls back to e.parameter, which contains the URL-encoded form data.
- JSON.parse(e.postData.contents): Attempts to parse the body of the request as JSON.
- e.parameter: Holds the form data if it was sent in URL-encoded format (as is typical for HTML forms).

sheet.appendRow([data.fname, data.email, data.message]);
- This appends the data from the form to the next available row in the Google Sheet. The data is extracted from the data object, which contains in the form input fields

return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
- After successfully appending the data to the sheet, this line returns a success message ("Success") to the client (the website or app that submitted the form).
  - ContentService.createTextOutput("Success"): Creates a plain text response containing the word "Success".
  - setMimeType(ContentService.MimeType.TEXT): Sets the MIME type of the response to plain text.

4. Deploy the Script as a Web App

Click on Deploy > Test deployments in the top-right corner of the script editor.
Select Manage deployments, then click on New Deployment.
In the "Select type" dropdown, choose Web app.
Under Execute as, choose Me.
Under Who has access, choose Anyone.
Click Deploy and copy the generated Web App URL and copy that.

5. PHP Code to Submit Form Data to Google Apps Script Web App

Html code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Submit Form</title>
</head>
<body>
    <form method="post" action="submit.php">
        <label for="name">Name:</label>
        <input type="text" name="name" required><br>

        <label for="email">Email:</label>
        <input type="email" name="email" required><br>

        <label for="message">Message:</label>
        <textarea name="message" required></textarea><br>

        <input type="submit" value="Submit">
    </form>
</body>
</html>

PHP code:


<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $url = 'YOUR_WEB_APP_URL'; // Replace with your Google Apps Script Web App URL

    $postData = array(
        'name' => $_POST['name'],
        'email' => $_POST['email'],
        'message' => $_POST['message'],
    );

    $ch = curl_init($url);


    $postFields = http_build_query($postData);

    curl_setopt($ch, CURLOPT_POST, 1); // Send a POST request
    curl_setopt($ch, CURLOPT_POSTFIELDS, $postFields); // Attach the POST fields
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); // Return the response as a string

    $response = curl_exec($ch);

    if ($response === false) {
        $error = curl_error($ch);
        echo "cURL error: $error";
    } else {
        echo "Server response: $response";
    }

    curl_close($ch);
}
?>

Blog

How to Integrate Google Sheets with a PHP Website Form: Step-by-Step Guide

Devam Chaudhari

1. Create a Google Sheet

2. Create a Google Apps Script Web App

3. Write the Google Apps Script to Accept Form Data

Join Our Newsletter. No Spam, Only the good stuff.

Related