How to use Fetch Json or Java Script with BQ

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.
UID as id,
SUM(Profit) as total_sales, SUM(Tips) as total_commisions

  • Then through a relation to the BQ Query I do a lookup of the allTime Totals I need.

Screen Shot 2023-04-17 at 5.49.01 PM

This is the first time I have an AllTime Number in Glide without GSheet and while NOT connecting to any of the rows that makeup that data :partying_face::rocket:

I realize a better way to get an all time number would be to keep a running total by ID and query the latest entry…

Methods aside I’m looking for a more efficient way to extract data from BQ.

Here’s what I found on using the BQ API but was unsuccessful in my attempts.
Thanks in advance!

How big is your dataset? column x row

At my peak I’m doing 2k rows a day… I’d also like to plan for much more

wow… i do up to 2 million in google sheets. Then I have to connect a new one…
How fast you are getting results from BQ? and from how many rows?

30 columns x ~1 Million Rows per year

I don’t have any experience with BQ… just wanna compare my results from GS, I have 2 million rows x 2 columns per connected sheet… so multiply by 4

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.

1 Like

It looks like this…

  "Trigger": "Completed",
  "Rank": “1”,
  "Name": "Jack",
  "IDNum": "#00QT5",
  "RunningTotal": “100”

Any guidance on the Java Script would be appreciated… I’d sure like to buy you a ton of beers

1 Like

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?

More efficient than what?

I don’t really understand why you’re messing around with Fetch JSON, when you could simply write SQL to get whatever you need out of BQ.

What am I missing?


big $ SAVINGS on fees :wink: plus many seconds or even minutes on query time… plus the comfort of dealing with data… plus more… that I will not explain here…

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

1 Like

Okay. I guess I’d need to understand more before I could comment any further.

1 Like

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.

Really? :woozy_face:

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).

You can read more about it here SQL CREATE VIEW, REPLACE VIEW, DROP VIEW Statements



SA query… dynamic and instant :wink: