Best answer

Google BigQuery error: Value of type STRING cannot be assigned to invoice_amount, which has type NUMERIC at [1:195]

  • 21 June 2023
  • 8 replies
  • 2371 views

Hi,

Hope you guys can help out. I’m trying to update an existing Google Bigquery table using this schema:

When creating a Zap to update the table i’m getting an error when updating the numeric field ‘invoice_amount:

The value of this field is ‘9.99’ which shouldn’t be a problem. I have the same error when writing to timestamp fields.

You guys have any ideas what is causing this issue an how to solve it?

Thanks in advance!

icon

Best answer by SamB 25 June 2024, 17:41

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

8 replies

Userlevel 7
Badge +11

Welcome to the Community, @joep87! 🙂

It looks like there are different sub-types available for Numeric field types (INT64, NUMERIC, BIGNUMERIC and FLOAT64). So I’m wondering if the one that has been selected for that field doesn’t accept decimals.

Do you think that could be the case here or is that field able to accept decimal points when entered directly in Google BigQuery?

With the timestamp fields I’m guessing that the date that is sent over isn’t in the exact format that Google BigQuery is expecting. It looks like it might be expecting to receive the time in a format like: 2020-01-01 00:00:00 UTC. Does that match the format of the date/times that have been selected for those timestamp fields?

If not, you may need to try using Formatter (Date / Time > Format) actions to convert the date/time values into the format that Google BigQuery is after. See Modify date and time formats in Zaps for more details.

Looking forward to hearing from you on this! 

Hi Sam,

Thanks for your answer! The field in Bigquery is a numeric field which should accept the value 9.99

When I use a zap to create a row the same value is being added without any error.

I’m also in touch with Zapier Premier Support. They recommended me to ‘reformat’ the value because it might be changed by Zapier to a string during the different steps. Still the same erorr.

I haven’t tried the trick with the timestamp yet. However when i choose to create a row the same value is being added without any problem.

Is it possbile we’re dealing with a bug here which changes all values to strings before they are being sent to Bigquery?

Userlevel 7
Badge +11

Thanks for getting back to me here, @joep87

As the invoice amount field should definitely be able to accept the value 9.99, I’m wondering if there might be a space ahead of the value that’s been selected for it. I’ve seen cases with other apps where an additional space for example caused the value in the field to be seen as text rather than a file object. So it could be that there’s something similar happening here. 

Can you take a look in the Zap and remove any additional spaces from the field to see if that sorts it?

For the timestamps perhaps how the Zap is sending the date/time is incorrect. If the Zap is changing the format of the date/times and amounts when it’s sending them then it yes, it could well be a bug. In which case you’ll want to continue the investigation with our Support team as they’re able to dig into the logs for the Zaps to get more details and can open up a bug report on your behalf.

Please do keep us updated on how it goes!  

Hi Sam,

Thanks again for your quick response!

I’ve been able to check for any additional spaces before or after the value. As you can see in the screenshots below this is not the case here.

I honestly think we’re looking at a bug here. Is there someone having similar issues?

Userlevel 7
Badge +11

You could well be right on that, @joep87!

I’ve not seen any similar reports come in yet so it may just be that you’re the first to come across this. I’m not able to open up a bug report from my end but our Support team can, so I’d definitely recommend continuing your email conversation with them. 

Hopefully they’ll be able to help get to the bottom of this soon! 🤞

Just been informed by Zapier Support that this is a bug which need to be addressed by developers. I didn’t get an ETA as the prioritization of resolving this issue is dependant on the amount of customers affected. If you run into this issue as well I suggest you contact Zapier Support referring to this topic.

Userlevel 7
Badge +9

Heya @joep87 - thank you for updating the thread with your support findings! 🤗

We’ll also keep the thread updated once a fix is launched.

Thanks again for raising this in community!

Userlevel 7
Badge +11

Hey folks, I’m happy to report that the bug has since been fixed! 😁🎉

To take advantage of this fix you’ll want to ensure your Zap is running the latest version of the Google BigQuery app. And if your Zaps are on the latest version of the Google BigQuery app and are still experiencing these errors please let us know in the Community or contact our Support team directly and we’ll be happy to assist further. 

In the meantime, happy Zapping! ⚡