Get all RoleAssignments from a document library even more than 5000 elements
only when HasUniqueRoleAssignments == true
let currentSort = { column: null, direction: 'asc' }; // Store the current sort state
// Creates the style element
function createStyleElement(id, content) {
var style = document.createElement("style");
style.type = "text/css";
style.id = id;
style.innerHTML = content;
if (style.styleSheet) {
style.styleSheet.cssText = content;
} else {
let st = document.getElementById(id);
if (st == undefined) {
var head = document.head || document.getElementsByTagName("head")[i];
head.appendChild(style);
} else {
st.innerHTML = content;
}
}
return style;
}
// Function to filter the table based on dropdown selection
function filterTable(columnIndex, value) {
let table, tr, td, i, select, selectedValue, txtValue;
table = document.querySelector("table");
tr = table.getElementsByTagName("tbody")[0].getElementsByTagName("tr");
select = table.getElementsByTagName("select")[columnIndex];
//debugger;
selectedValue = value;
// Loop through all table rows and hide those that don't match the filter
for (i = 0; i < tr.length; i++) {
td = tr[i].getElementsByTagName("td")[columnIndex];
if (td) {
txtValue = td.textContent || td.innerText;
if (selectedValue === "" || txtValue === selectedValue) {
tr[i].style.display = "";
} else {
tr[i].style.display = "none";
}
}
}
}
function sortTable(columnIndex, direction) {
let table, rows, switching, i, x, y, shouldSwitch;
table = document.querySelector("table");
switching = true;
let tbody = table.querySelector("tbody");
// Set the current sort state
currentSort.column = columnIndex;
currentSort.direction = direction;
while (switching) {
switching = false;
rows = tbody.rows;
for (i = 0; i < rows.length - 1; i++) {
shouldSwitch = false;
x = rows[i].getElementsByTagName("td")[columnIndex];
y = rows[i + 1].getElementsByTagName("td")[columnIndex];
let isNumber = false;
if (!isNaN(x.innerHTML)) {
// Check if rows should switch based on ascending or descending order
if (direction === 'asc') {
if (parseFloat(x.innerHTML) > parseFloat(y.innerHTML)) {
shouldSwitch = true;
break;
}
} else if (direction === 'desc') {
if (parseFloat(x.innerHTML) < parseFloat(y.innerHTML)) {
shouldSwitch = true;
break;
}
}
}
else {
// Check if rows should switch based on ascending or descending order
if (direction === 'asc') {
if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
shouldSwitch = true;
break;
}
} else if (direction === 'desc') {
if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
shouldSwitch = true;
break;
}
}
}
}
if (shouldSwitch) {
rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
switching = true;
}
}
}
// Function to generate the table
function generateTableFromJson2(jsonArray, select, addHeaders = true) {
const style = `
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 8px 12px;
text-align: left;
border: 1px solid #ddd;
}
tbody tr{
max-height: 15px;
}
th {
background-color: #f4f4f4;
color: #000;
}
/* Scrollable table wrapper */
.table-wrapper {
max-height: 800px;
overflow-y: auto;
border: 1px solid #ddd;
}
/* Style for dropdowns in header */
select {
width: 100%;
padding: 4px;
margin-top: 5px;
}
/* Style for the sorting arrows */
.sort-arrows {
cursor: pointer;
margin-left: 5px;
}
`;
createStyleElement("fdiStyle", style);
// Create table element
let table = document.createElement('table');
// Create table header
let header = table.createTHead();
let headerRow = header.insertRow(0);
// Get keys (headers) from the first object in the JSON array
//let keys = Object.keys(jsonArray[0]);
let keys = select.split(",");
if (addHeaders) {
keys.forEach((key, index) => {
if (key !== "__metadata") {
let th = document.createElement('th');
th.innerHTML = key;
// Create a dropdown (select) for filtering
let select = document.createElement('select');
select.addEventListener('change', function () {
const selectedValue = select.value;
filterTable(index, selectedValue);
});
// Populate dropdown with unique values from the JSON data
let uniqueValues = [...new Set(jsonArray.map(item => item[key]))];
// Add a default "All" option for no filter
let optionAll = document.createElement('option');
optionAll.value = "";
optionAll.text = `All`;
select.appendChild(optionAll);
// Create an option for each unique value
if (typeof (uniqueValues[0]) === typeof (1)) {
const pp = uniqueValues.sort((a, b) => {
if (a < b) {
return -1;
}
if (a > b) {
return 1;
}
return 0;
});
pp.forEach(value => {
let option = document.createElement('option');
option.value = value;
option.text = value;
select.appendChild(option);
});
} else
uniqueValues.sort().forEach(value => {
let option = document.createElement('option');
option.value = value;
option.text = value;
select.appendChild(option);
});
// Sort arrows for sorting the columns
let upArrow = document.createElement('span');
upArrow.innerHTML = '⬆️';
upArrow.classList.add('sort-arrows');
upArrow.onclick = () => sortTable(index, 'asc');
let downArrow = document.createElement('span');
downArrow.innerHTML = '⬇️';
downArrow.classList.add('sort-arrows');
downArrow.onclick = () => sortTable(index, 'desc');
th.appendChild(select); // Append the dropdown to the header
th.appendChild(upArrow); // Append the dropdown to the header
th.appendChild(downArrow); // Append the dropdown to the header
headerRow.appendChild(th);
}
});
}
// Create table body and populate rows with data
let tbody = document.createElement('tbody');
jsonArray.forEach((item) => {
let row = tbody.insertRow();
keys = select.split(",");
keys.forEach((key) => {
let cell = row.insertCell();
if (key !== "__metadata") {
cell.setAttribute("nowrap", "nowrap");
if (key === "RoleDefinitionBindings") {
cell.appendChild(generateTableFromJson2(item.RoleDefinitionBindings.results, "Name,Id", false));
} else if (key.indexOf("/") > 0) {
cell.innerHTML = item[key.split("/")[0]][key.split("/")[1]]
} else
cell.innerHTML = item[key]; // Insert each value from the JSON into the table cell
}
});
});
// Append the body to the table
table.appendChild(tbody);
return table;
}
function removeSlasches(select, datas) {
const ret = [];
const fields = select.split(',');
for (let i = 0; i < datas.length; i++) {
const toAdd = {};
for (let j = 0; j < fields.length; j++) {
if (fields[j].indexOf('/') > 0) {
const splitted = fields[j].split('/');
toAdd[splitted.join('')] = datas[i][splitted[0]][splitted[1]];
} else
toAdd[fields[j]] = datas[i][fields[j]];
}
ret.push(toAdd);
}
console.log("removeSlasches", ret);
return ret;
}
async function GetPermissionsInFolder(siteUrl, listUrl1, query) {
// Fetch options with headers for authentication and response format
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
//get web relativeUrl
var req = `${siteUrl}/_api/web?$select=ServerRelativeUrl`;
const webServerRelativUrl = (await (await fetch(req, fetchOptions)).json()).d.ServerRelativeUrl;
let query1 = "";
if (`${query}`.trim() !== "") {
query1 = `&$filter=${query}`;
query = ` and ${query}`;
}
//get firstId
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id asc`;
console.log("req", req);
const firstId = 0;//(await (await fetch(req, fetchOptions)).json()).d.results[0].Id;
console.log("firstId", firstId);
//get lastId
//req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$filter=Id gt 170 and Id lt 533&$top=1&$orderby=Id desc`;
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id desc`;
console.log("last", req);
const lastId = (await (await fetch(req, fetchOptions)).json()).d.results[0].Id;
console.log("lastId", lastId);
let startId = firstId;
let endId = firstId + 5000;
var allItems = [];
console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
console.log("query", query);
do {
var select = "?$select=FileDirRef,RoleAssignments,HasUniqueRoleAssignments,Id,Title,FileLeafRef,Modified,Created";
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId}${query}&$orderby=Id asc&$top=5000`;
console.log("req", req);
// Send the asynchronous GET request to the REST API endpoint
var respList1 = await fetch(req, fetchOptions);
const items = (await respList1.json()).d.results;
//get only items with unique permissions
const withUniquePermissions = items.filter(user => user.HasUniqueRoleAssignments);
console.log("withUniquePermissions", withUniquePermissions.length);
allItems.push(...withUniquePermissions);
startId += 5000;
endId += 5000;
console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
}
while (endId < lastId);
return allItems;
}
async function batchFetchPermissions(siteUrl, itemIds, listUrl1) {
const batchBoundary = "batch_" + new Date().getTime();
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
const webServerRelativUrl = (await (await fetch(`${siteUrl}/_api/web?$select=ServerRelativeUrl`, fetchOptions)).json()).d.ServerRelativeUrl;
const batchBody = itemIds.map((item) => {
return `
--${batchBoundary}
Content-Type: application/http
Content-Transfer-Encoding: binary
GET ${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items(${item.Id})/RoleAssignments?$expand=Member,RoleDefinitionBindings HTTP/1.1
`;
}).join("\n") + `\n--${batchBoundary}--`;
const digest = await GetDigestValue(siteUrl);//
//console.log("digest", digest);
const headers = {
Accept: "application/json;odata=verbose",
"X-RequestDigest": digest,
"Content-Type": `multipart/mixed; boundary="${batchBoundary}"`,
};
console.log("itemIds", itemIds.length);
const response = await fetch(`${siteUrl}/_api/$batch`, {
method: "POST",
headers,
body: batchBody,
});
const text = await response.text(); // Parse response manually (multipart)
return parseBatchResponse(text, itemIds);
}
async function GetDigestValue(siteUrl) {//
const fetchOptions = {
method: 'POST',
headers: {
'Accept': 'application/json;odata=verbose',
'Content-type': 'application/json;odata=verbose'
}
};
const response = await fetch(siteUrl + "/_api/contextinfo", fetchOptions);
return (await response.json()).d.GetContextWebInformation.FormDigestValue;
}
function nodeParser2(xml) {
var parser = new DOMParser();
var doc = parser.parseFromString(xml, "application/xml");
// Define a namespace resolver
var nsResolver = function (prefix) {
var ns = {
'atom': 'http://www.w3.org/2005/Atom',
'd': 'http://schemas.microsoft.com/ado/2007/08/dataservices',
'm': 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata',
'georss': 'http://www.georss.org/georss',
'gml': 'http://www.opengis.net/gml'
};
return ns[prefix] || null;
};
// Use the namespace resolver in the XPath evaluation
var result = doc.evaluate('/atom:feed/atom:entry', doc, nsResolver, XPathResult.ANY_TYPE, null);
var entries = [];
var entry = result.iterateNext();
while (entry) {
// Check if the entry contains a category with term="SP.User"
var category = entry.getElementsByTagNameNS('http://www.w3.org/2005/Atom', 'category');
let userName = "";
let permissions = [];
for (var i = 0; i < category.length; i++) {//
if (category[i].getAttribute('term') === 'SP.User') {
// Get the d:Email value
let email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Email');
if (email.length > 0 && email[0].textContent.trim() !== "") {
userName = email[0].textContent;//
} else {
email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Title');
userName = email[0].textContent;//
}
}
else if (category[i].getAttribute('term') === 'SP.Group') {
// Get the d:Email value
let email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Email');
if (email.length > 0 && email[0].textContent.trim() !== "") {
userName = email[0].textContent;//
} else {
email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Title');
userName = email[0].textContent;//
}
}
else if (category[i].getAttribute('term') === 'SP.RoleDefinition') {
var Name = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Name');
if (Name.length > 0) {
permissions.push(Name[0].textContent);
}
}
}
if (userName === "") {
debugger;
}
entries.push({
"userName": userName,
"permissions": permissions,
});
entry = result.iterateNext();
}
//console.log("entries:", entries);
return entries;
}
// Helper to parse batch responses
function parseBatchResponse(responseText, itemIds) {
//console.log("responseText", responseText);
var results = [];
const parts = responseText.split("--batchresponse");
let i = 0;
for (const part of parts) {
if (part.includes("HTTP/1.1 200 OK")) {
const xmlStart = part.indexOf("");
if (xmlStart > -1 && xmlEnd > -1) {
let xmlString = part.substring(xmlStart, xmlEnd + 8);
results.push({
"item": itemIds[i],
"permissions": nodeParser2(xmlString)
})
i++;
}
}
}
return results;
}
function chunkArray(array, chunkSize) {
let result = [];
for (let i = 0; i < array.length; i += chunkSize) {
// Utilise slice pour découper le tableau
result.push(array.slice(i, i + chunkSize));
}
return result;
}
const siteUrl = _spPageContextInfo.webAbsoluteUrl || "https://test.sharepoint.com/sites/BIPvvvv";
const withUniquePermissions = await GetPermissionsInFolder(siteUrl, "Shared%20Documents", "");
//"startswith(FileDirRef, '/sites/BIPvvvv/Shared%20Documents/General/07%20-%20Release%201')"
console.log("withUniquePermissions", withUniquePermissions);
console.log("withUniquePermissions length", withUniquePermissions.length);
const permmissionItemsAll = [];
if (withUniquePermissions.length > 0) {
const withUniquePermissionsCutted = chunkArray(withUniquePermissions, 30);
for (let i = 0; i < withUniquePermissionsCutted.length; i++) {
//debugger;
const permmissionItems = await batchFetchPermissions(siteUrl, withUniquePermissionsCutted[i], "Shared%20Documents");
permmissionItemsAll.push(...permmissionItems)
}
console.log("permmissionItems", permmissionItemsAll);
}
//FileDirRef,RoleAssignments,HasUniqueRoleAssignments,Id,Title,FileLeafRef,Modified,Created
const toDisplay = [];
let csv = "Id;Title;Modified;Created;UserPermission;Permissions;FileLeafRef;FileDirRef\n";
for (let i = 0; i < permmissionItemsAll.length; i++) {
const permmissionItem = permmissionItemsAll[i];
for (let j = 0; j < permmissionItem.permissions.length; j++) {
for (let k = 0; k < permmissionItem.permissions[j].permissions.length; k++) {
//debugger;
const item = {
"Id": permmissionItem.item.Id,
"FileDirRef": permmissionItem.item.FileDirRef,
"HasUniqueRoleAssignments": permmissionItem.item.HasUniqueRoleAssignments,
"Title": permmissionItem.item.Title,
"FileLeafRef": permmissionItem.item.FileLeafRef,
"FileDirRef": permmissionItem.item.FileDirRef,
"Modified": permmissionItem.item.Modified,
"Created": permmissionItem.item.Created,
"UserPermission": permmissionItem.permissions[j].userName,
"Permissions": permmissionItem.permissions[j].permissions[k]
}
csv += `${item.Id};${item.Title};${item.Modified};${item.Created};${item.UserPermission};${item.Permissions};${item.FileLeafRef};${item.FileDirRef}` + "\n";
toDisplay.push(item);
}
}
}
document.body.innerHTML = `<div id="tableContainer" class="table-wrapper"></div>`;
const table = generateTableFromJson2(toDisplay, "Id,Title,Modified,Created,UserPermission,Permissions,FileLeafRef,FileDirRef");
console.log("csv", csv);
// Append the table to the container
document.getElementById('tableContainer').appendChild(table);
navigator.clipboard.writeText(csv);