Last active
October 7, 2020 15:31
-
-
Save Akjosch/db6b3aea6c1cf44fbda373478ebdfeb9 to your computer and use it in GitHub Desktop.
Fun with reading Google Sheets in SugarCube
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
:: Show Responses [nobr] | |
/* assume setup.responseSheet is set to the ID of the sheet with the responses to some form */ | |
<<run setup.getSheetData(setup.responseSheet, function(data) { | |
State.variables.responses = data; | |
setPageElement("responses", "Responses"); | |
})>> | |
<div id="responses"></div> | |
:: Responses [nobr] | |
<table> | |
<tr><th>Name</th><th>Codename</th><th>Message</th></tr> | |
<<for _response range $responses>> | |
<tr><td><<- _response.Name>></td><td><<- _response.Codename>></td><td><<- _response.Message>></td></tr> | |
<</for>> | |
</table> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function zipobject(props, values) { | |
props = props || []; | |
values = values || []; | |
var i = -1; | |
const length = props.length; | |
const valuesLength = values.length; | |
const result = {}; | |
while( ++i < length ) { | |
result[props[i]] = i < valuesLength ? values[i] : undefined; | |
} | |
return result; | |
}; | |
const requests = new Map(); | |
var lastRequestId = 1; | |
window.google = { visualization: { Query: { | |
setResponse(r) { | |
var cols = r.table.cols.map(col => col.label.trim()); | |
var rows = r.table.rows.map(row => row.c.map(cell => cell ? cell.v : undefined)); | |
var data = []; | |
rows.forEach(row => data.push(zipobject(cols, row))); | |
var reqId = r.reqId; | |
if(requests.has(reqId)) { | |
requests.get(reqId).callback(data); | |
requests.delete(reqId); | |
} | |
} | |
} } }; | |
setup.getSheetData = function getSheetData(sheet, callback) { | |
var reqId = (lastRequestId ++); | |
var request = { | |
url: "https://docs.google.com/spreadsheets/d/" + sheet + "/gviz/tq", | |
dataType: "jsonp", | |
data: { tq: "select *", gid: 0, tqx: "reqId:" + reqId } | |
}; | |
var reqCallback = { | |
reqId: reqId, | |
callback: callback | |
}; | |
requests.set(String(reqId), reqCallback); | |
return jQuery.ajax(request); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
"use strict";function zipobject(props,values){props=props||[],values=values||[];for(var i=-1,length=props.length,valuesLength=values.length,result={};++i<length;)result[props[i]]=i<valuesLength?values[i]:void 0;return result}var requests=new Map,lastRequestId=1;window.google={visualization:{Query:{setResponse:function setResponse(r){var cols=r.table.cols.map(function(col){return col.label.trim()}),rows=r.table.rows.map(function(row){return row.c.map(function(cell){return cell?cell.v:void 0})}),data=[];rows.forEach(function(row){return data.push(zipobject(cols,row))});var reqId=r.reqId;requests.has(reqId)&&(requests.get(reqId).callback(data),requests.delete(reqId))}}}},setup.getSheetData=function(sheet,callback){var reqId=lastRequestId++;return requests.set(reqId+"",{reqId:reqId,callback:callback}),jQuery.ajax({url:"https://docs.google.com/spreadsheets/d/"+sheet+"/gviz/tq",dataType:"jsonp",data:{tq:"select *",gid:0,tqx:"reqId:"+reqId}})}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment