While working on real-world applications, you may want to store your form submissions on Google Spreadsheet. It helps to view all records at a time easily. In the past, I wrote an article about integrating Google Sheets API which allows writing data on Spreadsheet programmatically. Though it works perfectly, it’s a bit complex. Recently I found a better way of connecting Google Spreadsheets with your applications. With help of Google Apps Script, one can easily connect HTML forms to Google Spreadsheets.

It requires creating a web app using Google Apps Script. This web app has a public URL that can be invoked from the external application via HTTP POST requests. This is the same as an API. You’ll post your form data to the API URL which saves your data to the destination.

Handle POST Requests With Google Apps Script

Go to Google Drive and create a Spreadsheet where you want to store your form submissions. You may add a few headings at the first row to map results. In my case, I added the following headings.

Click on the menu Extensions -> Apps Script. It’ll open the Apps Script page in the new tab. Give a name to the project.

In the script editor, write the following code to create a public URL with POST requests. It can be done using the doPost() function of Apps Script. Add the following code to the editor and save it.

const doPost = (request = {}) => {
  const { parameter, postData: { contents, type } = {} } = request;

  if (type === 'application/json') {
    const jsonData = JSON.parse(contents);
    var row = [jsonData.name, jsonData.email, jsonData.subject, jsonData.message, new Date()];
    SpreadsheetApp.getActiveSheet().appendRow(row);
    //Logger.log(row);

    result = {
      status: 'success',
      message: 'Row is added.'
    };
    return ContentService.createTextOutput(JSON.stringify(result));
  }
};

Now we need to Authorize the script. From the left side menus, go to Triggers -> Add Trigger. It’ll open a popup with all values prefilled. Make sure the function is set to doPost.

Click the Save button which prompts for Authorization. You may see the screen which says Google hasn’t verified this app. Don’t worry about this message as we are creating the web app for internal purposes. Click on Advanced and proceed with it.

At last, we need to deploy our web app. Click on Deploy -> New deployment. Choose a web app from the settings icon.

Next, execute the web app as yourself, set access to Anyone, and hit the Deploy button.

You’ll get your web app URL. Copy it. It’s nothing but your public URL to which you have to send your form data via POST requests.

If you are modifying the above script, deploy the new version with the same steps and use the latest web app URL.

Connect HTML Forms to Google Spreadsheet

We’re done with creating a public web app. The next thing you need to do is send your submission to this URL. For this, let’s create a simple HTML form with a few fields mentioned in Spreadsheet’s heading.

<form method="post">
    <p>
        <input type="text" name="fullname" placeholder="Full Name" />
    </p>
    <p>
        <input type="email" name="email" placeholder="Email" />
    </p>
    <p>
        <input type="text" name="subject" placeholder="Subject" />
    </p>
    <p>
        <textarea name="message" cols="30" rows="10" placeholder="Message"></textarea>
    </p>
    <input type="submit" name="submit" value="Submit" />
</form>

When the form is submitted, we need to collect its data and send it to the Spreadsheet. I’ll use PHP cURL to send HTTP POST requests. You may choose any programming language to incorporate these HTTP requests.

While sending the request, you have to ‘follow HTTP 3xx responses as redirects’ because App Script serves a 301 redirect from the script.googleusercontent.com domain.

<?php
if ( isset($_POST['submit']) ) {
    $url = "WEB_APP_URL";

    extract($_POST);

    $data = array(
        'name' => $fullname,
        'email' => $email,
        'subject' => $subject,
        'message' => $message,
    );
    $payload = json_encode($data);

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); // follow redirects response
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $result = curl_exec($ch);
    curl_close($ch);
    $response = json_decode($result);
    if ('success' == $response->status) {
        echo "Form is submitted successfully.";
    } else {
        echo "Something went wrong. Try again later.";
    }
}
?>

Now give it a try, and your records should be added to Google Spreadsheet.

I hope you may learn how to connect HTML forms to the Google Spreadsheet using Google Apps Script. I would like to hear your thoughts and suggestions in the comment section below.

Related Articles

• Integration of Google Cloud Vision in PHP
• Dynamically Translate Text using Google Translation API in PHP
• Upload Files to Google Cloud Storage using PHP

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.