Using sheets as relational data

[benjaroa]

Hi,

I'm not a professional developer, just a guy looking for the right solution...

Sheetrock seems to be a nice one, but I have one big question: is it possible to load the content of several sheets and query them as relational ones?

Like:

SELECT p.name, d.url 
FROM people p, urls u 
WHERE p.id = u.people_id 

Combining one sheet with the people and a second one, with the urls (just an example, not my real data)

Regards and thanks in advance! Benjamín Roa

[benjaroa]

Looking around I found AlaSQL, maybe I can pass the data from a spreadsheet to AlaSQL using sheetrock.

What do you think?

[benjaroa]

Self response:

Trying to combine AlaSQL and Sheetrock, came to this:

<script src="https://code.jquery.com/jquery-3.1.1.slim.min.js"></script>
<script src="http://cdn.jsdelivr.net/alasql/0.2/alasql.min.js"></script> 
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-sheetrock/1.1.4/dist/sheetrock.min.js"></script> 

jQuery + AlaSQL + Sheetrock

<script type="text/javascript">
$(function() {
    // Test table
    alasql("CREATE TABLE data (`Team` varchar(50), `Pos` varchar(50), `First` varchar(50), `Last` varchar(50), `Bats` varchar(50), `AB` numeric, `R` numeric, `H` numeric, `HR` numeric, `RBI` numeric, `SB` numeric, `BA` numeric)");

    var myCallback = function (error, options, response) {
        if (!error) {
            insert = "";
            for (i = 0; i < response.raw.table.rows.length; i++) {
                insert = "insert into data (`Team`,`Pos`,`First`,`Last`,`Bats`,`AB`,`R`,`H`,`HR`,`RBI`,`SB`,`BA`) values (";
                datos = "";
                for (h = 0; h < response.raw.table.rows[i].c.length; h++){
                    if(response.raw.table.rows[i].c[h] == null){ val = ""; }else{ val = response.raw.table.rows[i].c[h].v}
                    datos += "'"+ val +"'";
                    if(h < response.raw.table.rows[i].c.length-1){ datos += ","; }
                }
                insert += datos;
                insert += ");";
                alasql(insert);
            }
        }
    };

    sheetrock({
        url: "https://docs.google.com/spreadsheets/d/1qT1LyvoAcb0HTsi2rHBltBVpUBumAUzT__rhMvrz5Rk/edit#gid=0",
        query: "select * where E = 'Both' order by L desc",
        callback: myCallback
    });
});
</script>

Surely the code could be better, but it works!

[chriszarate]

Interesting combination! Allows you to accumulate data from multiple Sheetrock requests and then query them later. Thanks.