Web Form to Google Sheet (No Backend)

A simple example of sending data from an ordinary web form straight to a Google Spreadsheet without a server.

This repo is just a quick example I made. If you want a full step-by-step tutorial, see:
https://github.com/dwyl/html-form-send-email-via-google-script-without-server

---

Why? (context)

A friend called and asked for a favor:

"Can you set up a web form that posts data to a Google Spreadsheet
without a (server) backend...? (We need it done by tomorrow)"

Never one to turn down a chance to help a friend and learn something new, I agreed to the task!

Whaaaat? (you can build things without backend?!)

The plan is to simulate the POST request an actual Google Form sends when it submits a survey response to its google ("results") spreadsheet. Sounds simple. it is. kinda ...

How? >> Google Apps Script

1. Open & Make a Copy of the Sample Spreadsheet

Open the Sample Google Spreasheet (Ensure you are signed into your Google Account)

https://docs.google.com/spreadsheets/d/10tt64TiALYhPMqR2fh9JzkuhxW7oC0rXXPb_pmJ7HAY

2. Open the Script Editor

Once you have made the copy, open the Script Editor so you can edit the App Script

3. Publish as a Web App

Without modifying the sample script, Publish it!

4. Deploy the Script as a Web App

Deploy the script as a "Web App"

5. Authorize the App for your Google Account

Authorize the script to access your Google Drive

6. Copy the URL of your newly deployed App

You will need the url for step {X} below...

I my case the url is:

https://script.google.com/macros/s/AKfycby2i5t13ccSQ9e-atuknlnPDbqKplF2QUVFiWIX_wnEPD34GM0/exec

But your will be different.

7. Run the Setup Script

The Setup Script gets the Name of your associated Google Spreadsheet so it knows where to put the data...

8. Re-Publish ...

Once you have run the Setup Script, test that it worked.

9. Test Success

Clicking that link will open a new page with a JSON result:

10. Confirm the Row was Inserted

Back in your spreadsheet, confirm that the row was inserted:

HTML Form

Now the fun part...

Copy the code from this index.html and paste it into your own index.html

12. Update the Script URL

Find the line in that defines the "ajax" request:

request = $.ajax({
  url: "https://script.google.com/macros/s/AKfycby2i5t13ccSQ9e-atuknlnPDbqKplF2QUVFiWIX_wnEPD34GM0/exec",
  type: "post",
  data: serializedData
});

And update it to be the url for your "App" which you copied in step 12.6 above!

Save and open the form in your web browser.

13. Submit the Form!

Enter some data and submit the form!

14. Confirm the Data you Submitted was Inserted in the Spreadsheet

Back in the spreadsheet, confirm that the row was inserted:

Customise!!

15. Customize the Fields as Required

<form id="gform">

  <p><label for="checkin">Checkin Date</label>
  <input id="checkin" name="checkin" type="date" value=""/></p>

  <p><label for="checkout">Checkout Date</label>
  <input id="checkout" name="checkout" type="date" value=""/></p>

  <p><label for="email">Email:</label>
  <input id="email" name="email" type="text" value=""/></p>

  <p><label for="guests">Number of Guests:</label>
  <input id="guests" name="guests" type="text" value=""/></p>

  <p id="result"></p>
  <input type="submit" value="Send"/>

</form>

Note: that the field names in the Google Spreadsheet (column headers) need to match the field names in the HTML exactly so no spaces in names, please... best to use single words and all lowercase or use underscores to separate words.

16. Submit the Form with the Custom Fields

Save index.html and open it in your browser:

Submit the form with some sample data!

17. Confirm that it inserted a new Row in the Spreadsheet

Back in your Google Sheet, confirm that the row was inserted:

Customize the Presentation

Grab the HTML from the existing page and customize the form to your heart's content. You won't need to update the JavaScript because its generic, however you will need to match the field names in your HTML form to the column headings in the Spreadsheet for the data to get inserted correctly.

Email

An additional requirement was to send an email (IKR!) when ever the HTML Form is submitted.
Thankfully, Google has a good Method for doing this: MaillApp

18. Invoke the MailApp.sendEmail Method

In your Google Spreadsheet Script call:

MailApp.sendEmail("[email protected]", // to
                  "[email protected]",       // from
                  "Your Subject Goes Here",      // email subject
                  "What ever you want to say");  // email body

For this new functionality to work in your script you will need to "Save a New Version" and (Re-)Deploy your App! (simply clicking the 💾 "Save" is Not Enough!)

19. Save a New Version of your Script

It's not immediately obvious but you have to click on "Manage Versions..."

Then create your new version:

20. Re-Publish the Updated Script as a Web App

Select the latest project version to deploy:

Click "OK". No need to update the script url in your HTML form (it does not change - there are pros & cons to this...)

Done.

Phase 2 - Phone Number Experiment

Hypothesis: for high value items some people will prefer to speak to a person on the phone instead of emails. Therefore having a form input for Phone Number will "convert"" better than email.

I've added two new files: _lon-phone.html and _par-phone.html to test this Hypothesis.

Want More?

If you want more, please ask!

Background Reading

• AJAX post to google spreadsheet: http://stackoverflow.com/questions/10000020/ajax-post-to-google-spreadsheet which points to:
  • https://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/
• MailApp Service: https://developers.google.com/apps-script/reference/mail/mail-app
• I Posted this OverFlow Question because I could not get MailApp.sendEmail to work: http://stackoverflow.com/questions/33180172/why-does-mailapp-sendemail-from-google-spreadsheet-script-not-work