Sort Table incorporated in GAS

Question

Hi I would like to ask if why the sort table function is not working if I put a condition wherein only specific data will be shown in the table. However, if I show all the data from the spreadsheet to the table the sorting function works fine. See sample code below:

    <table id="vllist" class="vllist1">
    <? var data = SpreadsheetApp
            .openById('spreadsheet ID')
            .getSheetByName("VL Request")
            .getDataRange()
            .getValues(); 
       var timeStamp = [0];     
       var rid = [1];     
       var ldap = [2];
       var aname = [7];
       var tlname = [8];
       var lob = [9];
       var dovl = [5];
       var userName = Session.getEffectiveUser().getUsername();
            ?> 

    <tr>
           <th colspan=5>Scheduler VIEW</th>    
       </tr> 
      <tr>
        <th onclick="sortTable(0)">Request ID</th>    
        <th onclick="sortTable(1)">LDAP</th>
        <th onclick="sortTable(2)">Agent Name</th>
        <th onclick="sortTable(3)">Team Lead</th>
        <th onclick="sortTable(4)">Lane</th>        
        <th onclick="sortTable(5)">Date of VL</th>
    </tr>

    <? for (var i = 1; i < data.length; i++) { ?>
            <tr> 
            <?var today = new Date(); 
             if (data[i][dovl] > today) { ?>

             <?
             var schedtimeStamp = data[i][schedtimeStampappr];
            var POCtimeStamp = data[i][POCtimeStampappr];
            var vldate = data[i][dovl];
            var formattedDateVL = (vldate.getMonth()+1) + '/' + vldate.getDate() + '/' + vldate.getYear();
            ?>

    <td>
                <?= data[i][rid] ?>
                </td>
                <td>
                <?= data[i][ldap] ?>
                </td>
                <td>
                <?= data[i][aname] ?>
                </td>
                <td>
                <?= data[i][tlname] ?>
                </td>
                <td>
                <?= data[i][lob] ?>
                </td>
                <td>
                <?= [formattedDateVL] ?>
                </td>
        <? } ?>

            </tr>
            <? } ?>

    </table>

<script>
function sortTable(n) {
  var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0;
  table = document.getElementById("vllist");
  switching = true;
  //Set the sorting direction to ascending:
  dir = "asc"; 
  /*Make a loop that will continue until
  no switching has been done:*/
  while (switching) {
    //start by saying: no switching is done:
    switching = false;
    rows = table.rows;
    /*Loop through all table rows (except the
    first, which contains table headers):*/
    for (i = 2; i < (rows.length - 1); i++) {
      //start by saying there should be no switching:
      shouldSwitch = false;
      /*Get the two elements you want to compare,
      one from current row and one from the next:*/
      x = rows[i].getElementsByTagName("TD")[n];
      y = rows[i + 1].getElementsByTagName("TD")[n];
      /*check if the two rows should switch place,
      based on the direction, asc or desc:*/
      if (dir == "asc") {
        if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
          //if so, mark as a switch and break the loop:
          shouldSwitch= true;
          break;
        }
      } else if (dir == "desc") {
        if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
          //if so, mark as a switch and break the loop:
          shouldSwitch = true;
          break;
        }
      }
    }
    if (shouldSwitch) {
      /*If a switch has been marked, make the switch
      and mark that a switch has been done:*/
      rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
      switching = true;
      //Each time a switch is done, increase this count by 1:
      switchcount ++;      
    } else {
      /*If no switching has been done AND the direction is "asc",
      set the direction to "desc" and run the while loop again.*/
      if (switchcount == 0 && dir == "asc") {
        dir = "desc";
        switching = true;
      }
    }
  }
}
</script>

Please check the code and let me know if there any other way or alternative to make a sorting table even if not all the data from the spreadsheet will be shown on the table. I just only the sorting to be fixed when each header will be clicked. :)

Does this answer your question? [Sorting HTML table with JavaScript](https://stackoverflow.com/questions/14267781/sorting-html-table-with-javascript) — Jeff Rush, Apr 13 '20 at 12:20

score 0 · Answer 1 · answered Apr 05 '20 at 20:09

Well first of all write this as a function on the server.

var data = SpreadsheetApp
            .openById('spreadsheet ID')
            .getSheetByName("VL Request")
            .getDataRange()
            .getValues(); 
       var timeStamp = [0];     
       var rid = [1];     
       var ldap = [2];
       var aname = [7];
       var tlname = [8];
       var lob = [9];
       var dovl = [5];
       var userName = Session.getEffectiveUser().getUsername();

When you get that done we can move on to the next step.

Sort Table incorporated in GAS

1 Answers1