StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Yes this is certainly possible, but can achieved in a variety of ways. </p> <p>One approach you could take with this is to retrieve all the data from the spreadsheet as JSON format and add it to the DOM as a HTML table. Then you can use a nice plugin like <a href="http://www.datatables.net/" rel="nofollow noreferrer">dataTables</a> which has a pretty good native search function. I'll give a basic example below. </p> <p>To retrieve the data you can use <a href="https://developers.google.com/gdata/samples/spreadsheet_sample" rel="nofollow noreferrer">Googles spreadsheet JSON API</a>. A basic example is below. </p> <pre><code><script src="http://spreadsheets.google.com/feeds/cells/*ID*/*WS*/public/values?alt=json-in-script&amp;callback=*FN*"></script> </code></pre> <ul> <li>Where <em>ID</em> is the spreadsheet's long ID. </li> <li>Where <em>WS</em> is the worksheet number e.g. 1,2,3 etc. </li> <li>Where <em>FN</em> is the function you want to call. In my below function i use importGSS</li> </ul> <p>Then I've written the below script that adds the data to a HTML table. It first adds the first row to a <code><thead></code> section and then adds the rest to the <code><tbody></code> section. </p> <pre><code>function cellEntries(json, dest) { var table = document.createElement('table'); var thead = document.createElement('thead'); var tbody = document.createElement('tbody'); var thr; var tr; var entries = json.feed.entry; var cols = json.feed.gs$colCount.$t; for (var i=0; i <cols; i++) { var entry = json.feed.entry[i]; if (entry.gs$cell.col == '1') { if (thr != null) { tbody.appendChild(thr); } thr = document.createElement('tr'); } var th = document.createElement('th'); th.appendChild(document.createTextNode(entry.content.$t)); thr.appendChild(th); } for (var i=cols; i < json.feed.entry.length; i++) { var entry = json.feed.entry[i]; if (entry.gs$cell.col == '1') { if (tr != null) { tbody.appendChild(tr); } tr = document.createElement('tr'); } var td = document.createElement('td'); td.appendChild(document.createTextNode(entry.content.$t)); tr.appendChild(td); } $(thead).append(thr); $(tbody).append(tr); $(table).append(thead); $(table).append(tbody); $(dest).append(table); $(dest + ' table').dataTable(); } </code></pre> <p>You can then call back the function with ... where <code>#Destination</code> is the <code><div></code> you want to add the HTML table to. </p> <pre><code>function importGSS(json){ cellEntries(json, '#Destination'); }; </code></pre> <p>Once all completed you'll see something like the below screenshot, the top the final results and the bottom the original spreadsheet. I've edited out some information. I hope this has been of some help. </p> <p><img src="https://i.stack.imgur.com/od9zu.png" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload