HTML Form to Google Sheet | Working Solution 2022

Recently, I’ve got a project in which I have to send data from HTML form to Google Sheet. After a lot of issues, I finally able to get a working solution. So, I am sharing a working solution that might help you in any of your project(s) and save your time.

Example Form

Suppose we have a following form in HTML and we’ll see how we can send its fields to google sheet.

<form id="send-to-sheet">
<input type="text" name="your-name">
<input type="email" name="your-email">
<input type="text" name="your-company">
<input type="submit" id="submitBtn"> 
</form> 

HTML Form to Google Sheet

To submit above form to google sheet, follow the below steps.

1. Create Google Sheet

First of all, you need to create a new google sheet. Click here and start a new blank spreadsheet.

Rename the newly added spreadsheet from ‘Untitled spreadsheet‘ to something that’s related to your project. ‘HTML form to Google Sheet‘ in our case.

Now, add your HTML form input fields “name attributes” in the first row of sheet. Add each cell field according to field names of your HTML form.

2. Create Script

Now, click on Tools -> Script Editor on top. It will open a new tab. Rename the script name from and make sure to wait for it to actually save and update the title before editing the script. I rename it to ‘HTML form to Google Sheet Script‘.

Now, delete the function myFunction() {} within the Code.gs tab and paste the following script.

Script credit to Jamie Wilson

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

Now save the script. File -> Save

3. Run Initial Setup Function

Now, its time to run the setup function. Click on Run -> Run function -> initialSetup. In the Authorization required popup, click Review Permission.

Sign in or select the already logged in google account. You may see a notice “This app isn’t verified”, ignore that and click Advanced and click ” Go to HTML form to Google Sheet Script (unsafe) ” on the bottom. Click Allow.

4. Add Trigger

Click on Edit -> Current project’s triggers and new page will open. Click Add Trigger or Create a new trigger. Now select the options as follows:

It will again ask for authentication, give access to it by selecting your google account.

5. Publish Project

Now come back to the script page. Click Publish -> Deploy as web app. Select Project version “new“.

Execute the app as: Select your google account. Select Anyone, even anonymous option of “Who has access to the app”

Copy “Current web app URL“, we’ll need that in the next step.

6. HTML Form to Google Sheet via Ajax

Paste the following JavaScript on your HTML form.

Don’t forget to replace “ID-OF-YOUR-FORM” with an actual id of your HTML form which is “send-to-sheet” in our example form above.

Also, make sure to replace “Your Web App URL here” with your script URL which we get in the previous step.

$('#ID-OF-YOUR-FORM').on('submit', function(e) {
 
e.preventDefault();

var jqxhr = $.ajax({

url: "Your Web App URL here",
method: "GET",
dataType: "json",
data: $('#ID-OF-YOUR-FORM').serialize()

}).success(

function(e)
{
  // Do something here on successful submit
}
);
});

If you followed all the above steps carefully and in the right way, your HTML form should start sending data to Google Spreadsheet successfully.

If you are facing any issue, or if the above steps worked for you, let us know in the comments section below.

Also Read:

• Infinite Scroll without Plugin (With CSS and jQuery Only)
• Display ACF Frontend Form for specific Fields Group
• Fix jQuery Click Event on Touch Devices (IOS)