- 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.
- 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).
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); } ?>