One of the common ways to store user data is through a web form. However, managing the collected data can be time-consuming and challenging, especially when you have a large amount of it. Fortunately, with Google Spreadsheet, you can easily store and manage your data. In this tutorial, we'll show you how to send website form filled data to a Google Spreadsheet using JavaScript and Apps Script.
Step 1: Create a Google Spreadsheet
To start, create a new Google Spreadsheet. You can create a new spreadsheet by going to Google Drive and clicking on the "New" button. From the drop-down menu, select "Google Sheets."
Step 2: Set Up the Spreadsheet
Once you've created a new spreadsheet, set up the columns that correspond to the form fields. For example, if you have a form with "Name," "Email," and "Message" fields, you should create three columns in the spreadsheet with the same headings.
Step 3: Create a New Google Apps Script
Next, you need to create a new Google Apps Script. To do this, click on the "Tools" menu in the spreadsheet, then select "Script editor." A new tab will open with a blank script.
Step 4: Add the Code
Now, it's time to add the code to your Google Apps Script. Replace the default code in the script editor with the following code:
var TO_ADDRESS = "youremail@gmail.com";
function formatMailBody(obj, order) {
var result = "";
if (!order) {
order = Object.keys(obj);
}
for (var idx in order) {
var key = order[idx];
result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
}
return result;
}
function sanitizeInput(rawInput) {
var placeholder = HtmlService.createHtmlOutput(" ");
placeholder.appendUntrusted(rawInput);
return placeholder.getContent();
}
function doPost(e) {
try {
Logger.log(e);
record_data(e);
var mailData = e.parameters;
var orderParameter = e.parameters.formDataNameOrder;
var dataOrder;
if (orderParameter) {
dataOrder = JSON.parse(orderParameter);
}
var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;
if (sendEmailTo) {
MailApp.sendEmail({
to: String(sendEmailTo),
subject: "Contact form submitted",
htmlBody: formatMailBody(mailData, dataOrder)
});
}
return ContentService
.createTextOutput(
JSON.stringify({"result":"success",
"data": JSON.stringify(e.parameters) }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) {
Logger.log(error);
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": error}))
.setMimeType(ContentService.MimeType.JSON);
}
}
function record_data(e) {
var lock = LockService.getDocumentLock();
lock.waitLock(30000);
try {
Logger.log(JSON.stringify(e));
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = e.parameters.formGoogleSheetName || "responses";
var sheet = doc.getSheetByName(sheetName);
var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var newHeader = oldHeader.slice();
var fieldsFromForm = getDataColumns(e.parameters);
var row = [new Date()];
for (var i = 1; i < oldHeader.length; i++) {
var field = oldHeader[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
var formIndex = fieldsFromForm.indexOf(field);
if (formIndex > -1) {
fieldsFromForm.splice(formIndex, 1);
}
}
for (var i = 0; i < fieldsFromForm.length; i++) {
var field = fieldsFromForm[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
newHeader.push(field);
}
var nextRow = sheet.getLastRow() + 1;
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
if (newHeader.length > oldHeader.length) {
sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
}
}
catch(error) {
Logger.log(error);
}
finally {
lock.releaseLock();
return;
}
}
function getDataColumns(data) {
return Object.keys(data).filter(function(column) {
return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
});
}
function getFieldFromData(field, data) {
var values = data[field] || '';
var output = values.join ? values.join(', ') : values;
return output;
}
Step 5: Publish the Script as a Web App
The final step is to publish the script as a web app. To do this, click on the "Publish" menu in the script editor and select "Deploy as web app." You'll need to grant the necessary permissions to access your Google Spreadsheet.
Step 6: Connect the Form to the Google Apps Script
Finally, you need to connect the form to the Google Apps Script. You can do this by adding a simple JavaScript to your form that will call the Google Apps Script and submit the form data. Add the following code to your form:
<script>
$("#submit-form").submit((e) => {
e.preventDefault();
$.ajax({
url: "your exec id",
data: $("#submit-form").serialize(),
method: "post",
success: function (response) {
document.getElementById("submit-form").reset();
document.getElementById("outputMessage").classList.add('success');
document.getElementById("outputMessage").innerHTML = "You have been added to our mailing list successfully";
return false
},
error: function (err) {
document.getElementById("outputMessage").classList.add('fail');
document.getElementById("outputMessage").innerHTML = "Our Server is busy right now. Try again later!"
return false
},
});
});
</script>
And that's it! You've successfully set up a connection between your form and Google Spreadsheet using JavaScript and Apps Script. Every time a user submits your form, the data will be automatically added to the Google Spreadsheet, where you can easily manage it.
In conclusion, using Google Spreadsheet to store user data collected from a web
Top comments (1)
Great tutorial, thanks for sharing!
Another workflow I’ve been testing is to use ChatGPT, Gemini, Qwen or DeepSeek to extract a PDF or paper form into structured JSON. Then Form Bridge in Google Sheets can generate the HTML form from that JSON and send submitted data directly back to the Sheet.
It also supports master-detail structures with repeatable rows, which is useful for things like orders, expense claims, registrations, or inspection forms.
Curious if anyone here is using a similar AI + JSON workflow for form generation.