Google Apps Script to Submit HTML5 Form Data to Google Sheets in Browser Using Javascript

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

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()
  }
}

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

<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>

</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