Keep The Row Value Same After Filtering. (Google App script, Spreadsheet)

Question

(SITUATION)

I have have bunch of data in my table with a button at the end of column for each row. The data is from Spreadsheet table and I showing the table in Modal dialogue. So whenever I clicked the button in the table, the program will get the that row to run/.setValue() in Google Spreadsheet according to the row. So if the button is in row 1, it will run/.setValue() row 1 in Spreadsheet as well.

(PROBLEM)

Now if I make filter function in modal dialogue and filter it based in name for example, few row will missing right. After filtering the table(let's say row 3 become row number 1), whenever I clicked the button, it will run the first row in Spreadsheet but it suppose to run row 3.

(QUESTION)

So the question is how to keep the row value the same even after I filtered it?

(MY CODE GS GET TABLE)

function getTable(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Query_Script");
  var data = sheet.getDataRange().getDisplayValues();
  Logger.log(data);
  return {'success': true,'data':data};
}


function getRow(row,column){
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("Query_Script");
  var dataRange = sheet.getDataRange();
  var dataValue = dataRange.getDisplayValues();

  //Execute when clicked approved button.
  var value = sheet.getRange(row+1,10).setValue("Approved");

    var scriptLastRow = sheet.getLastRow();
      for(var j=1; j<scriptLastRow; j++){
        var applyLdap = sheet.getRange(j+1,3).getValue();
      }
    //Sheet ROTA :
        var ssRota = ss.getSheetByName("ROTA");
        var rotaLastRow = ssRota.getLastRow();

        var getLdap = sheet.getRange(row + 1 , 3).getValue();

          for(var x=11; x<sheet.getLastColumn(); x++){
            //Changing col to row:
            var ldapIndex = ssRota.getRange(1,4,rotaLastRow,1).getValues();//highlight ldap column
            var ldapRow = ldapIndex.map(function(row){return row[0];});//changing from column to row
            var ldap = ldapRow.indexOf(getLdap)+1;//find ldap
            var getCol = sheet.getRange(row+1,x).getValue();//value=6,7
            
            Logger.log(ldap);
            Logger.log(getCol);
      //stop execute if no value:
        if(getCol === ""){
          false;

        }else if(getCol === 9 || getCol === 10){
          ssRota.getRange(ldap,getCol).setValue("L");

        }else if(getCol % 7 === 2 || getCol % 7 === 3){
          ssRota.getRange(ldap,getCol).setValue("L");
          
        }
        else{
          ssRota.getRange(ldap,getCol).setValue("V");
        }
    }
}

(MY CODE HTML FOR TABLE)

window.onload = function (){
        google.script.run.withSuccessHandler(onSuccess).getTable();
      }

      function onSuccess(data){
        if(data.success){
        //console.log(data.data);//{application array(4), success:true}&&[array(27),Array(27)]
        var html = '<table>';
        var row;
        for(var i=0; i<data.data.length; i++){
          html += '<tr>';
          row = i;
          console.log(row);//[0,1,2,3,]
            for (var j=0; j<9; j++){
              html += '<td>'+ data.data[i][j]+'</td>';
            }
            html += '<td>'+ '<button data-row="'+i+'" data-column="'+j+'" id="btn">Approved</button>'+'</td>';
            html += '</tr>';
          }
          html += '</table>';
          output.innerHTML = html;
          //console.log(data);//{data:Array(4), success:true}
          const table = document.querySelector("table");
          table.addEventListener('click',approve);
        }
      }

      function approve(e){
        const td = e.target;
        const [row, column] = [parseInt(td.dataset.row),parseInt(td.dataset.column)];
        console.log(td);
        console.log([row,column]);
        google.script.run.getRow(row,column);
        
        const table = document.querySelectorAll("button");
        table[row].style.backgroundColor = "red";
      }

(GS CODE FILTER)

function myFilter(ldap) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Query_Script");
  var originalData = sheet.getRange(2,1,sheet.getLastRow(),10).getValues();
  var testData = sheet.getDataRange().getDisplayValues();
  var getLdap = ldap;

  var dataAgent = testData.filter(function(info){return info[0] === "Email Address" || info[2] === ldap;});

  return dataAgent;

}

ldap.onchange = function(){
          console.log(this.value);
          google.script.run.withSuccessHandler(showFilter).myFilter(this.value);
        }
      
        function showFilter(dataAgent){
          console.log(dataAgent);

          var html = '<table>';
          var row;
            for(var i=0; i<dataAgent.length; i++){
          html += '<tr>';
          row = i;
          console.log(row);//[0,1,2,3,]
            for (var j=0; j<9; j++){
              html += '<td>'+ dataAgent[i][j]+'</td>';
            }
            html += '<td>'+ '<button data-row="'+i+'" data-column="'+j+'" id="btn">Approved</button>'+'</td>';
            html += '</tr>';
          }
          html += '</table>';
          output.innerHTML = html;
          
          const table = document.querySelector("table");
          table.addEventListener('click',approve);
        }

Answer 1

Here is my solution. Basically instead of filter data in google spreadsheet and extract data back to Modal dialogue, I just created new table and assign value to each button directly in modal dialogue.

document.addEventListener("DOMContentLoaded",function(){

    google.script.run.withSuccessHandler(generateTable).getTableData();

});

function generateTable(dataArray){

  dataArray.forEach(function(r){

    var row = document.createElement("tr");


    var col1 = document.createElement("td");
      col1.textContent = r[1];
    var col2 = document.createElement("td");
      col2.textContent = r[2];
    var col7 = document.createElement("button");
      const getRow = table.rows.length;
      col7.innerHTML = "Approve";
      col7.setAttribute("class",getRow)

    row.appendChild(col1);
    row.appendChild(col2);
    row.appendChild(col3);

    tbody.appendChild(row);

getTableData() is just GS code that take data from Spreadsheet. Then I start filter it:

name.onchange = function(){
        
     for(var i=1; i<table.rows.length; i++){
          if(table.rows[i].cells[0].textContent === this.value){
            console.log(table.rows[i].cells[0].textContent);
            table.rows[i].style.display = "";

          }else{
            table.rows[i].style.display = "none";
          }
      }
  } 

So now whenever I filter it and click the button, the button will return row index and update that particular row in Spreadsheet.