Column filtering on multiple tables called via Google Sheets

oliverater

Hello, I am relatively new to DataTables and Javascript, but have been going around in circles trying to solve this issue:

I have two tables, calling data from two separate tabs of a Google Sheet. The data displays fine.
I have managed to create a search function that works across both tables.
I am now trying to create two dropdown filters - ideally one for each the first and second column of both tables simultaneously.

The issue I keep running into is a blank dropdown, I believe due to the dropdown loading before the data has imported. I have tried to wrap the filter in an 'init' function, but then the dropdown button fails to ever show. I am wondering if it is possible to append two filters outside the containers wrapped around both tables, one for columns 0 of both tables, and one for columns 1 of both tables.

A similar issue was raised here: https://datatables.net/forums/discussion/23015/google-spreadsheet-and-individual-column-filtering but went unanswered.

Link to test case: http://live.datatables.net/fupixusa/1/edit?html,js,console,output
(Not sure why I can't get the output to show - code hasn't changed other than local scripts replaced...)

HTML below:

<div class="container">
    <p>
      <label for="mySearch">Search Tables</label>
      <input type="text" placeholder="Search..." id="mySearch">
    </p>
<div id="table-container">
<table id="biden" class="table table-hover table-striped table-sm" cellspacing="0" >
 <thead>
    <tr>
      <th class="th-sm">Topic
      </th>
      <th class="th-sm">Who
      </th>
      <th class="th-sm">Quote
      </th>
      <th class="th-sm">Source
      </th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th class="th-sm">Topic
      </th>
      <th class="th-sm">Who
      </th>
      <th class="th-sm">Quote
      </th>
      <th class="th-sm">Source
      </th>
    </tr>
  </tfoot>
  <tbody id="table">
  </tbody>
</table>
</div>
<div id="table-container">
  <table id="trump" class="table table-hover table-striped table-sm" cellspacing="0" >
    <thead>
      <tr>
        <th class="th-sm">Topic
        </th>
        <th class="th-sm">Who
        </th>
        <th class="th-sm">Quote
        </th>
        <th class="th-sm">Source
        </th>
      </tr>
    </thead>
    <tfoot>
      <tr>
        <th>Filter by Topic</th>
      </tr>
    </tfoot>
    <tbody id="table">
    </tbody>
  </table>
  </div>
</div>

JS:

$.getJSON("https://spreadsheets.google.com/feeds/list/1TSDRAVjUxgOIgAhh5Ly_5s7NwRaWkOedhAJu7haNBd8/2/public/full?alt=json", function (data) {

      
      var sheetData = data.feed.entry;
  
      var i;
      for (i = 0; i < sheetData.length; i++) {
        var topic = data.feed.entry[i]['gsx$topic']['$t'];
        var who = data.feed.entry[i]['gsx$who']['$t'];
        var quote = data.feed.entry[i]['gsx$quote']['$t'];
        var source = data.feed.entry[i]['gsx$source']['$t'].replace( /"/g, '&quot;' );
        let clip1 = source.split("https://").pop().split("/")[0];

        table1.row.add([
          topic,
          who,
          quote,
          '<a href=\"' + source + '\">' + clip1 + '</a>',
        ]).draw( false );
      }


    });

  

  var table1 = $('#biden').DataTable({
    "dom": '<"top">t<"bottom"i>p<"clear">',
    scrollY:        '75vh',
        scrollCollapse: true,
        paging:         false,
  lengthChange: false,
  pageLength: 12, 
  language:{
    search: "Filter:",
  },

  drawCallback: function (settings) {
    $('[data-toggle="tooltip"]').tooltip();
  },

  columnDefs: [
      { bSortable: false, targets: [2, 3] },

      { targets: 2,
    render: function checkPosition(data, type, row, meta) {
    if (window.matchMedia('(min-width: 401px) and (max-width: 737px)').matches) {
        return type === 'display' && data.length > 60 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">'+data.substr( 0, 75 ).replace( '&quot;', '"' )+'...</span>' :
        data;
    } else if (window.matchMedia('(max-width: 400px)').matches){ 
        return type === 'display' && data.length > 50 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">(Click to expand)</span>' :
        data;
    } else if (window.matchMedia('(min-width: 738px)').matches){ 
      return type === 'display' && data.length > 200 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">'+data.substr( 0, 200 )+'...</span>' :
        data;
      }
    }
  }
  ],
  
  responsive: true,
  "orderFixed": [[ 0, "asc" ]],
  rowGroup: {
            dataSrc: 0
        },
  "lengthMenu": [[12, 24, 48, -1], [12, 24, 48, "All"]],
    });

  $.getJSON("https://spreadsheets.google.com/feeds/list/1TSDRAVjUxgOIgAhh5Ly_5s7NwRaWkOedhAJu7haNBd8/3/public/full?alt=json", function (data) {
  
        
        var sheetData = data.feed.entry;
    
        var i;
        for (i = 0; i < sheetData.length; i++) {
          var topic = data.feed.entry[i]['gsx$topic']['$t'];
        var who = data.feed.entry[i]['gsx$who']['$t'];
        var quote = data.feed.entry[i]['gsx$quote']['$t'];
        var source = data.feed.entry[i]['gsx$source']['$t'].replace( /"/g, '&quot;' );
        let clip1 = source.split("https://").pop().split("/")[0];

        table2.row.add([
          topic,
          who,
          quote,
          '<a href=\"' + source + '\">' + clip1 + '</a>',
        ]).draw( false );
      }
  
  
      });
    const table2 = $('#trump').DataTable({
      
      "dom": '<"top">t<"bottom"i>p<"clear">',
      scrollY:        '75vh',
        scrollCollapse: true,
        paging:         false,
    lengthChange: false,
    pageLength: 12, 
    language:{
      search: "Filter:",
    },

    drawCallback: function (settings) {
    $('[data-toggle="tooltip"]').tooltip();
  },

  

  columnDefs: [
      { bSortable: false, targets: [2, 3] },


      { targets: 2,
    render: function checkPosition(data, type, row, meta) {
    if (window.matchMedia('(min-width: 401px) and (max-width: 737px)').matches) {
        return type === 'display' && data.length > 60 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">'+data.substr( 0, 75 ).replace( '&quot;', '"' )+'...</span>' :
        data;
    } else if (window.matchMedia('(max-width: 400px)').matches){ 
        return type === 'display' && data.length > 50 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">(Click to expand)</span>' :
        data;
    } else if (window.matchMedia('(min-width: 738px)').matches){ 
      return type === 'display' && data.length > 200 ?
        '<div data-toggle="tooltip" disabled title="'+data+'">'+data.substr( 0, 200 )+'...</span>' :
        data;
      }
    }
  }
  ],

  responsive: true,
  "orderFixed": [[ 0, "asc" ]],
  rowGroup: {
            dataSrc: 0
        },
    "lengthMenu": [[12, 24, 48, -1], [12, 24, 48, "All"]],

      });

  $('#mySearch').on( 'keyup click', function () {
    table1.column([0]).search($(this).val()).draw();
    table2.column([0]).search($(this).val()).draw();

  });

// Current attempt to create filter for 2nd table - dropdown fails to show without removing the init function

  $('#trump').on( 'init.dt', function () {

    var tableT = $('#trump').DataTable();
    tableT.columns(0).flatten().each( function ( colIdx ) {
                // Create the select list and search operation
                var select = $('<select />')
                    .appendTo(
                        tableT.column(colIdx).footer()
                    )
                    .on( 'change', function () {
                        tableT
                        .column( colIdx )
                        .search( $(this).val() )
                        .draw();
                    } );
 
                // Get the search data for the first column and add to the select list
                tableT
                    .column( colIdx )
                    .cache( 'search' )
                    .sort()
                    .unique()
                    .each( function ( d ) {
                        select.append( $('<option value="'+d+'">'+d+'</option>') );
                    } );
            });
  })

colin

The test case isn't running, it looks like jQuery isn't present, but a few other errors there too. We're happy to take a look, but that's a lot of code to wade through, so please ensure it's demonstrating the issue you want assistance with,

Colin

srouffy

Try loading jquery before datatables. That will at least get your test case to load so the problem can be addressed.