Complex google sheet reading

Hey guys ! I am trying to create app for my racing league . I want the user to get information specific to that race by pressing the races I made with the data grid list.

My purpose:I want the user to access specific but same data for each weekend .

I want 3 information to be displayed for each race. these are race position , race points and rating . but all data will differ according to each race as in google sheet.

My data grid


my google sheet

can my program be done ? and can you guide me .
thanks

Yes, Thunkable has a Data Viewer Grid component that will work well for this. You can search the forums or Google Data Viewer Grid Thunkable to get started with the documentation and video tutorials. If you have questions, just post a screenshot of your blocks and explain what is/is not working.

@tatiang i ve watched tons of videos , and people are doing with rowID to display 1 image or 1 text. But i have 20 races and all of them has different info and i want to show 3 columns per race . My question is whats the easiest way to do it actually . I know this can be madeable but as a beginner i just need guidance.

It sounds like you need to display position, points and rating for each race.

I would sync a Data Viewer Grid (DVG) to your Google Sheet but you need to arrange your sheet differently. You need to have one race per row, like this:

Then when you sync to the DVG, you can select the 3 data values and display them all at once. It’s possible you may need to make a custom DVG that has that many fields (labels).

your solution looks very promising , i ll update in 30 min time .

the thing is i am trying to get data for 20 drivers .
i tried this but nothing happened
image

I don’t understand how you have that set up and how the data connects to each other in your sheet. It’s possible you need a second Google sheet to hold driver info.

normally data comes from another excel and that excel connected to a system that gets data from the game

EDIT : to be clear Drivers and teams will never change. What I want to do is, with the help of the data viewer grid, the user will click on the race that he wants to see the details of the race. I want to show the Race Point, Race Position and Drl Rating, which change for each race, specific to the race.

will this work for you?

you use the race lookup using race to get the race_index
use race_index + 2 to get the column in the stats table, use driver and team for row.

split the cell found at (row, column) by delimeter “^” into position, points, rating

in terms of record id, make an artificial key concatenating driver and team when you load your table. that way the keys map to record id (ie. keys(3) will point to record_id (3)). create 2 lists of keys and record_id’s which are parallel. given a driver,team combo, you can obtain the record_id that corresponds to it and from theere obtain the rest of the record from the worksheet

2 Likes

i dont have that kind of knowledge yet but i ll try to do that sir thx for detailed answer

hey @manyone ,
just to be clear , pilots and names are constant . i skipped that part
i call race1 column with race_index+2 ( i assume race_index=0) than
with the help of delimeter i send the data to google sheets to make new 3 columns for each information.
then i read them back .Did i understand correctly ?
and
1-if its true is there a way to read column or can i use data viewer list .

am i right in my assumptions? assuming my example is your entire data.

  1. when you press italy button from a list (4 items) of races, you want to see a list of the 10 players, their team and the values in column D (ie. race2)?
    2, when you press spain button, you want to see the same but instead of column d you should see column f?
  2. where do those original values come from? are there 4 separate tables somewhere (one tab? per race) that carries the same info as above? my proposal was just to have google sheet automatically populate the table above with formulas that refer to the other 4 tables (presumably as 4 additional tabs) whenever those 4 tables are refreshed?

if this is true - you are just redisplaying static data, then your objective is to create a data viewer list depending on the race selected.
your challenge is to come up with one sheet that updates dynamically whenever new values for the sources arrive. it is mostly a google sheet challenge.
(or maybe i have the wrong assumption all along?)

first assumption correct @manyone

for the second part , we have a propgram and that program connected to the f12021 game and it gets race results and more stuff like this. and program creates new excel and that excel got formulation to fill 8 different excel tabs . but my task is to show the the last part of the chain to mobile.
EDIT: system updates infos because its connected to the game
EDIT 2 ; i told you that I want to show raceposition, racepoints and drl rating above. the reason I’m saying this is because if I can show raceposition, racepoints, and drl rating, I can show the others.(trying to clear question marks)

My task is to show qualification results , race position results and the penalty results

for example this is the penalty sheet


this is the qualifaction sheet

are these two tables the result of your program that generates all the excel tabs? then it’s a different approach you need (i’m still assuming your mobile app will take a race as input and will display the race stats for that race, for all drivers, in a scrollable data viewer list).

you need a new tab that will contain the selected race name, AND the equivalent race_number (say Bahrain = 1, Italy = 2. etc). this number will be used in the next tab below. the race_number can be obtained by vlookup.

then you need a RESULT tab which has all the players (and teams) and the following columns (QUAL_pos, QUAL_rating, PEN_pen1 and PEN_pen2, for example)

if race_num=1,
QUAL_pos - will come from column 3 of QUAL
QUAL_rating - will come from column 4 of QUAL
PEN_pen1 - will come from column 3 of PENALTY
PEN_pen2 - will come from column 5 of PENALTY
etc.
if race-num=2 ,
QUAL_pos - will come from column 5 of QUAL ,5=((race_num-1)*2+3)
QUAL_rating - will come from column 6 of QUAL ,6=((race_num-1)*2+4)
PEN_pen1 - will come from column 9 of PENALTY ,9= ((race_num-1)*6+3)
PEN_pen2 - will come from column 11 of PENALTY ,11= ((race_num-1)*6+5)

you can do all this using the INDEX function of excel.

the RESULT tab is ready to be loaded to your data viewer (it will have to be CUSTOMised to accomodate all your extra columns)

i hope this helps

thanks a lot will try that

hey @manyone sry for bothering again

i manage to copy with vlookup but Is this what you wanted me to do?

thanks again ( i made it at the same tab will that be a problem)

edit ; i manage to copy from another tab
my first tab for races

my result tab ( i write specific vlookup for every race is there a easy way to do it example :=VLOOKUP(A2;race_number!$A2:$B4;2) for bahrain=1
image

this is what the tab SEL will look like:
image

the idea is , your app will ask user for race name via drop down list then it should update cell A2 of this tab and cell B2 will compute automatically. you need this cell to be present in the spreadsheet so the tab RESULT can use it in the computation.

BTW, can you confirm that you already have QUAL and PENALTY tabs available? ie. are those inputs?

for the RESULT tab, i suggest you google the JOIN function in excel.

here’s an example of INDEX function.

assuming this is your SEL tab,
image

assuming this is your QUAL tab (actual name is Sheet6),
QUAL_tab

this is what RESULT tab would look like (note the formula for cell B2):
RESULT tab

it’s the above tab that loads into the thunkable data viewer list.

isnt there s a way to write race name when user clicked the race button on grid list?
yes i can confirm that QUAL and Penaly input

of course there is, if button is picked you’d have several of these:
image

or it could be from a drop down, then it’s another block
image

this will update the race column for the sel tab with value selected. no typing,

sir thanks for that ,but i think we re not using the same UI


mine is not like yours and do u want me to initalise sel_row_id