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)

const endpoint = `https://test.sharepoint.com/sites/fdiSandBox`;

const myHtttp = {
	getItemTypeForListName: async function (listTitle) {
		const fetchOptions = {
			method: 'GET',
			headers: {
				'Accept': 'application/json;odata=verbose',
				'Content-type': 'application/json;odata=verbose'
			}
		};
	
		const response = await fetch(endpoint + `/_api/web/lists/getbytitle('${listTitle}')/?$select=ListItemEntityTypeFullName`, fetchOptions);
		return response.json();
	},
	GetDisgestValue: async function () {//
		const fetchOptions = {
			method: 'POST',
			headers: {
				'Accept': 'application/json;odata=verbose',
				'Content-type': 'application/json;odata=verbose'
			}
		};
	
		const response = await fetch(endpoint + "/_api/contextinfo", fetchOptions);
		console.log(response);
		return response.json();
	}
}

const listItemEntityTypeFullName = (await myHtttp.getItemTypeForListName("test")).d.ListItemEntityTypeFullName;
const digest = (await myHtttp.GetDisgestValue()).d.GetContextWebInformation.FormDigestValue;
console.log(digest);

const toAdd = {
	__metadata: { type: listItemEntityTypeFullName },
	"Title": "test",
	"MyLookupId": 1
};
console.log("toAdd", toAdd);
// Fetch options with headers for authentication and response format
const fetchOptions = {
	method: 'POST',
	headers: {
		'Accept': 'application/json;odata=verbose',
		'Content-type': 'application/json;odata=verbose',
		'X-RequestDigest': digest
	},
	body: JSON.stringify(toAdd)
};

try {
	const response = await fetch(endpoint + `/_api/web/lists/getbytitle('test')/items`, fetchOptions);
	console.log("resp", response.json());
}
catch (e) {

	console.log(e);
}

Use JSON to set your object or array

And in your power automate use ParseJSON

let currentSort = { column: null, direction: 'asc' }; // Store the current sort state

// Creates the style element
function createStyleElement(id, content) {
    var style = document.createElement("style");
    style.type = "text/css";
    style.id = id;
    style.innerHTML = content;

    if (style.styleSheet) {
        style.styleSheet.cssText = content;
    } else {
        let st = document.getElementById(id);
        if (st == undefined) {
            var head = document.head || document.getElementsByTagName("head")[i];
            head.appendChild(style);
        } else {
            st.innerHTML = content;
        }
    }
    return style;
}


// Function to filter the table based on dropdown selection
function filterTable(columnIndex, value) {
    let table, tr, td, i, select, selectedValue, txtValue;
    table = document.querySelector("table");
    tr = table.getElementsByTagName("tbody")[0].getElementsByTagName("tr");
    select = table.getElementsByTagName("select")[columnIndex];
    //debugger;
    selectedValue = value;

    // Loop through all table rows and hide those that don't match the filter
    for (i = 0; i < tr.length; i++) {
        td = tr[i].getElementsByTagName("td")[columnIndex];
        if (td) {
            txtValue = td.textContent || td.innerText;
            if (selectedValue === "" || txtValue === selectedValue) {
                tr[i].style.display = "";
            } else {
                tr[i].style.display = "none";
            }
        }
    }
}
function sortTable(columnIndex, direction) {
    let table, rows, switching, i, x, y, shouldSwitch;
    table = document.querySelector("table");
    switching = true;
    let tbody = table.querySelector("tbody");

    // Set the current sort state
    currentSort.column = columnIndex;
    currentSort.direction = direction;

    while (switching) {
        switching = false;
        rows = tbody.rows;

        for (i = 0; i < rows.length - 1; i++) {
            shouldSwitch = false;
            x = rows[i].getElementsByTagName("td")[columnIndex];
            y = rows[i + 1].getElementsByTagName("td")[columnIndex];
            let isNumber = false;


            if (!isNaN(x.innerHTML)) {

                // Check if rows should switch based on ascending or descending order
                if (direction === 'asc') {
                    if (parseFloat(x.innerHTML) > parseFloat(y.innerHTML)) {
                        shouldSwitch = true;
                        break;
                    }
                } else if (direction === 'desc') {
                    if (parseFloat(x.innerHTML) < parseFloat(y.innerHTML)) {
                        shouldSwitch = true;
                        break;
                    }
                }
            }
            else {
                // Check if rows should switch based on ascending or descending order
                if (direction === 'asc') {
                    if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
                        shouldSwitch = true;
                        break;
                    }
                } else if (direction === 'desc') {
                    if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
                        shouldSwitch = true;
                        break;
                    }
                }
            }
        }
        if (shouldSwitch) {
            rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
            switching = true;
        }
    }
}
// Function to generate the table
function generateTableFromJson2(jsonArray, select, addHeaders = true) {
    const style = `
    table {
            width: 100%;
            border-collapse: collapse;
        }

        th, td {
            padding: 8px 12px;
            text-align: left;
            border: 1px solid #ddd;
        }

        tbody tr{
           max-height: 15px;
        }

        th {
            background-color: #f4f4f4;
            color: #000;
        }

        /* Scrollable table wrapper */
        .table-wrapper {
            max-height: 800px;
            overflow-y: auto;
            border: 1px solid #ddd;
        }
        /* Style for dropdowns in header */
        select {
            width: 100%;
            padding: 4px;
            margin-top: 5px;
        }

        /* Style for the sorting arrows */
        .sort-arrows {
            cursor: pointer;
            margin-left: 5px;
        }    
        `;
    createStyleElement("fdiStyle", style);





    // Create table element
    let table = document.createElement('table');

    // Create table header
    let header = table.createTHead();
    let headerRow = header.insertRow(0);

    // Get keys (headers) from the first object in the JSON array
    //let keys = Object.keys(jsonArray[0]);
    let keys = select.split(",");
    if (addHeaders) {
        keys.forEach((key, index) => {
            if (key !== "__metadata") {

                let th = document.createElement('th');
                th.innerHTML = key;

                // Create a dropdown (select) for filtering
                let select = document.createElement('select');

                select.addEventListener('change', function () {
                    const selectedValue = select.value;
                    filterTable(index, selectedValue);
                });

                // Populate dropdown with unique values from the JSON data
                let uniqueValues = [...new Set(jsonArray.map(item => item[key]))];

                // Add a default "All" option for no filter
                let optionAll = document.createElement('option');
                optionAll.value = "";
                optionAll.text = `All`;
                select.appendChild(optionAll);

                // Create an option for each unique value
                if (typeof (uniqueValues[0]) === typeof (1)) {
                    const pp = uniqueValues.sort((a, b) => {
                        if (a < b) {
                            return -1;
                        }
                        if (a > b) {
                            return 1;
                        }
                        return 0;
                    });
                    pp.forEach(value => {
                        let option = document.createElement('option');
                        option.value = value;
                        option.text = value;
                        select.appendChild(option);
                    });
                } else
                    uniqueValues.sort().forEach(value => {
                        let option = document.createElement('option');
                        option.value = value;
                        option.text = value;
                        select.appendChild(option);
                    });
                // Sort arrows for sorting the columns
                let upArrow = document.createElement('span');
                upArrow.innerHTML = '⬆️';
                upArrow.classList.add('sort-arrows');
                upArrow.onclick = () => sortTable(index, 'asc');

                let downArrow = document.createElement('span');
                downArrow.innerHTML = '⬇️';
                downArrow.classList.add('sort-arrows');
                downArrow.onclick = () => sortTable(index, 'desc');

                th.appendChild(select);  // Append the dropdown to the header
                th.appendChild(upArrow);  // Append the dropdown to the header
                th.appendChild(downArrow);  // Append the dropdown to the header
                headerRow.appendChild(th);
            }
        });
    }

    // Create table body and populate rows with data
    let tbody = document.createElement('tbody');
    jsonArray.forEach((item) => {
        let row = tbody.insertRow();
        keys = select.split(",");
        keys.forEach((key) => {
            let cell = row.insertCell();
            if (key !== "__metadata") {
                cell.setAttribute("nowrap", "nowrap");
                if (key === "RoleDefinitionBindings") {
                    cell.appendChild(generateTableFromJson2(item.RoleDefinitionBindings.results, "Name,Id", false));
                } else if (key.indexOf("/") > 0) {
                    cell.innerHTML = item[key.split("/")[0]][key.split("/")[1]]
                } else
                    cell.innerHTML = item[key];  // Insert each value from the JSON into the table cell
            }
        });
    });

    // Append the body to the table
    table.appendChild(tbody);


    return table;
}

function removeSlasches(select, datas) {
    const ret = [];
    const fields = select.split(',');
    for (let i = 0; i < datas.length; i++) {
        const toAdd = {};

        for (let j = 0; j < fields.length; j++) {
            if (fields[j].indexOf('/') > 0) {
                const splitted = fields[j].split('/');
                toAdd[splitted.join('')] = datas[i][splitted[0]][splitted[1]];
            } else
                toAdd[fields[j]] = datas[i][fields[j]];
        }
        ret.push(toAdd);
    }
    console.log("removeSlasches", ret);
    return ret;
}

// Function to get permissions for a SharePoint group
async function getSharePointPermissions(siteUrl, select) {

    // REST API endpoint to get group permissions
    const endpoint = `${siteUrl}`;

    // Fetch options with headers for authentication and response format
    const fetchOptions = {
        method: 'GET',
        headers: {
            'Accept': 'application/json;odata=verbose'
        }
    };

    console.log("endpoint", endpoint);
    // Send the asynchronous GET request to the REST API endpoint
    const response = await fetch(endpoint, fetchOptions);

    // Check if the response is OK (status code 200)
    if (!response.ok) {
        throw new Error(`Error fetching permissions: ${response.statusText}`);
    }

    // Parse the JSON response to extract permission data
    const data = await response.json();

    // Extract role assignments (permissions)
    const roleAssignments = data.d.results;
    console.log('roleAssignments', roleAssignments);
    console.log(JSON.stringify(roleAssignments));

    const D1 = removeSlasches(select, roleAssignments)
    const pattern2 = /\//g;
    console.log("json111", JSON.stringify(D1));
    const table = generateTableFromJson2(D1, select.replace(pattern2, ""));
    // Append the table to the container
    document.getElementById('tableContainer').appendChild(table);
}

// Usage example: Replace 'your-group-id' and 'your-site-url' with actual values
const select = "Member/Title,Member/Id,Member/LoginName,RoleDefinitionBindings";
let siteUrl = 'https://mySite.sharepoint.com/sites/Dev_wf';
siteUrl += `/_api/web/roleassignments/?$expand=RoleDefinitionBindings,Member&$select=${select}`; // Replace with the actual site URL
document.body.innerHTML = `<div id="tableContainer" class="table-wrapper"></div>`; await getSharePointPermissions(siteUrl, select); 

Power Automate Add Field To ContentType (reference Microsoft)

Note the following restrictions on adding a field (column) to a content type using the REST service:

• Site Columns cannot be added to a content type using the REST service.
• You can add a field to a site content type only if the field already exists on the parent content type.
• You can add a field to a content type associated with a list only if the field already exists on the list. To add a completely new field to a list content type, you have to first add it to the list and then add it to the content type in a separate call to the REST service.

Invalid - Must not be used. The value = 0.

Integer - Specifies that the field contains an integer value. The value = 1.

Text - Specifies that the field contains a single line of text. The value = 2.

Note - Specifies that the field contains multiple lines of text. The value = 3.

DateTime - Specifies that the field contains a date and time value or a date-only value. The value = 4.

Counter - Specifies that the field contains a monotonically increasing integer. The value = 5.

Choice - Specifies that the field contains a single value from a set of specified values. The value = 6.

Lookup - Specifies that the field is a lookup field. The value = 7.

Boolean - Specifies that the field contains a Boolean value. The value = 8.

Number - Specifies that the field contains a floating-point number value. The value = 9.

Currency - Specifies that the field contains a currency value. The value = 10.

URL - Specifies that the field contains a URI and an optional description of the URI. The value = 11.

Computed - Specifies that the field is a computed field. The value = 12.

Threading - Specifies that the field indicates the thread for a discussion item in a threaded view of a discussion board. The value = 13.

Guid - Specifies that the field contains a GUID value. The value = 14.

MultiChoice - Specifies that the field contains one or more values from a set of specified values. The value = 15.

GridChoice - Specifies that the field contains rating scale values for a survey list. The value = 16.

Calculated - Specifies that the field is a calculated field. The value = 17.

File - Specifies that the field contains the leaf name of a document as a value. The value = 18.

Attachments - Specifies that the field indicates whether the list item has attachments. The value = 19.

User - Specifies that the field contains one or more users and groups as values. The value = 20.

Recurrence - Specifies that the field indicates whether a meeting in a calendar list recurs. The value = 21.

CrossProjectLink - Specifies that the field contains a link between projects in a Meeting Workspace site. The value = 22.

ModStat - Specifies that the field indicates moderation status. The value = 23.

Error - Specifies that the type of the field was set to an invalid value. The value = 24.

ContentTypeId - Specifies that the field contains a content type identifier as a value. The value = 25.

PageSeparator - Specifies that the field separates questions in a survey list onto multiple pages. The value = 26.

ThreadIndex - Specifies that the field indicates the position of a discussion item in a threaded view of a discussion board. The value = 27.

WorkflowStatus - Specifies that the field indicates the status of a workflow instance on a list item. The value = 28.

AllDayEvent - Specifies that the field indicates whether a meeting in a calendar list is an all-day event. The value = 29.

WorkflowEventType - Specifies that the field contains the most recent event in a workflow instance. The value = 30.

MaxItems - Must not be used. The value = 31.

Power Apps Limit WorkArround 500

The column Id hit type is counter, we cannot use it to filter if we have more than 500 items with "<" ou ">"

But with created we can use delegation with > and <

You can build a pagination with date filtering

App loading

button next

button previous

Clear / Empty Browser Cache and Cookies

//clear bowser cache
self.addEventListener('activate', event => {
  event.waitUntil(
    caches.keys().then(cacheNames => {
      return Promise.all(
        cacheNames.map(cacheName => {
          return caches.delete(cacheName);
        })
      );
    })
  );
});
function deleteAllCookies() {
  const cookies = document.cookie.split(";");

  for (let i = 0; i < cookies.length; i++) {
    const cookie = cookies[i];
    const eqPos = cookie.indexOf("=");
    const name = eqPos > -1 ? cookie.substr(0, eqPos) : cookie;
    document.cookie = name + "=;expires=Thu, 01 Jan 1970 00:00:00 UTC;path=/;";
  }
}

// Call the function to delete all cookies
deleteAllCookies();

Get User Permissions By Mail And Javascript

let siteUrl = "https://mayTenant.sharepoint.com/sites/test"

async function getUserPermissions(siteUrl, userMail) {

    // REST API endpoint to get group permissions
    const endpoint = `${siteUrl}/_api/web/siteusers?$filter=Email eq '${userMail}'&$select=Id`;
    //const endpoint = `${siteUrl}/_api/web/siteusers?$filter=Email eq '${userMail}'&$select=LoginName`;

    // Fetch options with headers for authentication and response format
    const fetchOptions = {
        method: 'GET',
        headers: {
            'Accept': 'application/json;odata=verbose'
        }
    };

    console.log("endpoint", endpoint);
    // Send the asynchronous GET request to the REST API endpoint
    const response = await fetch(endpoint, fetchOptions);

    // Check if the response is OK (status code 200)
    if (!response.ok) {
        throw new Error(`Error fetching permissions: ${response.statusText}`);
    }

    // Parse the JSON response to extract permission data
    const data = await response.json();
    console.log("data", data);

    if (data.d.results.length === 0) {
        console.log("user not found", userMail);
    } else {

        const userId = `${data.d.results[0].Id}`;
        for (const user in data.d.results) {

            console.log("user", userMail);
        }
        let ret = "";
        const endpoint2 = `${siteUrl}/_api/web//RoleAssignments/GetByPrincipalId(${userId})/RoleDefinitionBindings`;
        // Send the asynchronous GET request to the REST API endpoint
        const response2 = await fetch(endpoint2, fetchOptions);
        const data2 = await response2.json();
        console.log("data2", data2);
        for (let i = 0; i < data2.d.results.length; i++) {
            ret += `User ${data2.d.results[0].Name} : ${data2.d.results[0].Description}\r\n`;
        }

        //by groups
        const getGroupsEndPoint = `${siteUrl}/_api/web/GetUserById(${userId})/groups?$select=Id,Title`;
        const response3 = await fetch(getGroupsEndPoint, fetchOptions);
        const data3 = await response3.json();
        console.log("data3", data3);

        for (let i = 0; i < data3.d.results.length; i++) {
            //get group permissions
            const endpoint4 = `${siteUrl}/_api/web//RoleAssignments/GetByPrincipalId(${data3.d.results[i].Id})/RoleDefinitionBindings`;
            const response4 = await fetch(endpoint4, fetchOptions);
            const data4 = await response4.json();
            console.log("data4", data4);
            for (let j = 0; j < data4.d.results.length; j++) {
                ret += `Group ${data3.d.results[i].Title} Id ${data3.d.results[i].Id} : ${data4.d.results[j].Name} : ${data4.d.results[j].Description}. ` + "\r\n";
            }
        }

        return `${ret}.`;
    }
    return null;
}


await getUserPermissions(siteUrl, "test-ext@test.com");

Permissions to Check:

Here are some common permissions encoded in the High and Low values:

• ViewListItems: 0x00000001
• AddListItems: 0x00000002
• EditListItems: 0x00000004
• DeleteListItems: 0x00000008
• OpenItems: 0x00000010
• ViewVersions: 0x00000020
• CancelCheckout: 0x00000040
• ManagePermissions: 0x00010000
• ManageWeb: 0x00040000