"We found an unexpected character in the JSON input" error in getting data from google analytics

[oiluj0002]

I need help!

When I need to import some Data from google analytics API in Power BI I get that error and can't import data anymore at least for hours. It is related to the data inside GA4 or another thing?

[kboyd]

This is happening to me as well, unlike most of the comments below I have never actually been able to pull my data in, seems like most of the replied below are related to failed refreshes of the data.  I do not see a solution posted anywhere.

[MongooseGeneral]

For me it definitely works to reduce the amount of data you are trying to pull - start as small as you can just to test it and then grow from there. In the end I have had to export data from GA4 each month and combine with the current month of live data to keep the dashboards up to date

[kboyd]

The problem is I am trying to export page level data, and due to the number of links on our site even if this is a small time period the number can be 300k+ rows.  So there really is no starting with a small amount of data, not to mention the connector does not use any sort of filtering.  The other issue is around the granularity, getting to the data is only one issue, getting the data by day is another challenge.  Without this ability I am not sure how much value the connector has.  We are already importing other platform essentials through API.

[MongooseGeneral]

Oh totally, I was trying to do a page views report for a while but it needed so much maintenance I gave up on it. GA4 to Google BigQuery then BigQuery to PowerBI is the best option for that I think.

[SLYZGUIN]

How did you proceed exactly to combine the data exported from GA4 each month with the current month's data? Could you provide the code you used to combine them?

[MongooseGeneral]

So it's just a case of getting the live data from the connector to match the layout of the GA4 export (or vice versa), then you can append the queries in transform mode in Power BI - so you combine the live api data with your export spreadsheet. If you can get the current month's data working through the connector that's the main hurdle.

[spinto]

Me pasa exactamente lo mismo, nunca pude publicar mis datos. Por ahora no veo otra solución que adquirir un conector externo, como Windsor por ejemplo.

[SLYZGUIN]

Help Needed: DataFormat.Error in JSON Input

Hi everyone,

I am encountering a DataFormat.Error with the following details:
DataFormat.Error: We found an unexpected character in the JSON input.
Details:
Reason = DataFormat.Error
Value = \31
Position = 0

Has anyone experienced this issue before or knows how to resolve it? Any help would be greatly appreciated!

Thank you in advance.

[aria233]

sometimes I will run into this error randomly once in a while. There doesn't seem to be a good solution and it isn't quite clear what the cause is either, but I've found that if I just leave it there through the night, the problem doesn't exist when I refresh it again on the next day morning, so maybe it's just the occasional connection to the api that jerks around a bit.

[ReportMaster]

Hi @oiluj0002 Seems like the 2.0 beta connector is still presenting some issues. As a workaround, maybe you can try to test your connection with a 3rd party tool. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the GA4 connector in the data sources list:

After that, just grant access to your GA4 account using your credentials, then on preview and destination page you will see a preview of your GA4 fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[WinnieZhang]

I got exactly same problem since this morning. It worked all good before.

I have 3 queries connected to GA4, the one is 500+ rows, one is around 6,000 rows, the other one might have lot more rows but I added Group process to summarise it into 200+ rows. 

All 3 queries didn't work this morning, showing the same error. I tried to create a new file and connect to GA4, got 50% chance to be able to successfully display the GA4 cubes. But once I switch back to the original file, I got 100% chance failed to get any data. 

I don't think there is anything to do with the JSON syntax, because they had been working well before this morning for a few months, and I didn't change the queries at all. Someone reckoned that was the data amount issue, but my dataset is only 500+ rows. Not sure what was changed over night. 

My Power BI desktop version is 2.124.2028.0 64-bit (December 2023)

[MongooseGeneral]

I think you might find if you leave it a while even with the original file and then try again it will work - especially if you disable loading of the larger ones and just try to enable them one at a time. I have a suspicion that we are hitting the API rate limit and it is returning an error which includes the unexpected character. This is a thread about a different issue (but which has also affected me at the same time as this) that I think is probably the cause of both problems:

Stuck at Evaluating Data for Google Analytics : r/PowerBI (reddit.com)

[WinnieZhang]

Yes, you make sense. My case was a bit different to the Reddit post discribed. My query had passed evaluating step, it stuck at the step of waiting for google analytics to respond. But yes, I agree it is very likely because the free API found the rate limit was reached. As the same queries started working since I filtered the date into a shorter range. It's annoy because sometimes I need to do the Y-o-Y comparison, if the data is too big, I wouldn't be able to download the rolling 12 months data. But, it is what it is....

[MongooseGeneral]

Yeah it's very hard to work with that's for sure. Hopefully it is something they will sort out at some point.

[aria233]

omg! I'm getting same error using 1.0, the connector to UA. It worked just fine this afternoon but not it fails every time now... Have you fixed that? or it just fixed itself later?? it's so annoying :(......

[SRW]

I am also facing this issue. I am using 2.0(Beta) connector. Some times data get refreshed but some times this error occures. I am taking data from different properities, all other properities are refreshing properly. But this is the problem with only one property having most Traffic.  Is it the issue with GA4 connector or the data that google analytics sending ?Can someone please take this issue on priority?

[MongooseGeneral]

I have got around it for my situation and it involved massively reducing the amount of data being requested in each query. It was only failing on the request for daily page views by country for the last quarter, not other requests involving less data. I resolved it by only pulling the past month's worth of data (so I now have to add in historical data to a spreadsheet each month), and splitting that into multiple (6) queries so that each request only asks for 5 days at a time. Obviously they all still run concurrently but it doesn't seem to cause the error.

Ridiculous but it did the job for me. I would suggest anyone else stuck tries choosing the transform option and heavily filtering the data straight away to see if that loads it.

[ErikSmithBPS]

This is maddening. It's only happening with one GA4 property for me, which is odd, but it's the one with the most traffic. All the other GA4 properties are refreshing just fine. I've spent hours trying to fix this, and am now stuck until something gets resolved. Hurry up and wait...

[andrew_796]

I am having the same issue. I am using the Implementation 2.0 (Beta). Waiting on a solution or fix.  

[Syndicate_Admin]

Hello, did you find some workaround for this issue? Thank you