Insert row into BQ - System.DBNull error

[lukrecjabil]

Hi all,

I'm quite newbie in AppSheet, so sorry if the solution to my problem is obvious. I'm trying to add a row to a table in BQ via AppSheet. All is good until my row in application has data for all columns. The problem is I want to add data only for some columns. The error is: "app sheet Cannot create parameter of type String with value of type System.DBNull bigquery". Columns in BQ are NULLABLE. Any ideas what should I change? All help will be appreciated. 

[diogolupcosta]

Hi and welcome to the comunity.

Maybe those articles would help u:

https://help.appsheet.com/en/articles/4592635-using-data-from-google-bigquery-with-connected-sheets and
https://help.appsheet.com/en/articles/5398107-using-data-from-google-bigquery-with-the-bigquery-data... 

[lukrecjabil]

Hi,

Thank you for your reply. Unfortunately I did not find a solution in above articles.

 Have a great day

[Koichi_Tsuji]

BQ connector is fairly new feature, so possible bugs I assume.
I suggest you formally send ticket through

https://www.appsheet.com/Support/Contact

to get hands on support.

Cc to

@Rachelgmoore 

[Rachelgmoore]

Thanks, @Koichi_Tsuji ! We'll take a look.

[mitchcystem]

Hello. Just wanted to know if this is solved? I really love AppSheet and is now also using BigQuery.  However, I am also experiencing System.DBNull error.

Also would really appreciate it if you could also help us with System.Decimal error. Thanks and God bless.

[JulioHernandez]

Hi!
I'm stuck with the same problems =( 
System.Decimal
System.DBNull 
I already sent my ticket but no response 

Any news?

[jstreit]

Same problem here as well.  Getting issues with almost all fields when inserting or editing.

[Steve]

Escalated.

[mitchcystem]

Anyway, this is my workaround (force bigquery to have N/A value instead of System.DBNull) since I can't seem to find any solution to this.

A. For System.DBNull:

I created a Google Sheet table to act as medium between AppSheet and BigQuery. And created automation 'Whenever a new row is added on Gsheet, add rows to Bigquery using values from GSheet and then Delete the row in Gsheet'

Basically, It involves two Behaviors:

1. Add a new row into another table using values from this table

using these sample formula...

IF(ISBLANK([TextField]),"N/A",[TextField])

IF(ISBLANK([NumberField]),0,[NumberField])

IF(ISBLANK([DateTimeField]),Now(),[DateTimeField])

2. Delete Row.

B. For System.Decimal:

I set my BigQuery schema to 'string' and my AppSheet schema to 'text'

Then just create virtual columns in appsheet using Decimal() function.

"this is just my workaround, hope this gives you idea or solution until someone finds a way to directly add/edit rows in BQ when there are blank or decimal fields involved."

[d_bahir]

I am exploring a fix for this issue, would you mind providing your AppSheetId so that I can add you into this fix.

[mitchcystem]

I'm not so sure if this is the AppSheetID that you mean:

BPNLTest-1001304967

[d_bahir]

If you go to my account, under the "My Account" header you will find it says "Id:"

[mitchcystem]

ID: 1001304967

[d_bahir]

Added you, give it a try.

[mitchcystem]

Wooow! You're a lifesaver, thank you very much for this. Will it also work with any future apps I will build using AppSheet and BigQuery? 👏👏👏

[d_bahir]

Yes it will be fixed for any app that you build.  I am rolling out this fix to all users, wanted to get a confirmation from the users that encountered this that it works correctly.

[mitchcystem]

Thank you very much.

[jstreit]

 When can we expect a fix for all users? 

[d_bahir · Accepted solution]

By the end of next week.

[jstreit]

Great! Thank you

[lukrecjabil]

Hi All,

From my side, I made a test and can confirm that the fix is working. Thank you all for your help :-).