Currently Google added the visualization services to Google sheet which means you can just share the sheet and use the share URL but replace the part that says edit?usp=sharing with gviz/tq? and you will get the data in the Google sheet.
Example:
https://docs.google.com/spreadsheets/d/[sheet ID]/gviz/tq?
this is so cool - all the data comes back as a json string ready for parsing using thunkable’s object blocks!
it shows the data in a different structure (but still useful) though.
for example my spreadsheet (that corresponds to one of my google forms) looks like this:
but the special notation /gviz/tq? rearranges the data into this form - i suppose it’s more suitable for visualization.
{
"version": "0.6",
"reqId": "0",
"status": "ok",
"sig": "1762132855",
"table": {
"cols": [
{
"id": "A",
"label": "Timestamp",
"type": "datetime",
"pattern": "M/d/yyyy H:mm:ss"
},
{
"id": "B",
"label": "what is your name",
"type": "string"
},
{
"id": "C",
"label": "do you live with your parents?",
"type": "string"
},
{
"id": "D",
"label": "how do you rate google form",
"type": "number",
"pattern": "General"
},
{
"id": "E",
"label": "what days can you work",
"type": "string"
},
{
"id": "F",
"label": "what show date do youw wish to attend",
"type": "date",
"pattern": "M/d/yyyy"
},
{
"id": "G",
"label": "what showing do you wish to attend",
"type": "datetime",
"pattern": "h:mm:ss am/pm"
}
],
"rows": [
{
"c": [
{
"v": "Date(2022,3,30,18,14,25)",
"f": "4/30/2022 18:14:25"
},
{
"v": "manny"
},
{
"v": "Y"
},
{
"v": 4,
"f": "4"
},
{
"v": "tue, wed"
},
{
"v": "Date(2022,3,22)",
"f": "4/22/2022"
},
{
"v": "Date(1899,11,30,11,0,0)",
"f": "11:00:00 AM"
}
]
},
{
"c": [
{
"v": "Date(2022,3,30,18,36,15)",
"f": "4/30/2022 18:36:15"
},
{
"v": "jose"
},
{
"v": "Y"
},
{
"v": 2,
"f": "2"
},
{
"v": "wed, fri"
},
{
"v": "Date(2022,4,4)",
"f": "5/4/2022"
},
{
"v": "Date(1899,11,30,19,30,0)",
"f": "7:30:00 PM"
}
]
},
{
"c": [
{
"v": "Date(2022,3,30,20,14,25)",
"f": "4/30/2022 20:14:25"
},
{
"v": "jason"
},
{
"v": "N"
},
{
"v": 1,
"f": "1"
},
{
"v": "wed,fri"
},
{
"v": "Date(2022,4,1)",
"f": "5/1/2022"
},
{
"v": "Date(1899,11,30,13,0,0)",
"f": "1:00:00 PM"
}
]
}
],
"parsedNumHeaders": 1
}
}
As you can see that it first shows the headers under the key cols then it list all the data under the key rows. The rows has two sub-keys v for values and f for the format of how the value is displayed.
This is amazing! I can get around the lack of support for Google Sheets for Education.
I set the sharing permissions on the Google Sheet to “anyone with the link can view” and used these blocks to format the JSON response correctly, display it in a Text Input so it can be copied and pasted into Best JSON Viewer and JSON Beautifier Online, and then parsed the formatted response to get the value in row 2, column 1 and in row 3, column 2 (subtract one from the row number you want, assuming you have header names for your columns):
And these blocks, when added right after the set label's text blocks above, will get the first and second column values from each row and join them as a list item and then display all rows in a list viewer:
More than that, you can actually filter results if you add SQL like query to the end of the URL. The query text has to be urlencoded.
So instead of /gviz/tq? you can do /gviz/tq?tq= and your query which will get only matching rows from the sheet.
Nice! And this site will let you encode a query: Query Language Reference (Version 0.7) | Charts | Google Developers
