# How to use Fetch Json or Java Script with BQ **URL:** \ **Category:** Ask for Help\ **Created:** [April 17, 2023, 9:58pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649 "2023-04-17T21:58:51Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 17, 2023, 9:58pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/1 "2023-04-17T21:58:51Z")
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. ```auto 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 ``` ![Screen Shot 2023-04-17 at 5.47.17 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/9/299cd5a9b9cab18a8bcaebce9bb413f865aa5eb9.jpeg) - 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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/0/c/0c69e6ba6c7596f2d9a237fa26c1218b164188d9.png) 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 🥳🚀 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! > **[BigQuery API  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/rest)** ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 17, 2023, 10:16pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/2 "2023-04-17T22:16:29Z")
How big is your dataset? column x row ---
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 17, 2023, 10:18pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/3 "2023-04-17T22:18:07Z")
At my peak I’m doing 2k rows a day… I’d also like to plan for much more ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 17, 2023, 10:19pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/4 "2023-04-17T22:19:17Z")
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? ---
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 17, 2023, 10:21pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/5 "2023-04-17T22:21:09Z")
> [@Uzo](#): > > How big is your dataset? column x row 30 columns x ~1 Million Rows per year ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 17, 2023, 10:22pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/6 "2023-04-17T22:22:12Z")
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 ---
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 17, 2023, 10:26pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/7 "2023-04-17T22:26:21Z")
Let’s assume I have a running total available by ID#…Could I get that number from BQ using a fetch JSON column? > [@Uzo](#): > > How fast you are getting results from BQ? and from how many rows? 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. ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 17, 2023, 10:27pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/8 "2023-04-17T22:27:46Z")
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 ---
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 17, 2023, 10:33pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/9 "2023-04-17T22:33:30Z")
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. ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 17, 2023, 10:34pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/10 "2023-04-17T22:34:40Z")
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. ---
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 17, 2023, 10:47pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/11 "2023-04-17T22:47:26Z")
It looks like this… ```auto [{ "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 ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 17, 2023, 10:48pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/12 "2023-04-17T22:48:20Z")
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? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 18, 2023, 3:47am UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/13 "2023-04-18T03:47:06Z")
> [@Eric\_Penn](#): > > Methods aside I’m looking for a more efficient way to extract data from BQ. 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? ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 18, 2023, 6:18am UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/14 "2023-04-18T06:18:33Z")
big $ SAVINGS on fees 😉 plus many seconds or even minutes on query time… plus the comfort of dealing with data… plus more… that I will not explain here… ---
**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\ **Post date:** [April 18, 2023, 6:54am UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/15 "2023-04-18T06:54:41Z")
> [@Darren\_Murphy](#): > > More efficient than what? 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 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 18, 2023, 6:59am UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/16 "2023-04-18T06:59:08Z")
Okay. I guess I’d need to understand more before I could comment any further. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 18, 2023, 10:15am UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/17 "2023-04-18T10:15:41Z")
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. ---
**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\ **Post date:** [April 18, 2023, 1:15pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/18 "2023-04-18T13:15:21Z")
> [@Darren\_Murphy](#): > > 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? 🥴 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](https://www.w3schools.com/sql/sql_view.asp) Saludos! . ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [April 18, 2023, 4:37pm UTC](https://community.glideapps.com/t/how-to-use-fetch-json-or-java-script-with-bq/60649/19 "2023-04-18T16:37:56Z")
SA query… dynamic and instant 😉