I am building an app for users to view a dashboard of basketball statistics, with users selecting the team, date range, and some other conditions to view the relevant stats. These conditions are currently used as inputs in a QUERY formula in my Google Sheet, but I need to be able to:
-Take in user-specific inputs for team, date range, and other conditions
-Use those inputs to update the relevant statistics
I’m running into trouble with passing the user-specific inputs back to the Google Sheet and recalculating the team statistics. Can anyone help me figure out how to do this smoothly?
Is this some kind of a Basketball Reference app? I would love to see it.
I can’t think of a good way to show user-specfic data in an interactive way like this, when you have to push back data to the Sheets and then use a Query. It would introduce a big lagging in performance, and no way to have it filtered to each user as far as I aware.
I transformed college play by play data into spreadsheet rows; if I get it working I’d be glad to share it.
I’m considering trying a few different ways to extract the data I need and do calculations within Glide as much as possible, but pushing the user specific inputs back to the Google Sheet has been a real sticking point so far.
Alternately, is there a clean way to filter & roll up rows of data within the app? I haven’t experimented with that approach yet.
1 tab will have team stats for the selected team, date range, and conditions; functionally this is one row
1 tab will have stats for each player for the selected team, date range, and conditions; I’m using an inline list to let the user go between the players, but these have 1 row per player; functionally this could be 1 row each on separate sheets, but I have it as ~9 rows on 1 sheet
I also want to add 1 tab to show the 5 most played lineups for the selected team and date range, with stats for each lineup; I am planning for this to be 5 rows.
Ideally, the home page asks the user to select a team from a choice list, and select start and end date from a date picker. Either on the home page or on the stats pages, the user can select individual players to be in or out, and some other game conditions, as choice lists.
My goal is for the stats (embedded in tables) to update as the user selects new conditions. It’s been challenging to figure out how to best set this up; I had used a QUERY function but am testing out other formulas to sum up some key data, and then using Math columns in the data editor to calculate the stats.
My main goal is to let my subscribers explore some of this data on their own, so if there’s a different structure I’d be open to that. The trickiest part is making sure one user’s inputs don’t affect the view for others.
From reading what you proposed I think it’s hard to do this in Glide only with some limitations on the current math/rollup. More flexibility is offered by QUERY but the key thing, as you say, is the user-specific one.
I would advise you to keep on trying things and if you have any specific questions to ask, come back here and we’ll try to help. As of now I don’t think there’s a good solution to your problem, which deals with the “date range” quite a lot and we don’t have the ability to compare dates in Glide.
To have it user specific, force the editing information to carry a tagline of the user, by doing this that data is unique to the user and is practically jibberish while resting on the sheet. When the user accesses the data the sheet pulls that specific cell as reference to the correct data which is another cell that has the tagline removed. Here is an example of this:
User submits 88 as a rating. The user’s email/unique mark is bob@gmail.com. You use a simple arrayformula that combines this data into bob@gmail.com88 (=a1&b1) so now the cell has a specific reference to allow you to pull from and isolate from others.
Thanks Joe, I was thinking about that and that’s why I asked him about the QUERY returning only 1 row or not. I was not sure how the QUERY results will be returned in the sheets, and how it would be displayed on the front end. A bit tricky when we have to do the calculation in the Sheets I think.
Just a thought about not using query. Depending on the complexity of the queries you are using you could use some if else columns to determine which records to return. It may be clinkier and more columns but may allow you to keep everything in glide and avoid the use of sheets as much
Hi @Drearystate@ThinhDinh, I am building a Projects Assessment app, which is going to lead me to have 100+ columns of calculations in the DataEditor. It is becoming much too complicated.
Before moving to another tool, I was wondering if there is a way to use G-Sheet queries with User Specific data, and I understand from the above exchanges that you have leads …
Does your approach works with this sort of app:
1/ Sheet “Projects”
lines: Projects >> created by the User
columns: 10 criteria per project >> a grade is given for each one by the User
2/ Sheet “Dashboard” (this is where the Dataeditor is too limitating)
lines: Projects (arrayformula, then relation of previous sheet)
columns: “User weight” per criteria + “Average” calculations
In addition, in the Dasbhoard sheet, there is a “Weight” (number entry) component per criteria; this allows Users to define different levels of importance per criteria, according to their analysis need.
Example:
In sheet “Project”: for Project 1, Criteria 1.1, User A & B marks it “4”
In sheet “Dashboard”:
. User A gives a weight “3” to Criteria 1.1.
. User B gives a weight “2”
the criteria grade becomes 4x3 for User A, and 4x2 for User B.
Without User Specific column, I don’t know how to do it, and therefore I had to move all G-Sheet query to the Dataeditor
