API REST - GET - DataFormat.Error: We found an unexpected character in the JSON input.

[Anonymous]

Hello!

I'm having some trouble connecting to SERVICENOW API REST. 

When I make a call with 1500 lines (sysparm_limit=1500) I can get the data thougth this call correctly, but if I raise the number of lines over 2000 I receive this error: DataFormat.Error: We found an unexpected character in the JSON input.

Is there a limitation with the number of lines retrieved or a parsing problem?

let
    Fonte = Json.Document(Web.Contents("https://YYYYYYYYYYYYYYYYY.service-now.com//api//now//table//u_fcu?sysparm_limit=2000"), 65001)
in
    Fonte

[Syndicate_Admin]

Hi,

Try to limit the number of rows retrieved by the request. This was the problem that I had with the ServiceNow API and there is a huge possible to be your problem too.

**

Google Translate

Try to limit the number of rows retrieved by the request. This was the problem I had with the ServiceNow API and there's a good chance it's your problem too.

[antonstorozhuk]

Hello,

I’d like to propose and alternative solution for you, you might want to try out our app - Power BI Connector for ServiceNow:

• https://store.servicenow.com/sn_appstore_store.do#!/store/application/87a42573879e0110fb5033773cbb35...
• https://www.alphaservesp.com/products/servicenow/power-bi/

We have a handy User and Admin Guides for your convenience, but feel free to reach out support team at support@alpha-serve.com if you need any assistance.

Thank you.

Regards,

Anton

===

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi!

We found a workaround for this parsing problem. We used the sysparm_fields to get just the columns that were needed. This way we could retrieve all the lines of the table.

let
Source = Json.Document(Web.Contents("https: //YYYYYYYYYYY .service-now. com/api/now/ table/tablename?sysparm_fields=  column1,column2,column3,column4,column5"))
in
Source

No problem was found getting all the columns in separated groups. 

Unfortunately, it's still unknown why it returns a parsing problem when I try to retrieve all the columns without specifying them. 

[john_colvin]

This is the only post I've found with this specific error. I'm having the same issue with a REST call to SharePoint. I'm already using a "select" statement to only get specific columns. Any ideas?

[Syndicate_Admin]

Hey,

My Spanish is worse than my English (lol), so I willl try to help.

The problem happens because the number of rows, but you can add filters to your request easyly. This link on Service Now documentation will help you.

https://developer.servicenow.com/dev.do#!/learn/learning-plans/quebec/servicenow_application_develop...

[mahoneypat]

I suggested you separate the Web.Contents from Json.Document to troubleshoot and make sure you are getting a Web response.  Now that you are, you can troubleshoot the Json part.  If wrapping it in Json.Document doesn't results in Lists and/or Records to expand, try the following:

- leave your step with just Web.Contents( ) and you should see a document icon with "JSON" on it (or some other text)

- right click on that icon and choose text instead of JSON so you can see the actual text returned

- see if it's value JSON, and, if needed, paste it here so others can help

Regards,

Pat

[Anonymous]

Hi @mahoneypat

I tried your approach, but unfortunately, it returns me just a few lines. Fewer than expected. 
While my error position is  35.523.241, the max position that I get from the copy table from PBI is 35.844 and the last line retrieves just a portion of the columns selected. 

[PhilipTreacy]

Hi @Anonymous 

If you get a JSON response when using only Web.Contents, copy/paste that response into a JSON validator to check it's correctly formed JSON

https://jsonlint.com/?code=

Regards

Phil

[Anonymous]

Hi @PhilipTreacy 

Hmmm, I'll give it a try. 

But is it safe to check on this website? Once I'll be testing some confidential data from a company....

[mahoneypat]

What is the "65001" in the Json.Document function?  Do you get a json doc with just the Web.Contents part?  See what you get and then open it with the right function.  You may need to specify json format in the web call.

Regards,

Pat

[Anonymous]

hello @mahoneypat !
Thanks for you time and help!

The "65001" is related to UTF-8 encoding. But it's not necessary due to the GET method from Servicenow brings back automatically the UTF-8 encoding. 

When I tried to get the data only through Web.Content it returns a JSON document which I can't parse after it. 

Parse by text, returns a limited amount of data and Parse by JSON returns the problem with DataFormat.Error thing. 

Regards,

Kendi

[PhilipTreacy]

Hi @Anonymous 

I'm not familiar with that API but I'd expect there is a limit to how much data can be returned in one request.  You can check the API documentation to find out.

https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/integrate/inbound_rest/concept/c_RESTAPI.html

That error you are getting usually indicates that JSON hasn't been returned, usually it's a HTML error message.

BTW you only need single/ in the URL e.g. /api/now/table

Regards

Phil

[Anonymous]

Hi @PhilipTreacy!

Thanks for your time!

Using the sysparm_limit I get the default line numbers of 10.000 and my query can't go any further from 1990 lines. 

sysparm_limit Limit to be applied on pagination. The default is 10.000.
Unusually large sysparm_limit values can impact system performance.

https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/integrate/inbound_rest/reference/...

When I try to get the info through python (request) I get the full content. Looking at the Position pointed by the PBI error message I can't find anything wrong, such as a  } 

headers = {"Accept":"application/xml"}
response = requests.get(url, auth=(user, pwd), headers=headers)

Thanks for the "/" tip!

Regards

Kendi