When developing Jamstack applications, the concern of efficiently sending data directly to a Google Sheet often arises, without dealing with excessive overhead or convoluted integrations. Whether it's saving data from a form, collecting email addresses for a waitlist, or managing newsletter subscriptions, this article presents a straightforward method to achieve this seamless integration.
Step 1: Create a Google Sheets Spreadsheet
Begin by opening Google Sheets and creating a new spreadsheet. Take note of the spreadsheet ID from the URL, which is the long alphanumeric string located between "/d/" and "/edit."
Step 2: Set Up the Google Apps Script
Head to script.google.com and create a new project. Replace the existing code block in code.gs with the following script 👇
function doPost(e) {
var sheet = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID').getSheetByName('Sheet1');
var data = JSON.parse(e.postData.contents);
var values = Object.values(data);
sheet.appendRow(values);
return ContentService.createTextOutput("Form submitted successfully!");
}Remember to replace 'YOUR_SPREADSHEET_ID' with the actual ID of your Google Sheets spreadsheet. Save the script and hit the deploy button.
Step 3: Deploy the Google Apps Script
Select "New Deployment" and leave it at "Anyone"
for open access and ease of integration. Hit deploy, and you've successfully deployed your Google Apps Script.
Step 4: Create a Simple Form
Let's now create a straightforward form to collect email addresses for a newsletter and save them to a Google Spreadsheet.
<!DOCTYPE HTML>
<html>
<head>
<title>Subscribe to Newsletter</title>
</head>
<body>
<form id="myForm">
<label for="email">Email</label>
<input type="email" id="email" name="email" required placeholder="your email">
<input type="submit" value="Submit">
</form>
</body>
<script>
document.getElementById("myForm").addEventListener("submit", submitForm);
function submitForm(event) {
event.preventDefault();
var formData = new FormData(event.target);
var formObject = {};
formData.forEach(function(value, key) {
formObject[key] = value;
});
fetch(
"https://script.google.com/macros/s/AKfycbyBo4-jKzZRtyzOw0jjqv8O6XkKjX6xXI1O68NShygMSid7JcQrN1FES82dyZ/exec",
{
method: "POST",
body: JSON.stringify(formObject),
}
)
.then(res => {
if (res.ok) {
alert("Hello, you've successfully subscribed to our newsletter");
} else {
// Handle error if needed
}
})
.catch(err => console.log(err));
event.target.reset();
}
</script>
</html>Conclusion:
With the implementation of the simple form and the Google Apps Script, submitting the form will automatically send data to the Google Sheets spreadsheet via the Fetch API. This streamlined process provides a seamless integration between your frontend and Google Sheets, making data management a breeze.
Cheers 🥂 for reading this far, By following these steps, you can easily send data from your frontend to Google Sheets without the hassle of complex integrations, and efficiently manage various data collection tasks within your Jamstack applications.
