Date filter with google sheets only acepts text data

Date filter with google sheets only acepts text data

SAGAHESAGAHE Posts: 3Questions: 1Answers: 1
edited April 2023 in Free community support

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.

This question has an accepted answers - jump to answer

Answers

  • SAGAHESAGAHE Posts: 3Questions: 1Answers: 1
    edited April 2023

    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>' )
                      } );
                  } );
    
    
    });
    

    }

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    edited April 2023

    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

  • SAGAHESAGAHE Posts: 3Questions: 1Answers: 1
    Answer ✓

    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.

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Thanks for the update.

    Allan

Sign In or Register to comment.