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 for Developers
Wow, I didn’t even know about this function. I’m so going to try! IDK if somebody in the world shares my google love. I don’t mean just a browser, but many apps and services it offers. I have to work a lot on the computer, and my job is somehow creative (sphere of ads and pr). I must deal with many tasks creatively and uniquely because plagiarism can be sued. So, as you can guess, I spend a lot of time thinking and forming every little thing. But google services like business card template help me to make at least some wor easier. I can take some ideas there. What google app/service do you like the most? Why?
i love google apps! have you tried https://tables.area120.google.com
it’s an airtable wannabe - it’s a self contained app that can create tables, do joins, create views (including kanban), build forms and even do some automation. and so easily! unfortunately you can’t connect thunkable to it (at least i don’t know how). but it’s possible to build integrated apps using only tables. see example apps.
That’s good to know! Thanks Jared!
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
