GAS web app, Javascript Table, doesnt sort properly on dates

Question

I have a table to consult data of what I have eaten

When I click to sort by the first column which is the date, it seems to do it fine except for the fact that it puts all the 10s right between 1 and 2, similarly with 20s, then after 3, it puts 30 and then 4,5,6,7,8 and 9

Example: 8/2/2021 Skim Milk -Protein+, Hacendado 426

8/2/2021 -Calories 150

8/2/2021 -Calories 189

8/2/2021 -Calories 230

8/10/2021 4 Cheese Pizza, Hacendado 49

8/10/2021 4 Cheese Pizza, Hacendado 91

8/10/2021 4 Cheese Pizza, Hacendado 87

8/10/2021 Totters Mini Animals Cereals, Dia 80

8/10/2021 Whole Milk, Hacendado 265

8/1/2021 -Fat 10

8/1/2021 Egg 176

8/1/2021 -Calories 550

8/1/2021 Chicken McNuggets 160

8/1/2021 Chocolate Pudding, Danone -Unit 100

8/1/2021 -Calories 100

7/31/2021 -Calories 1030

Dont mind the content of the 2 right columns

Here is the code

Code gs

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

  //GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
  function getData() {
    var spreadSheetId = "~longnumber~~dontmindthis~";                         //Google Sheet ID
    var dataRange = "FoodLogData!A2:C";                                                           //Data Sheet & Range

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

    return values;
  }

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

Index HTML

  <!DOCTYPE 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>
    <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
    <link rel="stylesheet" type="text/css"
      href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">

    <?!= include('JavaScript'); ?><!--INCLUDE JavaScript.html FILE-->
  </head>

  <body>
    <div class="container">
      <br>
      <div class="row justify-content-center mt-5">
        <table id="data-table" class="table table-striped table-sm table-hover table-bordered">
          <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
        </table>
      </div>
    </div>
  </body>

  </html>

Javascript HTML

  <script>
    /*
    *THIS FUNCTION CALL THE getData() FUNCTION IN THE Code.gs FILE, 
    *AND PASS RETURNED DATA TO showData() FUNCTION
    */
    google.script.run.withSuccessHandler(showData).getData();

    //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
    function showData(dataArray){
      $(document).ready(function(){
        $('#data-table').DataTable({
          data: dataArray,
          //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
          columns: [
            {"title":"Date"},
            {"title":"Food"},
            {"title":"Grams/%"}
            
          ]
        });
      });
    }
  </script>

You can use the DataTables date/time sorting plug-in for this. This will sort dates by chronological order, instead of by alphabetic order. You can see an example here - except in your case, you only have one date format that you need to handle. — andrewJames
– andrewJames, Commented Aug 10, 2021 at 20:21
It seems to be sorting the dates as strings. Try formatting them as dates — Cooper
– Cooper, Commented Aug 10, 2021 at 20:22

Cooper · Accepted Answer · 2021-08-10 20:36:39Z

1

I split the data on spaces so I probably messed up some of the columns (you could have avoided that by providing the data as a table).

I formatted them as dates and resorted:

8/1/2021	-Fat	10			426
8/1/2021	Egg	176
8/1/2021	-Calories	550
8/1/2021	Chicken	McNuggets	160
8/1/2021	Chocolate	Pudding,	Danone	-Unit	49
8/1/2021	-Calories	100			91
8/2/2021	Skim	Milk	-Protein+,	Hacendado	87
8/2/2021	-Calories	150			Dia	80
8/2/2021	-Calories	189
8/2/2021	-Calories	230
8/10/2021	4	Cheese	Pizza,	Hacendado
8/10/2021	4	Cheese	Pizza,	Hacendado
8/10/2021	4	Cheese	Pizza,	Hacendado
8/10/2021	Totters	Mini	Animals	Cereals,	100
8/10/2021	Whole	Milk,	Hacendado	265

You can use setNumberFormat on ranges

edited Aug 10, 2021 at 20:36

answered Aug 10, 2021 at 20:27

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

GAS web app, Javascript Table, doesnt sort properly on dates

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related