Good morning. I am working to build a web app using tabulator and appscripts with a google sheet "database" I guess you would call it. I found a tutorial online and have been following along. There have been a few issues that I have been able to find and fix. However; right now I am working to build the functionality where if you change the web app the google sheet updates in the background. I have (as far as I can tell) followed the tutorial exactly but can't seem to get the functionality to work. I have listed the code below and would appreciate any help.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://unpkg.com/tabulator-tables/dist/css/tabulator.min.css" rel="stylesheet">
</head>
<body>
<h1>Sequencing Webb App Test</h1>
<br>
<br>
<div id="jsData"></div>
<div id="alerts"></div>
<script type="text/javascript" src="https://unpkg.com/tabulator-tables/dist/js/tabulator.min.js"></script>
<script>
const elements = {}
document.addEventListener("DOMContentLoaded", pageLoad)
function pageLoad(){
elements.alerts = document.getElementById("alerts")
loadData();
}
function loadData(){
google.script.run
.withSuccessHandler((jsData) => {
// if data successfully return
const table = new Tabulator("#jsData", {
layout:"fitDataTable",
//height: 300, // set height of table (in CSS or here), this enables the Virtual DOM and improves render speed dramatically (can be any valid css height value)
data:jsData, //assign data to table
layout:"fitColumns", //fit columns to width of table (optional)
pagination: true,
paginationSize: 25,
paginationSizeSelector:[5, 10, 50],
paginationCounter:"rows",
columns:[ //Define Table Columns
{title:"Date", field:"Date"},
{title:"Requester", field:"Requester"},
{title:"Customer", field:"Customer"},
{title:"Issue Summary", field:"Issue Summar"},
{title:"Additional Information", field:"Additional Information"},
{title:"Indy", field:"Indy"},
{title:"Indy Action", field:"Indy Action"},
{title:"RDO", field:"RDO"},
{title:"RDO Action", field:"RDO Action"},
{title:"Capetown", field:"CTAction"},
{title:"Status", field:"Status"},
{title:"Condition", field:"Condition", width:50, editor:"list", editorParams: {values: ["New","In progress","Complete"]}},
{title:"ID", field:"ID", width:20,hozAlign:"center"},
],
})
// trigger an alert message when the row is clicked
// table.on("rowClick", function(e, row){
// alert("Row " + row.getData().Customer + " Clicked!!!!");
// })
table.on("cellEditied", function(cell){
const id = cell._cell.row.data.ID
const val = cell._cell.value
const field = cell._cell.column.field
// if the codition column is updated in the web app activate the if statement
if(field === "Condition"){
elements.alerts.textContent = "Saving Changes..."
google.script.run
.withSuccessHandler(() => {
elements.alerts.textContent = "Change Saved!"
})
.withFailureHandler((er) => {
elements.alerts.textContent = "Error Saving Changes!"
})
.editID({id: id, val: val});
}
})
// end if data successfully returned
})
.withFailureHandler((er) => {
})
.getData()
}
// var tabledata = [
// {id:1, name:"Oli Bob", age:"12", col:"red", dob:""},
// {id:2, name:"Mary May", age:"1", col:"blue", dob:"14/05/1982"},
// {id:3, name:"Christine Lobowski", age:"42", col:"green", dob:"22/05/1982"},
// {id:4, name:"Brendon Philips", age:"125", col:"orange", dob:"01/08/1980"},
// {id:5, name:"Margret Marmajuke", age:"16", col:"yellow", dob:"31/01/1999"},
// ];
</script>
</body>
</html>
function getData() {
const mainSS = SpreadsheetApp.getActive();
const mainSheet = mainSS.getSheetByName('Data');
const dataRange = mainSheet.getRange('A1').getDataRegion();
const displayDataRange = dataRange.getDisplayValues();
const headers = displayDataRange.shift();
// console.log(headers);
// console.log(dataRange);
const jsData = displayDataRange.map(r => {
const tempObject = {};
headers.forEach((header, i) => {
tempObject[header] = r[i]
})
return tempObject
})
console.log(jsData)
return jsData
}
// End of getData function
function editID(props){
const mainSS = SpreadsheetApp.getActive();
const mainSheet = mainSS.getSheetByName('Data');
const idCellMatch = mainSheet.getRange("N2:N").createTextFinder(props.id).findNext()
if(idCellMatch === null) throw new Error ("No matching record.")
const recordRowNumber = idCellMatch.getRow()
mainSheet.getRange(recordRowNumber, 13).setValue(props.val)
}
The expectation of the code is when I update a column (specifically the Condition column) the data will save and be sent back to the google sheet to be saved there as well. Right now, I can bring up the web app table and change the condition column as expected. However; the changes aren't returning to the google sheet to be saved.