How am I gonna add a search function using JS and GoogleSheet as a database?
what code am I expect to write to integrate the search system, what codes of appscript, js is needed. I already have the CRUD operation. I want to add or the integrate the search system to search a specific table row or filters out as you the type letter by letter that will match the letter of the name, email or even contact Number.
This is the JS part of my code:
let api = "https://script.google.com/macros/s/AKfycbzLAn-00sPGH_7hQSuY4S605WjaWa2g3KT92GKK0a9Y5eqM0B-lFf-rME8bWlSq0zLV/exec";
let form = document.querySelector("form");
let add = document.querySelector(".add");
let update = document.querySelector(".update");
let tbody = document.querySelector("tbody");
function addData() {
add.textContent="Submitting.."
let obj = {
name:form[0].value,
email:form[1].value,
contactNo:form[2].value
}
fetch(api,{
method:"POST",
body:JSON.stringify(obj)
})
.then(res => res.text())
.then(data => {
readData()
add.textContent="Submit"
form.reset();
});
}
function readData(){
fetch(api)
.then(res=>res.json())
.then(data=>{
let name = data.name;
let trtd = name.map(each=>{
return `
<tr>
<td class="data">${each[1]}</td>
<td class="data1">${each[2]}</td>
<td class="data2">${each[3]}</td>
<td class="edit"onclick="updateCall(this,${each[0]})">Edit</td>
<td class="delete"onclick="delData(${each[0]})">Delete</td>
</tr>
`
})
tbody.innerHTML=trtd.join("");
})
}
readData()
function delData(id){
fetch(api+`?del=true&id=${id}`)
.then(res=>res.text())
.then(data=>{
readData()
})
}
function updateCall(elm,id){
add.style.display="none"
update.style.display="unset"
let name = elm.parentElement.querySelector(".data").textContent;
form[0].value=name;
let email = elm.parentElement.querySelector(".data1").textContent;
form[1].value=email;
let contactNo = elm.parentElement.querySelector(".data2").textContent;
form[2].value=contactNo;
update.setAttribute("onclick",`updateData(${id})`)
}
function updateData(id){
update.textContent="Updating.."
fetch(api+`?update=true&id=${id}&data=${form[0].value}&data1=${form[1].value}&data2=${form[2].value}`)
.then(res=>res.text())
.then(data=>{
readData()
form.reset()
update.textContent="Update"
add.style.display="unset"
update.style.display="none"
})
}
and this is the Appscript for the Googlesheet of my code:
const app = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1bul4WYtSWN5-YKZnFv5ZPbOM1YWs0PCXxtmNO1g1hz4/edit?gid=0#gid=0");
const sheet = app.getSheetByName("Sheet1");
function doGet(req){
if(req.parameter.del){
sheet.deleteRow(req.parameter.id)
return ContentService.createTextOutput("Data Deleted!")
} else if(req.parameter.update){
sheet.getRange(req.parameter.id,2).setValue(req.parameter.data);
sheet.getRange(req.parameter.id,3).setValue(req.parameter.data1);
sheet.getRange(req.parameter.id,4).setValue(req.parameter.data2)
return ContentService.createTextOutput("Data Updated!")
} else {
let data = sheet.getDataRange().getValues();
data.shift();
let obj = {
name:data,
email:data,
contactNo:data
}
return ContentService.createTextOutput(JSON.stringify(obj))
}
}
function doPost(req){
let data = JSON.parse(req.postData.contents)
sheet.appendRow(["=row()",data.name,data.email,data.contactNo])
return ContentService.createTextOutput("Data Received!")
}
Ralphjan Tocong is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.