Get the content of the list based on different criteria (by default the default view is used)
Parameters:
Object
setup Optional
Options (see below)
String
setup.fields Optional, Default: ""
The fields you want to grab (be sure to add "Attachments" as a field if you want to know the direct link to an attachment)
String
setup.view Optional, Default: ""
If you specify a viewID or a viewName that exists for that list, then the fields/where/order settings for this view will be used in addition to the FIELDS/WHERE/ORDERBY you have defined (the user settings will be used first)
String|Array
setup.where Optional, Default: ""
The query string (like SQL syntax) (you'll need to use double \\ before the inside ' -- see example below); you can use an array that will make the sequential requests but it will return all the data into one array (useful for the Sharepoint 2010 throttling limit)
Boolean
setup.whereCAML Optional, Default: false
If you want to pass a WHERE clause that is with CAML Syntax only instead of SQL-like syntax -- see $SP().parse() for more info
Boolean
setup.whereEscapeChar Optional, Default: true
Determines if we want to escape the special chars that will cause an error (for example '&' will be automatically converted to '&') -- this is applied to the WHERE clause only
Function
setup.whereFct Optional, Default: function(w){return w}
Permits to apply your own function on the WHERE clause after conversion to CAML (can be useful also when you use the "view" parameter)
Function
setup.progress Optional
When using an array for the WHERE or the PAGING option then you can call the progress function (see the example)
String
setup.orderby Optional, Default: ""
The field used to sort the list result (you can also add "ASC" -default- or "DESC")
String
setup.groupby Optional, Default: ""
The field used to group by the list result
Integer
setup.rowlimit Optional, Default: 0
You can define the number of rows you want to receive back (0 is infinite)
Boolean
setup.paging Optional, Default: false
If you have defined the 'rowlimit' then you can use 'paging' to cut by packets your full request -- this is useful when there is a list view threshold (attention: we cannot use "WHERE" or "ORDERBY" with this option)
Integer
setup.page Optional, Default: infinite
When you use the `paging` option, several requests will be done until we get all the data, but using the `page` option you can define the number of requests/pages you want to get
String
setup.listItemCollectionPositionNext Optional, Default: ""
When doing paging, this is the index used by Sharepoint to get the next page
Boolean
setup.useIndexForOrderBy Optional, Default: false
Based on https://spservices.codeplex.com/discussions/280642#post1323410 it permits to override the 5,000 items limit in an unique call -- see the example below to know how to use it
Boolean
setup.expandUserField Optional, Default: false
When you get a user field, you can have more information (like name,email,sip,...) by switching this to TRUE
Boolean
setup.dateInUTC Optional, Default: false
TRUE to return dates in Coordinated Universal Time (UTC) format. FALSE to return dates in ISO format.
Object
setup.folderOptions Optional
Permits to read the content of a Document Library (see below)
String
setup.folderOptions.path Optional, Default: ""
Relative path of the folders we want to explore (by default it's the root of the document library)
String
setup.folderOptions.show Optional, Default: "FilesAndFolders_InFolder"
Four values: "FilesOnly_Recursive" that lists all the files recursively from the provided path (and its children); "FilesAndFolders_Recursive" that lists all the files and folders recursively from the provided path (and its children); "FilesOnly_InFolder" that lists all the files from the provided path; "FilesAndFolders_InFolder" that lists all the files and folders from the provided path
Boolean
setup.queryOptions Optional, Default: undefined
If you want to provide your own QueryOptions and overwrite the ones built for you -- it should be some XML code (see http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems.aspx)
Object
setup.join Optional
Permits to create a JOIN closure between the current list and another one: it will be the same syntax than a regular GET (see the example below) (it doesn't use yet the JOIN options provided with Sharepoint 2010)
String
setup.join.list Optional
Permits to establish the link between two lists (see the example below)
String
setup.join.url Optional, Default: 'current website'
The website url (if different than the current website)
String
setup.join.on Optional
Permits to establish the link between two lists (only between the direct parent list and its child, not with the grand parent) (see the example below)
String
setup.join.onLookup Optional
Permits to establish the link between two lists based on a lookup field... it's more optimized than the simple `join.on` (see the example below)
Boolean
setup.join.outer Optional, Default: false
If you want to do an outer join (you can also directly use "outerjoin" instead of "join")
Boolean
setup.calendar Optional, Default: false
If you want to get the events from a Calendar List
Object
setup.calendarOptions Optional
Options that will be used when "calendar:true" (see the example to know how to use it)
Boolean
setup.calendarOptions.splitRecurrence Optional, Default: true
By default we split the events with a recurrence (so 1 item = 1 day of the recurrence)
String|Date
setup.calendarOptions.referenceDate Optional, Default: today
This is the date used to retrieve the events -- that can be a JS Date object or a SP Date (String)
String
setup.calendarOptions.range Optional, Default: "Month"
By default we have all the events in the reference month (based on the referenceDate), but we can restrict it to a week with "Week" (from Monday to Sunday) (see https://www.nothingbutsharepoint.com/sites/eusp/Pages/Use-SPServices-to-Get-Recurring-Events-as-Distinct-Items.aspx)
Function
result Optional, Default: function(data,error)
A function with the data from the request as first argument, and the second argument is the error message in case something went wrong
Example:
$SP().list("List Name").get(function(data) {
for (var i=0; i<data.length; i++) console.log(data[i].getAttribute("Title"));
});
// with some fields and an orderby command
$SP().list("ListName","http://www.mysharepoint.com/mydir/").get({
fields:"Title,Organization",
orderby:"Title DESC,Test_x0020_Date ASC"
}, function getData(data) {
for (var i=0; i<data.length; i++) console.log(data[i].getAttribute("Title"));
});
// handle the errors
$SP().list("List Name").get(function(data,error) {
if (error) { alert(error) }
for (var i=0; i<data.length; i++) console.log(data[i].getAttribute("Title"));
});
// the WHERE clause must be SQL-like
// the field names must be the internal names used by Sharepoint
// ATTENTION - note that here we open the WHERE string with simple quotes (') and that should be your default behavior each time you use WHERE
var name = "O'Sullivan, James";
$SP().list("My List").get({
fields:"Title",
where:'Fiscal_x0020_Week > 30 AND Fiscal_x0020_Week < 50 AND Name = "'+name+'"'
}),function getData(row) {
for (var i=row.length;i--;) console.log(row[i].getAttribute("Title"));
});
// Same example but this time we write the name directly inside the query...
// So make sure to use a single backslash (\) if you have a simple quote ' inside your WHERE with a double quotes (") to open/close the string
$SP().list("My List").get({
fields:"Title",
where:'Fiscal_x0020_Week > 30 AND Fiscal_x0020_Week < 50 AND Name = "O\'Sullivan, James"'
}),function getData(row) {
for (var i=row.length;i--;) console.log(row[i].getAttribute("Title"));
});
// Or to use a double backslash (\\) if you have a simple quote ' inside your WHERE with a simple quote (') to open/close the string
$SP().list("My List").get({
fields:"Title",
where:"Fiscal_x0020_Week > 30 AND Fiscal_x0020_Week < 50 AND Name = 'O\\'Sullivan, James'"
}),function getData(row) {
for (var i=row.length;i--;) console.log(row[i].getAttribute("Title"));
});
// also in the WHERE clause you can use '[Me]' to filter by the current user,
$SP().list("My List").get({
fields:"Title",
where:"Author = '[Me]'"
},function getData(row) {
console.log(row[0].getAttribute("Title"));
});
// also in the WHERE clause you can use '[Today]' or '[Today-X]' with 'X' a number,
// Here it will return the records done yesterday
$SP().list("My List").get({
fields:"Title",
where:"Created = '[Today-1]'"
},function getData(row) {
console.log(row[0].getAttribute("Title"));
});
// Since 3.0.8, if you do a WHERE on a Date with the Time included, then it will compare with the tim
// see http://blogs.syrinx.com/blogs/sharepoint/archive/2008/08/05/caml-queries-with-dates.aspx
// here it will only show the items created at 2PM exactly -- if you want to check only the today, then use "Created = '2014-03-12'"
$SP().list("My List").get({
fields:"Title",
where:"Created = '2014-03-12 14:00:00'"
},function getData(row) {
console.log(row[0].getAttribute("Title"));
});
// We have a list called "My List" with a view already set that is called "Marketing View" with some FIELDS and a WHERE clause
// so the function will grab the view information and will get the data from the list with "Author = '[Me]'" and adding the view's WHERE clause too
$SP().list("My List","http://my.sharepoint.com/my/site/").get({
view:"Marketing View",
where:"Author = '[Me]'"
}, function(data) {
for (var i=data.length; i--;) console.log(data[i].getAttribute("Title") + " by " + data[i].getAttribute("Author"));
});
// use the paging option for the large list to avoid the message "the attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator"
// ATTENTION: if you use the WHERE option then it could return the "view threshold" error message because the packet from the WHERE is too big and SharePoint is too stupid...
$SP().list("My List").get({
fields:"ID,Title",
rowlimit:5000,
paging:true,
progress:function progress(nbItemsLoaded) {
// for each new page this function will be called
console.log("It's still loading... already "+nbItemsLoaded+" items have been loaded!");
}
}, function(data) {
console.log(data.length); // -> 23587
})
// add the `page` option to stop after a number of requests/pages
// for example you only want the last record from a list that has more than 5,000 items
$SP().list("My List").get({fields:"ID",orderby:"ID DESC",rowlimit:1,paging:true,page:1}, function(data) {
console.log("last ID : "+data[0].getAttribute("ID"));
})
// use `listItemCollectionPositionNext` to start from this index
$SP().list("My List").get({fields:"ID",orderby:"ID DESC",rowlimit:10,paging:true,page:1}, function(data, nextPageIndex) {
// get the next block
this.get{fields:"ID",orderby:"ID DESC",rowlimit:10,paging:true,page:1,listItemCollectionPositionNext:nextPageIndex}, function(data, nextPageIndex) {
// here we have the 2nd block of data into `data`
})
})
// We can also find the files from a Document Shared Library
$SP().list("Shared Documents","http://my.share.point.com/my_site/").get({
fields:"FileLeafRef,File_x0020_Size",
}, function getData(data) {
for (var i=0; i<<data.length; i++) console.log("FileName:"+data[i].getAttribute("FileLeafRef"),"FileSize:"+data[i].getAttribute("File_x0020_Size"));
});
// We can join two or more lists together based on a condition
// ATTENTION: in that case the DATA passed to the callback will return a value for "LIST NAME.FIELD_x0020_NAME" and not directly "FIELD_x0020_NAME"
// ATTENTION: you need to make sure to call the 'fields' that will be used in the 'on' clause
$SP().list("Finance and Expense","http://my.sharepoint.com/my_sub/dir/").get({
fields:"Expense_x0020_Type",
where:"Finance_x0020_Category = 'Supplies'",
join:{
list:"Purchasing List",
fields:"Region,Year,Expense_x0020_Type,Finance_x0020_Category,Cost",
where:"Region = 'EMEA' AND Year = 2012",
orderby:"Expense_x0020_Type,Finance_x0020_Category",
on:"'Purchasing List'.Expense_x0020_Type = 'Finance and Expense'.Expense_x0020_Type",
join:{
list:"Financial Static Data",
fields:"Region,Year,Expense_x0020_Type,Finance_x0020_Category,Forecast",
where:"Region = 'EMEA' AND Year = 2012",
on:"'Purchasing List'.Region = 'Financial Static Data'.Region AND 'Purchasing List'.Expense_x0020_Type = 'Financial Static Data'.Expense_x0020_Type"
}
}
},function getData(data) {
for (var i=0; i<data.length; i++)
console.log(data[i].getAttribute("Purchasing List.Region")+" | "+data[i].getAttribute("Purchasing List.Year")+" | "+data[i].getAttribute("Purchasing List.Expense_x0020_Type")+" | "+data[i].getAttribute("Purchasing List.Cost"));
});
// By default "join" is an "inner join", but you can also do an "outerjoin"
// ATTENTION: in that case the DATA passed to the callback will return a value for "LIST NAME.FIELD_x0020_NAME" and not directly "FIELD_x0020_NAME"
// ATTENTION: you need to make sure to call the 'fields' that will be used in the 'on' clause
$SP().list("Finance and Expense","http://my.sharepoint.com/my_sub/dir/").get({
fields:"Expense_x0020_Type",
where:"Finance_x0020_Category = 'Supplies'",
join:{
list:"Purchasing List",
fields:"Region,Year,Expense_x0020_Type,Finance_x0020_Category,Cost",
where:"Region = 'EMEA' AND Year = 2012",
orderby:"Expense_x0020_Type,Finance_x0020_Category",
on:"'Purchasing List'.Expense_x0020_Type = 'Finance and Expense'.Expense_x0020_Type",
outerjoin:{
list:"Financial Static Data",
fields:"Region,Year,Expense_x0020_Type,Finance_x0020_Category,Forecast",
where:"Region = 'EMEA' AND Year = 2012",
on:"'Purchasing List'.Region = 'Financial Static Data'.Region AND 'Purchasing List'.Expense_x0020_Type = 'Financial Static Data'.Expense_x0020_Type"
}
}
},function getData(data) {
for (var i=0; i<data.length; i++)
console.log(data[i].getAttribute("Purchasing List.Region")+" | "+data[i].getAttribute("Purchasing List.Year")+" | "+data[i].getAttribute("Purchasing List.Expense_x0020_Type")+" | "+data[i].getAttribute("Purchasing List.Cost"));
})
// Another example of "outerjoin", but this time with fields tied to a Lookup ID
// Here 1 Project can have several Deliverables based on field "Project ID", and 1 Deliverable can have several team members based on "Deliverable ID"
$SP().list("Projects").get({
fields:"ID,Project_x0020_Name",
where:"Status = 'In Progress'",
outerjoin:{
list:"Deliverables",
fields:"ID,Name",
onLookup:"Project_x0020_ID",
outerjoin:{
list:"Team Members",
fields:"ID,Deliverable_x0020_ID,Name",
onLookup:"Deliverable_x0020_ID"
}
}
}, function(data) {
var html = '<table class="table default"><thead><tr><th>Project ID</th><th>Project Name</th><th>Deliverable ID</th><th>Deliverable Name</th><th>Team ID</th><th>Member Name</th></tr></thead><tbody>'
for (var i=0;i<data.length; i++) {
html += '<tr><td>'+data[i].getAttribute("Projects.ID")+'</td><td>'+data[i].getAttribute("Projects.Project_x0020_Name")+'</td><td>'+data[i].getAttribute("Deliverables.ID")+'</td><td>'+data[i].getAttribute("Deliverables.Name")+'</td><td>'+data[i].getAttribute("Team Members.ID")+'</td><td>'+data[i].getAttribute("Team Members.Name")+'</td></tr>'
}
html += '</tbody></table>';
$('#part1').html(html);
})
// With Sharepoint 2010 we are limited due to the throttling limit (see here for some very interesting information http://www.glynblogs.com/2011/03/sharepoint-2010-list-view-throttling-and-custom-caml-queries.html)
// So for example if I do WHERE:'Fiscal_x0020_Year = 2012' it will return an error because I have 6,500 items
// So I'll do several requests for each Fiscal_x0020_Week into this Fiscal Year
var query=[],q=[];
for (var i=1; i<54; i++) {
q.push("Fiscal_x0020_Week = "+i);
if (i%8==0 || i == 53) {
query.push("("+q.join(" OR ")+") AND Fiscal_x0020_Year = 2012");
q=[]
}
}
// it returns :
// [
// "(Fiscal_x0020_Week = 1 OR Fiscal_x0020_Week = 2 OR Fiscal_x0020_Week = 3 OR Fiscal_x0020_Week = 4 OR Fiscal_x0020_Week = 5 OR Fiscal_x0020_Week = 6 OR Fiscal_x0020_Week = 7 OR Fiscal_x0020_Week = 8) AND Fiscal_x0020_Year = 2012",
// ...
// "(Fiscal_x0020_Week = 49 OR Fiscal_x0020_Week = 50 OR Fiscal_x0020_Week = 51 OR Fiscal_x0020_Week = 52 OR Fiscal_x0020_Week = 53) AND Fiscal_x0020_Year = 2012"
// ]
$SP().list("Sessions").get({
fields:"Title,Score",
where:query,
progress:function progress(current,max) {
// when we use an array for the WHERE clause we are able to provide `current` and `max`
console.log("Progress: "+current+" / "+max);
}
},function getData(data) {
console.log(data.length); // -> 6,523
});
// also regarding the throttling limit, you can query a list on a user column in using the User ID
// For example if John Doe is recorded as "328;#Doe, John" then you'll have to use the special operator "~="
$SP().list("Sessions").get({
fields:"Title",
where:'User ~= 328"
},function getData(data) {
console.log(data.length);
});
// if you want to list only the files visible into a folder for a Document Library
$SP().list("My Shared Documents").get({
fields:"BaseName,FileRef,FSObjType", // "BaseName" is the name of the file/folder; "FileRef" is the full path of the file/folder; "FSObjType" is 0 for a file and 1 for a folder (you need to apply $SP().cleanResult())
folderOptions:{
path:"My Folder/Sub Folder/",
show:"FilesOnly_InFolder"
}
});
// if you want to list all the files and folders for a Document Library
$SP().list("My Shared Documents").get({
fields:"BaseName,FileRef,FSObjType", // "BaseName" is the name of the file/folder; "FileRef" is the full path of the file/folder; "FSObjType" is 0 for a file and 1 for a folder (you need to apply $SP().cleanResult())
folderOptions:{
show:"FilesAndFolders_Recursive"
}
});
// How to retrieve the events from a Calendar List
// NOTE -- when "calendar:true" we automatically get some fields: "Title", "EventDate" -- the Start Date --, "EndDate", "RecurrenceData", Duration", fAllDayEvent", "fRecurrence", "ID"
$SP().list("My Calendar").get({
fields:"Description",
calendar:true,
calendarOptions:{
referenceDate:new Date(2012,4,4),
range: "Week"
}
where:"Category = 'Yellow'"
}, function(data) {
var events=[];
for (var i=0; i<data.length; i++) {
// several information are available -- see below
events.push({
Title: data[i].getAttribute("Title"),
StartDateTime: data[i].getAttribute("EventDate"), // you can use $SP().toDate() to have a JS Date
EndDateTime: data[i].getAttribute("EndDate"), // you can use $SP().toDate() to have a JS Date
Recurrence: (data[i].getAttribute("fRecurrence") == 1 ? true : false),
AllDayEvent: (data[i].getAttribute("fAllDayEvent") == 1 ? true : false),
RecurrenceEnd: (data[i].getAttribute("RecurrenceData")||"").replace(/.+([^<]+)<\/windowEnd>.+/,"$1"), // see the NOTE below
ID:data[i].getAttribute("ID"), // the ID for the recurrence events is special but can be also passed to "Display.aspx?ID="
Duration:1*data[i].getAttribute("Duration") // Duration is in SECONDS
})
// NOTE: with data[i].getAttribute("RecurrenceData") you'll find more info about the recurrence (like the end date for the serie, and much more),
// but because there are a lot of scenario, I won't handle the different cases.
// e.g. for a daily recurrence you can find the end date of the serie with: data[i].getAttribute("RecurrenceData").replace(/.+([^<]+)<\/windowEnd>.+/,"$1")
// --> it will return a SP Date
}
})
// [It doesn't work with Sharepoint 2013 anymore, only for SP2010]
// You can use `useIndexForOrderBy:true` to override the list view threshold -- see https://spservices.codeplex.com/discussions/280642
// To make it to work, you need :
// 1) to have "ID > 0 AND Another_Index_Column_Filtered" in the WHERE Clause (so at least two filters), and then we can add some other WHERE (even the not indexed columns)
// 2) To use `orderby`, with an indexed column
// 3) To use `useIndexForOrderBy:true`
// see the below example with Trainer an indexed column, and Equipment a column not indexed
// NOTE: you need to test your WHERE to see if it works or not, because it's vary a lot depending of the kind of WHERE clause you'll use
$SP().list("Calendar",'http://intranet.dell.com/services/Educate/Toolbox/scheduling_tool/').get({
fields:"Trainer",
where:'ID > 0 AND Trainer <> "" AND Equipment LIKE "Box"',
orderby:'Trainer',
useIndexForOrderBy:true
}, function(d) {
console.log(d.length)
})