I’m using a Google sheet where the table in the front end updates the data (Google webApp)
After some modification ( by adding DELETE in the comment section) the last 2 columns aren’t getting updated, I’m adding codes below for your references.
Thank you in advance.
main.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://unpkg.com/tabulator-tables@5.4.2/dist/css/tabulator_site.min.css" rel="stylesheet">
<style>
.data-table {
border: 1px solid #282828;
background-color: #111111;
}
.data-table .tabulator-header {
background-color: #080808;
font-family: Arial;
}
.data-table .tabulator-row {
background-color: #151515;
font-family: Arial;
}
.data-table .tabulator-row.tabulator-row-even {
background-color: #202020;
}
.data-table .tabulator-row.tabulator-selectable:hover {
background-color: #000;
}
.data-table .tabulator-row .tabulator-cell {
border-right-color: #393838;
}
.data-table .tabulator-row .tabulator-cell.tabulator-editing {
border: 1px solid #3FB449;
}
.data-table .tabulator-row .tabulator-cell input,
.data-table .tabulator-row .tabulator-cell select,
.data-table .tabulator-row .tabulator-cell textarea {
background-color: #121212;
color: #ccc;
}
.data-table .tabulator-footer {
background-color: #101010;
}
.data-table .tabulator-footer .tabulator-page,
.data-table .tabulator-footer .tabulator-page-size {
background: #ebebeb;
}
.data-table .tabulator-header .tabulator-col input,
.data-table .tabulator-header .tabulator-col select {
-webkit-box-sizing: border-box;
box-sizing: border-box;
padding: 4px 10px;
border: 1px solid #4b4b4b;
border-radius: 2px;
background: #1f1f1f;
color: #fff;
outline: none;
}
.data-table .tabulator-header .tabulator-col input:focus,
.data-table .tabulator-header .tabulator-col select:focus {
border-color: #3FB449;
}
.data-table .tabulator-header .tabulator-col input + input {
margin-left: 5px;
}
.data-table .tabulator-header .tabulator-cell {
color: #ccc !important;
}
.data-table .tabulator-tableholder .tabulator-table {
color: #fff;
background-color: #E54522 ;
}
.search-box-outer, .add-record-box-outer{
margin-bottom:1rem;
}
.search-box-inner, .add-record-box-inner{
background-color: #111111;
padding:0.5rem;
}
.search-box-inner input{
color: #fff;
padding:00.5rem;
background-color: #393838;
padding:00.5rem;
border: 1px solid #3FB449;
border-radius:00.2rem;
}
search-box-inner label{
color: #fff;
margin-righ:0.5rem;
padding:0.5rem;
}
.add-record-box-inner button{
color: #fff;
padding:00.5rem;
background-color: #393838;
padding:00.5rem;
border: 1px solid #3FB449;
border-radius:00.2rem;
cursor:pointer;
}
.row {
width: 100%;
text-align: left;
}
.block {
width: 100%;
display: inline-block;
}
</style>
</head>
<body>
<div class="row">
<div class ="search-box-outer">
<div class ="search-box-inner">
<div class ="add-record-box-outer">
<div class ="add-record-box-inner">
<label></label><input type="text" id="search" placeholder="Search Task...">
<button id="add-record">Add New Task</button>
</div>
</div>
</div>
</div>
<div id="data-table" class="data-table"></div>
</div>
<div id="alerts"></div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/luxon/3.0.4/luxon.min.js" integrity="sha512-XdACFfCJeqqfVU8mvvXReyFR130qjFvfv/PZOFGwVyBz0HC+57fNkSacMPF2Dyek5jqi4D7ykFrx/T7N6F2hwQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.4.2/dist/js/tabulator.min.js"></script>
<script src="luxon.js"></script>
<script>
const elements = {}
document.addEventListener("DOMContentLoaded",pageLoad)
function pageLoad(){
elements.alerts = document.getElementById("alerts")
elements.search = document.getElementById("search")
elements.addRecordButton = document.getElementById("add-record")
elements.search.addEventListener("input",searchData)
elements.addRecordButton.addEventListener("click",addRecord)
loadData()
}
function loadData(){
google.script.run
.withSuccessHandler((jsData) =>{
//If data successfully returned
//create Tabulator on DOM element with id "example-table"
elements.table = new Tabulator("#data-table", {
responsiveLayout:true,
height:505, // set height of table (in CSS or here)
data:jsData, //assign data to table
layout:"fitColumns", //fit columns to width of table (optional)
pagination:true,
paginationSize:10,
columns:[ //Define Table Columns
{title:"ID", field:"ID",width:100},
{title:"Task", field:"Task", editor:"input"},
{title:"Assigned", field:"Assigned", editor:"list", editorParams:{values:["Mark", "John","Peter","Mary"]}},
{title:"Status", field:"Status",editor:"list", editorParams:{values:["Important", "Not Urgent","This Week","Today","Urgent"]}},
{title:"Comments", field:"Comments",editor:"input"},
{title:"Task Progress", field:"Progress", hozAlign:"left", formatter:"progress", editor:true},
{title:"Complete", field:"Complete", hozAlign:"center",width:120, formatter:"tickCross",formatterParams:{crossElement: false}, sorter:"boolean", editor:true},
],
})
elements.table.on("cellEdited", function(cell){
// I added the below script.
if (cell.getValue().toUpperCase() == "DELETE" && cell.getColumn().getDefinition().title == "Comments") {
cell.getRow().delete();
}
//cell - cell component
const id = cell._cell.row.data.ID
const field = cell._cell.column.field
const type = cell._cell.column.definition.formatter
const val = type === "tickCross"? Number(cell._cell.value) : cell._cell.value
console.log(cell._cell)
if (["Assigned","Task","Comments","Status","Complete","Progress"].includes(field)){
elements.alerts.textContent = "Saving Changes..."
google.script.run
.withSuccessHandler(()=>{
elements.alerts.textContent = "SAVED!"
clearAlerts(elements.alerts)
})
.withFailureHandler((er)=>{
elements.alerts.textContent = "ERROR Saving Changes"
clearAlerts(elements.alerts)
})
.editCell({id: id, val: val, field: field})
}
});
//end if data successfully returned
})
.withFailureHandler((er) => {
})
.getData()
}
function clearAlerts(el){
setTimeout(() => {
el.textContent = ""
},2500)
}
function searchData(e){
elements.table.setFilter("Task", "like", e.target.value);
}
function addRecord(){
google.script.run
.withSuccessHandler((newId)=>{
elements.table.addRow({ID:newId}, true)
})
.withFailureHandler((er)=>{
console.log("Error adding the new record")
})
.addRecord()
}
</script>
</body>
</html>
dataServerSide.gs
function getData() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName("Data")
const dataRange = ws.getRange("A1").getDataRegion()
const data = dataRange.getDisplayValues()
const headers = data.shift()
// console.log(headers)
// console.log(data)
const jsData = data.map(r => {
const tempObject = {}
headers.forEach((header,i) => {
tempObject[header] = r[i]
})
return tempObject
})
console.log(jsData)
return jsData
}//end of get Data function
function editCell(props){
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName("Data")
const idCellMatched = ws.getRange("A2:A").createTextFinder(props.id).matchEntireCell(true).matchCase(true).findNext()
const columnCellMatched = ws.getRange("1:1").createTextFinder(props.field).matchEntireCell(true).matchCase(true).findNext()
if(idCellMatched === null) throw new Error("No Matching Record")
if(columnCellMatched === null) throw new Error("Invalid Field")
const recordRowNumber = idCellMatched.getRow()
const recordColumnNumber = columnCellMatched.getColumn()
if (props.val.toUpperCase() == "DELETE") {
ws.deleteRow(recordRowNumber);
} else {
ws.getRange(recordRowNumber, recordColumnNumber).setValue(props.val);
}
}
function addRecord(){
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName("Data")
const newId = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM-yyyy|hh:mm:ss")
Logger.log(newId)
ws.appendRow([newId])
return newId
}
I’ve added screenshot for understanding as well
>Solution :
In your situation, how about the following modification?
HTML & Javascript side:
From:
if (cell.getValue().toUpperCase() == "DELETE" && cell.getColumn().getDefinition().title == "Comments") {
To:
const value = cell.getValue();
if (typeof value == "string" && value.toUpperCase() == "DELETE" && cell.getColumn().getDefinition().title == "Comments") {
Google Apps Script side:
In this case, please modify editCell(props) as follows.
From:
if (props.val.toUpperCase() == "DELETE") {
To:
if (typeof props.val == "string" && props.val.toUpperCase() == "DELETE") {