Counting Google Sheet column and navigating to value

Hi All,

I am wanting to create a block set that does the following function. I would like it to count the values in a Google Sheets column,(s1,s1,s2,s2,s2), and select the value that has not appeared less than 3 times, in this case s1. Then I would like the function to navigate to a specific screen that produced the value, in this case screen 1. I anticipate having up to 1000 screens.

Is this possible with the Thunkable tools at my disposal or will I need to make a script or something like that?

Do not make 1,000 screens.

Make one screen and change content based on the values that are important to you. Trust me. If you ever find yourself creating more than about ten screens, stop and ask for help here. There’s almost always a way to make the code more efficient and use a single screen or just a few screens to do the same thing.

I find your wording confusing (“the value that has not appeared less than 3 times”) but I think you mean the value that appears fewer than three times…?

You would need to loop through the column and tally each value. There’s probably a better way to do that with the Google Sheets API but I’m not sure exactly what that would be.

To help you further with the loop method, I’d need to know more about your data. For example, would you ever have more than one value that appears fewer than three times (such as s1, s1, s2, s2, s2, s3)? And also, why do you feel you need 1,000 screens? What kind of app is this?

Thanks for the quick reply.

The app purpose. Students will review an exercise and rate the exercise, hence the many slides. They will need to complete the exercise correctly three times. They will then rate the exercise as Easy, Medium, Hard. If they complete an easy task less than three times, the next day the easy exercise will appear again. The exercises must be followed in a sequential order. During new lessons they will need to review previous lessons and one new lesson time allowing. Medium and Hard tasks will be repeated after the student repeats the Easy tasks from previous days. Tasks can only be completed once a day. Is this possible to code utilizing the blocks with Thunkable?

I agree with not making 1000 slides, and see now that I am limited by my lack of programming understanding. But that now brings me back to square one. Using the 1000 slide method would allow me to collect the slides numbers as a data point then do the tally method you are describing. Any help is greatly appreciated tatiang!

Absolutely! Is it simple? No, and it will be time-consuming to create but Thunkable is definitely up to the task.

You’ll need to figure out how to store all of that data. My preferred data source is Firebase because you can create just about any structure you want. But if you use Google Sheets, you’ll be limited to what can fit in a single row. I would think that would still work for what you described. Are there 1,000 different exercises?

I would start by sketching out the way the data will be stored. That might look like sample data for a few users assuming one of them has just started on the app, one is midway through, and one is mostly done (just thinking out loud here…). Post your ideas here for feedback.

@WLearnMusic To Tatian’s point–if you create an app with 1000 different screens, your app is likely to not work at all. Unfortunately, there is only so much memory you can use with a Thunkable app and we have seen where having too many screens can take up all of this memory and render your app unusable.

1 Like

I would add that even if you could successfully implement 1,000 screens, it’s just a bad idea. What if a day later, you decide to change the button images on those screens? Or you misspelled a word and didn’t catch it? Not only did you spend countless hours setting up the screens, selecting the images, typing in the label text… but now you have to make every little change 1,000 times. Argh! :exploding_head: And I feel that way about apps with 50 screens, too. Any project that has that much repetition could probably be created using a single screen and dynamic data. Thunkable excels at that so why not take advantage?

1 Like

Thanks all. From my understanding, I will have one slide that will pull from FireBase and display the data if it meets certain conditions.

I want to make sure I am understanding before i try to fumble through the blocks.

When student presses on Exercise button in a category, there will be a request to query the database for previous activity on exercises in that category. It will check if exercise was completed fewer than 3 times and present the exercises in a sequential order starting from 1 to 1000. The student will be moved to the dynamic exercise page. Student will then rate the exercise as easy medium hard and that will be recorded to the database. And be presented a new exercise using the logic above.

What blocks do you think i need to use for this? I have the blocks necessary to save data to the firebase and google sheets.

All help is greatly appreciated and if I can get the functionality of this section of the app to work I would be ecstatic with Thunkable.

This is a really broad question. Obviously, you’ll need data source blocks to get and update data. If you use Firebase, you’ll be using cloud variables. Beyond that, you may want to watch tutorial videos or read the documentation for various aspects of your app.

As you get started, it may be helpful to post screenshots or links to your project and then ask specific questions about things that are not working.