Google Docs Spreadsheet Javascript Form Collection
Some of the ideas in this post are based from Martin Hawksey.
Google spreadsheets is a great way of and it integrates perfectly with Google forms. But what if you wanted to use your own custom form on your own website without having to use any nasty embeding.
Here is a simple way to insert data from an external form to a spreadsheet:
Setup the Spreadsheet Code
-
Go into your Google Docs and create a new spreadsheet. Name it whatever you’d like.
-
In the menu bar, click on
Tools
and selectScript editor...
from the dropdown. -
Click on
Blank Project
and replace the default script with the following script:
-
Save the project (Files -> Save) and name it whatever you’d like.
-
Click on
Run
in the menu bar and selectsetUp
. Then go ahead and authorize it in the popup window. -
Click on
Run
in the menu bar and selectsetUp
again. -
Click on
Publish
in the menu bar and selectDeploy as Web App...
. Give the project a version, make sure it is executing as yourself, and make sureAnyone, even anonymous
has access to the app. Then clickDeploy
. -
Copy the
Current web app URL
that has been generated, we’ll use that in the form later.
Setup the Form
Create a html document with your form (notice the use of the name
attribute, I’ll discuss this later):
Make sure to include jQuery (because of $.post
) before the following javascript and paste in the url from the web app URL that we generated earlier:
Setup the Spreadsheet
Now we have the code for the sheet and the form to send data to the sheet. The last step is to setup the columns in the sheet. Columns in the sheet (A1, B1, C1, …) must correspond to the name
attribute found on the input in the form in order to accept data from the form. So if I have a an input in my form with name="First Name"
, I must have a column in my sheet with First Name
.
Also, you can easily capture the timestamp of form submission within the sheet. You can simply have a column called Timestamp
in your sheet, no input in your form required.
Resoucres
Working example:
Gist with all files referenced in this post.