[Solved] Selecting one of hundreds of lists from a dataviewer and getting the values in that list?

I want to create an app that inspires writing by taking a single word, selected from a dataviewer, and listing words that are similar.

For example, a writer wants to write a scene about how beautiful winter is but doesn’t know how to describe winter very elegantly. They would find the word “winter”, see the associated words to help describe it (“snow”, “white”, “cold”, “lonely”,“gentle”, etc), and then describe the scene using these extra ideas.

I just began using Thunkable recently so I don’t know much about it.

These are my thoughts so far:

  1. The datasource (with words and their associated words) will be in an google sheets table (because that’s the only one I’m familiar with right now) with all the list names on the first row and the data in vertical columns below each name. Then I will be able to hold hundreds of lists in the same file.

What I have successfully done so far:

  1. I have successfully imported a datasource and verified that all the correct list names and list values are there.
  2. I have created a button which will make the listview visible, allow the user to select a value from a list, after which the listview will become invisible again and the button text will be replaced with the selected list item (e.g. I press the button, select “one” from the list view, and the button now says “one”).
  3. I have found a way to randomly select a value from a specific list using the randomnumber function with row id.

My difficulties so far:

  1. Although I have successfully gotten data from a list, the list was preselected before I tested the app. I could only program it to show data from a single list which was specified by name beforehand. This is very limited because I want the user to be able to select their list name and THEN get the corresponding list values to that list.
  2. I don’t understand the other types of datasources well enough to determine if this would be possible with them instead of google sheets (i.e. will airtable or excel allow me to do something different?).

If anything in this explanation doesn’t make sense, please tell me.

Also, if this isn’t possible in Thunkable, please tell me now before I waste too much time and effort.

Thanks.

1 Like

Welcome to Thunkable.

The best approach is to either provide screenshots of your code and example outcome or provide a shared link to the project or a project that reproduces your issue so other can look into it and provide suggestions.
You will also need a shared link to your Google sheet or a sample sheet with the same structure.

i took the liberty of interpreting your request. you probably need a structure opposite to what you originally thought. i assumed you might need a google sheet that looks like this:
image

my first screen shows the sorted list of all words in column word so it’s really using a data source pointing to the spreadsheet.

it’s a different story for processing the word that was selected, i’m using the Visualization API Query Language from google because it features a fast way of obtaining row/s from the spreadsheet using a simplified SQL statement with a WHERE condition and the returned value is a json string containing the row/s that satisfy the query.

you can read more about this process here

here’s the word selection screen
image
and here’s what you get when you select a word to obtain the related words:


.
here’s the link to the spreadsheet:

and here’s a link to the project
https://x.thunkable.com/projects/632aa7733b27ec0224e0513a/ab6da954-35cf-4ab8-ac85-36bf9caa8d45/designer

i hope this works for you.

2 Likes

Hey @jaredsbror5 did these examples help you get over the finish line on this

I won’t be able to check until tonight because I have to work, but I promise that I’ll check tonight.

I am running into a problem with running a copy of your project.

Before I say what that is, I just want to say that I don’t understand how WebApi works, so I am first trying to get the app working exactly as yours does. I know that copying isn’t the same as learning, but right now I’m focusing on getting something done.

With that in mind, I will explain the issue.

Whenever I test out your project directly from your project (which is read only) it works just fine. However, whenever I make a copy of your project to see what you’ve done and I test it, I can click on the first word_list but not the second page because the second screen disappears about one second after popping up.

Testing the original:

Testing the copy:

Apparently the error is here, and the error only exists in the copy projects.

As far as I can tell the error (on the bottom left) occurs because there is no Text_Input6 anywhere in the project.
snip2

What is the purpose of this statement?


Some other questions real quick.

  1. Can I simply delete all datasources besides wordplus? They seem unused.

  2. Can I delete webApi1 and Alert1? They seem unused.


If you could help me, that would be awesome! It’s good to know that what I asked is possible.

1 Like

I will try to answer this question until you get a response from @manyone himself.

@manyone is not using the Thunkable standard way of connecting to a data source using the connection option but instead using the Google Visualization Service. In this service, the Google sheet is presented in a JSON form inside a non-JSON header so to be able to work with the JSON part is is necessary to read only part of the Visualization response therefore Input6 was used just to check that the correct part of the response is obtained. The actual work is done using the variable gswdobj.

Hope this helps.

1 Like

thanks @muneer! you are correct - i was just using it to verify the return response i get after the webapi call - i simply forgot to remove it. it is ok to remove.

1 Like

when a thunkable app uses a worksheet as a data source, and you try to remix it, the worksheet turns into a local db. to restore the original design you need to do the following:

  1. save the spreadsheet (i gave a link) under the same name in your google drive.
  2. bring it as a data source inside thunkable.
  3. delete the old db from the data source.
  4. edit your thunkable app and redo all references to the old db so they point to the new one. by redo, i mean, bring the block related to the new db and transfer the enclosed contents from the same block in the old db the delete the old block.

regarding the text_input6 variable,please delete if from the project - i was using it for debugging

yes you can delete all other datasources besides wordplus. you can also delete webapi1 and alert1 (they are remnants from an old project).

you have to replace the markedfield value below so it points to YOUR spreadsheet

here’s how to obtain the value
go to drive.google.com and open your version of wordplus google sheet, press the Share button at the right upper corner. under General Access click Anyone with the link, then press copy link - it will be now in your clipboard, paste it in notepad and delete tail end as shown below.

for example,

https://docs.google.com/spreadsheets/d/118jbb1tOsZgeH18XSLmiTZ7Ft3hrMwYTKRshNJm1tI8/edit?usp=sharing

the remaining string below is what you paste unto the highlighted field in the block above.
https://docs.google.com/spreadsheets/d/118jbb1tOsZgeH18XSLmiTZ7Ft3hrMwYTKRshNJm1tI8

1 Like

Thanks for the advice. I was able to get it to work by getting the right datasource in the project and I deleted all the unnecessary components, creating a barebones project right here:
https://x.thunkable.com/projects/6331ed1a2014e30224fedc9c/7a7cae1f-dc17-4ec3-8e14-bc47f2cdabf3/designer

However I have some more questions about the specific sections right here:


What is the /gvis/tq?tq=?


What does the select A, count(B), etc mean? What does the %27 mean?

Snip3
From what I understand of WebApi’s, a signal is received, verified, then sent back. How do these variables relate to webapi’s and their signals?

  1. Does this mean my lists can only be 48 characters long? Do I have to adjust the length of the string depending on the amount of items?
  2. What is the table.rows( - ).c[1].v?

Now that I have a working example, I want to understand it better.

What is the /gvis/tq?tq=?
gvis is a required parameter for reading a google sheet but intended for google’s visual language. tq?tq= is also required. all together it might be saying, read the sheet for google’s viz language using this text query.

What does the select A, count(B), etc mean? ?
pardon me about that - it’s a comment box from the last project i copied this from - it should have read - (as a comment and as a query) - select B where A=‘(the item selected)’. it is query, like SQL (structured query language) being used in real databases, but simplified - yet powerful for most needs.
in the above, A refers to the first column, B refer to the 2nd column - hence the query string “select B where A=‘(the item selected)’” is really saying, get me column B for ALL rows from sheet where column A equals the value supplied bounded by apostrophes.

What does the %27 mean?
i believe it means apostrophe (ie. single quote)

How do these variables relate to webapi’s and their signals?
webapi is one of 2 ways for thunkable to communicate with a site in the internet

this block
image

is like typing this on your browser:
https://docs.google.com/spreadsheets/d/18jtMRFEy0ytpV0t10AXAXNl41SDwosi8F66XrKjLBUQ/gviz/tq?tq=select%20B%20where%20A%3D%27winter%27

go ahead, try it - and see what you get!
so it is important to define and encode your query just right.
in this example we know there are no duplicate words (ie. only one “winter”) but this query feature is capable of returning multiple rows.
for example you can code, “select name, item_bought, price where date=‘2022-09-26’ and age>24”
and you can get all rows that satisfy that condition.

Does this mean my lists can only be 48 characters long? Do I have to adjust the length of the string depending on the amount of items?
the json string is found at column 48 of the returned response.
What is the table.rows( - ).c[1].v?
it refers to value of the variable at the first row, column 1 (ie. c[1].v)

read more about this access method in this link and the embedded link:

1 Like

Thanks for all the help you’ve given me so far.

I have what should be my final question on this thread.

Why is my app giving me errors when I use any dataset besides yours?
If I modify the dataset with the lists of words and rword_lists, whenever I click on a list name on the list of “words”, it then gives me a blank white screen when testing. The google sheets which I am using is also public (CreativeWordsSpreadsheet - Google Sheets) so apparently that shouldnt be an issue (according to your quiz app description)

Here is a video which shows it.

The lists work fantastically when they don’t give me white screens. If I can just get over the hurdle of creating my own datasources, then I can create custom lists.

Thanks!

can you share your project link? do not use the’share’ button… instead,edit your program as normal then cut n paste to me the actual url of the project

I’m going to send you the link through private messaging.

I sent it. If you didn’t receive it, send me a message.

For reference for anyone reading this, this is the answer I got.

It works perfectly now. Thank you for all your help!

1 Like