Hi everyone,
I need some help. I am by no means a programmer 
, but following tutorials I am trying to put together a form with WebApp for check out/in and reserving tools.
The trouble I have is that I would like to prevent tools that have been already checked out from being check out again. Similarly, I want to avoid having tools reserved when they have been already checked out.
Now, I’ve managed to block double check-outs, but I can’t prevent the reservations and I need help with that.
<!DOCTYPE html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Geräte Management System</title>
<base target="_top">
<!-- Metro UI -->
<link rel="stylesheet" href="https://cdn.korzh.com/metroui/v4/css/metro-all.min.css">
<link href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.5.2/animate.css" rel="stylesheet">
<style>
</style>
<!-- HEAD HTML -->
</head>
<!-- BODY HTML -->
<body>
<div class="container">
<h1 class="brand">Geräte & Autos Management System</h1>
<div class="wrapper animated bounceInLeft">
<div class="company-info">
<h3>AAB Archäologie</h3>
<ul>
<li><span class='mif-home fg-darkTeal'></span> company address</li>
<li> <span class='mif-phone fg-darkTeal'></span>company email address</li>
<li><span class='mif-envelop fg-darkTeal'></span>company phone</li>
</ul>
</div>
<div class="main">
<h3>Geräte aus- oder einchecken</h3>
<form id="main-form">
<div class="form-group">
<label>Nachname</label>
<input type="text" placeholder="Nachname" id="lname" data-prepend="<span class='mif-user fg-darkTeal'></span>"><br>
</div>
<div class="form-group">
<label>Persönlicher Code</label>
<input type="password" data-role="big-input" placeholder="Code fĂĽr den eingefĂĽgten Nachnamen" id="persid" data-prepend="<span class='mif-key fg-darkTeal'></span>"><br>
</div>
<div class="form-group">
<label>Gerätename</label>
<input type="text" data-role="big-input" placeholder="Gerätename (wie auf dem Etikett)" id="gerid" data-prepend="<span class='mif-wrench fg-darkTeal'></span>"><br>
</div>
<div class="form-group">
<label>Kommentar</label>
<textarea data-role="big-textarea" placeholder="relevante Kommentare über die Geräte" id="kommentar"data-prepend="<span class='mif-paragraph-left fg-darkTeal'></span>" rows="7"></textarea><br>
</div>
<div class="form-group">
<button id="checkOut" type="submit" data-action="Check OUT">Check OUT</button>
<button id="checkIn" type="submit" data-action="Check IN">Check IN</button>
<button id="reserviert" type="submit" data-action="IN Reserviert">Reserve</button>
</div>
<div id="message" class="d-none">
Error!!
</div>
</form>
</div>
</div>
</div>
</body>
<!-- SCRIPT .JS -->
<!-- Metro UI -->
<script src="https://cdn.korzh.com/metroui/v4/js/metro.min.js"></script>
<script>
const CheckInOutApp = {}
CheckInOutApp.onLoad = function () {
CheckInOutApp.form = document.getElementById("main-form")
CheckInOutApp.lnameInput = document.getElementById("lname")
CheckInOutApp.persidInput = document.getElementById("persid")
CheckInOutApp.geridInput = document.getElementById("gerid")
CheckInOutApp.kommentarInput = document.getElementById("kommentar")
CheckInOutApp.checkOutButton = document.getElementById("checkOut")
CheckInOutApp.checkInButton = document.getElementById("checkIn")
CheckInOutApp.reserviertButton = document.getElementById("reserviert")
CheckInOutApp.message = document.getElementById("message")
CheckInOutApp.form.addEventListener ("submit",CheckInOutApp.onSubmit)
CheckInOutApp.checkOutButton.addEventListener("click",CheckInOutApp.checkInOut)
CheckInOutApp.checkInButton.addEventListener("click",CheckInOutApp.checkInOut)
CheckInOutApp.reserviertButton.addEventListener("click",CheckInOutApp.checkInOut)
} // CheckInOutApp.onLoad function
CheckInOutApp.onSubmit = function(e){
e.preventDefault()
console.log("Form Submitted")
console.log(this)
} //CheckInOutApp.onSubmit function
CheckInOutApp.checkInOut = function(e){
const management = {
lname: CheckInOutApp.lnameInput.value,
persid: CheckInOutApp.persidInput.value,
gerid: CheckInOutApp.geridInput.value,
kommentar: CheckInOutApp.kommentarInput.value,
action: e.target.dataset.action
}
google.script.run.withSuccessHandler(() => {
lname: CheckInOutApp.lnameInput.value = ""
persid: CheckInOutApp.persidInput.value = ""
gerid: CheckInOutApp.geridInput.value = ""
kommentar: CheckInOutApp.kommentarInput.value = ""
}).withFailureHandler(() => {
CheckInOutApp.message.classList.remove("d-none")
setTimeout(() => {CheckInOutApp.message.classList.add("d-none") },3000)
}).checkInOut(management)
} // CheckInOutApp.checkInOut function
document.addEventListener("DOMContentLoaded",CheckInOutApp.onLoad)
</script>
</body>
</html>
//Code.gs
function doGet() {
return HtmlService.createHtmlOutputFromFile ("Form")
}
//function acceptData(formData){
//console.log(formData}
function checkInOut(management){
// const management = {
// lname: "Savu",
// persid: "5584",
// gerid: "Ray",
// action: "Check OUT"
// }
console.log(management)
if(!["Check OUT","Check IN"].includes(management.action)){
throw new Error ("Check IN or Check OUT Failed")
return
}
console.log("Initial check passed")
const ss = SpreadsheetApp.getActiveSpreadsheet ()
const wsData = ss.getSheetByName("Data")
const wsPersonal = ss.getSheetByName("Personal")
const personalData = wsPersonal.getRange(2,1,wsPersonal.getLastRow() -1,2).getValues()
const matchingPersonal = personalData.filter(r=> r[0].toString() === management.persid && r[1] === management.lname)
const wsInventar = ss.getSheetByName("Inventar")
const inventarName = wsInventar.getRange(2,1,wsInventar.getLastRow() -1,1).getValues()
const matchingInventar = inventarName.filter (r=> r[0] === management.gerid)
if (matchingPersonal.length !== 1){
throw new Error ("Check IN or Check OUT Failed")
return
}
if (matchingInventar.length !== 1){
throw new Error ("Check IN or Check OUT Failed")
return
}
const idsData = wsData.getRange(2,4,wsData.getLastRow() -1,2).getValues()
const matchingIdsData = idsData.filter(r => r[0].toString() === management.gerid)
console.log(matchingIdsData)
const latestAction = matchingIdsData.length === 0 ? "Check IN" : matchingIdsData[matchingIdsData.length -1][1]
console.log(latestAction)
if(latestAction === management.action){
throw new Error ("Check IN or Check OUT Failed")
return
}
wsData.appendRow([new Date(),management.lname,management.persid,management.gerid,management.action])
}
Extra: I only have a general error message for when something is not correctly inserted, but I would like them to be more concise depending on the action which is wrong (last name not valid, personal code not valid etc.). This is however not so urgent. I appreciate any help. 