SPGetListItemsJson

Function

$().SPServices.SPGetListItemsJson

Certification

Functionality

SPGetListItemsJson combines several SPServices capabilities into one powerful function. By calling GetListItemChangesSinceToken, parsing the list schema, and passing the resulting mapping and data to SPXmlToJson automagically, we have a one-stop shop for retrieving SharePoint list data in JSON format. No manual mapping required!

Prerequisites

None

Syntax

$().SPServices.SPGetListItemsJson({
  webURL: "",
  listName: "",
  viewName: "",
  CAMLQuery: "",
  CAMLViewFields: "",
  CAMLRowLimit: "",
  CAMLQueryOptions: "",
  changeToken: "",
  contains: "",
  mapping: null,
  mappingOverrides: null,
  debug: false
});

webURL

The URL of the Web (site) which contains the list. If not specified, the current site is used. Examples would be: "/", "/Accounting", "/Departments/HR", etc. Note: It's always best to use relative URLs.

listName

The name or GUID of the list which contains the parent/child relationships. If you choose to use the GUID, it should look like: "{E73FEA09-CF8F-4B30-88C7-6FA996EE1706}". Note also that if you use the GUID, you do not need to specify the webURL if the list is in another site.

CAMLQuery

The CAMLQuery option allows you to specify the filter on the list. CAMLQuery here should contain valid CAML such as:

CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq></Where></Query>"

See the MSDN documentation for GetListItemsChangesSinceToken for the syntax.

CAMLViewFields

If specified, only the columns in CAMLViewFields plus some other required columns are retrieved. This can be very important if your list has a lot of columns, as it can reduce the amount of data returned from the call. See the MSDN documentation for GetListItemsChangesSinceToken for the syntax.

CAMLRowLimit

This option can be used to limit the number of items retrieved from the list. See the MSDN documentation for GetListItemsChangesSinceToken for the syntax.

CAMLQueryOptions

This option can be used to specify additional options for retrieval from the list. See the MSDN documentation for GetListItemsChangesSinceToken for the syntax.

changeToken

GetListItemChangesSinceToken passes back a changeToken on each call. If you are making calls after the initial one and pass in the changeToken value, only the changes since that token will be retrieved. See the MSDN documentation for GetListItemsChangesSinceToken for the syntax.

contains

This option allows you to pass in an additional filter for the request. It should be a valid CAML clause. See the MSDN documentation for GetListItemsChangesSinceToken for the syntax.

mapping

If you have created your own mapping, as specified in SPXmltoJson, pass it as this option. If present, the function will use your mapping and ignore the list schema returned by GetListItemChangesSinceToken.

mappingOverrides

If you want the function to use the list schema returned by GetListItemChangesSinceToken for the majority of the columns but you would like to specify your own mapping for some of the columns, pass those mappings in using the mappingOverrides option.

As an example, this mappingOverride would only change the way the two specified columns are converted by the SPXmlToJson function internally in the call (the JSON objectType is not available from the list schema):

mappingOverrides: {
  ows_FiscalPeriodData: {
    mappedName: "FiscalPeriodData",
    objectType: "JSON"
  },
  ows_FiscalPeriodNames: {
    mappedName: "FiscalPeriodNames",
    objectType: "JSON"
  }
}

debug

Setting debug: true indicates that you would like to receive messages if anything obvious is wrong with the function call, like using a column name which doesn't exist. I call this debug mode.

Returns

The function returns a JavaScript object like so:

{
  changeToken: "",
  mapping: {},
  data: [],
  deletedIds: []
}

changeToken

The changeToken as returned by GetListItemChangesSinceToken. This token can be passed to subsequent calls to the function. The various parts of the changeToken have specific meaning, but you should treat it as an immutable string.

mapping

The mapping used to parse the data into JSON. This mapping will include any specific overrides you specified as well as the automatically created mappings. You can pass this mapping into the function on subsequent calls to reduce overhead, though the function saves the mapping in a local data store for reuse.

data

The main reason we make the call, the data property is an object containing all of the retrieved data in JSON format, as specified in SPXmlToJson.

deletedIds

If this is call 2-n to the function, deletedIds will contain an array of IDs for list items which have been deleted since the prior call.

Example

In this example, I’m reading items from a list containing information about trainees in courses. Two of the columns – Scores and Attendance – are Multiple lines of text columns which contain JSON in text format.

var traineePromise = $().SPServices.SPGetListItemsJson({
    listName: "Trainees",
    CAMLQuery: "<Query><Where><Eq><FieldRef Name='accountName' LookupId='TRUE'/><Value Type='Integer'>" + opt.traineeId + "</Value></Eq></Where></Query>",
    changeToken: opt.changeToken,
    mappingOverrides: {
        ows_Scores: {
            mappedName: "Scores",
            objectType: "JSON"
        },
        ows_Attendance: {
            mappedName: "Attendance",
            objectType: "JSON"
        }
    }
});

$.when(traineePromise).done(function() {

    thisTraineeUser = this.data;

});