I have an HTML code form for our website page and want to connect it to Google Sheet to get Data Entry. I followed the instructions multiple times like the sample of the code.gs and made sure to get the correct WEB URL but it’s not working for me at all.
I tried help from chatgpt and I only got confused…I’m not an expert coder or something too. I just wanna learn this lol
This is the HTML Code of the form and it works really well with the website page function wise and design-wise so we want to keep it this way if possible: link here
I don’t have the Google Apps Script code anymore…must’ve have deleted it on the Google Sheet trying to go over again a lot of times. Appreciate your help lots
I tried copying the samples for code.gs to put in Google Apps Script, made sure I copied the correct WEB url and put on the HTML code in the website. I created the Google Sheet, input the data on the first row. It never worked.
New contributor
Roena Gubat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Web App using Callback Pattern to Sheets
I modified your code and there seems to be an error, I use google.script.run instead for client-side to call server-side script functions. I also include withSuccessHandler(function(response) { ... }) for a callback function that will be executed when the server-side function is running to return a response.
How to Setup Sheets and App Script
- To create a New Spreadsheet Click Create a New SpreadSheet.
- After Creating SpreadSheet Click Extension on the Menu Bar then click App Script.
-
After Clicking the App Script it will redirect to a New Window Page of App Script Development Platform.
-
Copy the code and Paste your Code.gs and index.html.
How to Deploy Web App
- Click Deploy then New Deployment.
- Click the Cog Icon, click Web App then Deploy.
- You can read more about Web App Deployment here.
Code.gs
function doGet() {
return HtmlService.createHtmlOutputFromFile('index').setTitle('Web Form App to Sheets');
}
function postForm(data) {
try {
let toAppend = []
const header = ["email", "Feelingnervousanxiousoronedge","Notbeingabletostoporcontrolworrying","Worryingtoomuchaboutdifferentthings","Troublerelaxing", "Restlesstroublesittingstill","Becomingeasilyannoyedirritable","Feelingafraidasifsomethingawfulwillhappen","score","result"];
const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
header.forEach((x) => {
Object.keys(data).forEach((key) => {
if(x === key){
toAppend.push(data[key].toString());
}
})
})
ss.appendRow(toAppend)
return {status:"success"};
} catch (err) {
return err;
}
}
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Anxiety Checkup Form</title>
<style>
/* Styling the form */
.form-container {
max-width: 600px;
margin: 0 auto;
font-family: 'Roboto', sans-serif;
/* Default font */
background-color: #ffffff;
/* White background */
border: 1px solid #ccc;
/* Border around the form */
border-radius: 10px;
/* Rounded corners */
padding: 20px;
box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1);
/* Subtle shadow */
box-sizing: border-box;
/* Ensure padding is included in the width */
}
h2,
.note {
text-align: center;
font-size: 18px;
color: #333;
/* Dark gray text */
}
.note {
font-size: 12px;
color: #555;
/* Medium gray */
margin-bottom: 10px;
}
label {
display: block;
font-size: 14px;
/* Label font size */
font-weight: regular;
/* Regular labels */
margin-bottom: 5px;
text-align: left;
color: #333;
/* Dark gray text */
text-transform: none;
/* Use proper sentence casing */
}
input[type="email"],
select {
width: calc(100% - 20px);
/* Adjust for padding */
padding: 10px;
font-size: 14px;
margin-bottom: 15px;
border: 1px solid #ccc;
border-radius: 5px;
box-sizing: border-box;
/* Ensure padding is included in width */
color: #333;
/* Dark gray text */
background-color: #fff;
/* White background */
}
button {
background-color: #e79659;
color: white;
padding: 10px 20px;
font-size: 16px;
border: none;
border-radius: 5px;
cursor: pointer;
}
button:hover {
background-color: #e79659;
}
#result {
margin-top: 20px;
font-size: 16px;
color: #333;
/* Dark gray text */
text-align: center;
}
</style>
</head>
<body>
<div class="form-container">
<form id="checkupForm">
<label for="email">Email</label>
<input type="email" id="email" name="email" placeholder="Enter your email" required />
<label>1. Feeling nervous, anxious, or on edge</label>
<select name="q1" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<label>2. Not being able to stop or control worrying</label>
<select name="q2" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<label>3. Worrying too much about different things</label>
<select name="q3" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<label>4. Trouble relaxing</label>
<select name="q4" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<label>5. Restless, trouble sitting still</label>
<select name="q5" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<label>6. Becoming easily annoyed, irritable</label>
<select name="q6" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<label>7. Feeling afraid as if something awful will happen</label>
<select name="q7" required>
<option value="" disabled selected>Select a value</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<button type="button" onclick="calculateScore()">Submit</button>
</form>
<div id="result"></div>
</div>
<script>
function calculateScore() {
const form = document.getElementById('checkupForm');
const email = form.querySelector('input[type="email"]').value;
const inputs = form.querySelectorAll('select');
let score = 0;
if (!email) {
alert('Please enter your email.');
return;
}
const answers = [];
for (let input of inputs) {
if (!input.value) {
alert('Please answer all questions.');
return;
}
const value = parseInt(input.value);
score += value;
answers.push(value);
}
let resultText = `Your score is ${score}. `;
let resultCategory;
if (score <= 4) {
resultCategory = "You are doing fine.";
} else if (score <= 7) {
resultCategory = "Your anxiety is high.";
} else {
resultCategory = "Your anxiety level is really high. Please consider seeking professional help.";
}
resultText += resultCategory;
document.getElementById('result').innerText = resultText;
// Prepare data to send to Google Sheets
const data = {
email: email,
Feelingnervousanxiousoronedge: answers[0],
Notbeingabletostoporcontrolworrying: answers[1],
Worryingtoomuchaboutdifferentthings: answers[2],
Troublerelaxing: answers[3],
Restlesstroublesittingstill: answers[4],
Becomingeasilyannoyedirritable: answers[5],
Feelingafraidasifsomethingawfulwillhappen: answers[6],
score: score,
result: resultCategory
};
google.script.run.withSuccessHandler(function(response) {
console.log(response);
if (response.status === "success") {
console.log("Data saved to Google Sheets!");
alert("Your response has been submitted successfully!");
form.reset();
} else {
console.error("Error saving data:", response.error || response);
alert("There was an issue submitting your form. Please try again.");
}
}).postForm(data);
}
</script>
</body>
</html>
Sample Output:
References:
-
Class google.script.run (Client-side API)
-
JavaScript Array forEach()
8