This is a new app project that came when was working
with SharePoint Online (Office 365),
I had a Library with folders with more then 5000
documents and wasn't able to access the View, well... nothing new, only needed to
make a query and move the documents, i used the same approach in SharePoint
2010 ECMAScript and done, batch 1000 by 1000 to another folder.
Then i think to myself how a normal user or
"Admin" of a site (can IT or Business) will manage this?
I get surprise how people get stuck with this
type of situations, even when they are instructed to avoid that, this happen
very often.
Objective:
My goal was to create a simple tool using JSOM
to migrate documents from folder with more then 5000 Documents and avoid the
error message.
Just to mitigate when the problem is already there and then start to resolve it....
Just to mitigate when the problem is already there and then start to resolve it....
This solution audits folders, in a Document
Library with a defined number of documents (e.g >5000 Documents).
This Folder Url will be use to get the ID of the
Documents and migrate to other/New Folder in the same Document Library and
avoid the following error.
No, This is not another article talking about
configuring the List Thresholds in Web application and Server side code,
"SharePoint Online don't allow to change this values or make
Server Side code".
If you are interested in that area
please read this articles about that.
Here some articles talking about configuration
and working with List Thresholds:
Manage List Threshold Files App Solution
This solution was made using the ECMAScript and
JSOM to track and make bulk migration of Documents.
This Solution can be implemented in the
following tested environments:
- Office
365
- SharePoint
2013 Standard/Enterprise
App Manifest:
- Hosting-Type: SharePoint-Hosted
- Scope: Web
- Permissions: Write
Tools:
- Visual Studio 2013
- SharePoint App
- Fiddler
- Internet Explorer (Developer Tools)
- You can use "Napa" Office 365 Development Tools to
Who should Test:
- Developers in their Test Environment with Know-How of SharePoint Lists and SharePoint JSOM.
This is a example made with JSOM and SharePoint Online.
I don't recommend publish in production. If you made, will be by your own risk.
Functionality of the Solution
Audit Folders:
- This
section give the option to select the Document Library and make a auditory
to identify the number of items by Folder and Sub-folder.
- It
is possible filter the Search by the numbers of Document by Folder.
- By
default the value is 0, but you can change to 5000 to return folder with
more than 5000 Documents.
- The option "Copy url" will copy the serverRelativeUrl to "List Folder" field in "Get Items ID".
Get Items ID:
- This section gives the option to select a Folder and retrieve the ID's Files. This array of ID's will be use to make the bulk move.
- It is possible to filter a define number of ID's to be moved, (max. is 1000) because of specific REST Url Query.
- The ID's helps the bulk moving of Documents.
Move Items/Files:
- In this section it is possible to create Sub Folders in a Document Library
- Add a new Folder Path in the Destination Folder and
select the option "Create Folder"
·
Move Document to another Folder
o Select the folder where the documents should move. Copy paste the ID's from the Tab "Get Items ID" and paste in Tab "Move Items/Files"
o
Select the option "Move Items/Files" and wait until the process is finish.
- Add a new Folder Path in the Destination Folder and select the option "Create Folder"
Boring
Part:
- You can only move 1000 documents each time and that takes time...
Reason and Tests
associated:
If you Query a List with more then 5000 items, the list view threshold of SharePoint will block and provide a error Message.
This issue was very well manage in this App using the SharePoint 2010 REST call "_vti_bin/ListData.svc" to the Res(T)cue.
But made some tests using the CAML and REST (the new _api from 2013) using JSOM to get other possible solutions, but the results weren't good...
Here are the results.
CAML
CAML with Filter:
This CAML Query will return the threshold error because the Document Library has more then 5000 item, value defined by default in the Web Application threshold option "even when i call only 1000 items".
Example:
camlQuery.set_viewXml('<View
Scope=\'RecursiveAll\'><Query><Where><And><Eq><FieldRef
Name=\'FSObjType\' /><Value
Type=\'int\'>0</Value></Eq><Eq><FieldRef
Name=\'FileDirRef\' /><Value Type=\'Lookup\'>'/sites/Dev/Document/Example'</Value></Eq></And></Where></Query><ViewFields><FieldRef
Name=\'ID\' /><FieldRef Name=\'FileDirRef\' /></ViewFields>
<RowLimit>1000</RowLimit></View>');
Error message:
"The
attempted operation is prohibited because it exceeds the list view threshold
enforced by the administrator."
CAML noFilter:
This CAML Query return the first 5000 items but it is not possible make filters.
This is not good because if you have different folders, you aren't sure about the values that are fetched.
If you try to make any type of filter will return the threshold error.
Example work for 5000:
camlQuery.set_viewXml('<View Scope=\'RecursiveAll\'><Query><Where></Where></Query><RowLimit>5000</RowLimit></View>');
Error:
If you try to include a folder url will be consider as filter and then returna "threshold" error.
camlQuery.set_folderServerRelativeUrl('/sites/Dev/Document/Example');
Output Image:
REST
After testing the CAML Query, the same Test's were made using REST url's and you will see the same behavior in this methods
REST by Folder
"/sites/Dev/_api/SP.AppContextSite(@target)/web/getFolderByServerRelativeUrl('/sites/Dev/Document/Example')/Files?@target='hostweburl'
Output Image:
REST with Filter
/sites/Dev/_api/SP.AppContextSite(@target)/web/lists/getbytitle('Document')/Items?$top=5000&$filter=(FileDirRef
eq '/sites/Dev/Document/Example')&@target='hostweburl'
Output Image:
REST All Items
This
REST URL Query return 5000 Items from a Document Library but with no filter associated.
/sites/Dev/_api/SP.AppContextSite(@target)/web/lists/getbytitle('Document')/Items?$top=5000&@target='hostweburl'
Output Image:
REST ListData.svc
After this Tests the only one that was able to make query in Folder with more then 5000 items was the _vti_bin/ListData.svc/(documentLibrary) and (query associated).
After this the app was able to call the Document Library Data in a specific Filter.
For this case was use the following filter:
$filter=((Path eq '[Url to Folder]') and (ContentType ne 'Folder'))
//return the Folder URL
Move Documents Method
Get Folders Method
Here is the Link for the Solution.
To resolve the threshold issue you can index the Columns in the List and use the
http://blogs.msdn.com/b/sowmyancs/archive/2013/07/31/sharepoint-list-throttling-amp-indexed-columns.aspx
In the CAML Query you can use the normal Query but take in consideration the Orderby option with the parameter "UseIndexForOrderBy"
OrderBy Element
https://msdn.microsoft.com/en-us/library/office/ms467378.aspx
I hope you like this new article.
Output Image:
REST ListData.svc
After this Tests the only one that was able to make query in Folder with more then 5000 items was the _vti_bin/ListData.svc/(documentLibrary) and (query associated).
After this the app was able to call the Document Library Data in a specific Filter.
For this case was use the following filter:
$filter=((Path eq '[Url to Folder]') and (ContentType ne 'Folder'))
ECMAScript Methods:
Get the ID's of a Folder with more then 5000 Documents
The Max REST call is 1000 items)
Get ID of Documents in Document Library Method
executor.executeAsync(
{
url:
appweburl + "/../_vti_bin/ListData.svc/" + Document + "?$top="+item+"&$select=Id&$filter=((Path
eq '" + FolderFilter + "') and (ContentType ne 'Folder'))"
,
method: "GET",
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
var jsonObject = JSON.parse(data.body);
var results = jsonObject.d;
if (results.length > 0) {
onDataReturned(results);
}
else {
CloseWaitForm();
alert("No result!!");
}
},
error: function (xhr) {
CloseWaitForm();
alert(xhr.status + ': ' + xhr.statusText);
}
}
Open Folder Tree view
function LaunchTargetPicker(TextBoxId) {
var callback = function (dest) {
if (dest != null && dest != undefined && dest[3] != null) {
$("#" + TextBoxId).val($("#IdLists
option:selected").val().split(";")[0] +
dest[3]);
}
};
var iconUrl = "/_layouts/15/images/smt_icon.gif?rev=32";
SP.SOD.executeFunc('pickertreedialog.js', 'LaunchPickerTreeDialogSelectUrl', function () {
//Open the Treview in the Document Library URL
LaunchPickerTreeDialogSelectUrl('CbqPickerSelectListTitle', 'CbqPickerSelectFolderText', 'websListsFolders', $("#IdLists option:selected").val().split(";")[1], appweburl + "/../", $("#FolderFilter").val(), '', '', iconUrl, '', callback, 'true', '');
});
}
Move Documents Method
function MoveItems() {
Items = $('#CopyItemID').val().split(";");
CountMov = 0;
context = new SP.ClientContext(appweburl);
var factory = new SP.ProxyWebRequestExecutorFactory(appweburl);
context.set_webRequestExecutorFactory(factory);
app = new SP.AppContextSite(context, hostweburl);
var site = app.get_web();
var list = site.get_lists().getByTitle($("#IdLists option:selected").text());
currentItem =
list.getItemById(Items[CountMov]);
file = currentItem.get_file();
context.load(currentItem, 'File.Name');
context.executeQueryAsync(MoveFileHandler,
onQueryFailed);
}
function MoveFileHandler() {
try {
if (file != null) {
var _destinationlibUrl;
if ($('#DestinationFolder').val().slice(-1) == "/")
{ _destinationlibUrl = $('#DestinationFolder').val()
+ file.get_name(); }
else {
_destinationlibUrl = $('#DestinationFolder').val()
+"/"+ file.get_name();
}
file.moveTo(_destinationlibUrl,
SP.MoveOperations.allowBrokenThickets);
context.executeQueryAsync(MoveSuccess, onQueryFailed);
}
} catch (e) {
alert(e.message);
}
}
Get Folders Method
function GetFolders() {
$('#Audit').html('');
context = new SP.ClientContext(appweburl);
var factory = new SP.ProxyWebRequestExecutorFactory(appweburl);
context.set_webRequestExecutorFactory(factory);
app = new SP.AppContextSite(context, hostweburl);
var site = app.get_web();
var list = site.get_lists().getByTitle($("#IdLists option:selected").text());
folderCollection = list.get_rootFolder();
context.load(folderCollection);
context.executeQueryAsync(successHandler,
onQueryFailed);
}
function successHandler(sender, args) {
recursiveMethod(folderCollection);
}
The call the folder in a Recursive way. (back to school...)
I recommend to call the Folders in a recursive
way, this avoid possible issues with the limit of the List View.
The recursive Method for folders with more than 5000 Documents was the only one that worked for me, even with this, it is not possible to get sub Folders from a Folder with more then 5000 Document using ECMAScript of Client Object Model.
Here is the Link for the Solution.
To resolve the threshold issue you can index the Columns in the List and use the
http://blogs.msdn.com/b/sowmyancs/archive/2013/07/31/sharepoint-list-throttling-amp-indexed-columns.aspx
In the CAML Query you can use the normal Query but take in consideration the Orderby option with the parameter "UseIndexForOrderBy"
OrderBy Element
https://msdn.microsoft.com/en-us/library/office/ms467378.aspx
I hope you like this new article.
Support
Links:
SharePoint
2013 Boundaries
Working
with List View Thresholds in SharePoint 2013
