Having troubles with SharePoint REST API (not Graph)

[RomyGutbier]

I am trying to create an object using WebAPI Services and have been successful in some of the actions.  I am having issues with using getbytitle('ListTitlename')/Items.  It works in postman but BluePrism returns the following error message:
"Internal : Unexpected error Data Type mismatch in array: Element '' has type instead of expected type 'System.Int64'"

I believe it has something to do with the json collection being returned that is not in the format required.  

Has anyone been able to get this to work and if so, what did you do to get it to work?  

I am sure I will have additional problems with other requests but this solution may assist.

The full URL is: GET https://DomainName.sharepoint.com/TeamName/GroupName/_api/web/lists/getbytitle('ListName')/Items

Thanks!

------------------------------
Romy Gutbier
Test Lead
Ausnet Services
Australia/Melbourne
------------------------------

[shashank.kumar280]

Hi Romy,

It seems like there is issue in parsing the response. Are you using any JSON path filter in your response parameter that might be causing this? If you can post a screenshot of the response in POSTMAN that can help in debugging as well.



------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

[ewilson]

Hi @Romy Gutbier,

I've run into​ this issue in the past when communicating with Outlook via REST. The issue is likely that the response from SharePoint is composed of multiple JSON objects of the same type (i.e. an array of objects). As Blue Prism/Newtonsoft interprets the JSON, it basically generates a data type mapping for the columns of the output Collection (aka DataTable) setting the data type of each column based on the values it encounters in the first object (ex. string -> Text, Int64 -> Number, bool -> Flag, etc).

If a property in the object is not set (I.e. no value) or if it's value is set to null, it will be interpreted as a string. Later on, you may run into another instance of that object where the property is actually set and you find that it's supposed to be something other than a string. Since the DataTable column has already been defined as string, you get the type mismatch error.

To address this in Outlook, I ended up writing custom code to parse the JSON response myself. That way I could control to type mapping and ensure if a property was supposed to be Int64 I set it that way.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ewilson]

@Romy Gutbier,

Here's the thread where this issue was discussed relative to Outlook.​ 

To elaborate a bit more on my solution, the response JSON (the complete body contained in Response Content) was made up of an array of Message objects. It was a value (isReadReceiptRequested) within the object definition that was killing to deserialization. Instead of deserializing the entire object, I just pulled out the JSON that made up the individual Message objects and returned that as a Collection of strings to the calling BP process/VBO. That way the user could decide what they wanted to do with it. It was a compromise. I could have defined the entire Message object within the code, but being a pretty complex object model would have created a maintenance nightmare.

The action in question is called List Messages. Here's what the body definition looks like now:

And then in the Global Code I have this method defined:

// Pull out the enclosed JSON objects (ex. Message object) contained in the overall JSON response data
// and return those as a collection of strings.
public DataTable GetDataTableOfValues(string jsonData, string columnName)
{
	DataTable data = new DataTable();
	DataColumn col = new DataColumn(columnName, typeof(string));
	data.Columns.Add(col);
	
	JObject o = JObject.Parse(jsonData);

	foreach (JObject val in o["value"])
	{
		data.Rows.Add(val.ToString());
	}	
	
	return data;
}
​

I'm sure you can adapt this to handle your situation with SharePoint.

Cheers,



------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------