Editable data-table html & app script

64 views
Skip to first unread message

John Emmanuel Dela Cruz

unread,
Dec 11, 2024, 3:11:59 AM12/11/24
to Google Apps Script Community
So I have this spreadsheet tab wherein it contains multiple data from column A - Q. There are only 5 columns that are editable. I used data-table to extract the data from the sheet to a webpage. It work just fine displaying all of the data, what I need is to be able to edit the table from the HTML and it will also change the data on the spreadsheet real time. So If I edit the row on column A from the webpage, it will also change the data on the spreadsheet. I don't know if that is possible. I also need some sort of code to refresh the page every 2 seconds to have the latest update from the spreadsheet to the data-table. 

Here's my code on code.gs: 
//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getDataTable() {
  var spreadSheetId = "spreadsheet ID"; //CHANGE
  var dataRange = "SampleRange!A3:Q"; //CHANGE

  var range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange);
  var values = range.values;

  return values;
}

and here's the code my html.

<html>

<head>
  <base target="_top">
  <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/2.1.8/css/dataTables.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/3.2.0/css/buttons.dataTables.css">
  <script src="https://code.jquery.com/jquery-3.7.1.js"></script>

</head>

<body>
   <div class="container">
    <br>
    <div class="row">
      <table id="data-table" class="table table-striped table-sm table-hover table-bordered">
       
      </table>
    </div>
    </div>
</body>
</html>
<script>
  google.script.run.withSuccessHandler(showData).getDataTable();
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({        
        data: dataArray,
        columns: [
          {"title":"Date"}, <-- this need to be editable
          {"title":"Name"}, <-- this need to be editable
          {"title":"Case #"},
          {"title":"Account Name"},
          {"title":"Country"},
          {"title":"Status"},
          {"title":"Case Reason"},
          {"title":"Date Time Opened"},
          {"title":"Last Modified Date"},
          {"title":"Booking Status"},
          {"title":"Date Breaching"},
          {"title":"Remaining Hours Left"},
          {"title":"Cases Status"},
          {"title":"Status"}, <-- this need to be editable
          {"title":"Time In"}, <-- this need to be editable
          {"title":"Time Out"} <-- this need to be editable
        ],
      });
    });​
  }


   function copyToClipboard(id) {
        var from = document.getElementById(id);
        var range = document.createRange();
        window.getSelection().removeAllRanges();
        range.selectNode(from);
        window.getSelection().addRange(range);
        document.execCommand('copy');
        window.getSelection().removeAllRanges();        
 }

</script>
Reply all
Reply to author
Forward
0 new messages