Is there a way to use Fetch Json or Java Script with BQ so I can get the values I need in one column instead of entirely new table?
Here I moved some sample data into BQ.
With a query I can extract AllTime Totals into a table in Glide… that’s Cool! Previously I did this with a sumIF in Google Sheets. A sumIF would give me a total without connecting additional rows to Glide.
SELECT
UID as id,
SUM(Profit) as total_sales, SUM(Tips) as total_commisions
FROM
`project-bolt-384014.Database.CompleteRecords`
GROUP BY UID
LIMIT
2000
Let’s assume I have a running total available by ID#…Could I get that number from BQ using a fetch JSON column?
I’m only testing with 10,000 rows so I’m not sure. It seems slow but that’s probably because I’m doing a sum when I should just be grabbing the latest entry with a running total on the line.
I already have a list of ID’s… adding an entirely new table from a query is duplicating my list of ID’s.
if it is only 10K rows, you can fetch the whole data into Glide and do the query on the user device… it will be instant… i posted an example of my App somewhere, with 2M rows x 3 columns
Ok so if I’m understanding you correctly I would fetch all the rows and then do a query on the user device?.. What would the syntax look like in the Fetch Column? (That’s where I’m stuck)
Assuming I have a running total I was under the impression I could just fetch the latest entry and work with that.
I do not know how BQ is structured, so I can’t help you with that… I do GS
But if you can fetch BQ, then use the Java column to break it down, not fetch query… what is the BQ JSON looks like?
Try to open a new BQ with maybe 3 rows and fetch it. Then you can see how to break it down and write JavaScript to query it. When you fetch big data, Glide will get stock when you try to see that data.
Show me 2 rows of data… this is one row. It looks like it will repeat column names for each row, which will create a gigantic data load, can you fetch only values?
It costs a lot of rows. It duplicates a list of iD’s that are already in another table. E.g total sales for all iDs. There’s probably an approach I’m not seeing.
Also I was trying to eliminate a =filter from GSheet by replacing it with a BQ table but relations to computed columns don’t work on queryable tables (need that for my use case). I kinda already knew that… hoping it wasn’t true.
I know many of you speak of working with JSON so thought maybe I was missing something
My memory of your setup is a bit fuzzy. But, is that filter formula dynamic in that you can change a value via the App and that triggers it to load a separate set of rows? If that’s the case, then you might struggle with Big Query. One of the limitations of BQ is that you cannot dynamically modify table queries. Once you define a table query, then it’s set. The only way to modify it is via the table query editor in the builder.
I was going to ask @Eric_Penn to create a template where one can change the query parameters dynamically and improve the results, but your note changes everything.
I really need to test BQ with Glide asap to know the pros and cons.
I’m only testing with 10,000 rows so I’m not sure. It seems slow but that’s probably because I’m doing a sum when I should just be grabbing the latest entry with a running total on the line.
Eric, how long does this query take to give a response each time you run it: 6, 8, 15 sec?
There is a plan B to improve that response time if this is high: create/use a “View table” in BQ (technically, it is a virtual table that stores the data/results from a previously defined SQL statement).
plus many seconds or even minutes on query time… plus the comfort of dealing with data… plus more… that I will not explain here…