How to Connect HTML Form To Google Sheets In a Web App?
How To Create Web App With Google Apps Script — Part I
Introduction
In today’s digital age, businesses are increasingly relying on automation to streamline processes and improve efficiency. Google Workspace offers a powerful suite of applications that can be easily integrated to create automated workflows. This tutorial will walk you through the process of creating a web app that integrates Gmail, Sheets, Documents, and Calendar.
In this part, we’ll connect the HTML form with Google Sheets and save the appointment date.
You can find the source code for this project here.
Initial Setup
Initial setup includes creating a working directory and folder structure, creating two folders: Frontend and Backend. Inside the front end, we’ll create two files index.html and index.js. We’ll also be using Bootstrap and JQuery. And implementing JavaScript to validate form fields.
Index.html
<!DOCTYPE html>
<html><head>
<base target="_top">
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-T3c6CoIi6uLrA9TneNEoa7RxnatzjcDSCmG1MXxSR1GAsXEV/Dwwykc2MPK8M2HN" crossorigin="anonymous">
</head>
<body>
<div class="container-fluid px-5">
</div>
<div class="row m-4">
<div class="col-2">
</div>
<div class="col-8 col-sm-3">
<form >
<div class="mb-3">
<label for="email" class="form-label">Email address</label>
<input type="email" class="form-control" id="email" aria-describedby="emailHelp">
<div id="emailHelp" class="form-text">I'll never share your email with anyone else.</div>
</div>
<div class="mb-3">
<label for="name" class="form-label">Name</label>
<input type="text" class="form-control" id="name" aria-describedby="nameHelp">
</div>
<div class="mb-3">
<label for="address" class="form-label">Address</label>
<input type="text" class="form-control" id="address" aria-describedby="addressHelp">
<div id="addressHelp" class="form-text">Enter your address</div>
</div>
<div class="mb-3">
<label for="company" class="form-label">Company </label>
<input type="text" class="form-control" id="company" aria-describedby="companyHelp">
<div id="companyHelp" class="form-text">Enter your Organizations Details</div>
</div>
<div class="mb-3">
<select class="form-select" aria-label="Services">
<option selected>You need me to</option>
<option value="Create Add-On">Create Add-On</option>
<option value="Review Code">Review Code</option>
<option value="Build Web-App">Build Web-App</option>
<option value="other">Other</option>
</select>
</div>
<div class="mb-3">
<label for="date" class="form-label">When?</label>
<input type="date" class="form-control" id="date" name="date" aria-describedby="dateHelp">
<div id="dateHelp" class="form-text">Select Date Of Appointment On Weekdays.</div>
</div>
<div class="mb-3">
<label for="time" class="form-label">What Time?</label>
<input type="time" class="form-control" id="time" name="time" min="09:00" max="17:00" value="10:00"
aria-describedby="timeHelp">
<div id="timeHelp" class="form-text">Please select time between 9:00 AM to 5:00 PM</div>
</div>
<div class="mb-3">
<label for="messageArea">Message</label>
<textarea class="form-control" placeholder="Leave your message here" id="messageArea" name="messageArea"></textarea>
</div>
<div class="mb-3 form-check">
<input type="checkbox" class="form-check-input" id="termsAndConditions" name="termsAndConditions">
<label class="form-check-label" for="termsAndConditions">I agree with the <a href="https://nibeshkhadka.com"
target="_blank">terms and conditions </a></label>
</div>
<button type="submit" name="submit" id="submit" class="btn btn-primary">Send</button>
</form>
</div>
<div class="col-2">
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"
integrity="sha384-C6RzsynM9kWDrMNeT87bh95OGNyZPhcTNXj1NW7RuBCsyN/o0jlpcV8Qyq46cDfL"
crossorigin="anonymous"></script>
<script src="https://code.jquery.com/jquery-3.7.1.js" integrity="sha256-eKhayi8LEQwp4NKxN+CfCh+3qOVUtJn3QNZ0TciWLP4="
crossorigin="anonymous"></script>
<script>
</script>
<script src="./index.js" ></script>
</div>
</body>
</html>Index.js
$(document).ready(function () {
let name = $("#name");
let email = $("#email");
let address = $("#address");
let company = $("#company");
let date = $("#date");
let time = $("#time");
let purpose = $("select");
let message = $("#messageArea");
let dateWarningText = $('#dateHelp');
let timeWarningText = $('#timeHelp');
let submitButton = $('#submit');
let termsAndConditions = $('#termsAndConditions');
// set min date value to today
let today = new Date().toISOString().split('T')[0];
document.getElementsByName("date")[0].setAttribute('min', today);// warn user when weekend is selected as appointment date
$('#date').change(function (e) {
let d = new Date(e.target.value)
// warn if sunday or saturday
if (d.getDay() === 0 || d.getDay() === 6) {
dateWarningText.css('color', 'red');
$('#date').after(dateWarningText);
} else {
// else reset color to default
dateWarningText.css('color', "")
}
})
// warn user when office hour is not selected as appointment time
$('#time').change(function (e) {
console.log(document.getElementsByName("time")[0].value.slice(0, 2));
if (Number(e.target.value.slice(0, 2)) < 9 || Number(e.target.value.slice(0, 2)) > 17) {
timeWarningText.css('color', 'red');
}
else {
timeWarningText.css('color', '');
}
});
// disable button unless agreed to terms and conditions as well as all fields are filled.
function handleButtonClick(e) {
if (name.val().length <= 0 || address.val().length <= 0 || email.val().length <= 0 || company.val().length <= 0 || date.val().length <= 0 || time.val().length <= 0 || purpose.val().trim() === "You need me to" || !termsAndConditions.is(":checked")) {
alert("Please fill in all input boxes");
e.preventDefault(); // don't reload
}
else {
// for now let's just console log a message
console.log("All data are valid. They can be submitted to the backend.")
e.preventDefault();
}
}
submitButton.click(function (e) { handleButtonClick(e); });
});The form validation will detect empty fields, warn the user of invalid dates or times, and disable submission on empty fields. Also, we’ve made sure that any date before today can’t be selected. Validation is very basic, the project’s main focus is creating an automatic ecosystem using Google Workspace, not front-end development.
Next, we’ll connect the form with the backend. This involves implementing a backend script to handle form submissions, establishing a connection between the front end and the backend, submitting form data to the backend on submission, and storing appointment details in Google Sheets.
Using Google Spreadsheet as a Database
Coding Remotely In VS Code With Clasp
Let’s first go to Google Drive and create a Google Sheets. You can download the one I’m using from the assets folder in the GitHub repo. Then create a bound script from the spreadsheet’s tab.
To pull this project into our local directory we’ll use clasp. If you don’t have the clasp installed then check out my tutorial on using clasp with VS code. We’ll need Project Script ID to link our remote project to this cloud project. It can be found in our Apps Script project from Project Settings>Project ID.
After that in the command line inside of the project directory use this command to clone the project there.
clasp clone "YOUR PROJECT ID" --rootDir .The period, “.”, is for the current directory if you’re not inside the project folder make sure to provide a proper path instead.
Establishing a Connection between Frontend and Backend
Now, we’ll work on establishing communication between HTML form and Google Spreadsheet. For that let’s make some changes in our index.js file inside the frontend folder.
First, we’ll define BASE_URL which will be the URL address that we’ll use as API to call the backend. Its value is the URL we get after deploying our Apps Script as a Web App later on. We’ll also define the basic payload(options) that’ll be part of the JS fetch method.
const BASE_URL = "";
const PAYLOAD = {
method: "GET",
redirect: "follow",
headers: {
"Content-Type": "text/plain;charset=UTF-8",
},
};We’ll pass all the values of input fields in the HTML form as a query string. We’ll implement a function called convertPayloadToUrlEncodes() to effectively convert the key-value pairs extracted from the HTML form into a standardized query string format
// convert from {name:Nibesh, address:Helsinki} to name=Nibesh&address=Helsinki
function convertPayloadToUrlEncodes(params = {}) {
return Object.entries(params)
.map(([key, value]) => [encodeURIComponent(key), encodeURIComponent(value)].join('='))
.join('&');
};Now let’s write code for the function that’ll be making HTTP requests to the spreadsheet.
async function fetchData(params = { reqType: "nothing" }) {
// modify url
const url = `${BASE_URL}?${convertPayloadToUrlEncodes(params)}`;
const res = await fetch(url, PAYLOAD);
const data = await res.json();
console.log(data)
}Apps Script does not support standard request protocols like POST, GET, PUT, or DELETE. Instead, we must utilize a unique identifier, ‘reqType’ in this instance, to determine the appropriate function to execute. This approach will be mirrored in the backend codebase, where a switch case mechanism will handle such requests. For now, it’s crucial to understand that the ‘nothing’ reqType signifies a connection check, not any data manipulation. Additionally, given the lack of front-end actions beyond logging, we will simply record the data returned by the backend.
Next, let’s change the else block of our handleButtonClick() function.
else {
fetchData()
e.preventDefault();
}We’re not passing any values for now because we only want to check if the connection works. With all this is what our index.js file looks like.
const BASE_URL = "";
const PAYLOAD = {
method: "GET",
redirect: "follow",
headers: {
"Content-Type": "text/plain;charset=UTF-8",
},
};
function convertPayloadToUrlEncodes(params = {}) {
return Object.entries(params)
.map(([key, value]) => [encodeURIComponent(key), encodeURIComponent(value)].join('='))
.join('&');
};
async function fetchData(params = { reqType: "nothing" }) {
const url = `${BASE_URL}?${convertPayloadToUrlEncodes(params)}`;
const res = await fetch(url, PAYLOAD);
const data = await res.json();
console.log(data)
}
$(document).ready(function () {
let name = $("#name");
let email = $("#email");
let address = $("#address");
let company = $("#company");
let date = $("#date");
let time = $("#time");
let purpose = $("select");
let message = $("#messageArea");
let dateWarningText = $('#dateHelp');
let timeWarningText = $('#timeHelp');
let submitButton = $('#submit');
let termsAndConditions = $('#termsAndConditions');
// set min date value to today
let today = new Date().toISOString().split('T')[0];
document.getElementsByName("date")[0].setAttribute('min', today);
$('#date').change(function (e) {
var d = new Date(e.target.value)
// warn if sunday or saturday
if (d.getDay() === 0 || d.getDay() === 6) {
dateWarningText.css('color', 'red');
$('#date').after(dateWarningText);
} else {
dateWarningText.css('color', "")
}
})
// time
$('#time').change(function (e) {
console.log(document.getElementsByName("time")[0].value.slice(0, 2));
if (Number(e.target.value.slice(0, 2)) < 9 || Number(e.target.value.slice(0, 2)) > 17) {
timeWarningText.css('color', 'red');
}
else {
timeWarningText.css('color', '');
}
});
// disable button unless agreed to terms and conditions as well as all fields are filled.
function handleButtonClick(e) {
console.log("handleButtonClick is called");
console.log(name.val());
if (name.val().length <= 0 || address.val().length <= 0 || email.val().length <= 0 || company.val().length <= 0 || date.val().length <= 0 || time.val().length <= 0 || purpose.val().trim() === "You need me to" || !termsAndConditions.is(":checked")) {
alert("Please fill in all input boxes");
e.preventDefault(); // don't reload
}
else {
fetchData()
e.preventDefault();
}
}
submitButton.click(function (e) { handleButtonClick(e); });
});Web App and DoGet() or DoPost()
Before we write server-side code. Let’s briefly understand how exactly does connection is established as a Web App in an app script project. Apps Script provides two methods for this DoGet and DoPost. We can use either of them. These two are the method that receives the requests from the front end.
Both of these functions provide access to a JS object, “e” as mentioned in the documentation. This parameter provides access to the query strings that we’ll pass in fetchData() as part of the URL, returned by the convertPayloadToUrlEncodes() function. Each parameter can be accessed from the parameter JS object inside the e parameter. For instance: The values in the https://script.google.com/.../exec?username=jsmith&age=21 URL can be accessed as e.parameter.name and e.parameter.age.
Create an API with Apps Script
Now let’s get back to our project. We’ll create a new file “api.js” inside of our backend folder in VS code.
function doGet(e) {
try {
var reqType = e.parameter.reqType;
var param = e.parameter;
switch (reqType) {
default:
return contentService({
status: 200,
message: "Connection Successfull",
data: "",
});
}
} catch (err) {
contentService({
status: 400,
message: "Error",
data: err,
});
}
}
function contentService(data) {
return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(
ContentService.MimeType.JSON
);
}As I’ve already mentioned we’ll be using the query string reqType as the keyword to switch the API request. Since now we’re just establishing a connection we’ll only define the default switch case and return a connection successful message. We’ll also define error just in case.
ContentService in Apps Script can be used to return the output as JSON like I’m doing here.
If you’re using the DoPost() instead of the DoGet() method, make sure to change the method from “GET” to “Post” in index.js’s Payload, or else you can’t establish a connection.
Now, we’ll have to push changes from VS code to the cloud. Before that, I’ll create another file, .claspignore, in our root directory and frontend folder’s path there, because I don’t need a frontend folder in my apps script project.
**/frontend/**Now push the backend folder to the cloud with the command clasp push .
Deploying the App Script Project and Acquiring URL
Remember our BASE_URL is still empty. Before pushing changes to the cloud project, we must deploy the web app to make it accessible online. This deployment process generates a unique URL as the entry point for accessing the web app. We'll need to capture this URL to establish a proper connection from the front. Let's deploy our script as a Web App.
- Go to the apps script project in the cloud and click on the deploy button in the top right corner.
- On the popup window:
- Select Web App
- Select “Anyone” for Who has access? dropdown
- Deploy the project
- Copy the script URL and paste it as the value for
BASE_URL.
After saving everything load the HTML file in the browser fill in every input field and submit the data. The console of the browser should have logged the connection established message.
Centralized Storage for Project IDs and API Keys
Before we start coding to save the data to the spreadsheet let’s first create a configuration file, named cofig.js. It’ll be a file where we’ll save our ID for various files and folders, from Google Drive that are part of this project. We’ll also save the API key of ChatGPT here.
var GPT_SECRET_KEY = ""; // gpt secert key
var FORM_FOLDER_ID = "";// folder to store forms created from questions
var DOCS_FOLDER_ID = "";// folders to store docs ceated from user's response to the forms
var TEMPLATE_ID = "";// ID for the template used to create Docs
var APPOINTMENT_CALENDAR_ID = ""; // id for Google Calendar used to notify for calendar
var MARK_REVIEWED_IMAGE_LINK_ID = ""; // id for the image that'll be embedded into the docs
var SCRIPT_URL = ""; // URL returend after deploying the script as web app.Read comments for the purpose creation of each variable.
Create Folders and Files that are necessary for this project into your Google Drive. Mine looks like the image given below. You can download images and templates from the assets folder in the GitHub repo.
You can get Folder’s and Doc’s ID from its URL after opening it as shown in the image below.
To get IDs from files like PDF, images, etc, First, get the shareable link and then copy the highlighted section as shown in the image below.
To get or create the Secret Key for the Chat GPT, go to your profile on OpenAi.com and follow the instructions in the image below.
For URL leave it empty cause we don’t need it now.
Make sure to save all those values and push the changes to the cloud with a clasp.
Utilizing the var keyword for variable declaration instead of let or const ensures global accessibility from other files within the project.
Additionally, you’ll find the config_4_tutorial.js file instead of the config.js file in the GitHub repo, ensuring its confidentiality.
Saving HTML Form Into Google Sheets
Now, let’s save the data to the spreadsheet. For that make the following changes inside the switch method of the api.js file.
//...... continue
switch (reqType) {
case "updateAppointment":
const newAppointmentData = { ...fakeAppointmentData };
newAppointmentData.name = param.name;
newAppointmentData.email = param.email;
newAppointmentData.address = param.address;
newAppointmentData.company = param.company;
newAppointmentData.purpose = param.purpose;
newAppointmentData.date = param.date;
newAppointmentData.time = param.time;
newAppointmentData.message = param.message;
return contentService(updateAppointmentDataInSheets(newAppointmentData));
// continue ...We’re using the reqType updateAppointment as keyword here. We'll have to pass this same keyword in our index.js file later on. If the condition is satisfied we call the function updateAppointmentDataInSheets() and pass the values as JS object. We'll create this function and fakeAppointmentData JS object inside a new file sheet.js in the backend folder.
var ss = SpreadsheetApp.getActiveSpreadsheet();
// all data except the timestamp
var fakeAppointmentData = {
email: "[email protected]",
name: "Arttu Karjalainen",
address: "Helsinki",
company: "Himali Coders",
date: "2023-10-19",
time: "10:00",
message: "An issue with the script needs fixing. It was working fine, but then I changed ownership of some google folders (since I'm using the later for work typically so it's more convenient to have everything owned by that account). Now we're seeing attached error 'access denied driveapp' on execute population.",
purpose: "Review Code",
}
function updateAppointmentDataInSheets(appointmentData = fakeAppointmentData) {
try {
const sheet = ss.getSheetByName("Appointments");
const data = sheet.getDataRange().getValues();
const currentTimeStamp = getReadableDate();
if (data.length === 1) {
data.push([currentTimeStamp, appointmentData.name, appointmentData.email, appointmentData.address, appointmentData.company, appointmentData.purpose, appointmentData.date, appointmentData.time, appointmentData.message,"","","","","",""])
} else {
for (let i = 1; i < data.length; i++) {
// if name, email and time-stamp
if (data[i][0].slice(0, 10) === currentTimeStamp.slice(0, 10) && data[i][1] === appointmentData.name && data[i][2] === appointmentData.email) {
data[i][0] = currentTimeStamp;
data[i][3] = appointmentData.address;
data[i][4] = appointmentData.company;
data[i][5] = appointmentData.purpose;
data[i][6] = appointmentData.date;
data[i][7] = appointmentData.time;
data[i][8] = appointmentData.message;
// if there's a match then break the loop
break;
} else {
// if none match than append to new row
if (i === data.length - 1) {
console.log("Not match")
data.push([currentTimeStamp, appointmentData.name, appointmentData.email, appointmentData.address, appointmentData.company, appointmentData.purpose, appointmentData.date, appointmentData.time, appointmentData.message,"","","","","",""])
}
}
}
}
console.log(data);
// set new values
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
catch (e) {
return {
status: 400,
message: "Error",
data: String(e),
}
}
return {
status: 200,
message: "Appointment Sheets has been successfully updated",
data: appointmentData,
}
}A few things to notice in the function:
- The object
fakeAppointmentData, is very useful, especially for quick testing purposes. - The function
getReadableDate()returns a current date, both date and time, in human-readable format. We'll create it in a moment. - In this If block,
if (data[i][0].slice(0, 10) === currentTimeStamp.slice(0, 10) && data[i][1] === appointmentData.name && data[i][2] === appointmentData.email), we're making sure that multiple appointment submissions from clients, if they're on the same day, don't get added but updated. - Inside else block, here:
if (i === data.length - 1), we just want to push the data only once on the last iteration.
Now let’s create a new file utils.js and write code for getReadableDate().
function getReadableDate(date = Date.now()) {
const dt = new Date(date);
// padding as two digit, if incase the values returned have 1 digit only
// for instace, days/month from 1-9, time from 1-9.
return `${dt.getDate().toString().padStart(2, "0")}/${dt.getUTCMonth().toString().padStart(2, "0")}/${dt.getFullYear()},${dt.getHours().toString().padStart(2, "0")}:${dt.getMinutes().toString().padStart(2, "0")}`
}If you’re coding locally, push changes to the cloud with clasp push .
You may encounter an error because you haven’t pulled in the manifest(appsscript.json) file after that last deployment. Either copy and paste it into your local repo or pull the changes using clasp pull before pushing.
To run the function:
- First, open your sheets file in the GAS editor.
- Select the function in the dropdown in the taskbar and run it.
You should be able to see the new column with values from the fakeAppointmentData object.
Now, to make it work from the front end we’ll need to redeploy this project and get a new URL. Follow the procedure as we did during test deployment and inside the index.js file, change the BASE_URL value with this new URL.
const BASE_URL = "Your Script URL";For Web Apps in Google Apps Script, whenever you make new changes to the script, if that change is needed to be reflected in the front, you’ll have to redeploy the script as the web app again and use the latest script URL as API.’
After that, the last change we need to make is to pass the input fields and appropriate reqType , updateAppointment, as object parameters into fechData() function inside the handleButtonClick() functions else block.
function handleButtonClick(e) {
console.log("handleButtonClick is called");
console.log(name.val());
if (name.val().length <= 0 || address.val().length <= 0 || email.val().length <= 0 || company.val().length <= 0 || date.val().length <= 0 || time.val().length <= 0 || purpose.val().trim() === "You need me to" || !termsAndConditions.is(":checked")) {
alert("Please fill in all input boxes");
e.preventDefault(); // don't reload
}
else {
const appointmentData = {
reqType: "updateAppointment",
name: name.val(),
address: address.val(),
email: email.val(),
company: company.val(),
date: date.val(),
time: time.val(),
purpose: purpose.val(),
message: message.val()
};
fetchData(appointmentData);
e.preventDefault();
}Now everything is ready. Re-submit the value from your HTML form, it should work you’ll see a new row in your spreadsheet and your browser’s console should log: { status: 200, message: "Appointment Sheets has been successfully updated", data: <appointmentData>, }
Summary
Alright, this concludes part I of the tutorial series. Here we:
- Validated HTML form in simple ways.
- Connected HTML form to the Spreadsheet.
- Learned about API calls using DoGet or DoPost.
- Saved the HTML form submission to the spreadsheet like a SQL database.
In the next part of the series, we’ll use Chat GPT to create 10 questions based on the data submitted by users and save those questions into our spreadsheet. You can find the whole series as a video tutorial from here.
This is Nibesh Khadka. Make sure to like and share this post. Consider subscribing to the email list to get notified regularly about my posts.
