SharePoint Get Doc Lib Size avoid error 429

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)

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