Lately, I created an HTML form where the results are sent to a Google spreadsheet. I used it for analytics purposes, but it's useful even just for keeping track of the form's results. After trying all kinds of solutions and even searching beyond the 2nd page on Google, I found something that worked for me and I thought worth sharing.
Prerequisites: HTML and basic JavaScript knowledge.
The credit for this solution goes to Jamie Wilson who created and uploaded it to Github.
First, Create the Spreadsheet
- Go to Google Sheets and
Start a new spreadsheet
with theBlank
template. Disclaimer: It doesn't really matter how you name the file. - On the first row, write timestamp on the first column and email on the second column.
Create a Google Apps Script
- Click on
Tools > Script Editor…
which should open a new tab. - Name the script
Submit Form to Google Sheets
or however you want for you to remember what the script does. - Delete the
function myFunction() {}
block withing theCode.gs
tab. - Paste the following script in its place and
File > Save
:
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
function intialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
If you want to better understand what this script is doing, check out the
form-script-commented.js
file in the repo for a detailed explanation.
Run the setup function
- Now, go to
Run > Run Function > initialSetup
to run this function. - In the
Authorization Required
dialog, click onReview Permissions
. - Sign in or pick the Google account associated with this project.
- You should see a dialog that says
Hi {Your Name}, Submit Form to Google Sheets wants to...
- Then click
Allow
Add a new project trigger
- Click on
Edit > Current project’s triggers
. - In the dialog click
No triggers set up. Click here to add one now
. - In the dropdowns select
doPost
- Set the events fields to
From spreadsheet
andOn form submit
- Then click
Save
Publish the project as a web app
- Click on
Publish > Deploy as web app…
. - Set
Project Version
toNew
and putinitial version
in the input field below. - Leave
Execute the app as:
set toMe(your@address.com)
. - For
Who has access to the app:
selectAnyone, even anonymous
. - Click
Deploy
. - In the popup, copy the
Current web app URL
from the dialog. - And click
OK
.
IMPORTANT! If you have a custom domain with Gmail, you might need to click OK, refresh the page, and then go to
Publish > Deploy as web app…
again to get the proper web app URL. It should look something likehttps://script.google.com/a/yourdomain.com/macros/s/XXXX…
.
Input your web app URL
Open the file named index.html
. On line 7 replace <SCRIPT URL>
with your script url:
<form name="submit-to-google-sheet">
<input name="email" type="email" placeholder="Email" required>
<button type="submit">Send</button>
</form>
<script>
const scriptURL = '<SCRIPT URL>'
const form = document.forms['submit-to-google-sheet']
form.addEventListener('submit', e => {
e.preventDefault()
fetch(scriptURL, { method: 'POST', body: new FormData(form)})
.then(response => console.log('Success!', response))
.catch(error => console.error('Error!', error.message))
})
</script>
As you can see, this script uses the Fetch API, a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses FormData to pass in our data as URL parameters.
Adding additional form data
To capture additional data, you'll just need to create new columns with titles matching exactly the name
values from your form inputs. For example, if you want to add first and last name inputs, you'd give them name
values like so:
<form name="submit-to-google-sheet">
<input name="email" type="email" placeholder="Email" required>
<input name="firstName" type="text" placeholder="First Name">
<input name="lastName" type="text" placeholder="Last Name">
<button type="submit">Send</button>
</form>
Then, you can create new headers with the exact, case-sensitive name
values. Just go to the spreadsheet and add the names to the first row, each name in a different column.
Related Polyfills
Some of this stuff is not yet fully supported by browsers or doesn't work on older ones. Here are some polyfill options to use for better support.
Since the FormData polyfill is published as a Node package and needs to be compiled for browsers to work with, a good option for including these is using Browserify's CDN called wzrd.in. This service compiles, minifies and serves the latest version of these scripts for us.
You'll want to make sure these load before the main script handling the form submission. e.g.:
<script src="https://wzrd.in/standalone/formdata-polyfill"></script>
<script src="https://wzrd.in/standalone/promise-polyfill@latest"></script>
<script src="https://wzrd.in/standalone/whatwg-fetch@latest"></script>
<script>
const scriptURL = '<SCRIPT URL>'
const form = document.forms['submit-to-google-sheet']
...
</script>
And... Yep, this is it.
After trying several different methods to do this "simple" action, that's the only one that worked for me - so I hope it helps. Feel free to share it/save it for future use and feel free to comment in case you have ideas to improve it!
Latest comments (43)
What if I just want to push certain inputs not all?
Hi Omer.
Great job.
I have a task to stack data on different sheets. Could you please tell me how to modify the script to pass the sheetName value from the site?
Hi Omer
Nice work
I have the task of stacking data on different sheets. Could you please tell me how to modify the script to pass the "sheetName" value from the site?
You commented the same thing on Dec 17 '21 and on March 26.
How is this helpful? :)
Hi Omer. I get error TypeError: Cannot read property 'getId' of null
Please tell me what is causing this error. Thnks!
drive.google.com/file/d/1bIhcizg8C...
It's true, FabForm looks like a great solution. (someone mentioned it as well last December: dev.to/fabform/comment/1kha4 )
The thing is I wrote this article two years ago, on January 2020. I looked for this kind of a solution for 7 (!!!) pages on google :)
Couldn't find any other solution than this one that was suggested by Jamie Wilson . If other, easier solutions came to the market since then - I'm truly happy because finding and implement this solution was a bit of a mess. [You can see it by the amount of tech support I had to give here in the comments section :) ]
Hi Omer, I follow the steps I getting email message on this line --> form.addEventListener('submit', e => { saying that TypeError: Cannot read property 'addEventListener' of undefined I just copy what you told me.
What about security? anybody can be a fishing attack on spreadhseet. I can send tons of request by fetch method to overflow the spreadhseet. Is there any solution for this security threate?
Hi Omer! Thank you so much for this post. As a high schooler, your tutorial is much easier to follow than others.
I am sending data from a js file (without an HTML form) to my google sheet and I do get a "Success! {}" message in the console and the timestamp updates, but the rest of the columns on my sheet never update with data. I have checked to make sure my column headers and code match up and are case sensitive.
My code on Google Apps Script is the exact same as yours, and this is how I am sending data from my js file:
Could you please help me out?
Solved it! This link explains the solution: github.com/jamiewilson/form-to-goo...
But, one small change from the link above is not putting quotes in FormData() because it signifies a string, and the workaround should be to create a null HTML Form element.
So use:
var sendingData = new FormData()
Instead of:
var sendingData = new FormData('')
Solution code for js file:
I was suprised how no-one got this doGet() not found error.
I know on many Youtubers have provided the same code, they run script and it worked for them, don't know how and data was put in spreadsheet as well.
Can't i upload more than one image??
Hi Omer. Your script works very well and I am grateful to you!
I now have an issue: on submitting data from my HTML form to the sheet, how can I get a Redirect, so the user is sent to a Success or Error page?
Many thanks!
can you please tell me where have you added index.html file, on appscript or your on vs code???
Hi there mauroian, glad it helped!
About the redirecting part: How do you decide whether the user is being sent to the success/error page? Is it based on whether the form was sent successfully to the spreadsheet, by one of the form's fields or by another criterion?
All I need is that the form was sent successfully to the spreadsheet. In which case, I'd like the user to be redirected to a success page :)
Try to find this line on the current code:
And try to replace it with (You can pick either one of the options with comments, no need for both of them):
Same goes for the error part, it should do the trick.
Done that. Now nothing gets submitted to the spreadsheet (and no redirect or anything else happens).
By the way, I think this might be important: in the original code, which was working, when I successfully submitted data to the spreadsheet, my HTML page did not change at all. It continued to show the input fields and submit button.
Basically, the line:
.then(response => console.log('Success!', response))
did nothing, as no 'Success!' message was shown in my page.
So, the issue could be in how/what the google script sends as response, or in the original code in index.html itself.
I am stuck at the moment... would be so happy if this worked!
It wasn't meant to do anything else besides sending to the spreadsheet.
In the following line:
Everything between the { .... } will happen after the form is being submitted and you can modify it as much as you want, anything that happens there is up to your limits and not really relevant to this article or this mechanism :)
This whole article was only about the "sending-to-spreadsheet" part and nothing else, this is why nothing is happening other than that.
It already feels more of a tech support session than an easy js fix so just feel free to ping me wherever you'd like through one of the contact channels in my profile. The redirecting part is pure JS and can be found online but I'll try to help you out.
I have this error saying
Google hasn't verified this app
The app is requesting access to sensitive info in your Google Account
Hey Omer, this is a cool way of doing this. I actually just built a free service that does this without any need for Javascript code.
I'd love to hear what you think: sheetmonkey.io/
Bro can you run 2 scripts at the same time? 2nd script will be used for Sheet2
I haven't tried it, but it might work. Have you tried to copy the "doPost" function and change the sheet name? Just remember to do everything on the same spreadsheet.
Some comments may only be visible to logged-in visitors. Sign in to view all comments.