Blog
SharePoint Get All Documents Permissions
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);
SharePoint Rest Get All Items In A folder even more than 5000
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;
allItems.push(...items);
startId += 5000;
endId += 5000;
console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
}
while (endId < lastId);
return allItems;
}
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const items = await GetPermissionsInFolder(siteUrl, "Shared%20Documents", "");
console.log("items", items);
console.log("items length", items.length);
SharePoint List Json Formating
JSON Formatting in SharePoint Online: A Comprehensive Guide
JSON formatting in SharePoint Online enables users to customize the visual presentation of list and library data without requiring complex code. It allows for dynamic styling, icons, calculated values, and even conditional formatting based on field data. In this article, we’ll explore JSON formatting basics, advanced examples, limitations, and best practices.
What is JSON Formatting in SharePoint Online?
JSON formatting is a declarative approach to customize how fields, rows, or views appear in SharePoint lists and libraries. It involves writing JSON code that defines the logic and styling based on field values.
Examples of JSON Formatting
1. Change Background Color Based on Approval Status
If the field "ApprovalStatus" has a value of `"pending"`, set the background color to yellow. JSON Code:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"background-color": "=if(@currentField == 'pending', '#FFFF00', '')",
"padding": "5px"
},
"txtContent": "@currentField"
}
2. Display an Alert Icon if Cost Equals 100 Add a warning icon next to the Cost field if its value is `100`. JSON Code:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"children": [
{
"elmType": "span",
"txtContent": "@currentField"
},
{
"elmType": "span",
"style": {
"margin-left": "10px",
"color": "red"
},
"attributes": {
"iconName": "Warning"
},
"condition": "=if(@currentField == 100, true, false)"
}
]
}
3. Concatenate Two Fields Combine two text fields, FirstName and LastName, into a single formatted output. JSON Code:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "='Full Name: ' + [$FirstName] + ' ' + [$LastName]"
}
4. Add 3 Days to a Date Field Display a date that is 3 days later than the value in a field called StartDate. JSON Code:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=formatDate(addDays(@currentField, 3), 'yyyy-MM-dd')"
}
Limitations of JSON Formatting
1. Read-Only:
- JSON formatting is purely visual; it does not change the actual field value or data in the list.
2. Complex Logic:
- While simple conditions are supported, JSON formatting cannot handle complex business logic or external data calls.
3. Limited Interactivity:
- JSON formatting cannot create interactive elements like buttons with advanced event handling.
4. Field Dependencies:
- If a calculated field is hidden in the view, JSON that relies on it may break.
5. Performance Impact:
- Excessive formatting, especially on large lists, may slow down rendering.
Best Practices for JSON Formatting
1. Keep it Simple:
- Avoid overly complex JSON. Keep the code readable and maintainable.
2. Validate JSON:
- Use online JSON validators or tools like VS Code with JSON extensions to catch syntax errors.
3. Optimize for Performance:
- Test formatting on large lists to ensure it doesn’t degrade performance.
4. Use Conditional Logic:
- Use `if` statements sparingly and test all possible conditions.
5. Test Across Browsers:
- Ensure formatting looks consistent across browsers, especially for icons and colors.
6. Document Your JSON:
- Add comments (externally, as SharePoint JSON doesn’t support comments) to document logic and purpose.
Conclusion
JSON formatting in SharePoint Online is a powerful tool to enhance list and library views. By applying dynamic styling and conditional logic, you can improve data visualization and user experience. However, its read-only nature and complexity for advanced scenarios mean it’s best suited for lightweight customizations.
By following best practices and understanding its limitations, you can use JSON formatting effectively in your SharePoint projects.
const endpoint = `https://test.sharepoint.com/sites/fdiSandBox`;
const myHtttp = {
getItemTypeForListName: async function (listTitle) {
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose',
'Content-type': 'application/json;odata=verbose'
}
};
const response = await fetch(endpoint + `/_api/web/lists/getbytitle('${listTitle}')/?$select=ListItemEntityTypeFullName`, fetchOptions);
return response.json();
},
GetDisgestValue: async function () {//
const fetchOptions = {
method: 'POST',
headers: {
'Accept': 'application/json;odata=verbose',
'Content-type': 'application/json;odata=verbose'
}
};
const response = await fetch(endpoint + "/_api/contextinfo", fetchOptions);
console.log(response);
return response.json();
}
}
const listItemEntityTypeFullName = (await myHtttp.getItemTypeForListName("test")).d.ListItemEntityTypeFullName;
const digest = (await myHtttp.GetDisgestValue()).d.GetContextWebInformation.FormDigestValue;
console.log(digest);
const toAdd = {
__metadata: { type: listItemEntityTypeFullName },
"Title": "test",
"MyLookupId": 1
};
console.log("toAdd", toAdd);
// Fetch options with headers for authentication and response format
const fetchOptions = {
method: 'POST',
headers: {
'Accept': 'application/json;odata=verbose',
'Content-type': 'application/json;odata=verbose',
'X-RequestDigest': digest
},
body: JSON.stringify(toAdd)
};
try {
const response = await fetch(endpoint + `/_api/web/lists/getbytitle('test')/items`, fetchOptions);
console.log("resp", response.json());
}
catch (e) {
console.log(e);
}
Power Apps Send JSON to Power Automate
Use JSON to set your object or array
And in your power automate use ParseJSON
SharePoint get by Rest List Role DefinitionBindings
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;
}
// Function to get permissions for a SharePoint group
async function getSharePointPermissions(siteUrl, select) {
// REST API endpoint to get group permissions
const endpoint = `${siteUrl}`;
// Fetch options with headers for authentication and response format
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
console.log("endpoint", endpoint);
// Send the asynchronous GET request to the REST API endpoint
const response = await fetch(endpoint, fetchOptions);
// Check if the response is OK (status code 200)
if (!response.ok) {
throw new Error(`Error fetching permissions: ${response.statusText}`);
}
// Parse the JSON response to extract permission data
const data = await response.json();
// Extract role assignments (permissions)
const roleAssignments = data.d.results;
console.log('roleAssignments', roleAssignments);
console.log(JSON.stringify(roleAssignments));
const D1 = removeSlasches(select, roleAssignments)
const pattern2 = /\//g;
console.log("json111", JSON.stringify(D1));
const table = generateTableFromJson2(D1, select.replace(pattern2, ""));
// Append the table to the container
document.getElementById('tableContainer').appendChild(table);
}
// Usage example: Replace 'your-group-id' and 'your-site-url' with actual values
const select = "Member/Title,Member/Id,Member/LoginName,RoleDefinitionBindings";
let siteUrl = 'https://mySite.sharepoint.com/sites/Dev_wf';
siteUrl += `/_api/web/roleassignments/?$expand=RoleDefinitionBindings,Member&$select=${select}`; // Replace with the actual site URL
document.body.innerHTML = `<div id="tableContainer" class="table-wrapper"></div>`; await getSharePointPermissions(siteUrl, select);
Power Automate Add Field To ContentType
Power Automate Add Field To ContentType (reference Microsoft)
Note the following restrictions on adding a field (column) to a content type using the REST service:
- Site Columns cannot be added to a content type using the REST service.
- You can add a field to a site content type only if the field already exists on the parent content type.
- You can add a field to a content type associated with a list only if the field already exists on the list. To add a completely new field to a list content type, you have to first add it to the list and then add it to the content type in a separate call to the REST service.
Invalid - Must not be used. The value = 0.
Integer - Specifies that the field contains an integer value. The value = 1.
Text - Specifies that the field contains a single line of text. The value = 2.
Note - Specifies that the field contains multiple lines of text. The value = 3.
DateTime - Specifies that the field contains a date and time value or a date-only value. The value = 4.
Counter - Specifies that the field contains a monotonically increasing integer. The value = 5.
Choice - Specifies that the field contains a single value from a set of specified values. The value = 6.
Lookup - Specifies that the field is a lookup field. The value = 7.
Boolean - Specifies that the field contains a Boolean value. The value = 8.
Number - Specifies that the field contains a floating-point number value. The value = 9.
Currency - Specifies that the field contains a currency value. The value = 10.
URL - Specifies that the field contains a URI and an optional description of the URI. The value = 11.
Computed - Specifies that the field is a computed field. The value = 12.
Threading - Specifies that the field indicates the thread for a discussion item in a threaded view of a discussion board. The value = 13.
Guid - Specifies that the field contains a GUID value. The value = 14.
MultiChoice - Specifies that the field contains one or more values from a set of specified values. The value = 15.
GridChoice - Specifies that the field contains rating scale values for a survey list. The value = 16.
Calculated - Specifies that the field is a calculated field. The value = 17.
File - Specifies that the field contains the leaf name of a document as a value. The value = 18.
Attachments - Specifies that the field indicates whether the list item has attachments. The value = 19.
User - Specifies that the field contains one or more users and groups as values. The value = 20.
Recurrence - Specifies that the field indicates whether a meeting in a calendar list recurs. The value = 21.
CrossProjectLink - Specifies that the field contains a link between projects in a Meeting Workspace site. The value = 22.
ModStat - Specifies that the field indicates moderation status. The value = 23.
Error - Specifies that the type of the field was set to an invalid value. The value = 24.
ContentTypeId - Specifies that the field contains a content type identifier as a value. The value = 25.
PageSeparator - Specifies that the field separates questions in a survey list onto multiple pages. The value = 26.
ThreadIndex - Specifies that the field indicates the position of a discussion item in a threaded view of a discussion board. The value = 27.
WorkflowStatus - Specifies that the field indicates the status of a workflow instance on a list item. The value = 28.
AllDayEvent - Specifies that the field indicates whether a meeting in a calendar list is an all-day event. The value = 29.
WorkflowEventType - Specifies that the field contains the most recent event in a workflow instance. The value = 30.
MaxItems - Must not be used. The value = 31.
Power Apps Limit 500 WorkArround
Power Apps Limit WorkArround 500
The column Id hit type is counter, we cannot use it to filter if we have more than 500 items with "<" ou ">"
But with created we can use delegation with > and <
You can build a pagination with date filtering
App loading
button next
button previous
