Connecting your HTML forms to a Google spreadsheet

In case you would like a video test it out on youtube at

.
HTML kinds are one a part of the frontend growth cycle that each internet developer has needed to take care of at one level or one other of their profession, and just lately a pal of mine requested if there was a solution to save information entered on an HTML kind with none backend, and the Reply was YES!!.

On this article, I’d be educating you how one can hyperlink a easy HTML kind to a google spreadsheet With the simplest, quickest method doable. All you want is an HTML kind, a Google account (to create the Google sheet), and the flexibility to repeat and paste.

The steps beneath are used to hyperlink the kinds and Sheet collectively:

1.) Create your HTML Type and add the suitable enter fields. For this instance I’d be making a suggestions kind with a reputation and message discipline
.

2.) Then log in to your Google account and create a Spreadsheet, fill within the first row of the sheet with the title of the enter fields in your HTML kind. OMIT THE FIRST COLUMN; it will be used to trace the date of every entry.

3.) whereas nonetheless on the sheet, click on on the extension menu and choose app script. This is able to open up in one other browser tab .

4.)

Rename the app script from “untitled undertaking” to no matter you need. I renamed mine to “suggestions kind”. After substitute the myFunction operate with the one beneath

const sheetName="Sheet1"
const scriptProp = PropertiesService.getScriptProperties()

operate intialSetup () {
 const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
 scriptProp.setProperty('key', activeSpreadsheet.getId())
}

operate doPost (e) {
 const lock = LockService.getScriptLock()
 lock.tryLock(10000)

 strive {
   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(operate(header) {
     return header === 'Date' ? new Date() : e.parameter[header]
   })

   sheet.getRange(nextRow, 1, 1, newRow.size).setValues([newRow])

   return ContentService
     .createTextOutput(JSON.stringify({ 'consequence': 'success', 'row': nextRow }))
     .setMimeType(ContentService.MimeType.JSON)
 }

 catch (e) {
   return ContentService
     .createTextOutput(JSON.stringify({ 'consequence': 'error', 'error': e }))
     .setMimeType(ContentService.MimeType.JSON)
 }

 lastly {
   lock.releaseLock()
 }
}

.
Save the undertaking (Ctrl + S or click on the floppy disk icon).

5.) Run the script. This could deliver up a permission dialog, observe the steps and grant all permissions required. Once you get to this half
click on upfront and proceed to the shape. If permissions have been given correctly, then it is best to see this
.

6.) create triggers for the script by clicking on the set off icon(alarm) on the sidebar and clicking the fab-like button so as to add a set off. This is able to open up a modal like this

Fill within the following choices:

• Select which operate to run: “doPost”
• Select which deployment ought to run: “Head”
• Choose occasion supply: “From Spreadsheet”

• Choose occasion kind: “On kind submit”
  Then save, this may require one other permission request which it is best to grant.

  7.) After saving, click on on the blue Deploy button on the highest proper nook and choose “New Deployment”. Then click on the “Choose kind Icon” and choose “Net App”.

  8.) Within the kind that seems fill within the description discipline, this may be no matter you need. Within the “execute as” discipline, choose “Me”. after which in “who has entry” choose “anybody”

  9.) Deploy and replica the net URL that’s proven afterward.

With the above steps, we’re completed with the google sheet side. All that’s left now could be to hyperlink the HTML Type to the sheet. There are a number of strategies of doing this however I’d be exhibiting you the one I understand to be the better and most versatile. Copy and paste the code beneath in a script tag.

<script>
   const kind = doc.querySelector("#kind")
   const submitButton = doc.querySelector("#submit")
   const scriptURL = 'https://script.google.com/macros/s/AKfycbwG9vCMBREFM4suhSiTdVPFu7-F-6JclKyZGGuKjFS-dqaZT6kKXS6r_15kub3YH2R5yw/exec'

   kind.addEventListener('submit', e => {
     submitButton.disabled = true
     e.preventDefault()
     let requestBody = new FormData(kind)
     fetch(scriptURL, { technique: 'POST', physique: requestBody})
       .then(response => {
          alert('Success!', response)
          submitButton.disabled = false
         })
       .catch(error => {
       alert('Error!', error.message)
         submitButton.disabled = false

       }
       )
   })
</script>

The above script:

• submits the shape information.
• prevents the web page from reloading.
• Disables the submit button whereas the information is being despatched to
  stop double clicks.

Conclusion.

In case you observe the directions above, then it is best to have the ability to any kind of HTML kind to a google spreadsheet. Beneath is an inventory of useful hyperlinks.