I'm trying to build a sidebar HTML form which adds the data entered into the form as a new row of the sheet when the "Submit" button is pressed. I've successfully managed to create the sidebar and the HTML form, but I can't seem to figure out how to make the data entered into the form submit and add a row. I'm pretty novice at coding, and I can't figure out why this is not working - I've followed several tutorials exactly as shown and none of them seem to work.
Here is the javascript part:
//@OnlyCurrentDoc
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu("Intake Form")
.addItem("Show Intake Form", "showAdminSidebar")
.addToUi();
}
function showAdminSidebar() {
var widget = HtmlService.createHtmlOutputFromFile("Intake Form.html");
widget.setTitle("Intake Form");
SpreadsheetApp.getUi().showSidebar(widget);
}
function appendRowFromFormSubmit(form) {
var row = [form.jobID, form.customerName, form.customerAddress, form.customerPostcode, form.customerPhone, form.customerEmail, form.applianceMake, form.applianceModel, form.reportedFault, form.servicesRequired, form.serviceLocation, form.inspectionFee, form.call-outFee];
SpreadsheetApp.getActiveSheet(Worksheet).appendRow(row);
}
Here is the code for the HTML form:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function submitForm() {
google.script.run.appendRowFromFormSubmit(document.getElementById("intakeForm"));
}
</script>
</head>
<body>
<h2>Enter Customer Details</h2>
<form id="intakeForm">
<label for="jobID">Job ID</label>
<input type="text" id="jobID" name="jobID"><br><br>
<label for="customerName">Customer Name</label>
<input type="text" id="customerName" name="customerName"><br><br>
<label for="customerAddress">Customer Address</label>
<input type="text" id="customerAddress" name="customerAddress"><br><br>
<label for="customerPostcode">Customer Postcode</label>
<input type="text" id="customerPostcode" name="customerPostcode"><br><br>
<label for="customerPhone">Customer Phone</label>
<input type="text" id="customerPhone" name="customerPhone"><br><br>
<label for="customerEmail">Customer email</label>
<input type="text" id="customerEmail" name="customerEmail"><br><br>
<label for="applianceMake">Appliance Make</label>
<input type="text" id="applianceMake" name="applianceMake"><br><br>
<label for="applianceModel">Appliance Model</label>
<input type="text" id="applianceModel" name="applianceModel"><br><br>
<label for="reportedFault">Reported Fault</label>
<input type="text" id="reportedFault" name="reportedFault"><br><br>
<div>
<label for="servicesRequested">Services Requested:</label><br>
<input type="checkbox" id="inspection" name="servicesRequested" value="Inspection" multiple>
<label for="inspection">Inspection</label><br>
<input type="checkbox" id="servicing" name="servicesRequested" value="Servicing">
<label for="servicing">Servicing</label><br>
<input type="checkbox" id="repairs" name="servicesRequested" value="Repairs">
<label for="repairs">Repairs</label><br>
<input type="checkbox" id="refurbishedAppliance" name="servicesRequested" value="Refurbished Appliance">
<label for="refurbishedAppliance">Refurbished Appliance</label><br><br>
<div>
<label for="serviceLocation">Services Requested:</label><br>
<input type="checkbox" id="atWorkshop" name="serviceLocation" value="At Workshop">
<label for="atWorkshop">At Workshop</label><br>
<input type="checkbox" id="onsite" name="serviceLocation" value="Onsite">
<label for="onsite">Onsite</label><br>
<div>
<label for="inspectionFee">Inspection Fee</label>
<input type="text" id="inspectionFee" name="inspectionFee"><br><br>
<label for="calloutFee">Call-out Fee</label>
<input type="text" id="calloutFee" name="calloutFee"><br><br>
<input type="button" value="Submit" onclick="submitForm();">
</form>
</body>
</html>
Any ideas on how to get the form data to add as a new row when I press "Submit"?
The following works on my end;
Code.gs module;
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Intake Form')
.addItem("Show Intake Form", "showAdminSidebar")
.addToUi();
}
// Sidebar
function showAdminSidebar() {
var widget = HtmlService.createHtmlOutputFromFile("Intake Form.html");
widget.setTitle("Intake Form");
SpreadsheetApp.getUi().showSidebar(widget);
}
// Inserting data from the form
function addData(array) {
SpreadsheetApp.getActiveSheet().appendRow(array);
}
.
Intake Form.html module;
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
background: #A5B6B5;
background-image: linear-gradient(rgba(203, 255, 84, 0.5) .05em, transparent .1em), linear-gradient(90deg, rgba(203, 255, 84, 0.5) .02em, transparent .1em);
background-size: 1em 1em;
}
</style>
<script>
function submitForm() {
// Select all 'input' elements with type= 'text' or type= 'checkbox' where checkbox type input elements are 'checked'
const inputs = document.querySelectorAll('input[type="text"],input[type="checkbox"]:checked');
let array = [];
for (const input of inputs) {
array.push(input.value);
}
if (array.join('') == '') {
alert('The form is empty!');
return;
}
// Send data to the sheet
google.script.run.addData(array);
// Clear form
inputs.forEach(input => input.value = '');
}
</script>
</head>
<body>
<h2>Enter Customer Details</h2>
<form id="intakeForm">
<label for="jobID">Job ID</label>
<input type="text" id="jobID" name="jobID"><br><br>
<label for="customerName">Customer Name</label>
<input type="text" id="customerName" name="customerName"><br><br>
<label for="customerAddress">Customer Address</label>
<input type="text" id="customerAddress" name="customerAddress"><br><br>
<label for="customerPostcode">Customer Postcode</label>
<input type="text" id="customerPostcode" name="customerPostcode"><br><br>
<label for="customerPhone">Customer Phone</label>
<input type="text" id="customerPhone" name="customerPhone"><br><br>
<label for="customerEmail">Customer email</label>
<input type="text" id="customerEmail" name="customerEmail"><br><br>
<label for="applianceMake">Appliance Make</label>
<input type="text" id="applianceMake" name="applianceMake"><br><br>
<label for="applianceModel">Appliance Model</label>
<input type="text" id="applianceModel" name="applianceModel"><br><br>
<label for="reportedFault">Reported Fault</label>
<input type="text" id="reportedFault" name="reportedFault"><br><br>
<div>
<label for="servicesRequested">Services Requested:</label><br>
<input type="checkbox" id="inspection" name="servicesRequested" value="Inspection" multiple>
<label for="inspection">Inspection</label><br>
<input type="checkbox" id="servicing" name="servicesRequested" value="Servicing">
<label for="servicing">Servicing</label><br>
<input type="checkbox" id="repairs" name="servicesRequested" value="Repairs">
<label for="repairs">Repairs</label><br>
<input type="checkbox" id="refurbishedAppliance" name="servicesRequested" value="Refurbished Appliance">
<label for="refurbishedAppliance">Refurbished Appliance</label><br><br>
</div>
<div>
<label for="serviceLocation">Services Requested:</label><br>
<input type="checkbox" id="atWorkshop" name="serviceLocation" value="At Workshop">
<label for="atWorkshop">At Workshop</label><br>
<input type="checkbox" id="onsite" name="serviceLocation" value="Onsite">
<label for="onsite">Onsite</label><br>
</div>
<label for="inspectionFee">Inspection Fee</label>
<input type="text" id="inspectionFee" name="inspectionFee"><br><br>
<label for="calloutFee">Call-out Fee</label>
<input type="text" id="calloutFee" name="calloutFee"><br><br>
<input type="button" value="Submit" onclick="submitForm();">
</form>
</body>
</html>