Add 3 tables using this recource in app script-CodePudding

I was reading this blog and I wanted to implement the same what he did in there but with two more tables, I was able to get the data but pushing each one to the correct table was not possible, as it pushes the last returned values to the first table. here is the blog https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/ and here is my code as it is mentioned in the blog above

this is the HTML file

<html>
       <head>
          <base target="_top">
          <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
          <script src="https://code.jquery.com/jquery-3.5.1.js"></script>      
          <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
          <?!= include('JavaScript'); ?><!--INCLUDE JavaScript.html FILE-->
          <?!= include('StyleCSS'); ?>
          <?!= include('StyleCSSmini'); ?>
          <?!= include('Bootstrap1'); ?>
          <?!= include('Bootstrap2'); ?>
          <?!= include('dataTablesmin'); ?>
       </head>
       <title></title>
       <body >
          <div >
             <h5>Admins View</h5>
          </div>
          <section >
             <br><br><br><br>
             <div id="col-1">
                <div id="table-wrapper">
                   <div id="table-scroll">
                      <br><br>
                      <table id="data-table-1" style= "align:center;text-align:center;" >
                         <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
                      </table>
                   </div>
                   <br><br>
                </div>
             </div>
             <div id="col-2">
                <br><br>
                <div >
                   <div id="table-wrapper">
                      <div id="table-scroll">
                         <br><br>
                         <table id="data-table-2" style= "align:center;text-align:center;" >
                            <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
                         </table>
                      </div>
                      <br><br>
                   </div>
                </div>
             </div>
             <div id="col-3">
                <div id="table-wrapper">
                   <div id="table-scroll">
                      <br><br>
                      <table id="data-table-3" style= "align:center;text-align:center;" >
                         <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
                      </table>
                   </div>
                   <br><br>
                </div>
             </div>
          </section>
          <div>
             <div >
                <div >
                   <br><br>
                </div>
             </div>
          </div>
       </body>
    </html>

this is in indexgs

        function doGet() {
      return HtmlService.createTemplateFromFile('index').evaluate();
    }
    
    //GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
    function getData() {
    
      var spreadSheetId = "1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig"; //CHANGE
    
      var dataRange1 = "Sheet1!A2:E"; //CHANGE
    
      var range1 = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange1);
      var values1 = range1.values;
    
    
    
      return values1; 
      
    }
    
    function getData() {
    
      var spreadSheetId = "1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig"; //CHANGE
    
    
    
    
      var dataRange2 = "sheet2!A2:D"; //CHANGE
    
      var range2 = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange2);
    
      var values2 = range2.values;
    
    
      return values2;                             ;
      
    }
    
    
    function getData() {
    
      var spreadSheetId = "1_eBCSgD2Uzyh9pvB0v2Qu3Sl_T4NaW7HkpQ9iAtN3Ig"; //CHANGE
    
    
    
    
      var dataRange3 = "sheet3!A2:D"; //CHANGE
    
      var range3 = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange3);
    
      var values3 = range3.values;
    
    
      return values3; 
      
    }
    

    //INCLUDE JAVASCRIPT AND CSS FILES
    //REF: https://developers.google.com/apps-script/guides/html/best-practices#separate_html_css_and_javascript
    
    function include(filename) {
      return HtmlService.createHtmlOutputFromFile(filename)
        .getContent();
    }
    
    //Ref: https://datatables.net/forums/discussion/comment/145428/#Comment_145428
    //Ref: https://datatables.net/examples/styling/bootstrap4

this is in javascript.html

//THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table-1').DataTable({
        data: dataArray,
        lengthMenu: [
          [3000, 6000, 9000, 12000],
          [3000, 6000, 9000, 12000],
        ],
        //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title":"Model"},
          {"title":"Serial Number"},
          {"title":"Layout"},
          {"title":"AD"},
          {"title":"Email"},
        ]
      });
    });
  }

  google.script.run.withSuccessHandler(showData1).getData();

  //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY

    function showData1(dataArray1){
    $(document).ready(function(){
      $('#data-table-2').DataTable({
        data1: dataArray1,
        lengthMenu: [
          [3000, 6000, 9000, 12000],
          [3000, 6000, 9000, 12000],
        ],
        //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title":"Serial Number"},
          {"title":"User Name/Email"},
          {"title":"Info"},
          {"title":"Device Type"},
        ]
      });
    });
  }

  google.script.run.withSuccessHandler(showData2).getData();

  //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY

    function showData2(dataArray2){
    $(document).ready(function(){
      $('#data-table-3').DataTable({
        data2: dataArray2,
        lengthMenu: [
          [3000, 6000, 9000, 12000],
          [3000, 6000, 9000, 12000],
        ],
        //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title":"IMEI"},
          {"title":"User Name"},
          {"title":"Email"},
          {"title":"Model"},
        ]
      });
    });
  }

CodePudding user response：

You're getting close, but you need to get acquainted with the documentation to better understand how the success handlers work. For example, this line:

  google.script.run.withSuccessHandler(showData).getData();

This is saying "run the server-side function getData(), then if it succeeds, run the client-side function showData() and pass it the values returned from the server".

In your case you declared getData() in the server-side three times, which just overwrites it so you're just running the same function three times and getting the same result. You should have named the server functions getData(), getData1() and getData2() to correspond to the local showData(), showData1() and showData2(), then call them like this:

  google.script.run.withSuccessHandler(showData).getData();
  google.script.run.withSuccessHandler(showData1).getData1();
  google.script.run.withSuccessHandler(showData2).getData2();

But I think there are better ways to do it. If you rewrite the functions you can avoid repeating the getData() and showData() functions. You can replace them with this:

Javascript.html:

  function showData(dataArray, table){
      var headers = dataArray.shift()
      var columns = []
      for (var header in headers){
        columns.push({"title": headers[header]})
      }
      table.DataTable({
        data: dataArray,
        lengthMenu: [
          [3000, 6000, 9000, 12000],
          [3000, 6000, 9000, 12000],
        ],
        columns: columns
      });
  }

$(document).ready(function(){
  google.script.run.withSuccessHandler(showData).withUserObject($('#data-table-1')).getData("Sheet1!A1:E");
  google.script.run.withSuccessHandler(showData).withUserObject($('#data-table-2')).getData("Sheet2!A1:D");
  google.script.run.withSuccessHandler(showData).withUserObject($('#data-table-3')).getData("Sheet3!A1:D");
});

Code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function getData(dataRange) {

  var spreadSheetId = "your-id"; 
  var range = Sheets.Spreadsheets.Values.get(spreadSheetId, dataRange);
  var values = range.values;
  return values;

}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
}

Explanation:

I added a range parameter to getData() so you can just send the A1 notation of the sheet to the server without having to write one function for each range.
I included the top row in the range so you can use shift() to pull the header names directly from the sheet.
I also added withUserObject() to send a reference to the table element back to the callback function.

In the end to build your tables you just need to add this:

$(document).ready(function(){
google.script.run.withSuccessHandler(showData).withUserObject($('#data-table-1')).getData("Sheet1!A1:E");
});

Where you only have to specify the table object $('#data-table-1') and the sheet range "Sheet1!A1:E". There's still room for improvement so I recommend you check out the documentation to get a feel for how it works.

Sources:

Communicating with server functions