Building a Web App Form to Submit Data to Google Sheets using Google Apps Script

Google Apps Script provides a powerful platform for automating tasks and extending the functionality of Google Workspace applications. In this tutorial, we’ll walk you through the process of creating a web app form that submits data to a Google Sheet when submitted. We’ll cover both the client-side code (HTML and JavaScript) and the server-side code (Google Apps Script), along with an example of the Google Sheet data structure.

Step 1: Set Up Your Google Sheet

Let’s start by setting up the Google Sheet where the form submissions will be stored. Create a new Google Sheet and add headers to define the data structure. Here’s an example table:

Name	Email	Message
John Doe	john@example.com	Hello World!
Jane Doe	jane@example.com	Goodbye World!

Step 2: Write the Google Apps Script Code

Next, we’ll write the Google Apps Script code to handle form submissions and append them to the Google Sheet.

function doPost(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const data = JSON.parse(e.postData.contents);
  const { name, email, message } = data;

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

  return ContentService.createTextOutput("Form submitted successfully!").setMimeType(ContentService.MimeType.TEXT);
}

Step 3: Create the Web App Interface

Now, let’s create the HTML form and the JavaScript code to handle form submission.

<!DOCTYPE html>
<html>
<head>
  <title>Web App Form</title>
</head>
<body>
  <h1>Submit Data to Google Sheets</h1>
  <form id="submitForm">
    <label for="name">Name:</label><br>
    <input type="text" id="name" name="name"><br>
    <label for="email">Email:</label><br>
    <input type="email" id="email" name="email"><br>
    <label for="message">Message:</label><br>
    <textarea id="message" name="message"></textarea><br>
    <input type="submit" value="Submit">
  </form>

  <script>
    document.getElementById("submitForm").addEventListener("submit", function(event) {
      event.preventDefault();
      submitForm();
    });

    function submitForm() {
      const formData = {
        name: document.getElementById("name").value,
        email: document.getElementById("email").value,
        message: document.getElementById("message").value
      };

      fetch("https://script.google.com/macros/s/your_script_id/exec", {
        method: "POST",
        body: JSON.stringify(formData)
      })
      .then(response => response.text())
      .then(data => {
        alert(data);
        document.getElementById("submitForm").reset();
      })
      .catch(error => console.error("Error:", error));
    }
  </script>
</body>
</html>

Step 4: Deploy the Web App

In the Google Apps Script editor, click on Deploy > New deployment.
Choose Web app as the deployment type.
Set who has access to the app and deploy.
Copy the deployed web app URL.

Step 5: Test the Web App

Open the deployed web app URL in a browser, fill out the form, and submit it. You should see a success message, and the form data should appear in your Google Sheet.

Conclusion

Congratulations! You’ve successfully created a web app form that submits data to a Google Sheet using Google Apps Script. This setup can be customized further to suit your specific needs, such as adding validation, implementing authentication, or enhancing the user interface.

Using the built in method of passing data to a function

Here’s the updated HTML and JavaScript code:

<!DOCTYPE html>
<html>
<head>
  <title>Web App Form</title>
</head>
<body>
  <h1>Submit Data to Google Sheets</h1>
  <form id="submitForm">
    <label for="name">Name:</label><br>
    <input type="text" id="name" name="name"><br>
    <label for="email">Email:</label><br>
    <input type="email" id="email" name="email"><br>
    <label for="message">Message:</label><br>
    <textarea id="message" name="message"></textarea><br>
    <input type="submit" value="Submit">
  </form>

  <script>
    document.getElementById("submitForm").addEventListener("submit", function(event) {
      event.preventDefault();
      submitForm();
    });

    function submitForm() {
      const formData = {
        name: document.getElementById("name").value,
        email: document.getElementById("email").value,
        message: document.getElementById("message").value
      };

      google.script.run.submitForm(formData);
    }
  </script>
</body>
</html>

And the corresponding Google Apps Script code:

function submitForm(data) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  sheet.appendRow([data.name, data.email, data.message]);
}

With this setup, when the form is submitted, the submitForm() function in the client-side JavaScript code will send the form data to the submitForm() function in the server-side Google Apps Script using google.script.run. The server-side function will then append the form data to the Google Sheet.

Remember to deploy the web app again after making these changes, and ensure that the Google Apps Script function submitForm() is published to be accessible from the web app.