Date filter with google sheets only acepts text data

SAGAHE

Hi, this is my code
https://live.datatables.net/rezopowi/1/

date filter only works when i have text format data, data its in a google sheets spreadsheet, obviously when data is captured it has date format, so in every new register dataTable doesnt show records, if i go to the spreadsheet and format date to text datatable shows all records. if i delete date filter datatable works again but with date formats. i cant find why it needs text format date. Thanks in advance.

SAGAHE

this its my code
function showData(dataArray){

        var minDate, maxDate;

        // Custom filtering function which will search data in column four between two values
        $.fn.dataTable.ext.search.push(
            function( settings, data, dataIndex ) {
                var min = minDate.val();
                var max = maxDate.val();
                var date = new Date( moment(data[20], 'DD/MM/YYYY').format('YYYY-MM-DD') );

                if (
                    ( min === null && max === null ) ||
                    ( min === null && date <= max ) ||
                    ( min <= date   && max === null ) ||
                    ( min <= date   && date <= max )
                ) {
                    return true;
                }
                return false;
            }
        );


$(document).ready(function(){

  minDate = new DateTime($('#min'), {
    format: 'DD/MM/YYYY'
});
maxDate = new DateTime($('#max'), {
    format: 'DD/MM/YYYY'
});

var table = $('#data-table').DataTable({
data: dataArray,

    columnDefs:[{
         target: [29],
            render: function (data, type, row) {
                return `

                    <button class="btn btn-sm btn-outline-dark " onclick="editarUsuarioModal(this) ">Editar</button>
                `;

            },


    },
    {"className": "dt-center", "targets": "_all"},
    {"className": "dt-center", "targets": [29] }],

    /*responsive: true,*/
    //MATCH WITH YOUR SELECTED DATA RANGE
    columns: [

      {"title":"ID"},
      {"title":"Cuenta"},
      {"title":"Campus"},
      {"title":"Programa"},
      {"title":"Nombre"},
      {"title":"Apaterno"},
      {"title":"Amaterno"},
      {"title":"Autorización"},
      {"title":"CURP"},
      {"title":"Edad"},
      {"title":"Sexo"},
      {"title":"Opción"},
      {"title":"Tipo"},
      {"title":"Inicio"},
      {"title":"Término"},
      {"title":"Email"},
      {"title":"Teléfono"},
      {"title":"Hablante"},
      {"title":"Discapacidad"},
      {"title":"Folio"},
      {"title":"Solicitud"},
      {"title":"Fase"},
      {"title":"Impresión"},
      {"title":"Recepción"},
      {"title":"Adeudo"},
      {"title":"Constancia"},
      {"title":"Entrega"},
      {"title":"Dias"},
      {"title":"Observaciones"}



      ],

      buttons: [
        {
            extend:    'copyHtml5',
            text:      '<i class="fa fa-files-o"></i>',
            titleAttr: 'Copiar'
        },
        {
            extend:    'excelHtml5',
            text:      '<i class="fa fa-file-excel-o"></i>', 
            titleAttr: 'Excel'
        },
        {
            extend:    'print',
            text:      '<i class="fa fa-print"></i>',
            titleAttr: 'Imprimir'
        },
        {
            extend:    'pdfHtml5',
            text:      '<i class="fa fa-file-pdf-o"></i>',
            titleAttr: 'PDF',
            title: 'REPORTE DE SOLICITUDES DE CERTIFICADOS',
            orientation: 'landscape',
              pageSize: 'Legal',
              exportOptions: {
              columns: [1, 3, 4, 5, 6, 7, 11, 12, 20, 21]},
              customize: function ( doc ) {
              doc.content[1].table.widths = [
              '10%',
              '15%',
              '10%',
              '10%',
              '10%',
              '10%',
              '5%',
              '10%',
              '10%',
              '10%'
            ]}
        },
        {
         extend: 'colvis',
         text: '<i class="bi bi-list-columns"></i>',
         className: 'btn-primary', 
                titleAttr: 'Mostrar/Ocultar Columnas'
            }

    ],


      dom: 
          "<'row'<'col-sm-12 col-md-4'l><'col-sm-12 col-md-4'B><'col-sm-12 col-md-4'f>>" +
          "<'row'<'col-sm-12'tr>>" +
          "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>"
  }); 
    $('#min, #max').on('change', function () {
    table.draw();
          });
    $(".filterhead").each( function ( i ) {
                  var select = $('<select><option value=""></option></select>')
                      .appendTo( $(this).empty() )
                      .on( 'change', function () {
                        var term = $(this).val();
                          table.column( i ).search(term, false, false ).draw();
                      } );
                  table.column( i ).data().unique().sort().each( function ( d, j ) {
                        select.append( '<option value="'+d+'">'+d+'</option>' )
                  } );
              } );


});

}

kthorngren

It would help if the test case had a representation of the date data format you are having difficulties with. The test case has some errors. Maybe reduce the test case down to the code necessary to show the issue.

Start by removing the HTML table and populating the dataArray variable so data: dataArray, has data to populate the table.

Kevin

SAGAHE

Please check as solved, i dont know if my spreadsheet was buuged but i change conection to another spreadsheet and now everything works, weird, becose it was almost empty with the same format in every column.

allan

Thanks for the update.

Allan