Link your HTML Form to Google Sheet in just 3 Steps

1
Link your HTML Form to Google Sheets in just 3
Steps
by Pawan
Table of Contents

• Introduction
• Step 1 Simple UI Design
• Step 2 Generate URL with App Script
• Submit Custom HTML form data to Google Sheets
• Conclusion

Introduction
2
One of the most frustrating things about HTML
forms is that you have to do a lot of work
before extracting any useful information from
them. But in this article, we show you how you
can link an HTML form to Google Sheets. But
before might I recommend reading our other blog
post about building an HTML Form and linking to
PHPMailer for emails? Or you can learn to build a
full API with PHP, JSON, and Postman. Now,
lets jump straight to the coding part of the
HTML form to Google Sheets
Step 1 Simple UI Design
Like always we begin our journey with the
front-end part. And for rapid UI building, we
will use BootStrap 5. However, let me ask a
question why do we need an HTML form? Why they
are so important that you will never see a
website without them? Well, the answer is HTML
forms are a simple yet great way for a website to
gather feedback from its potential clients and
customers. After all, a well-running website
needs to engage readers and solve their
problems. Thats why we will first code a clean
and beautiful UI design for our HTML form. And
then we use Google Apps Script HTML form submit
to receive data in a Google Spreadsheet. This
means we are essentially replacing our databases
like MySQL or MongoDB with a simple Google
Sheet. And we all know how easy it is to operate
a Google Spreadsheet. Also, dont worry our code
works beautifully with localhost like XAMPP or
WAMP on a local PC. No need for a live server to
test this.
3
UI Design of HTML Form Enter the below Code in
your HTML file for making this UI form. I have
named my file index.html since this is the
only file we have.
lt!doctype htmlgt lthtml lang"en"gt ltheadgt ltmet
a charset"utf-8"gt ltmeta name"viewport"
content"widthdevice-width, initial-scale1"gt
lttitlegtHTML form submit to Google
Sheetlt/titlegt ltlink href"https//cdn.jsdelivr.n
et/npm/bootstrap_at_5.2.0/dist/css/bootst
rap.min.css" rel"stylesheet"gt lt/headgt
4
ltbodygt ltdiv class"container text-center"gt ltbr
gt lth1gtHTML form submit to Google
Sheetlt/h1gt ltbrgt ltdiv class"card mx-auto"
style"width 18rem"gt ltdiv class"card-body"gt
ltform name"submit-to-google-sheet"gt ltdiv
class"mb-3"gt ltinput name"fullname" type"text"
class"form-control" placeholder"Fullname"
requiredgt lt/divgt ltdiv class"mb-3"gt ltinput
name"email" type"email" class"form-control"
placeholder"Email" requiredgt lt/divgt ltbutton
type"submit" class"btn btn-primary"gtSendlt/butto
ngt
5
lt/formgt lt/divgt lt/divgt lt/divgt ltscript
src"https//cdn.jsdelivr.net/npm/bootstrap_at_5.2.0/
dist/js/bootstra p.bundle.min.js"gtlt/scriptgt lt/b
odygt lt/htmlgt
Now that we are done with our UI part. Lets get
started on the second step! We need to create a
Google Spreadsheet.
Step 2 Generate URL with App Script
Before I forget, let me tell you something. This
code enables us to submit custom HTML form data
to Google Sheets is written by levinunnink on his
GitHub Repo so feel free to show him some love
too. Moving on, now we must generate a Google
Sheet App URL which will give us the ability to
send data to Google Sheets when we submit our
HTML form. We will walk through the whole
together dont worry!
6
1. We give our Google Spreadsheet the name
submit-to-google-sheet. You can give a
different name. Just remember that we need this
name later on.
Create a Google Sheet for linking it to our HTML
Form 2. We give the same name to our Google
Sheet columns that our name attribute has in
HTML form. Remember our first input field has a
name of fullname and the second one as
email. Note You can add more fields in HTML
form. But dont forget to add them to your
Google Sheets as well. And keep them in the
correct order.
Set these Google Sheet fields (Names must be same
as HTML form Fields name attribute)
7
3. After correctly setting the field names in
Google Sheets, navigate to Extension Tab. And
choose the Apps Script function to click. This
will redirect you to a separate page.
Choose the App Script option from the Extension
Tab 4. In this Apps Script page, you will see a
sample code of the JavaScript function. No
tension! You dont need to learn JavaScript here.
Just follow along and we will send HTML form
data to Google spreadsheet in no time.
Code that needs to be replaced in the App Script
of Google Sheet
8
5. Now we just copy-paste our JavaScript code.
Here is the code var sheetName 'Sheet1'
var scriptProp PropertiesService.getScriptProper
ties() function intialSetup () var
activeSpreadsheet SpreadsheetApp.getActiveSpread
sheet() scriptProp.setProperty('key',
activeSpreadsheet.getId()) function doPost
(e) var lock LockService.getScriptLock()
lock.tryLock(10000) try var doc
SpreadsheetApp.openById(scriptProp.getProperty('ke
y')) 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)
9
return header 'timestamp' ? new Date()
e.parameterheader ) 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 .createT
extOutput(JSON.stringify( 'result' 'error',
'error' e )) .setMimeType(ContentService.MimeT
ype.JSON) finally lock.releaseLock()
10

After copy-paste your view will look like this
How our code looks after replacing the App Script
of Google Sheet Note We are almost halfway
through the coding part of sending HTML form
data to google spreadsheet. 6. Next, we just run
our Apps Script.
Now just Run this App Script in Google Sheet
11
7. When you run the script, you will be prompted
by Google to Grant permission to the script.
Give Permission to the Apps Script 8. Once
giving permission you will see the successful
execution of Apps Script. Like in the below
image shown.
Execution log which says that your code ran
successfully 9. Next step in our Google Apps
script HTML form submission is to add a trigger
and choose the correct option for this trigger.
Which are mentioned below
12

• Choose which function to run Select doPost.
  Since we are submitting our HTML form via the
  POST HTTP method.
• Choose which deployment should run Select
  Head.
• Select event source Select From spreadsheet.
• Select event type Select On form submit.
• Failure notification settings Select which one
  you prefer. We recommend the Notify me Daily
  option.

How to add Trigger and its options in Google
Sheet App Script 10. We are almost to the end.
Now that we have added a trigger to Apps Script.
We go to the Deploy button and choose the new
deployment option here.
13
Do a new deployment and publish your app 11.
When publishing the Apps Script we need to give
access to everyone. So in the who has access
option choose Anyone. Then again you might get
prompted to give access. Then finally you will
see your deployment URL.
Getting the final URL for our HTML Form to Google
Sheet 12. Copy our Apps Script deployment URL.
We will need for next section of the HTML form
to Google sheet.
14
Submit Custom HTML form data to Google Sheets
Now we have almost all the components ready. We
copy and paste the below code into our
index.html. And place our Apps Script
deployment URL in this code. And we are done.
ltscriptgt const scriptURL 'Your URL will be
here' const form document.forms'submit-to-googl
e-sheet' form.addEventListener('submit', e gt
e.preventDefault() fetch(scriptURL,
method 'POST', body new FormData(form) ) .the
n(response gt console.log('Success!',
response)) .catch(error gt console.error('Error!
', error.message)) ) lt/scriptgt
Congratulations! Without any paid script or tool
we have linked our Google Sheet with an HTML
form. After the successful submission of the HTML
form.
15
You will see the message in the console log and
you can check to see that you are receiving data
in Google Spreadsheet.
HTML Form to Google Sheets after successful
submission Feel free to download the whole code
of this project at our GitHub Repo. And check
out the blog post for submitting a form without
any refresh.
Download the code of the HTML Form to Google Sheet
Conclusion
We hope in this blog post we have shown you how
to send HTML form data to Google spreadsheet.
All the steps are super-easy. And unlike
databases like MySQL or MongoDB where we need to
use PHP to see data, Google sheet is way easy to
use. Do check out our project where we built a
form and linked it with the MySQL database.
16
If you have any other questions or concerns about
this code script, please contact us anytime.
Thank you for reading, we are always excited when
one of our posts is able to provide useful
information on a topic like this! Ta-Da! Keep
Coding!