Using Sheets as a Callable Database for a Google Site

[Davis Jones]

I'm trying to use a Google Sheet at a database that can be queried by the public through a Google Site. Have you tried to do this? Any ideas?

[Riël Notermans]

Several.

Sheets have an interface by themselves.

You can append the sheet URL with:

 https://docs.google.com/spreadsheets/d/.../gviz/tq?tq=QUERY

You would have to give access to everyone with link or public ofcourse (or only users that have access).

This works pretty good to be honest. We have build several applications this way.

This code I use in clientside:

function querySheet(query, id, sheet, callback) {

var cb = "cB" + Math.random().toString(36).substr(2, 5);

ajaxStart();

$.ajax({

url: "https://docs.google.com/spreadsheets/d/" + id + "/gviz/tq",

jsonpCallback: cb,

// Tell jQuery we're expecting JSONP

dataType: "jsonp",

// Url parameters:

data: {

headers: 1,

sheet: sheet,

tq: query,

tqx: "responseHandler:" + cb,

//access_token: googleauththoken-only_for_non_public

}

})

.success(

function (json) {

if (json.status == "ok") {

callback(getArrayFromGvizTable(json));

}

else {

console.log(json);

alert("Er is een fout opgetreden: \r\n" + json.errors[0].message + "\r\n" + json.errors[0].reason + "\r\n" + json.errors[0].detailed_message);

}

}

)

.fail(function (jqXHR, error, errorThrown) {

ajaxStop();

alert("Geen toegang");

console.log("Spreadsheet: " + errorThrown);

});

//ajaxStop();

}

function getArrayFromGvizTable(json) {

var m = json.table;

var mheaders = m.cols;

var pheaders = {};

mheaders.forEach(function (element, index) {

pheaders[element.label] = index;

});

var rowarray = [];

for (var i = 0; i < m.rows.length; i++) {

var a = {};

for (var key in pheaders) {

var strippedKey = key.toLowerCase().replace(/ /, "");

if (m.rows[i]["c"][pheaders[key]] && m.rows[i]["c"][pheaders[key]]["v"]) {

a[strippedKey] = m.rows[i]["c"][pheaders[key]]["v"];

}

else {

a[strippedKey] = "";

}

}

rowarray.push(a);

}

return rowarray;

}

Riël Notermans

Op woensdagen vrij • Zzapps B.V. • High Tech Campus 9, 5656 AE Eindhoven • T 040 711 41 94 • E ri...@zzapps.nl • www.zzapps.nl

WIJ MAKEN HET ONDENKBARE MOGELIJK

On Wed, May 15, 2019 at 10:41 PM Davis Jones <da...@eazl.co> wrote:

I'm trying to use a Google Sheet at a database that can be queried by the public through a Google Site. Have you tried to do this? Any ideas?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fdb4c609-a619-4f97-a3f8-ca0219118ff2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Riël Notermans]

You might remove the ajaxStart() functions, it is just used to show a spinner on my app.

Riël Notermans

Op woensdagen vrij • Zzapps B.V. • High Tech Campus 9, 5656 AE Eindhoven • T 040 711 41 94 • E ri...@zzapps.nl • www.zzapps.nl

WIJ MAKEN HET ONDENKBARE MOGELIJK

[Davis Jones]

Awesome, thanks Riël. I'm going to try to run a modified version of your code as a script inside an HTML block. I'm also going to use some of the code built by Amit Agarwal in this (super cool) tutorial to create some input fields that are used as arguments in the query.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Brian Pugh]

If you need a way to make a published sheet editable on a Google site, follow the directions here. They actually work!

Brian Pugh IT/Educational Technology Support Staff Associated Hebrew Schools Danilack Middle School p: 416.494.7666, 153 e: bp...@ahschools.com www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

On Wed, May 15, 2019 at 4:41 PM Davis Jones <da...@eazl.co> wrote:

I'm trying to use a Google Sheet at a database that can be queried by the public through a Google Site. Have you tried to do this? Any ideas?

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.