Newlines still saved in BigQuery even after removing them in GUI query mode

💬 App Building
1

I’m using a TextInput in Retool and registering data to BigQuery.
When I enter text in the input, it seems that newline characters are being saved in BigQuery, even though I didn’t press Enter.

In the GUI query mode, I tried to remove control characters (including newlines) with this expression:

{{ some_text_input.value
  ? some_text_input.value.replace(/[\x00-\x1F\x7F]/g, '')
  : { RT$BQ_TYPE: "STRING", value: null } }}

However, newlines still get stored in BigQuery.
Is there a way to completely avoid saving newline characters when inserting data to BigQuery?

Thank you!

2

Hey @mayaas - I'm not sure why any control characters would be added to your input in the first place, but you're definitely doing the right thing to strip them. Just to narrow down the scope of the issue, can you look at the payload of of your request via the browser's Network dev tool? It would be helpful to verify that the string is correctly formatted when it leaves the client.

3

Hey, thanks for the advice!
I followed your suggestion and checked the request payload via the browser’s Network dev tools — and I can confirm that there are no control characters (like \n) present in the data being sent. The input string looks clean when it leaves the client.

However, for some reason, a newline (0A) still ends up in the string once it reaches BigQuery. So I suspect the issue is happening somewhere between Retool and BigQuery, not at the input level.

As a temporary fix, I'm stripping line breaks using REGEXP_REPLACE() on insert, but I'd love to get to the root cause if you’ve seen this kind of behavior before.

Appreciate your help!

4

Thanks for digging into that! It's still possible that the Retool backend is doing something weird. Have you tried - if such a thing is possible, as I'm not super familiar with BigQuery - executing a similar query from a tool like Postman? Anything that would community directly with BigQuery, essentially.