rezzcode ∞

Posted on May 8

Submit an HTML Form to Google Sheet

#webdev #html #productivity #programming

When creating a web app, storing and managing data is a crucial step. While traditional web servers can host your app, you might need a cost-effective and straightforward solution for data storage. Google Sheets can be an excellent choice for this purpose, requiring no additional tools or purchases.

This guide will show you how to connect an HTML form to Google Sheets to store submitted data seamlessly, but if you just need the code you can head straight to the ---> file.

Step 1: Create an HTML Form
Step 2: Create a Google Sheet and Add Apps Script
Step 3: Deploy Apps Script
Step 4: Connect Apps Script Link to Form Action
Step 5: Conclusion

Step 1: Create an HTML Form

To start, create a basic HTML form that will send data to Google Sheets. Below is a sample structure for the form:

<!DOCTYPE html>  
<html lang="en">  
<head>  
  <meta charset="UTF-8">  
  <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  <title>Submit Form to Google Sheet</title>  
</head>  
<body>  
  <h1>Submit Your Details</h1>  
  <form id="submit-form" action="Your Apps Script URL" method="POST">  
    <label for="name">Name:</label>  
    <input type="text" id="name" name="name" required><br><br>  

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

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

    <button type="submit">Submit</button>  
  </form>  
</body>  
</html>

You will need to replace your Apps Script URL with the URL generated after deploying the Apps Script (covered in later steps).

Step 2: Create a Google Sheet and Add Apps Script

Next, create a Google Sheet where the form submissions will be stored and add column headers (e.g., Name, Email, Message) to define the data fields.

  https://docs.google.com/spreadsheets

[!NOTE]

Ensure that the email you are using for the sheet is the default email in the next step, else your script app will fail to load.

All column header names must be the same as the HTML Input names.

Access Apps Script:

In your Google Sheet, click on the Extensions menu.
Select Apps Script from the dropdown.

Name the Project:

In the Apps Script interface, click Untitled Project at the top left.
Enter a name for your project, then click Rename.

Add the Script Code:

In the Code.gs file, add the following script:

function doPost(e) {
    try {
        // Open the Google Sheet by URL
        const sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/.../edit?usp=sharing").getSheetByName("Sheet1");

        // Extract data from the POST request
        const data = e.parameter;

        // Append a new row to the sheet with form data
        sheet.appendRow([
            data["name"],     // same as name field in the sheet
            data["email"],   // same as email field in the sheet
            data["message"]  // same as message field in the sheet
        ]);

        // Return a successful response
        return ContentService.createTextOutput(
            JSON.stringify({ result: "success", message: "Data submitted successfully" })
        ).setMimeType(ContentService.MimeType.JSON);

    } catch (error) {
        // Handle errors and send error response
        return ContentService.createTextOutput(
            JSON.stringify({ result: "error", message: error.message })
        ).setMimeType(ContentService.MimeType.JSON);
    }
}

The code's functionality

doPost(e), This method is used in Web Apps when we want to send data from the form through the post method, then the doPost function is called by Apps Script.
in line three, we have declared Google Sheet through the URL in the sheet variable, where we use the getSheetByName("Sheet1") method to ensure that we target the sheet named Sheet1 within the spreadsheet.
We have sent the value of multiple columns which have more than one value, i.e. name, so we have used the e.parameters object to contain key-value pairs sent in the POST request and accessible as e.parameter["name"], etc. By doing this, whatever data has been sent will be received.
The sheet.appendRow method is used to append a new row to the sheet. It accepts an array of values, with each value corresponding to a column in the row. For instance, the name field is added to the first column, email to the second, and message to the third. You can type the value of all the columns by separating them with a comma (,).
In line 15, we check if the data is successfully added to the sheet, where we create a success message using JSON.stringify to convert the results to a JSON object and send the response back with a success status.
In line 19, we check if an error occurs, and if yes, a JSON response with an error status and the error message is sent back.

So now that you know how it all functions, go to your Google sheet and click on the share button; click on the dropdown where it is written view, then change it to editor, after copying the link and in your script, replace Your Google Sheet URL with the URL you have copied of your spreadsheet. If your sheet’s name is not Sheet1, then update getSheetByName("Sheet1") on the script accordingly, then save the script.

Step 3: Deploy Apps Script

It's now time to deploy the project. To do this, you have to click on the Deploy button. After that, you have to click on New deployment. By doing this, the following window will open.

On the above screen, click on the Setting icon, then select Web App. After, give a descriptive name to the new deployment, giving access to anyone, then click on deploy. The following window will pop up, where you'll have to permit authorised access by clicking on the Authorise Access button.

Using the script in this way is not safe; there are security issues. That is why here Google gives you a warning and informs you that whatever you are doing is unsafe, still, if you want to authorise it, then select the (unsafe) option by clicking advanced, then go to unsafe.

After giving all the permissions, you are finally provided with a web app URL. You have to copy this link, and the work of Apps Script will be over. Now you have to go to the HTML Form that you created first to connect it with the app script.

Step 4: Connect Apps Script Link to Form Action

You will have to give a Web App Link on the Action Attribute of whatever HTML Form you have created. Another way to do this would be to pass it over a js script on your HTML like this:

<script>
const scriptURL = 'your copied app link';
    const form = document.forms['submit-to-google-sheet'];

    form.addEventListener('submit', e => {
        e.preventDefault();

        fetch(scriptURL, {
            method: 'POST',
            body: new FormData(form)
        })
        .then(response => response.json())
        .then(data => {
            console.log('Success!', data);
            alert("Form submitted successfully!");
        })
        .catch(error => {
            console.error('Error!', error.message);
            alert("There was an error submitting the form.");
        });
    });
</script>

or just pass it in the HTML directly like this:

<form method="POST" action="your copied app link">
  <!-- other fields -->
</form>

By giving the link, your form is now connected to Apps Script and is ready to send data to a Google Sheet, and the connection is complete.

Conclusion

By following this guide, you’ve successfully integrated an HTML form with Google Sheets, enabling seamless data collection without the need for complex backend infrastructure. This approach not only simplifies your workflow but also provides a cost-effective solution for storing and managing form submissions.

The flexibility of Google Apps Script allows for further customisation, such as sending email notifications, adding data validation, or even triggering workflows based on submissions. With this setup, you're equipped to handle basic data collection tasks efficiently.

DEV Community