Member Avatar for jonsan32

I've figured out how to make an embedded google spreadsheet sortable, but I can't get it to be searchable.

is my site, and I have no access to or knowledge of php. In the past I've used another piece of google code to achieve these results (see my table @ ), but I had to manually insert each entry, rather than use the simpler embedded spreadsheet. I've posted the current code below. Any help would be great. Thanks!

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;
function drawVisualization() {

var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/ccc?key=0AmWD_Na4J_4BdDllczJiVHFibnYwd2FabjhiQmlmZmc');

query.setQuery('SELECT A, B, C, D, E, F, G, H, I, J, K order by A asc label A "Name", B "M/F", C "Class", D "State", E "Height", F "Weight", G "Wing", H "Vert", I "Speed", J "Shot", K "Last Insp."');
query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}

var data = response.getDataTable();

visualization = new google.visualization.Table(document.getElementById('table'));
visualization.draw(data, {legend: 'bottom'});
}
google.setOnLoadCallback(drawVisualization);
</script>
<div id="table"></div>
Edited by jonsan32
  • 2 Contributors
  • 4 Replies
  • 214 Views
  • 4 Months Discussion Span
  • Latest Post Latest Post by jonsan32

Dani AI

Generated

A client-side search is possible without PHP by filtering the DataTable returned by the Google Visualization query and redrawing the table with a DataView. That keeps the existing embed + sortable headers while adding instant search. ’s suggestions (tablesorter / list.js) are useful alternatives, but the following plugs directly into the visualization flow already used by .

Add a simple text input above the table container and, after the Query response creates the DataTable, use a DataView to show only matching rows. The example below assumes the DataTable is in sheetData, the table visualization is tableViz, and tableOptions holds the draw options.

// HTML: <input id="filterBox" placeholder="Filter table...">

// call after the Google Visualization Query has returned and created sheetData and tableViz
var filterInput = document.getElementById('filterBox');

function applyFilter() {
  var term = (filterInput.value || '').trim().toLowerCase();
  if (!term) {
    tableViz.draw(sheetData, tableOptions);
    return;
  }

  var matching = [];
  for (var r = 0; r < sheetData.getNumberOfRows(); r++) {
    for (var c = 0; c < sheetData.getNumberOfColumns(); c++) {
      var cell = sheetData.getValue(r, c);
      if (cell != null && String(cell).toLowerCase().indexOf(term) !== -1) {
        matching.push(r);
        break;
      }
    }
  }

  var view = new google.visualization.DataView(sheetData);
  view.setRows(matching);
  tableViz.draw(view, tableOptions);
}

// light debounce to avoid excessive redraws
var debounce;
filterInput.addEventListener('input', function () {
  clearTimeout(debounce);
  debounce = setTimeout(applyFilter, 150);
});

Notes and troubleshooting:

  • Ensure the spreadsheet is accessible to the Visualization API (published or shared for viewing) or the Query will fail.
  • To restrict search to certain columns, change the inner loop to only check desired column indices.
  • For large datasets, client-side filtering can get slow; consider adding pagination (DataTables) or fetching smaller slices from Sheets (Tabletop/Sheets API) if necessary.
  • Clicking a column header will still sort the currently displayed (filtered) rows; to preserve a programmatic sort, sort the original DataTable before creating the DataView.

Recommended Answers

All 4 Replies

Member Avatar for Member #949455

How can I make my sortable table searchable?

I think you post this question in the wrong section.

Rather than HTML/CSS it sohuld have been in Javascript.

You can used a plugin like this one:

http://tablesorter.com/docs/

or this one:

Both links and example has Html/CSS and jQuery.

So you can modify it.

Member Avatar for jonsan32

Thanks. Sorry for the mis-post. Not knowing where to start is usually my greatest obstacle.

As for the links you provided, they don't include search. I'm familiar with plugins like those. I believe the only way to do search is through php... which I have no server for. I imght be screwed on this one.

Member Avatar for Member #949455

I believe the only way to do search is through php... which I have no server for. I imght be screwed on this one.

Not necessary. You can used jQuery list:

http://listjs.com/

Since I look over your table it's kinda like a Excel Spreadsheet.

I mean you try this

I mean you can always comment the button so noone can edit stuff.

If you need to edit and add stuff just uncomment the button and add the stats while online.

If someone was to type something to search it will get the stats.

I think you might need to redesign your table from the spreadsheet format to the webpage format.

I'm not a basketball fan but stats are not bad to read.

Member Avatar for jonsan32

That's an awesome link. Thank you so much!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.