Home > Software engineering >  The columns aren't getting Updated in Google Sheets
The columns aren't getting Updated in Google Sheets

Time:10-21

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/[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:["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

enter image description here

CodePudding user response:

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") {
  • Related