I'm following a YouTube tutorial, I wrote the following script watching the video.
As you see in the screenshot, I want to delete the row when we type in the comment section as "DELETE" from the table. (The code is editable in the table itself and not in a form type)
Is there a way to do that?
main.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://unpkg.com/[email protected]/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 >
<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" ></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/[email protected]/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:["Aadhil", "Asram","Aariff","Hasitha","Proboth"]}},
{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){
//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()
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
}
CodePudding user response：
I believe your goal is as follows.
- You want to delete a row when
DELETEis put to the column "Comments" in the table.
In this case, how about the following modification?
From:
elements.table.on("cellEdited", function(cell){
To:
elements.table.on("cellEdited", function(cell){
// I added the below script.
if (cell.getValue().toUpperCase() == "DELETE") {
cell.getRow().delete();
}
or, if you want to also check the column, please use the following modification.
elements.table.on("cellEdited", function(cell){
// I added the below script.
if (cell.getValue().toUpperCase() == "DELETE" && cell.getColumn().getDefinition().title == "Comments") {
cell.getRow().delete();
}
- When this script is run, when the cell of column "Comments" is edited to "DELETE", the row is deleted.
Reference:
Added:
From I want to delete the row from the front end table and as well as in the Google sheet table (backend), in this case, please modify editCell(props) of Google Apps Script as follows.
From:
ws.getRange(recordRowNumber,recordColumnNumber).setValue(props.val)
To:
if (props.val.toUpperCase() == "DELETE") {
ws.deleteRow(recordRowNumber);
} else {
ws.getRange(recordRowNumber, recordColumnNumber).setValue(props.val);
}
Note:
When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in the report "Redeploying Web Apps without Changing URL of Web Apps for new IDE".