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)

Tension électrique – Cours simplifié & QCM

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);
        }

        //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}`);
    }
}
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const fetchOptions = {
    method: 'GET',
    headers: {
        'Accept': 'application/json;odata=nometadata'
    }
};

//get web
let resp = await ExecuteQuery(`${siteUrl}/_api/web?$select=Title,Id,ServerRelativeUrl`, fetchOptions);

const web = (await resp.json()).d;
console.log(web);

const top1 = 5;
let pagination = 0;
let itemCount = 1;
const separator = ";";
let csv = `Title${separator}LeafName${separator}DirName${separator}DeletedDate${separator}DeletedByName${separator}DeletedByEmail${separator}ItemType${separator}Size\n`;
while (itemCount > 0) {
    //&$filter=ItemState eq 0
    let url = `${siteUrl}/_api/site/RecycleBin?$select=DeletedByEmail,DeletedByName,DeletedDate,DirName,LeafName,Size,ItemId,Title,ItemType&$top=${top1}&$orderby=DeletedDate asc&$skip=${pagination}`;
    resp = await ExecuteQuery(url, fetchOptions);
    console.log(resp);
    const RecycleBinItems = (await resp.json());
    console.log(RecycleBinItems);
    for (let i = 0; i < RecycleBinItems.value.length; i++) {
        const item = RecycleBinItems.value[i];
        //console.log(`Title: ${item.Title}, Deleted By: ${item.DeletedByName}, Deleted Date: ${item.DeletedDate}`);
        csv += `${RecycleBinItems.value[i].Title}${separator}`;
        csv += `${RecycleBinItems.value[i].LeafName}${separator}`;
        csv += `${RecycleBinItems.value[i].DirName}${separator}`;
        csv += `${RecycleBinItems.value[i].DeletedDate}${separator}`;
        csv += `${RecycleBinItems.value[i].DeletedByName}${separator}`;
        csv += `${RecycleBinItems.value[i].DeletedByEmail}${separator}`;
        csv += `${RecycleBinItems.value[i].ItemType}${separator}`;
        csv += `${RecycleBinItems.value[i].Size}`;
        csv += `\n`;
    }
    itemCount = RecycleBinItems.value.length;
    //if (itemCount < top1) break;
    pagination += top1;
}
console.log(csv);

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);

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);

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);