Bringing a SUM total over from BigQuery has commas

Need help APIs
1

Hello,

I have a question regarding using the API to query a BigQuery database and retrieve a SUM. I’m using Make.com as the middleware, and I can confirm that the total is being sent back to Bubble successfully. However, when I display the result on a page, it appears as: 6, 8, 6, 6, 8, 5, ., 5, 6, 7, instead of the correct format: 686685.567.

I’ve attached screenshots from BigQuery and the page setup in Bubble showing how the text is formatted. Any guidance on resolving this issue would be greatly appreciated.

Thank you!

2024-12-16_09-44-04
2024-12-16_09-44-041920×911 239 KB

2024-12-16_09-40-53
2024-12-16_09-40-531920×911 167 KB

2

Can you share how are you sending the information to Bubble?
Are you sending a POST to your endpoint? Can you share the setup?

3

@sales12 Why don’t you directly retrieve the date from BigQuery using the API connector? This approach offers greater flexibility and eliminates the need for a Make subscription.

4

Sure, thanks for your reply. Sending it as a webhook response. See the image below.

2024-12-16_10-15-58
2024-12-16_10-15-581920×911 133 KB

5

That’s a great question! I’m just testing things out at this point, but that would definitely be the best approach without using Make. Do you have any recommended readings or how-to guides for this? Thank you!

6

Inside Make.com, try to insert a custom header in this POST

content-type = application/json

See if works.

1 Like
7

Here is how to do it:

  1. Create a Google service account with access to BigQuery
  2. Add a new API to the Bubble API connector you can name it “BigQuery”
  3. Authentication type: JSON Web Token (add the service account email and private key)
  4. Use POST request to get the data
    URL: https://bigquery.googleapis.com/bigquery/v2/projects/[project_id]/queries
    Body:
    {“query”:“<write_your_query>”,
    “timeoutMs”:200000,
    “useLegacySql”:false
    }
1 Like
8

This is great! Thank you! I am stuck getting an auth error when trying initialize the call. Do you use https://oauth2.googleapis.com/token as the Access Toke Endpoint? Does anything need to be placed in the scope field?

9

Thank you! It does have the option for a header in the post, but unfortunately that didn’t work.

10

Did you reinitialize you endpoint in Bubble?

11

I did, it threw an error. With no header it does return the correct amount, see image below.

2024-12-16_12-17-17
2024-12-16_12-17-17943×548 65.5 KB

12

Token endpoint: https://oauth2.googleapis.com/token
Scope: https://www.googleapis.com/auth/bigquery

1 Like
13

Thanks! I am in. I also had to replace all of the \n in my key to carriage returns.

I noticed that the data is not coming in as it was using Make. AI response below. This sounds like it might be workload-intensive if we’re querying a lot of data. Does that sound right to you, or do you know of a workaround to display data in a repeating group?

----------------- AI REPONSE ------------------------
Ah, I see! When you’re working with Bubble and posting queries to BigQuery, the challenge often lies in correctly formatting and mapping the response data into Bubble’s data structure, especially for displaying in a repeating group. Here’s how you can tackle it:

Steps to Handle BigQuery Response in Bubble

1. Understand Bubble’s Repeating Group Requirements

  • A repeating group in Bubble expects a list of objects where each object has the same structure (fields).
  • In your BigQuery response, the rows are in the rows array, and each row contains an f array (field values) that maps to the schema.

2. Transform BigQuery Rows

  • Bubble doesn’t directly understand the nested structure (rows > f > v).
  • You need to transform the rows into flat objects (e.g., {id: "595887", type: "invoice", date: "2022-12-14", ...}).
  • This can be done using an API Workflow in Bubble or by preprocessing the response with a custom plugin or external service (e.g., Integromat/Make).
14

@sales12 It should’t be workload-intensive.

Yes, that’s one of the challenges when dealing with BigQuery JSON responses. It returns the data in two arrays: one containing the query schema and the other containing a list of data rows.

Here is how to deal with it in bubble:

  • Add a repeating group
  • Type of content “bigquery row”,
  • Data Source Select the right bigquery API from the external API list
  • Repeating group elements, you can start with a text element and display row’s each item’s f each item’s v
1 Like
15

Thank you for your help! This will be much better than what I was doing.