Blog
PowerApps functions
Parse text to number
Filter('Workflow Tasks'; ID = Value(txtId.Text))
Add datas (listItem)
Patch(NewVoie;Defaults(NewVoie);{Num_x00e9_rovoie:"0"&LookUp(NewVoie;ID=1).Num_x00e9_rovoie}))
Update context, and forms datas
SubmitForm(FormBeneficiaires);;ResetForm(FormBeneficiaires);; NewForm(FormBeneficiaires);; UpdateContext({showPopup:false});
If(IsBlankOrError(SubmitForm(Form1)), Set(saveStatus, "An error occured" & Form1.Error), Set(saveStatus, "Operation succeded"))
Navigate to another form
Navigate(Page_infos_enregistrements)
Get query string parameter and set a variable
Set(InitiativeId; Param("ID"))
Get a field from your datasource by ID
First(Filter(Initiatives; ID=1)).Nom
And Or Not
Or(And(Radio1.Selected.Value=4; !IsBlank(txtComment.Text));Radio1.Selected.Value<4)
Update Lookup Field
Patch(
ResultatAnalyses;
First(//here item to update
Filter(
ResultatAnalyses;
Affaire.Id = currentAffaire.ID And Analyse.Id = ThisItem.ID
)
);
{
Title: "notused";
Commentaires: txtGalComment.Text;
Gravite: Rating1.Value;
Affaire: {//lookup field name
Id: currentAffaire.ID;//id of lookup
Value: LookUp(
Affaires;//list who contains lookup value
ID = currentAffaire.ID;//id of lookup
currentAffaire.Title//title of lookup value
)
}
}
)
Patch Choice
TypeIntervention: {Value: dtvTypeIntervention.Selected.Value}
Execute automate with json
'My workflow'.Run(
JSON(
{
SolutionId: selectedSolution.ID,
ImageContent: UploadedImage14.Image
},
JSONFormat.IncludeBinaryData
)
);
Reg ex to get cleaned string
Clear(AttachmentsCollection);
ForAll(
RenameColumns(DataCardValue91.Attachments, "Name", "Name1"),
Collect(
AttachmentsCollection,
Name1
)
);Set(Title1, First(AttachmentsCollection).Value);Set(FileName1, Concat( Split(First(AttachmentsCollection).Value, "" ), If( IsMatch(Result, "([^A-Za-z0-9\.\-])" ), "",Result ) ))
Save Form
SubmitForm(Form1);;If(!IsBlankOrError( Form1.Error); Notify("Une erreur est survenue lors de la sauvegarde " & Form1.Error; NotificationType.Error);Notify("La savegarde a réussi";NotificationType.Information);;Set(currentElement; Form1.LastSubmit))
Sort columns
Set(Month, Distinct(SortByColumns(CurrentMonthMails, "Year", Ascending, "Month", Ascending), Month))
Set date
Set(StartDate, DateAdd(DateTimeValue( Day(Today) &"/"& Month(Today) &"/"& Year(Today) &" 00:00:00"), -30));
Sum
Sum(Filter(CurrentMonthMails, Month = ThisItem.Result ), uniqMails)
SharePoint RestShow Users List
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 = `
body{
background-color: #f8f8f8;
}
.parentDiv{
height:400px;
overflow-x:scroll;
white-space: nowrap;
}
ul
{
list-style-type: none;
}
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 {
height: 800px;
overflow-y: auto;
border: 1px solid #ddd;
overflow-x:scroll;
}
/* 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 === "permissions") {
if (item.permissions !== undefined && item.permissions.length > 0) {
const ul = document.createElement('ul');
item.permissions.forEach((perm) => {
let li = document.createElement('li');
const tablePerm = document.createElement('table');
const tbodyPrem = document.createElement('tbody');
let rowPrem = tbodyPrem.insertRow();
let cellPerm = rowPrem.insertCell();
//member
cellPerm.appendChild(generateTableFromJson2([perm.Member], "LoginName,Title", false))
//perms
cellPerm = rowPrem.insertCell();
cellPerm.appendChild(generateTableFromJson2(perm.RoleDefinitionBindings.results, "Name", false));
tablePerm.appendChild(tbodyPrem);
li.appendChild(tablePerm);
ul.appendChild(li);
});
cell.appendChild(ul);
} else {
cell.innerHTML = " ";
}
} 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 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 sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function ExecuteQuery(req, fetchOptions, maxRetry = 3, wait = 1, trynum = 1) {
console.log("ExecuteQuery", req, fetchOptions, maxRetry, wait, trynum);
if (trynum >= maxRetry) {
console.log("ExecuteQuery Error", req);
console.log("ExecuteQuery Error", fetchOptions);
throw new Error(`ExecuteQuery error! maxRetry >= trynum`);
}
try {
let startDate = new Date();
let diffMinutes = 0;
respList1 = await fetch(req, fetchOptions);
endDate = new Date();
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000));
console.log(`seconds ${diffMinutes} queryNumber ${trynum}`)
//avoid 429 too much queries
if (!respList1.ok && respList1.status == 429) {
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
} else if (!respList1.ok && respList1.status == 503) {//avoid 503 server unavailable / connections error
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
} else if (!respList1.ok && respList1.status == 403) {//avoid 403 reload page in another tab
//debugger;
window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
} else if (!respList1.ok) {
console.log("ExecuteQuery Error", respList1);
const errorDetails = await respList1.text(); // Get error details from the response
console.log("ExecuteQuery Error", respList1);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
return respList1
} catch (error) {
console.log(error);
throw new Error(`HTTP error! Status: ${error}`);
}
}
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
//get web server relative url
let resp = await ExecuteQuery(`${siteUrl}/_api/web?$select=ServerRelativeUrl`, fetchOptions);
let usrType1 = await resp.json();
console.log(usrType1.d);
const webRelativeUrl = usrType1.d.ServerRelativeUrl;
//get user list
resp = await ExecuteQuery(`${siteUrl}/_api/web/lists?$select=RootFolder/ServerRelativeUrl,Id&$expand=RootFolder`, fetchOptions);
usrType1 = await resp.json();
let userListId = "";
usrType1.d.results.forEach(list => {
if (list.RootFolder.ServerRelativeUrl.toLowerCase() === (webRelativeUrl + "/_api/web/siteuserinfolist").toLowerCase() ||
list.RootFolder.ServerRelativeUrl.toLowerCase() === (webRelativeUrl + "/_catalogs/users").toLowerCase()) {
userListId = list.Id;
}
});
if(userListId === ""){
console.log("list not found");
throw new Error("list not found");
}
resp = await ExecuteQuery(`${siteUrl}/_api/web/lists(guid'${userListId}')/items?$select=FirstName,IsActive,LastName,UserName,Modified,Author/Title,JobTitle,Department,IsSiteAdmin,EMail,Title,Name,ContentTypeDisp,Deleted,PrincipalCount,ID&$expand=Author`, fetchOptions);
let j = await resp.json();
console.log(j.d);
resp = await ExecuteQuery(`${siteUrl}/_api/web/siteusers?$select=ID,PrincipalType,IsShareByEmailGuestUser`, fetchOptions);
const usrType = await resp.json();
j.d.results.forEach(element => {
//debugger;
const usr = usrType.d.results.find(u => u.Id === element.ID);
//console.log("usrType", usrType);
if (usr !== undefined) {
element.PrincipalType = usr.PrincipalType;
element.IsShareByEmailGuestUser = usr.IsShareByEmailGuestUser;
} else {
element.PrincipalType = -1;
element.IsShareByEmailGuestUser = false;
}
});
document.body.innerHTML = ``;
const table = generateTableFromJson2(j.d.results, "EMail,Title,PrincipalType,IsShareByEmailGuestUser,Name,IsActive,FirstName,LastName,UserName,Modified,Author/Title,JobTitle,Department,IsSiteAdmin,Deleted,ID");
// Append the table to the container
document.getElementById('tableContainer').appendChild(table);
Sharepoint Audit List Permissions batch
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const listUrl = "Shared%20Documents";
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function generateGuid() {
await sleep(10);// * trynum
return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
const r = Math.random() * 16 | 0;
const v = c === 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
});
}
async function getRequestDigest() {
const fetchOptions = {
method: "POST",
headers: { Accept: "application/json;odata=verbose" },
credentials: "include",
};
const data = (await (await ExecuteQuery(`${siteUrl}/_api/contextinfo`, fetchOptions)).json());
return data.d.GetContextWebInformation.FormDigestValue;
}
async function ExecuteQuery(req, fetchOptions, maxRetry = 3, wait = 10000, trynum = 1) {
console.log("ExecuteQuery", req, fetchOptions, maxRetry, wait, trynum);
if (trynum >= maxRetry) {
console.log("ExecuteQuery Error", req);
console.log("ExecuteQuery Error", fetchOptions);
throw new Error(`ExecuteQuery error! maxRetry >= trynum`);
}
try {
let startDate = new Date();
let diffMinutes = 0;
respList1 = await fetch(req, fetchOptions);
endDate = new Date();
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000));
console.log(`seconds ${diffMinutes} queryNumber ${trynum}`)
//avoid 429 too much queries
if (!respList1.ok && respList1.status == 429) {
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 2);// * trynum
if (fetchOptions.method === "POST") {
//regnerate the digest
fetchOptions["X-RequestDigest"] = await getRequestDigest();
}
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
}
//avoid 503 server unavailable / connections error
if (!respList1.ok && respList1.status == 503) {
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(1000 * wait);// wait 10 seconds
if (fetchOptions.method === "POST") {
//regnerate the digest
fetchOptions["X-RequestDigest"] = await getRequestDigest();
}
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
}
//avoid 403 reload page in another tab
if (!respList1.ok && respList1.status == 403) {
//debugger;
window.open(siteUrl, '_blank')
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log("waiting due to error 403");
await sleep(10000);// wait 10 seconds
window.open(siteUrl, '_blank')
await sleep(1000 * wait);// wait 10 seconds
if (fetchOptions.method === "POST") {
//regnerate the digest
fetchOptions["X-RequestDigest"] = await getRequestDigest();
}
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
}
if (!respList1.ok) {
console.log("ExecuteQuery Error", respList1);
const errorDetails = await respList1.text(); // Get error details from the response
console.log("ExecuteQuery Error", respList1);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
return respList1
} catch (error) {
console.log(error);
throw new Error(`HTTP error! Status: ${error}`);
}
}
async function GetFolderSize(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 ExecuteQuery(req, fetchOptions)).json()).d.ServerRelativeUrl;
// get total items count
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/?$select=ItemCount`;
const ItemsCount = (await (await ExecuteQuery(req, fetchOptions)).json()).d.ItemCount;
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${query1}`;
console.log("req", req);
const firstId = (await (await ExecuteQuery(req, fetchOptions)).json()).d.results[0].Id;
console.log("firstId", firstId);
//get lastId
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id desc${query1}`;
console.log("last", req);
const lastId = (await (await ExecuteQuery(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}`);//FileSizeDisplay
console.log("query", query);//_UIVersionString File_x0020_Size
const startDate = new Date();
let endDate = new Date();
let diffMinutes = 0;
let queryNumber = 1;
do {
//to avoid 429 error
if (queryNumber % 10 == 0) {
console.log(`sleep 1 minute queryNumber ${queryNumber}`);
await sleep(60000); // 60 000 ms = 1 minute
}
var select = "?$select=File/Length,File/UIVersionLabel,File_x0020_Type,Id,HasUniqueRoleAssignments,FileRef,FileLeafRef,FileDirRef,Created,Modified,Author/Title,Author/EMail,Editor/Title,Editor/EMail,ContentTypeId";
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId} and Id le ${lastId}${query}&$orderby=Id asc&$top=5000&$expand=File,Author,Editor`;
console.log("req", req);
// Send the asynchronous GET request to the REST API endpoint /_api/site/usage
let respList1 = null;
try {
respList1 = await ExecuteQuery(req, fetchOptions);
endDate = new Date();
queryNumber++;
} catch (error) {
console.log("error", error);
return allItems;
}
const items = (await respList1.json()).d.results;
allItems.push(...items);
startId += 5000;
endId += 5000;
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000 * 60));
console.log(`startId ${startId} endId ${endId} lastId ${lastId} diffMinutes ${diffMinutes} queryNumber ${queryNumber}, ItemsCount : ${ItemsCount}`)
await sleep(1500); // 1,5 second
//debugger;
}
while (startId <= lastId);
return allItems;
}
async function batchFetchPermissions(queriess) {
const queries = [...queriess];
console.log("batchFetchPermissions", queries);
const batchBoundary = "batch_" + await generateGuid();
const batchBody = queries.map((query) => {
return `
--${batchBoundary}
Content-Type: application/http
Content-Transfer-Encoding: binary
GET ${query.req} HTTP/1.1
Accept: application/json;odata=verbose
`;
}).join("\n") + `\n--${batchBoundary}--`;
const digest = await getRequestDigest();//
console.log("batchBody", batchBody.length);
const headers = {
Accept: "application/json;odata=verbose",
"X-RequestDigest": digest,
"Content-Type": `multipart/mixed; boundary="${batchBoundary}"`,
};
const response = await fetch(`${siteUrl}/_api/$batch`, {
method: "POST",
headers,
body: batchBody,
});
const text = await response.text(); // Parse response manually (multipart)
const responses = text.split("HTTP/1.1 200 OK");
const results1 = [];
for (let k = 1; k < responses.length; k++) {
const parts = responses[k].split("\r\n\r\n")[1];
// The JSON is the last part after the headers
let jsonString = parts[parts.length - 1];
// Parse the JSON
jsonString = parts.split("\r\n")[0];
const d = {
Id: `${queries[k - 1].Id}`,
Index: k,
result: JSON.parse(jsonString.trim()).d
}
results1.push(d);
}
return [...results1];
}
const maxBatch = 20;
async function LoadUniquePermissions(items) {
let batchCount = 0;
let requests = [];
var req = `${siteUrl}/_api/web?$select=ServerRelativeUrl`;
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
const perms = [];
const webServerRelativUrl = (await (await ExecuteQuery(req, fetchOptions)).json()).d.ServerRelativeUrl;
for (let i = 0; i < items.length; i++) {
items[i].permId = await generateGuid();
if (items[i].HasUniqueRoleAssignments) {
requests.push({
Id: items[i].permId,
req: `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl}')/items(${items[i].Id})/RoleAssignments?$expand=Member,RoleDefinitionBindings&$select=Member/Id,Member/LoginName,Member/Title,RoleDefinitionBindings/Name`
});
batchCount++;
}
if (requests.length === maxBatch) {
perms.push(await batchFetchPermissions(requests));
requests = [];
}
}
if (requests.length > 0) {
perms.push(await batchFetchPermissions(requests));
requests = [];
}
for (let i = 0; i < items.length; i++) {
const perm = perms.flat().filter(f => `${f.Id}` == `${items[i].permId}`);
if (items[i].HasUniqueRoleAssignments && perm.length === 1) {
items[i].permissions = perm[0].result.results;
}
}
console.log("with perm", items);
return items;
}
const disgest = await getRequestDigest();
console.log("disgest", disgest);
const items = await GetFolderSize(siteUrl, listUrl, "Id le 145000");//(Id eq 225 or Id eq 226) Id gt 270
const rest = await LoadUniquePermissions(items);
In javascript
SharePoint Get Doc Lib Size avoid error 429
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function ExecuteQuery(req, fetchOptions, maxRetry = 3, wait = 1, trynum = 1) {
console.log("ExecuteQuery", req, fetchOptions, maxRetry, wait, trynum);
if (trynum >= maxRetry) {
console.log("ExecuteQuery Error", req);
console.log("ExecuteQuery Error", fetchOptions);
throw new Error(`ExecuteQuery error! maxRetry >= trynum`);
}
try {
let startDate = new Date();
let diffMinutes = 0;
respList1 = await fetch(req, fetchOptions);
endDate = new Date();
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000));
console.log(`seconds ${diffMinutes} queryNumber ${trynum}`)
//avoid 429 too much queries
if (!respList1.ok && respList1.status == 429) {
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
}
//avoid 503 server unavailable / connections error
if (!respList1.ok && respList1.status == 503) {
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
}
//avoid 403 reload page in another tab
if (!respList1.ok && respList1.status == 403) {
//debugger;
window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
}
if (!respList1.ok) {
console.log("ExecuteQuery Error", respList1);
const errorDetails = await respList1.text(); // Get error details from the response
console.log("ExecuteQuery Error", respList1);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
return respList1
} catch (error) {
console.log(error);
throw new Error(`HTTP error! Status: ${error}`);
}
}
async function GetFolderSize(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 ExecuteQuery(req, fetchOptions)).json()).d.ServerRelativeUrl;
// get total items count
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/?$select=ItemCount`;
const ItemsCount = (await (await ExecuteQuery(req, fetchOptions)).json()).d.ItemCount;
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${query1}`;
console.log("req", req);
const firstId = (await (await ExecuteQuery(req, fetchOptions)).json()).d.results[0].Id;
console.log("firstId", firstId);
//get lastId
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id desc${query1}`;
console.log("last", req);
const lastId = (await (await ExecuteQuery(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}`);//FileSizeDisplay
console.log("query", query);//_UIVersionString File_x0020_Size
const startDate = new Date();
let endDate = new Date();
let diffMinutes = 0;
let queryNumber = 1;
do {
//to avoid 429 error
if (queryNumber % 10 == 0) {
console.log(`sleep 1 minute queryNumber ${queryNumber}`);
await sleep(60000); // 60 000 ms = 1 minute
}
var select = "?$select=File/Length,File/UIVersionLabel,File_x0020_Type,Id,HasUniqueRoleAssignments,FileRef,FileLeafRef,FileDirRef,Created,Modified,Author/Title,Author/EMail,Editor/Title,Editor/EMail,ContentTypeId";
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId} and Id le ${lastId}${query}&$orderby=Id asc&$top=5000&$expand=File,Author,Editor`;
console.log("req", req);
// Send the asynchronous GET request to the REST API endpoint /_api/site/usage
let respList1 = null;
try {
respList1 = await ExecuteQuery(req, fetchOptions);
endDate = new Date();
queryNumber++;
} catch (error) {
console.log("error", error);
return allItems;
}
const items = (await respList1.json()).d.results;
allItems.push(...items);
startId += 5000;
endId += 5000;
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000 * 60));
console.log(`startId ${startId} endId ${endId} lastId ${lastId} diffMinutes ${diffMinutes} queryNumber ${queryNumber}, ItemsCount : ${ItemsCount}`)
await sleep(1500); // 1,5 second
//debugger;
}
while (startId <= lastId);
//console.log("allItems", allItems);
console.log("queryNumber", queryNumber);
return allItems;
}
let totalSize = 0
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const items = await GetFolderSize(siteUrl, "Shared%20Documents", "");//startswith(ContentTypeId,'0x0101')
//calculate file Size
let separator = ";"
let csv = `Id${separator}HasUniqueRoleAssignments${separator}FromParent${separator}Title${separator}FileRef${separator}FileDirRef${separator}FileLeafRef${separator}Created${separator}CreatedMin${separator}Modified${separator}ModifiedMin${separator}AuthorTitle${separator}AuthorEMail${separator}EditorTitle${separator}EditorEMail${separator}FileLength${separator}MB${separator}GB${separator}FileUIVersionLabel${separator}File_x0020_Type${separator}ContentTypeId${separator}count\n`;
//display lastID
if (items !== undefined && items !== null && items.length > 0) {
console.log(`lastId ${items[items.length - 1].Id}`);
}
//check child
//get folders with unique permissions
const folder = items.filter(file => file.HasUniqueRoleAssignments && file.ContentTypeId.startsWith('0x0120'));
for (let k = 0; k < folder.length; k++) {
for (let i = 0; i < items.length; i++) {
if(items[i].FromParent == undefined)
items[i].FromParent = false;
if (items[i].HasUniqueRoleAssignments == false && items[i].FileDirRef.startsWith(folder[k].FileRef)) {
items[i].HasUniqueRoleAssignments = true;
items[i].FromParent = true;
}
}
}
for (let i = 0; i < items.length; i++) {
csv += `${items[i].Id}${separator}`;
csv += `${items[i].HasUniqueRoleAssignments}${separator}`;
csv += `${items[i].FromParent}${separator}`;
csv += `${items[i].Title}${separator}`;
csv += `${items[i].FileRef}${separator}`;
csv += `${items[i].FileDirRef}${separator}`;
csv += `${items[i].FileLeafRef}${separator}`;
csv += `${items[i].Created}${separator}`;
csv += `${items[i].Created.substring(0, 10)}${separator}`;
csv += `${items[i].Modified}${separator}`;
csv += `${items[i].Modified.substring(0, 10)}${separator}`;
csv += `${items[i].Author.Title}${separator}`;
csv += `${items[i].Author.EMail}${separator}`;
csv += `${items[i].Editor.Title}${separator}`;
csv += `${items[i].Editor.EMail}${separator}`;
//if is a file
if (items[i].File != undefined && items[i].File.Length != undefined) {
totalSize += parseInt(items[i].File.Length);
csv += `${items[i].File.Length}${separator}`;
csv += `${(items[i].File.Length / (1024 * 1024)).toFixed(2)}${separator}`;
csv += `${(items[i].File.Length / (1024 * 1024 * 1024)).toFixed(2)}${separator}`;
csv += `${items[i].File.UIVersionLabel}${separator}`;
csv += `${items[i].File_x0020_Type}${separator}`;
}
else {
//is folder
csv += `${separator}`;
csv += `${separator}`;
csv += `${separator}`;
csv += `${separator}`;
csv += `${separator}`;
}
csv += (`${items[i].ContentTypeId}`.startsWith('0x0120') ? "folder" : `${items[i].ContentTypeId}`);
csv += `${separator}1\n`;
}
const totalSizeGB = (totalSize / (1024 * 1024 * 1024)).toFixed(2);
console.log("totalSize B", totalSize);
console.log("total size MB", (totalSize / (1024 * 1024)).toFixed(2));
console.log("total size GB", totalSizeGB);
console.log("items length", items.length);
console.log("csv", csv);
console.log("items", items);
Get SharePoint Document Library Size
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function GetFolderSize(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;
console.log("firstId", firstId);
// get lastId
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);
const startDate = new Date();
let endDate = new Date();
let diffMinutes = 0;
let queryNumber = 0;
do {
// to avoid 429 error
if(queryNumber % 10 == 0){
await sleep(60000); // 60 000 ms = 1 minute
}
var select = "?$select=File/Length";
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId}${query}&$orderby=Id asc&$top=5000&$expand=File`;
console.log("req", req);
let respList1 = null;
try {
respList1 = await fetch(req, fetchOptions);
if (!respList1.ok) {
const errorDetails = await respList1.text();
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
endDate = new Date();
queryNumbercatch (error) {
console.log("error", error);
return null;
}
const items = (await respList1.json()).d.results;
allItems.push(...items);
startId += 5000;
endId += 5000;
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000 * 60));
console.log(`startId ${startId} endId ${endId} lastId ${lastId} diffMinutes ${diffMinutes} queryNumber ${queryNumber}`)
}
while (endId < lastId);
console.log("queryNumber", queryNumber);
return allItems;
}
let totalSize = 0
const siteUrl = "Your site Url";
const items = await GetFolderSize(siteUrl, "Your doclib Name", "startswith(ContentTypeId,'0x0101')");
// calculate file Size
for (let i = 0; i < items.length; i++) {
if (items[i].File != undefined && items[i].File.Length != undefined) {
totalSize += parseInt(items[i].File.Length);
}
}
const totalSizeGB = (totalSize / (1024 * 1024 * 1024)).toFixed(2);
console.log("totalSize B", totalSize);
console.log("total size MB", (totalSize / (1024 * 1024)).toFixed(2));
console.log("total size GB", totalSizeGB);
console.log("items length", items.length);
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function GetFolderSize(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;
console.log("firstId", firstId);
//get lastId
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}`);//FileSizeDisplay
console.log("query", query);//_UIVersionString File_x0020_Size
const startDate = new Date();
let endDate = new Date();
let diffMinutes = 0;
let queryNumber = 0;
do {
//to avoid 429 error
if(queryNumber % 10 == 0){
await sleep(60000); // 60 000 ms = 1 minute
}
var select = "?$select=File/Length";
req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId}${query}&$orderby=Id asc&$top=5000&$expand=File`;
console.log("req", req);
// Send the asynchronous GET request to the REST API endpoint /_api/site/usage
let respList1 = null;
try {
respList1 = await fetch(req, fetchOptions);
if (!respList1.ok) {
const errorDetails = await respList1.text(); // Get error details from the response
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
endDate = new Date();
queryNumber++;
} catch (error) {
console.log("error", error);
return null;
}
const items = (await respList1.json()).d.results;
allItems.push(...items);
startId += 5000;
endId += 5000;
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000 * 60));
console.log(`startId ${startId} endId ${endId} lastId ${lastId} diffMinutes ${diffMinutes} queryNumber ${queryNumber}`)
}
while (endId < lastId);
//console.log("allItems", allItems);
console.log("queryNumber", queryNumber);
return allItems;
}
let totalSize = 0
const siteUrl = "Your site Url";
const items = await GetFolderSize(siteUrl, "Your doclib Name", "startswith(ContentTypeId,'0x0101')");
//calculate file Size
for (let i = 0; i < items.length; i++) {
if (items[i].File != undefined && items[i].File.Length != undefined) {
totalSize += parseInt(items[i].File.Length);
}
}
const totalSizeGB = (totalSize / (1024 * 1024 * 1024)).toFixed(2);
console.log("totalSize B", totalSize);
console.log("total size MB", (totalSize / (1024 * 1024)).toFixed(2));
console.log("total size GB", totalSizeGB);
console.log("items length", items.length);
//console.log("items", items);
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
let respList1 = await fetch(`yoursiteUrl/_api/site/usage`, fetchOptions);
if (!respList1.ok) {
const errorDetails = await respList1.text(); // Get error details from the response
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
const usage = await respList1.json();
console.log("usage", `${usage.d.Usage.Storage/ (1024 * 1024 * 1024)} Gb`);
In Tools
Calendrier Annuel Avec numero de Semaine
Calendrier Annuel avec numéro de semaine
Modèles de sites SharePoint modernes (2025)
| Code du modèle | Nom du modèle | Description | Type de site associé |
|---|---|---|---|
| STS#3 | Site d’équipe (Team Site) | Site de collaboration pour les équipes, avec bibliothèques de documents, listes de tâches, calendrier et intégration avec Microsoft Teams. | Groupe Microsoft 365/Teams |
| GROUP#0 | Site d’équipe moderne | Équivalent moderne de STS#3, toujours lié à un groupe Microsoft 365 (et donc à Teams si activé). | Groupe Microsoft 365/Teams |
| SITEPAGEPUBLISHING#0 | Site de communication (Communication Site) | Pour diffuser des informations (actualités, rapports, événements) à large audience. Pas lié à Teams. | Site autonome |
| COMMSITE#0 | Site de communication moderne | Version moderne du site de publication, optimisé pour la communication visuelle et les composants web. | Site autonome |
| STS#0 | Site vide (Blank Site) | Site SharePoint vide, sans listes ni bibliothèques préconfigurées. | Site autonome |
| BDR#0 | Centre de documents | Pour gérer et organiser des documents en masse, souvent utilisé comme archive. | Site autonome |
| SRCHCEN#0 | Centre de recherche | Site dédié à la recherche avancée dans SharePoint. | Site autonome |
| SRCHCENTERLITE#0 | Centre de recherche léger | Version simplifiée du centre de recherche. | Site autonome |
| EDISC#0 | Centre de découverte électronique | Pour la gestion des données dans un contexte juridique ou de conformité. | Site autonome |
| POINTPUBLISHING#0 | Site de publication | Pour publier des pages web avec flux de travail d’approbation (classique). | Site autonome |
2. Modèles classiques (moins utilisés en 2025, mais toujours disponibles)
| Code du modèle | Nom du modèle | Description |
|---|---|---|
| STS#1 | Site d’équipe classique | Ancienne version du site d’équipe, sans intégration moderne avec Teams. |
| STS#2 | Site d’équipe vide | Site d’équipe sans contenu préconfiguré. |
| WIKI#0 | Site Wiki | Pour créer une base de connaissances collaborative sous forme de wiki. |
| BLOG#0 | Site de blog | Pour publier des articles sous forme de blog. |
| DEV#0 | Site de développement | Pour les développeurs, avec des listes de suivi de bugs et de tâches. |
| PROJECTSITE#0 | Site de projet | Pour gérer des projets, avec des listes de tâches et des jalons. |
| COMMUNITY#0 | Site communautaire | Pour créer une communauté avec forums de discussion. |
| VISPRJS#0 | Référentiel Visio | Pour stocker et partager des diagrammes Visio. |