Welcome folks today in this blog post we will be looking on how to submit html5 form data
to google sheets
in browser using javascript. All the full source code of the application is shown below.
Get Started
In order to get started you need to make a new google sheets
and edit with google apps script
as shown below
First of rename your google sheets to any name and save it
Now you need to add three columns inside this spreadsheet which is the Date,Email & Name as you can see below
And now you need to copy paste the below google apps script
code inside code.gs
file as shown below
Code.gs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
const sheetName = 'Sheet1' const scriptProp = PropertiesService.getScriptProperties() function intialSetup () { const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet() scriptProp.setProperty('key', activeSpreadsheet.getId()) } function doPost (e) { const lock = LockService.getScriptLock() lock.tryLock(10000) try { const doc = SpreadsheetApp.openById(scriptProp.getProperty('key')) const sheet = doc.getSheetByName(sheetName) const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0] const nextRow = sheet.getLastRow() + 1 const newRow = headers.map(function(header) { return header === 'Date' ? 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() } } |
As you can see in the above code we are getting the reference of the Spreadsheet App and then we are getting the information from the submitted html5
form by the user and then inserting the details such as Email
and Name
of the person to the respective fields. The date will automatically get added with the today date.
Now you need to run firstly the method called initialSetup
which allows the app to get the required permissions of the spreadsheet api to modify add and delete sheets as shown below
After you click the run button you will see the below screen as shown below
Adding Triggers to App
Now we need to add the below triggers to this web app. Triggers are just the actions what should happen when the user submits the form and sends the data. Where the data should go so here we will adding a trigger simply to transfer that information to the google sheet as shown below
Deploying the App
Now guys we will be deploying the web app to the live url on the internet. For this you need to follow the below instructions
Now just copy paste the web app
url which is deployed as shown above.
Creating the HTML5 Form
Now guys we will be creating the simple user html5 form where we will have input fields for name and email and then we will have the submit button and inside the action attribute of the form you need to copy paste the url of the web app which is deployed and also the method of the form will be post.
index.html
1 2 3 4 5 |
<form method="POST" action="####webappurl####"> <input name="Email" type="email" placeholder="Email" required> <input name="Name" type="text" placeholder="Name" required> <button type="submit">Send</button> </form> |
Now if you run this index.html
inside the browser and submit the information you will see automatically the data will be inserted into the google sheets