Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <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>&lt;script src="http://spreadsheets.google.com/feeds/cells/*ID*/*WS*/public/values?alt=json-in-script&amp;amp;callback=*FN*"&gt;&lt;/script&gt; </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>&lt;thead&gt;</code> section and then adds the rest to the <code>&lt;tbody&gt;</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 &lt;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 &lt; 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>&lt;div&gt;</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>
    singulars
    1. This table or related slice is empty.
    1. POIs it possible to create a public database (spreadsheet) search with Google Scripts?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMogsdad
    1. USdev
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POIs it possible to create a public database (spreadsheet) search with Google Scripts?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThank you, this is a wonderful start. I'm a little more perceptive on using and adapting; less so on writing from scratch. While I've played around somewhat, I do have a few questions: - The HTML script call for the data is to be put on the webpage correct? Likewise, DataTable must also be called on the webpage, but does positioning matter? - The `cellEntries`, `importGSS`, and `$(document).ready` _(if I understand correctly from the DataTables)_ must be in the spreadsheet scripting, correct?
      singulars
      1. PO
      1. USNick Mischler
    2. COSorry yes I should have pointed out the ordering does matter. The Google `<script>` needs to placed underneath where you have declared the `importGSS()` and `cellEntries()` functions so I've just added it to the bottom of the page. What I have also done is added the `dataTables()` declaration to the bottom of `cellEntries()` so I know it creates it once the data is loaded e.g. `$(dest).dataTable();`
      singulars
      1. PO
      1. USdev
    3. COI think I'm getting it, but I'm afraid I have a few more questions: - "#Destination" in the code above is supposed to relate to a `<div id="#Destination">` wherever on the page I wish the table to appear, correct? - The flow of the document should be: header: `define JQuerry, define DataTables, define the above JS code`, body: `define div for table`, footer: `Google script`, correct? - The Google `<script>` only passes the spreadsheet data to the already defined function, correct?
      singulars
      1. PO
      1. USNick Mischler
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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