Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsweb-applicationsmodal-dialog

Creating button in modal dialogue and run code in spreadsheet

I making a table and show it in the modal dialogue so that buttons will appear for each row in the table. My question is how to make the button in the modal dialogue run for specific row in spreadsheet? Example : click first button in first row in modal dialogue, will run and change data in first row of spreadsheet. Do I need to create specific ID for each buttons?

My GS code:
function leadRespond(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Query_Script");
  var dataRange = sheet.getDataRange();
  var dataValue = dataRange.getDisplayValues();
   
  var temp = HtmlService.createTemplateFromFile("lead");
  temp.data = {application : dataValue};
  var html = temp.evaluate().setWidth(1200).setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html,"Manage Leave");
}

HTML code:
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
</head>
  <body>
    <h1>Leave Application</h1>
    <div id="output"></div>
    
   
    <script>
      var output = document.getElementById("output");
      
      window.onload = function (){
        google.script.run.withSuccessHandler(onSuccess).getTable();
      }

      function onSuccess(data){
        if(data.success){
        console.log(data.data);
        var html = '<table>';
        var row;
        for(var i=0; i<data.data.length; i++){
          html += '<tr>';
          row = i;
          //console.log(row);
          for (var j=0; j<9; j++){
            html += '<td>'+ data.data[i][j]+'</td>';
          }
          html += '<td>'+ '<button onclick="approve()">Approved</button>'+'</td>';
          html += '</tr>';
        }
          html += '</table>';
          output.innerHTML = html;
        console.log(data);
        }
      }

      function approve(){
        google.script.run.getRow();
        console.log("test");
      }

    </script>
  </body>
</html>

Code with google.script.run :

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(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Query_Script");
  for (var i=0; i<dataValue.length; i++){
      var row = "";
      var rowNum;
        for (var j = 0; j < 9; j++) {
          if (dataValue[i][j]) {
            row = row + dataValue[i][j];  //row = "" + range(0,0) [emailAddress], row = range(0,0)+ range(0,1)[emailAddress,Timestamp]
          }
        row = row + ",";
        }
        row = row + " Row num " + i;
        rowNum = i;
        
        Logger.log(row);
        Logger.log(rowNum);
    }
}
Solution

  • Use custom html-data attributes and delegate event to <table>:

    html += '<td>'+ '<button data-row="'+i+'" data-column="'+j+'">Approved</button>'+'</td>';

    //...
  output.innerHTML = html;
  console.log(data);
  const table = document.querySelector("table");
  table.addEventListener('click', approve);
}

function approve(e){
  const td = e.target;
  const [row, column] = [td.dataset.row,td.dataset.column];
  google.script.run.modifyRows(row,column);
}