As I have explained in one of my earlier post that, there are 2 REST APIs to retrieve items from SharePoint: –

• Items – GET request and,
• GetItems – POST request. CAML query in the body.

In this post, I’ll be discussing about the latter.

Premise

For the purpose of this post, I’ll be using the following columns: –

• Predecessors – Lookup
• AssignedTo – Person or Group
• DueDate – Date
• Blog State – Managed Metadata

I was unable to retrieve the following data properly using the CAML in REST: –

• Lookup
• Person or Group

CAML

Before getting into the actual solution, let’s elaborate a bit on the issue. I used the following select request in my CAML. Since, I was leveraging the CAML query, I choose not to use the $select operand.

executor.executeAsync({
	url: appweburl + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'guid')/getitems?@target='" + hostWebUrl + "'",
	method: "POST",
	scope: this,
	body: "{ 'query' : {'__metadata': { 'type': 'SP.CamlQuery' }, 'ViewXml': '<View><Query/><ViewFields><FieldRef Name=\"DueDate\" /><FieldRef Name=\"AssignedTo\" /><FieldRef Name=\"Blog_x0020_State\" /><FieldRef Name=\"Predecessors\" /></ViewFields></View>' } }",
	headers: {
		"Accept": "application/json; odata=verbose",
		"content-type": "application/json; odata=verbose"
	},
	success: PK.prototype.successHandler,
	error: PK.prototype.errorHandler
});

However, in the response, I only received the id of the Lookup and Person columns

$expand

Not good enough! So next, I tried using the standard REST format with $expand.

url: appweburl + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'guid')/getitems?$select=AssignedTo/ID,AssignedTo/FirstName,AssignedTo/LastName,DueDate,Predecessors/Title,Blog_x0020_State/Term&$expand=AssignedTo,Predecessors&@target='" + hostWebUrl + "'"

However, something even more weird happened. I received,

Microsoft.SharePoint.Client.UnknownError! 😐

Before proceeding further, I must state this,

Unless you have an obligation to use getItems, I personally recommend using the items API for this case scenario.

Solution

The trick here is that,

instead of querying the fields directly, request the same via FieldValuesAsText

This is actually pretty interesting. We no longer have to worry about using $expand on certain, selective fields. We can create a generic request model where, we only need to use $expand on FieldValuesAsText. The format of the response will also be constant.

executor.executeAsync({
	url: appweburl + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'guid')/getitems?$select=FieldValuesAsText/AssignedTo,FieldValuesAsText/DueDate,FieldValuesAsText/Predecessors,FieldValuesAsText/Blog_x0020_State&$expand=FieldValuesAsText&@target='" + hostWebUrl + "'",
	method: "POST",
	scope: this,
	body: "{ 'query' : {'__metadata': { 'type': 'SP.CamlQuery' }, 'ViewXml': '' } }",
	headers: {
		"Accept": "application/json; odata=verbose",
		"content-type": "application/json; odata=verbose"
	},
	success: PK.prototype.successHandler,
	error: PK.prototype.errorHandler
});

Output

{
	"d": {
		"results": [{
				"__metadata": {
					"id": "c5f49e33-d456-45b9-8f5d-fe2155502837",
					"uri": "https://collabion-cdc8ba69ab4968.sharepoint.com/PkTeamSite-Sub2/Expand-GetItemsListItemData-Piyush/_api/Web/Lists(guid'9ded7e87-7bdd-4845-a849-e6c0a67ea635')/Items(3)",
					"etag": "\"2\"",
					"type": "SP.Data.TaskListItem"
				},
				"FieldValuesAsText": {
					"__metadata": {
						"id": "https://collabion-cdc8ba69ab4968.sharepoint.com/PkTeamSite-Sub2/Expand-GetItemsListItemData-Piyush/_api/Web/Lists(guid'9ded7e87-7bdd-4845-a849-e6c0a67ea635')/Items(3)/FieldValuesAsText",
						"uri": "https://collabion-cdc8ba69ab4968.sharepoint.com/PkTeamSite-Sub2/Expand-GetItemsListItemData-Piyush/_api/Web/Lists(guid'9ded7e87-7bdd-4845-a849-e6c0a67ea635')/Items(3)/FieldValuesAsText",
						"type": "SP.FieldStringValues"
					},
					"Predecessors": "Create first draft",
					"AssignedTo": "Piyush Singh",
					"DueDate": "8/2/2017",
					"Blog_x005f_x0020_x005f_State": "Review;Submit"
				}
			}
		]
	}
}

🙂

Key Takeaways

• This entire solution, as a SharePoint Add-In, is available on GitHub.
• The add-in, has been assigned the read permission at the SiteCollection level.
• There cannot be an empty body for this request. We need to provide at least an empty ViewXml in the body.
• $expand doesn’t work on fields directly, however, it works very nicely on FieldValuesAsText.
• This approach overcomes the issue of reading and parsing different data formats. For ex, for a Managed Metadata field, we need to read the Label property. For Person it’s title. For a lookup, it can be anything. But in here, you can read each value with just their field’s name.
• In order to read the Managed Metadata values, the add-in at least requires the Read permission on Taxonomy. However, the same is not required while reading the value from FieldValuesAsText.
• Since, FieldValuesAsText, is a text property, any HTML field queried through it will be returned as blank.
• The code solution, provided here, uses JavaScript class. Hence, it will not be working in IE. But, it’s just a design model. The solution, however, is browser independent.