Is it possible to create a public database (spreadsheet) search with Google Scripts?

Question

I'm trying to create a website where users can come and look for a set of resources, something like a portal, or a database like JSTOR. I am using Weebly; this website will eventually be turned over to someone who does not know computers well, so I'm trying to keep things simple (and free, where doable).

My thought was to use Google Spreadsheets/Forms to handle the input and storage of the data for each individual resources (Title, Author, Type, Topic, Country, etc.), and then find some some method of creating a search function that could placed on the website. Any user could arrive at the site, put in whatever criteria they want to look for, and any resources in the database would be listed out for the user to further investigate. Users would not be adding data to the spreadsheets; only querying it for data.

My first question is such a script/arrangement possible and can it be embedded into a website page? My second question is what would the best approach be?

Answer 1

Yes this is certainly possible, but can achieved in a variety of ways.

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 dataTables which has a pretty good native search function. I'll give a basic example below.

To retrieve the data you can use Googles spreadsheet JSON API. A basic example is below.

<script src="http://spreadsheets.google.com/feeds/cells/*ID*/*WS*/public/values?alt=json-in-script&amp;callback=*FN*"></script>

• Where ID is the spreadsheet's long ID.
• Where WS is the worksheet number e.g. 1,2,3 etc.
• Where FN is the function you want to call. In my below function i use importGSS

Then I've written the below script that adds the data to a HTML table. It first adds the first row to a <thead> section and then adds the rest to the <tbody> section.

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

You can then call back the function with ... where #Destination is the <div> you want to add the HTML table to.

function importGSS(json){
   cellEntries(json, '#Destination');
};

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.