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)

// Fonction pour comparer les champs de deux listes SharePoint
async function compareSharePointLists(urlSite1, urlSite2, ListRelativeUrl1, ListRelativeUrl2) {
    // Fonction auxiliaire pour obtenir les champs de la liste
    async function getListFields(urlSite, ListRelativeUrl) {
        const response = await fetch(`${urlSite}/_api/web/lists/getbytitle('${ListRelativeUrl}')/fields?$select=InternalName,TypeAsString`, {
            method: 'GET',
            headers: {
                'Accept': 'application/json;odata=verbose',
            },
        });

        if (!response.ok) {
            throw new Error(`Error fetching fields for list at ${ListRelativeUrl}: ${response.statusText}`);
        }

        const data = await response.json();
        return data.d.results;
    }

    try {
        // Obtenir les champs des deux listes
        const fields1 = await getListFields(urlSite1, ListRelativeUrl1);
        const fields2 = await getListFields(urlSite2, ListRelativeUrl2);

        // Comparer les champs
        fields1.forEach(field1 => {
            const matchingField = fields2.find(field2 => field2.InternalName === field1.InternalName);

            if (!matchingField) {
                console.log(`Le champ ${field1.InternalName} est manquant dans ${ListRelativeUrl2} de ${urlSite2}`);
            } else if (field1.TypeAsString !== matchingField.TypeAsString) {
                console.log(`Le type de champ ${field1.InternalName} ne correspond pas dans ${ListRelativeUrl2} de ${urlSite2} : ${field1.TypeAsString} vs ${matchingField.TypeAsString}`);
            }
        });

        fields2.forEach(field2 => {
            const matchingField = fields1.find(field1 => field1.InternalName === field2.InternalName);

            if (!matchingField) {
                console.log(`Le champ ${field2.InternalName} type ${field2.TypeAsString} est manquant dans ${ListRelativeUrl1} de ${urlSite1}`);
            }
        });
    } catch (error) {
        console.error('Erreur lors de la comparaison des champs des listes :', error);
    }
}

// Example usage
compareSharePointLists(
'https://test.sharepoint.com/sites/DoceboMigration', 
'https://test2.sharepoint.com/sites/fdiSandBox', 
'testConfiguration',
'fdiTasks');

// Function to list all SharePoint groups of a site collection with their permission levels
async function listSharePointGroupsWithPermissions(siteUrl, targetDivId) {
    // Check if the siteUrl and targetDivId are provided
    if (!siteUrl || !targetDivId) {
        console.error("Site URL and target div ID must be provided.");
        // L'URL du site et l'ID de la div cible doivent être fournis.
        return;
    }

    // Construct the REST API URL to get the groups
    const groupsEndpoint = `${siteUrl}/_api/web/sitegroups`;

    try {
        // Fetch the groups from the SharePoint site
        const groupsResponse = await fetch(groupsEndpoint, {
            method: 'GET',
            headers: {
                'Accept': 'application/json;odata=verbose',
            },
        });

        // Check if the response is ok
        if (!groupsResponse.ok) {
            throw new Error(`Error fetching groups: ${groupsResponse.statusText}`);
            // Erreur lors de la récupération des groupes :
        }

        // Parse the JSON response
        const groupsData = await groupsResponse.json();
        const groups = groupsData.d.results;

        // Get the target div element
        const targetDiv = document.getElementById(targetDivId);
        if (!targetDiv) {
            throw new Error(`Target div with ID ${targetDivId} not found.`);
            // Div cible avec l'ID introuvable.
        }

        // Clear the target div
        targetDiv.innerHTML = '';
        
        const permsEndpoint = `${siteUrl}/_api/web/roleassignments?$expand=Member/users,RoleDefinitionBindings`;
        const permsResponse = await fetch(permsEndpoint, {
            method: 'GET',
            headers: {
                'Accept': 'application/json;odata=verbose',
            },
        })
        const permsData = await permsResponse.json();
            const roleAssignments = permsData.d.results;
        if (!permsResponse.ok) {
            throw new Error(`Error fetching roleassignments ${group.Title}: ${permsResponse.statusText}`);
            // Erreur lors de la récupération des utilisateurs pour le groupe
        }
        // Iterate through each group and display its information
        for (const group of groups) {
            // Fetch the role assignments for the group
            

            // Fetch the users in the group
            const usersEndpoint = `${siteUrl}/_api/web/sitegroups(${group.Id})/users`;
            const usersResponse = await fetch(usersEndpoint, {
                method: 'GET',
                headers: {
                    'Accept': 'application/json;odata=verbose',
                },
            });

            // Check if the response is ok
            if (!usersResponse.ok) {
                throw new Error(`Error fetching users for group ${group.Title}: ${usersResponse.statusText}`);
                // Erreur lors de la récupération des utilisateurs pour le groupe
            }

            // Parse the JSON response
            const usersData = await usersResponse.json();
            const users = usersData.d.results;

            // Create a div for the group
            const groupDiv = document.createElement('div');
            groupDiv.className = 'group';

            console.log(group.Title);
            // Create a header for the group
            const groupHeader = document.createElement('h3');
            groupHeader.textContent = `Group: ${group.Title}`;
            // Groupe :
            groupDiv.appendChild(groupHeader);

            // Create a list for the permission levels
            console.log("permissionsList", roleAssignments, group);
            const permissionsList = document.createElement('ul');
            roleAssignments.forEach(roleAssignment => {
                if(roleAssignment.PrincipalId == group.Id){
//Description Name
            roleAssignment.RoleDefinitionBindings.results.forEach(RoleDefinitionBinding => {
                    //RoleDefinitionBindings.results
                    const permissionItem = document.createElement('li');
                    permissionItem.textContent = `Permission Level: '${RoleDefinitionBinding.Name}' ${RoleDefinitionBinding.Description}`;
                    // Niveau de permission :
                    permissionsList.appendChild(permissionItem);
            });
                }
            });
            groupDiv.appendChild(permissionsList);

            // Create a list for the users in the group
            const usersList = document.createElement('ul');
            users.forEach(user => {
                const userItem = document.createElement('li');
                userItem.textContent = `User: ${user.Title}`;
                // Utilisateur :
                usersList.appendChild(userItem);
            });
            groupDiv.appendChild(usersList);

            // Append the group div to the target div
            targetDiv.appendChild(groupDiv);
        }
    } catch (error) {
        // Log the error to the console
        console.error(`Error: ${error.message}`);
        // Erreur :

        // Optionally, display the error message in the target div
        const targetDiv = document.getElementById(targetDivId);
        if (targetDiv) {
            targetDiv.innerHTML = `

Error: ${error.message}

`; //

Erreur : } } } // Example usage await listSharePointGroupsWithPermissions('https://test.sharepoint.com/sites/ssss', 'vpc_WebPart.unknown.a90bc6dc-fba2-4b5c-b7e2-f72005f01a14');

Here are the CSS classes used in JSON column formatting as sp-field-severity:

sp-field-severity--low: This class is used to indicate a low severity level. It typically applies a specific style to represent low urgency or importance1234.

sp-field-severity--good: This class is used to indicate a good or positive status. It applies a style that signifies a favorable condition1234.

sp-field-severity--warning: This class is used to indicate a warning level. It applies a style that signifies caution or a moderate level of urgency1234.

sp-field-severity--severeWarning: This class is used to indicate a severe warning level. It applies a style that signifies a high level of urgency or importance12354.

sp-field-severity--blocked: This class is used to indicate a blocked or critical status. It applies a style that signifies a very high level of urgency or a critical issue12354.

These classes are part of the SharePoint framework and are used to apply consistent styling across different columns based on the severity or status of the data.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField == 'CheckMark', 'sp-field-severity--good', if(@currentField == 'Completed', 'sp-field-severity--good', if(@currentField == 'Forward', 'sp-field-severity--low', if(@currentField == 'Warning', 'sp-field-severity--warning', if(@currentField == 'Error', 'sp-field-severity--severeWarning', if(@currentField == 'ErrorBadge', 'sp-field-severity--blocked', '')))))) + ' ms-fontColor-neutralSecondary'"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "@currentField"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}




CheckMark
Completed
Error
Warning
Forward
ErrorBadge


Add
AddFriend
Admin
Airplane
Alert
AlignCenter
AlignLeft
AlignRight
Archive
ArrowDown
ArrowLeft
ArrowRight
ArrowUp
Attach
Back
Blocked
Bold
Bookmark
Calendar
Camera
Cancel
Checkbox
CheckboxComposite
CheckboxIndeterminate
Checkmark
ChevronDown
ChevronLeft
ChevronRight
ChevronUp
CircleRing
Clear
Clock
Close
Cloud
Code
CollapseMenu
Color
Comment
Contact
Copy
CreditCard
DataUsage
Delete
Dismiss
Document
Edit
Education
Emoji
Error
ErrorBadge
Event
Favorite
Filter
Flag
Folder
Forward
Gift
Globe
Group
Help
History
Home
Important
Info
Italic
Link
List
Location
Lock
Mail
Map
Megaphone
Mention
Message
More
Music
NavigateBack
NavigateForward
News
Note
Open
Paste
Pause
People
Phone
Photo
Pin
Play
Print
Product
Redo
Refresh
Remove
Reply
Save
Search
Send
Settings
Share
Shop
ShoppingCart
SignOut
Sort
Star
Status
Story
Tag
Task
Trash
Undo
Unlock
Upload
User
Video
View
Warning
Work

# Parameters
$siteUrl = "https://xxxx.sharepoint.com/sites/xxxx"
$outputFilePath = "report4.txt"
$doclibpath = "/Connectivity"

# Connect to the SharePoint site
Connect-PnPOnline -Url $siteUrl -UseWebLogin

# Initialize the output file
if (Test-Path $outputFilePath) {
    Remove-Item $outputFilePath
}
New-Item -Path $outputFilePath -ItemType File

# Define the search query to find '.url' files
$query = "Path:$($siteUrl)* And FileExtension:url"
$query = "Path:$($siteUrl)$($doclibpath)* And *.url"

# Execute the search query
$searchResults = Submit-PnPSearchQuery -Query $query -ErrorAction Stop -All -TrimDuplicates $true -SelectProperties "Path,FileName"
Write-Host "nb files found $($searchResults.TotalRows)"
# Check if there are any search results
if ($null -eq $searchResults -or $searchResults.TotalRows -eq 0) {
    Write-Host "No '.url' files found in the search results."
    Exit
}

# Initialize a variable to store combined content
$combinedContent = ""
$count = 0;
# Iterate through each search result
foreach ($result in $searchResults.ResultRows) {# ResultRows PrimarySearchResults
    # Get the file URL 
    $fileUrl = $result.Path
    if(-not $result.Path.ToLower().EndsWith(".url")){
        continue;
    }
    $count++;
    # Get the file content
    $fileContent = $null;
    # $fileContent = Get-PnPFileContent -Url $fileUrl -AsString -ErrorAction Stop
    try {
        $fileContent = Get-PnPFile -Url $fileUrl.Replace("https://eutelsatgroup.sharepoint.com", "") -AsString 
    }
    catch {
        Write-Host "File '$($fileUrl)' error. $($_)"
        continue;
    }
    #$fileContent = Get-PnPFile -Url $fileUrl -AsString
    # Check if the file content is null or empty
    if ([string]::IsNullOrEmpty($fileContent)) {
        Write-Host "File '$fileUrl' is empty or null."
        continue
    }

    # Append the file content to the combined content
    $combinedContent += $result.FileName + "`n"
    $combinedContent += $fileUrl + "`n"
    $combinedContent += $fileContent + "`n`n"
}

# Write the combined content to the output file
Add-Content -Path $outputFilePath -Value $combinedContent

# Disconnect from SharePoint Online
Disconnect-PnPOnline

Write-Host "nb files treated: $($count) Combined content written to '$($outputFilePath)'."

function getUrlParams() {
  const params = new URLSearchParams(window.location.search);
  const urlParams = {};
  for (const [key, value] of params.entries()) {
    urlParams[key] = value;
  }
  return urlParams;
}
async function getSharePointListFields(listId) {
  const apiUrl = `${_spPageContextInfo.webAbsoluteUrl}/_api/web/lists(guid'${listId}')/fields?$select=InternalName&$orderby=InternalName&$filter=Hidden eq false`;

  try {
    const response = await fetch(apiUrl, {
      method: 'GET',
      headers: {
        'Accept': 'application/json;odata=verbose',
        'Content-Type': 'application/json;odata=verbose',
      },
      credentials: 'include', // Include credentials for authenticated requests
    });

    if (!response.ok) {
      throw new Error('Network response was not ok');
    }

    const data = await response.json();
    return data.d.results;
  } catch (error) {
    console.error('Error fetching SharePoint list fields:', error);
    return [];
  }
}
// Usage
const urlParams = getUrlParams();
const listId = urlParams.List;
window.Fields = "";
if (listId) {
  getSharePointListFields(listId).then((fields) => {
    console.log('SharePoint List Fields:', fields);
    // Process the fields as needed
	fields.map((f) => {
	window.Fields += `${f.InternalName}\n`;
		
	});
    console.log(window.Fields);
  });
} else {
  console.error('List ID not found in URL parameters');
}

How to give permission for an Azure app you has permission : Sites.Selected

You must be admin of Azure

In Graph Explorer use this request to get the Azure Id of you Site

Add for permission Sites.selected and Site.FullControl for the account (in modify permissions Tab)

https://graph.microsoft.com/v1.0/sites/m365x87466739.sharepoint.com:/sites/allcompany?$select=id

replace the m365x87466739 by your Tenant name, and allcompany by the name / url of your site, 

it wiil returns you the azure id of your site

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites(id)/$entity",
    "id": "m365x87466739.sharepoint.com,1dc83cb4-d304-4afa-a0ff-cf33270f1c8b,e615f00e-ac05-4dfc-928e-e51688e8273b"
}

Then post a news query https://graph.microsoft.com/v1.0/sites/m365x87466739.sharepoint.com,1dc83cb4-d304-4afa-a0ff-cf33270f1c8b,e615f00e-ac05-4dfc-928e-e51688e8273b/permissions

{
  "roles": ["write"],//permission level you want to give
  "grantedToIdentities": [{
    "application": {
      "id": "9fb5c53a-5f25-4100-ba33-9a4595390c27",//this is you App Id
      "displayName": "AppSharePointDocebo"
    }
  }]
}

then you can use Connect-PnPOnline -Url $siteUrl -ClientId $clientId -Thumbprint $thumbprint -Tenant $tenantId

Use SharePoint Migration Tool to upload your folder in a document library, here base settings in a json file

{
  "Tasks": [
    {
      "SourcePath": "\\\\mySite.fr\\divisions\\Processed matrices\\Doc1",
      "TargetPath": "https://test.sharepoint.com/sites/test1",
      "TargetList": "FDbase",
      "TargetListRelativePath": "Satellites/Doc1",
      "Options": {
        "PreserveFileSharePermissions": false,
        "MigrateHiddenFiles": true,
        "MigrateReadOnlyFiles": true,
        "PreserveLastModifiedTime": true,
        "PreserveCreatedTime": true,
        "SkipEmptyFolders": false
      }
    }
  ]
}

Base hooks context

import { WebPartContext } from "@microsoft/sp-webpart-base";

export interface iMAContext {
    context: WebPartContext;
}

component

import * as React from 'react';
import styles from './MaCountries.module.scss';
import type { IMaCountriesProps } from './IMaCountriesProps';
import { iMAContext } from '../../../entities/iMAContext';
//import { escape } from '@microsoft/sp-lodash-subset';

export const MaCountriesContext = React.createContext({} as iMAContext);
export const MaCountries: React.FunctionComponent = (props: IMaCountriesProps) => {
  
  return (
        <MaCountriesContext.Provider value={{ context: props.context }}><div>
dfvdfv
</div></MaCountriesContext.Provider >
    
  );
};

webpart

import * as React from 'react';
import * as ReactDom from 'react-dom';
import { Version } from '@microsoft/sp-core-library';
import {
  type IPropertyPaneConfiguration,
  PropertyPaneTextField
} from '@microsoft/sp-property-pane';
import { BaseClientSideWebPart } from '@microsoft/sp-webpart-base';
import { IReadonlyTheme } from '@microsoft/sp-component-base';

import * as strings from 'MaCountriesWebPartStrings';
import { MaCountries } from './components/MaCountries';
import { IMaCountriesProps } from './components/IMaCountriesProps';

export interface IMaCountriesWebPartProps {
  description: string;
}

export default class MaCountriesWebPart extends BaseClientSideWebPart {
 private _isDarkTheme: boolean;
  public render(): void {
    const element: React.ReactElement = React.createElement(
      MaCountries,
      {
        description: this.properties.description,
        context: this.context,
        isDarkTheme: this._isDarkTheme
      }
    );

    ReactDom.render(element, this.domElement);
  }

  protected onInit(): Promise {
    return this._getEnvironmentMessage().then(message => {
    });
  }

  private _getEnvironmentMessage(): Promise {
    if (!!this.context.sdks.microsoftTeams) { // running in Teams, office.com or Outlook
      return this.context.sdks.microsoftTeams.teamsJs.app.getContext()
        .then(context => {
          let environmentMessage: string = '';
          switch (context.app.host.name) {
            case 'Office': // running in Office
              environmentMessage = this.context.isServedFromLocalhost ? strings.AppLocalEnvironmentOffice : strings.AppOfficeEnvironment;
              break;
            case 'Outlook': // running in Outlook
              environmentMessage = this.context.isServedFromLocalhost ? strings.AppLocalEnvironmentOutlook : strings.AppOutlookEnvironment;
              break;
            case 'Teams': // running in Teams
            case 'TeamsModern':
              environmentMessage = this.context.isServedFromLocalhost ? strings.AppLocalEnvironmentTeams : strings.AppTeamsTabEnvironment;
              break;
            default:
              environmentMessage = strings.UnknownEnvironment;
          }

          return environmentMessage;
        });
    }

    return Promise.resolve(this.context.isServedFromLocalhost ? strings.AppLocalEnvironmentSharePoint : strings.AppSharePointEnvironment);
  }

  protected onThemeChanged(currentTheme: IReadonlyTheme | undefined): void {
    if (!currentTheme) {
      return;
    }

    this._isDarkTheme = !!currentTheme.isInverted;
    const {
      semanticColors
    } = currentTheme;

    if (semanticColors) {
      this.domElement.style.setProperty('--bodyText', semanticColors.bodyText || null);
      this.domElement.style.setProperty('--link', semanticColors.link || null);
      this.domElement.style.setProperty('--linkHovered', semanticColors.linkHovered || null);
    }

  }

  protected onDispose(): void {
    ReactDom.unmountComponentAtNode(this.domElement);
  }

  protected get dataVersion(): Version {
    return Version.parse('1.0');
  }

  protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {
    return {
      pages: [
        {
          header: {
            description: strings.PropertyPaneDescription
          },
          groups: [
            {
              groupName: strings.BasicGroupName,
              groupFields: [
                PropertyPaneTextField('description', {
                  label: strings.DescriptionFieldLabel
                })
              ]
            }
          ]
        }
      ]
    };
  }
}

hide native CSS

@import '~@fluentui/react/dist/sass/References.scss';

div[data-automation-id="pageHeader"] {
    display: none;
}

Shared css

@import '~@fluentui/react/dist/sass/References.scss';

/* remove this color TODO*/
html,
body {
    background-color: #d2d9dd !important;
}

:global root-148.root-148.root-148.root-148.root-148 {
    background-color: #d2d9dd !important;
}

:global CanvasSection {
    background-color: #d2d9dd !important;
}
//global, hide not decored css or native hoverride
:global .fui-DatePicker__popupSurface {
    background-color: #fff;
}

/* end remove this color TODO*/
$eulightBlue: #007dba;
$eulightDark: #10377A;
$euAlert: #AB0000;
$euGrey: #ECF6FA;
$euligthGrey: #D8E4EA;
$euTextColor: #4C7A8F;
$euAlertOrange: #E38100;
$euOkGreen: #00B400;
$red: #FA505C;

//bold
$fontBoldWeight: 600;
$fontBoldSize: 14px;
//bolder
$fontBolderWeight: 600;
$fontBolderSize: 16px;
//normal
$fontNormalWeight: 500;
$fontNormalSize: 12px;
//small
$fontSmallWeight: 500;
$fontSmallSize: 10px;

.shared {
    .eutclear {
        display: block;
        clear: both;
        line-height: 0;
    }

    .eutclearfix:before,
    .eutclearfix:after {
        display: table;
        clear: both;
        content: '';
    }

    .container {
        // border: 1px solid black;
        width: 100%;
        // margin-right: auto;
        // margin-left: auto;
    }

    @media (min-width: 576px) {
        .container {
            max-width: 540px;
        }
    }

    @media (min-width: 768px) {
        .container {
            max-width: 720px;
        }
    }

    @media (min-width: 992px) {
        .container {
            max-width: 960px;
        }
    }

    @media (min-width: 1200px) {
        .container {
            max-width: 1140px;
        }
    }

    .containerFluid {
        width: 100%;
        padding-right: 15px;
        padding-left: 15px;
        margin-right: auto;
        margin-left: auto;
    }

    .row {
        display: -webkit-box;
        display: -ms-flexbox;
        display: flex;
        -ms-flex-wrap: wrap;
        flex-wrap: wrap;
    }

    .col3 {
        margin: auto;
        -webkit-box-flex: 0;
        max-width: 100%;
        flex: none;
        -ms-flex: none;
    }

    @media (min-width: 576px) {
        .col3 {
            max-width: 100%;
            flex: none;
            -ms-flex: none;
        }
    }

    @media (min-width: 768px) {
        .col3 {
            -webkit-box-flex: 0;
            -ms-flex: 0 0 32%;
            flex: 0 0 32%;
            max-width: 32%;
        }
    }

    @media (min-width: 992px) {
        .col3 {
            -webkit-box-flex: 0;
            -ms-flex: 0 0 31%;
            flex: 0 0 31%;
            max-width: 31%;
        }
    }

    @media (min-width: 1200px) {
        .col3 {
            -webkit-box-flex: 0;
            -ms-flex: 0 0 32%;
            flex: 0 0 32%;
            max-width: 32%;
        }
    }
}

Get all RoleAssignments from a document library even more than 5000 elements

only when HasUniqueRoleAssignments == true

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

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

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


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

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

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

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

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


            if (!isNaN(x.innerHTML)) {

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

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

        tbody tr{
           max-height: 15px;
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


    return table;
}

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

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

async function GetPermissionsInFolder(siteUrl, listUrl1, query) {
    // Fetch options with headers for authentication and response format
    const fetchOptions = {
        method: 'GET',
        headers: {
            'Accept': 'application/json;odata=verbose'
        }
    };

    //get web relativeUrl
    var req = `${siteUrl}/_api/web?$select=ServerRelativeUrl`;
    const webServerRelativUrl = (await (await fetch(req, fetchOptions)).json()).d.ServerRelativeUrl;

    let query1 = "";
    if (`${query}`.trim() !== "") {
        query1 = `&$filter=${query}`;
        query = ` and ${query}`;
    }
    //get firstId
    req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id asc`;
    console.log("req", req);
    const firstId = 0;//(await (await fetch(req, fetchOptions)).json()).d.results[0].Id;
    console.log("firstId", firstId);
    //get lastId
    //req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$filter=Id gt 170 and Id lt 533&$top=1&$orderby=Id desc`;
    req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id desc`;
    console.log("last", req);
    const lastId = (await (await fetch(req, fetchOptions)).json()).d.results[0].Id;
    console.log("lastId", lastId);


    let startId = firstId;
    let endId = firstId + 5000;
    var allItems = [];

    console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
    console.log("query", query);
    do {
        var select = "?$select=FileDirRef,RoleAssignments,HasUniqueRoleAssignments,Id,Title,FileLeafRef,Modified,Created";
        req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId}${query}&$orderby=Id asc&$top=5000`;
        console.log("req", req);
        // Send the asynchronous GET request to the REST API endpoint
        var respList1 = await fetch(req, fetchOptions);
        const items = (await respList1.json()).d.results;

        //get only items with unique permissions
        const withUniquePermissions = items.filter(user => user.HasUniqueRoleAssignments);

        console.log("withUniquePermissions", withUniquePermissions.length);
        allItems.push(...withUniquePermissions);
        startId += 5000;
        endId += 5000;
        console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
    }
    while (endId < lastId);

    return allItems;
}

async function batchFetchPermissions(siteUrl, itemIds, listUrl1) {
    const batchBoundary = "batch_" + new Date().getTime();
    const fetchOptions = {
        method: 'GET',
        headers: {
            'Accept': 'application/json;odata=verbose'
        }
    };
    const webServerRelativUrl = (await (await fetch(`${siteUrl}/_api/web?$select=ServerRelativeUrl`, fetchOptions)).json()).d.ServerRelativeUrl;
    const batchBody = itemIds.map((item) => {
        return `
--${batchBoundary}
Content-Type: application/http
Content-Transfer-Encoding: binary

GET ${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items(${item.Id})/RoleAssignments?$expand=Member,RoleDefinitionBindings HTTP/1.1
`;
    }).join("\n") + `\n--${batchBoundary}--`;
    const digest = await GetDigestValue(siteUrl);//
    //console.log("digest", digest);
    const headers = {
        Accept: "application/json;odata=verbose",
        "X-RequestDigest": digest,
        "Content-Type": `multipart/mixed; boundary="${batchBoundary}"`,
    };

    console.log("itemIds", itemIds.length);
    const response = await fetch(`${siteUrl}/_api/$batch`, {
        method: "POST",
        headers,
        body: batchBody,
    });

    const text = await response.text(); // Parse response manually (multipart)
    return parseBatchResponse(text, itemIds);
}

async function GetDigestValue(siteUrl) {//
    const fetchOptions = {
        method: 'POST',
        headers: {
            'Accept': 'application/json;odata=verbose',
            'Content-type': 'application/json;odata=verbose'
        }
    };

    const response = await fetch(siteUrl + "/_api/contextinfo", fetchOptions);
    return (await response.json()).d.GetContextWebInformation.FormDigestValue;
}


function nodeParser2(xml) {

    var parser = new DOMParser();
    var doc = parser.parseFromString(xml, "application/xml");

    // Define a namespace resolver
    var nsResolver = function (prefix) {
        var ns = {
            'atom': 'http://www.w3.org/2005/Atom',
            'd': 'http://schemas.microsoft.com/ado/2007/08/dataservices',
            'm': 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata',
            'georss': 'http://www.georss.org/georss',
            'gml': 'http://www.opengis.net/gml'
        };
        return ns[prefix] || null;
    };

    // Use the namespace resolver in the XPath evaluation
    var result = doc.evaluate('/atom:feed/atom:entry', doc, nsResolver, XPathResult.ANY_TYPE, null);

    var entries = [];
    var entry = result.iterateNext();
    while (entry) {
        // Check if the entry contains a category with term="SP.User"
        var category = entry.getElementsByTagNameNS('http://www.w3.org/2005/Atom', 'category');
        let userName = "";
        let permissions = [];
        for (var i = 0; i < category.length; i++) {//
            if (category[i].getAttribute('term') === 'SP.User') {
                // Get the d:Email value
                let email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Email');

                if (email.length > 0 && email[0].textContent.trim() !== "") {
                    userName = email[0].textContent;//
                } else {
                    email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Title');
                    userName = email[0].textContent;//
                }
            }
            else if (category[i].getAttribute('term') === 'SP.Group') {
                // Get the d:Email value
                let email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Email');

                if (email.length > 0 && email[0].textContent.trim() !== "") {
                    userName = email[0].textContent;//
                } else {
                    email = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Title');
                    userName = email[0].textContent;//
                }
            }
            else if (category[i].getAttribute('term') === 'SP.RoleDefinition') {
                var Name = entry.getElementsByTagNameNS('http://schemas.microsoft.com/ado/2007/08/dataservices', 'Name');
                if (Name.length > 0) {
                    permissions.push(Name[0].textContent);
                }
            }
        }

        if (userName === "") {
            debugger;
        }
        entries.push({
            "userName": userName,
            "permissions": permissions,
        });
        entry = result.iterateNext();
    }

    //console.log("entries:", entries);
    return entries;
}

// Helper to parse batch responses
function parseBatchResponse(responseText, itemIds) {
    //console.log("responseText", responseText);
    var results = [];
    const parts = responseText.split("--batchresponse");

    let i = 0;
    for (const part of parts) {
        if (part.includes("HTTP/1.1 200 OK")) {
            const xmlStart = part.indexOf("");
            if (xmlStart > -1 && xmlEnd > -1) {
                let xmlString = part.substring(xmlStart, xmlEnd + 8);
                results.push({
                    "item": itemIds[i],
                    "permissions": nodeParser2(xmlString)
                })
                i++;
            }
        }
    }

    return results;
}
function chunkArray(array, chunkSize) {
    let result = [];
    for (let i = 0; i < array.length; i += chunkSize) {
        // Utilise slice pour découper le tableau
        result.push(array.slice(i, i + chunkSize));
    }
    return result;
}


const siteUrl = _spPageContextInfo.webAbsoluteUrl || "https://test.sharepoint.com/sites/BIPvvvv";
const withUniquePermissions = await GetPermissionsInFolder(siteUrl, "Shared%20Documents", "");
//"startswith(FileDirRef, '/sites/BIPvvvv/Shared%20Documents/General/07%20-%20Release%201')"
console.log("withUniquePermissions", withUniquePermissions);
console.log("withUniquePermissions length", withUniquePermissions.length);

const permmissionItemsAll = [];
if (withUniquePermissions.length > 0) {
    const withUniquePermissionsCutted = chunkArray(withUniquePermissions, 30);
    for (let i = 0; i < withUniquePermissionsCutted.length; i++) {
        //debugger;
        const permmissionItems = await batchFetchPermissions(siteUrl, withUniquePermissionsCutted[i], "Shared%20Documents");
        permmissionItemsAll.push(...permmissionItems)

    }
    console.log("permmissionItems", permmissionItemsAll);
}

//FileDirRef,RoleAssignments,HasUniqueRoleAssignments,Id,Title,FileLeafRef,Modified,Created
const toDisplay = [];
let csv = "Id;Title;Modified;Created;UserPermission;Permissions;FileLeafRef;FileDirRef\n";
for (let i = 0; i < permmissionItemsAll.length; i++) {
    const permmissionItem = permmissionItemsAll[i];
    for (let j = 0; j < permmissionItem.permissions.length; j++) {
        for (let k = 0; k < permmissionItem.permissions[j].permissions.length; k++) {
            //debugger;
            const item = {
                "Id": permmissionItem.item.Id,
                "FileDirRef": permmissionItem.item.FileDirRef,
                "HasUniqueRoleAssignments": permmissionItem.item.HasUniqueRoleAssignments,
                "Title": permmissionItem.item.Title,
                "FileLeafRef": permmissionItem.item.FileLeafRef,
                "FileDirRef": permmissionItem.item.FileDirRef,
                "Modified": permmissionItem.item.Modified,
                "Created": permmissionItem.item.Created,
                "UserPermission": permmissionItem.permissions[j].userName,
                "Permissions": permmissionItem.permissions[j].permissions[k]
            }
            csv += `${item.Id};${item.Title};${item.Modified};${item.Created};${item.UserPermission};${item.Permissions};${item.FileLeafRef};${item.FileDirRef}` + "\n";
            toDisplay.push(item);
        }
    }
}

document.body.innerHTML = `<div id="tableContainer" class="table-wrapper"></div>`;

const table = generateTableFromJson2(toDisplay, "Id,Title,Modified,Created,UserPermission,Permissions,FileLeafRef,FileDirRef");

console.log("csv", csv);

// Append the table to the container

document.getElementById('tableContainer').appendChild(table);

navigator.clipboard.writeText(csv);

async function GetPermissionsInFolder(siteUrl, listUrl1, query) {
    // Fetch options with headers for authentication and response format
    const fetchOptions = {
        method: 'GET',
        headers: {
            'Accept': 'application/json;odata=verbose'
        }
    };

    //get web relativeUrl
    var req = `${siteUrl}/_api/web?$select=ServerRelativeUrl`;
    const webServerRelativUrl = (await (await fetch(req, fetchOptions)).json()).d.ServerRelativeUrl;

    let query1 = "";
    if (`${query}`.trim() !== "") {
        query1 = `&$filter=${query}`;
        query = ` and ${query}`;
    }
    //get firstId
    req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id asc`;
    console.log("req", req);
    const firstId = 0;//(await (await fetch(req, fetchOptions)).json()).d.results[0].Id;
    console.log("firstId", firstId);
    //get lastId
    //req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$filter=Id gt 170 and Id lt 533&$top=1&$orderby=Id desc`;
    req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items?$select=Id&$top=1&$orderby=Id desc`;
    console.log("last", req);
    const lastId = (await (await fetch(req, fetchOptions)).json()).d.results[0].Id;
    console.log("lastId", lastId);


    let startId = firstId;
    let endId = firstId + 5000;
    var allItems = [];

    console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
    console.log("query", query);
    do {
        var select = "?$select=FileDirRef,RoleAssignments,HasUniqueRoleAssignments,Id,Title,FileLeafRef,Modified,Created";
        req = `${siteUrl}/_api/web/getlist('${webServerRelativUrl}/${listUrl1}')/items${select}&$filter=Id ge ${startId} and Id lt ${endId}${query}&$orderby=Id asc&$top=5000`;
        console.log("req", req);
        // Send the asynchronous GET request to the REST API endpoint
        var respList1 = await fetch(req, fetchOptions);
        const items = (await respList1.json()).d.results;
        allItems.push(...items);
        startId += 5000;
        endId += 5000;
        console.log(`startId ${startId} endId ${endId} lastId ${lastId}`)
    }
    while (endId < lastId);

    return allItems;
}



const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const items = await GetPermissionsInFolder(siteUrl, "Shared%20Documents", "");
console.log("items", items);
console.log("items length", items.length);

JSON Formatting in SharePoint Online: A Comprehensive Guide

JSON formatting in SharePoint Online enables users to customize the visual presentation of list and library data without requiring complex code. It allows for dynamic styling, icons, calculated values, and even conditional formatting based on field data. In this article, we’ll explore JSON formatting basics, advanced examples, limitations, and best practices.

Microsoft reference

GitHub reference

What is JSON Formatting in SharePoint Online?

JSON formatting is a declarative approach to customize how fields, rows, or views appear in SharePoint lists and libraries. It involves writing JSON code that defines the logic and styling based on field values.

Examples of JSON Formatting

1. Change Background Color Based on Approval Status

If the field "ApprovalStatus" has a value of `"pending"`, set the background color to yellow. JSON Code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": "=if(@currentField == 'pending', '#FFFF00', '')",
    "padding": "5px"
  },
  "txtContent": "@currentField"
}

2. Display an Alert Icon if Cost Equals 100 Add a warning icon next to the Cost field if its value is `100`. JSON Code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "span",
      "txtContent": "@currentField"
    },
    {
      "elmType": "span",
      "style": {
        "margin-left": "10px",
        "color": "red"
      },
      "attributes": {
        "iconName": "Warning"
      },
      "condition": "=if(@currentField == 100, true, false)"
    }
  ]
}

3. Concatenate Two Fields Combine two text fields, FirstName and LastName, into a single formatted output. JSON Code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "='Full Name: ' + [$FirstName] + ' ' + [$LastName]"
}

4. Add 3 Days to a Date Field Display a date that is 3 days later than the value in a field called StartDate. JSON Code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=formatDate(addDays(@currentField, 3), 'yyyy-MM-dd')"
}

Limitations of JSON Formatting

1. Read-Only:

 - JSON formatting is purely visual; it does not change the actual field value or data in the list.

2. Complex Logic:

- While simple conditions are supported, JSON formatting cannot handle complex business logic or external data calls.

3. Limited Interactivity:

- JSON formatting cannot create interactive elements like buttons with advanced event handling.

4. Field Dependencies:

- If a calculated field is hidden in the view, JSON that relies on it may break.

5. Performance Impact:

- Excessive formatting, especially on large lists, may slow down rendering.

Best Practices for JSON Formatting

1. Keep it Simple:

- Avoid overly complex JSON. Keep the code readable and maintainable.

2. Validate JSON:

- Use online JSON validators or tools like VS Code with JSON extensions to catch syntax errors.

3. Optimize for Performance:

- Test formatting on large lists to ensure it doesn’t degrade performance.

4. Use Conditional Logic:

- Use `if` statements sparingly and test all possible conditions.

5. Test Across Browsers:

- Ensure formatting looks consistent across browsers, especially for icons and colors.

6. Document Your JSON:

- Add comments (externally, as SharePoint JSON doesn’t support comments) to document logic and purpose.

Conclusion

JSON formatting in SharePoint Online is a powerful tool to enhance list and library views. By applying dynamic styling and conditional logic, you can improve data visualization and user experience. However, its read-only nature and complexity for advanced scenarios mean it’s best suited for lightweight customizations.

By following best practices and understanding its limitations, you can use JSON formatting effectively in your SharePoint projects.

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

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

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

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

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

	console.log(e);
}

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

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

How to manage SharePoint / Teams permissions

In every Teams, in background there is a SharePoint site.

How to access SharePoint associated with Teams

Browse your Teams files

1.Clic on SharePoint « Settings » icon

2.Click on « Site permissions » link

3.Click « advanced permissions settings »

 

 

Check user / Office 365 group permission

On SharePoint site advanced permissions page you can check user / Office 365 group permissions

SharePoint standard defaults groups

On SharePoint Site creation, by default 3 groups are created

By default, all content on the SharePoint site inherits permissions from the parent SharePoint site.

Allow only a few members or group for a folder

You can set specific permissions in SharePoint / Teams for :

For each person / groups, you can set permission level :

Allow only a few members or group for a folder

By defaut the folder inherits parent folder or library permission, to break permissions and apply new ones :

Create specific permissions (Read / write but not delete)

By default in SharePoint we have 5 permissions levels

You can clic on default permission level to affine them but

 it’s not a good practice

.

To add a new permission level (in site permissions)

Share / give permission to external user

Sharing with external users governance is managed at Tenant level

By default a Teams “private channel” cannot be Shared with externals users / domain 

With public site or channel, your can Share your SharePoint site :

vvvvvx

ccc

let siteUrl = '';


async function getUsersGroups(siteUrl) {

    // 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();
    console.log("data", data);
    let csv = "Title;Email;LoginName;Id;IsSiteAdmin;UserOnly;GroupTitle;GroupId;Owner;Description;AllowMembersEditMembership;AllowRequestToJoinLeave;AutoAcceptRequestToJoinLeave;GroupId;Limited\r\n";
    for (const user of data.d.results) {

        //debugger;
        if (user.Groups && user.Groups.results && user.Groups.results.length > 0) {
            csv += `${user.Title};${user.Email};${user.LoginName};${user.Id};${user.IsSiteAdmin};true;;;;;;;;;NoLimited\r\n`;
            for (const group of user.Groups.results) {
                let limited = "not"
                if (`${group.Title}`.indexOf("imited") > 0) {
                    limited = "Limited";
                }
                csv += `${user.Title};${user.Email};${user.LoginName};${user.Id};${user.IsSiteAdmin};group;${group.Title};${group.Id};${group.OwnerTitle};${group.Description};${group.AllowMembersEditMembership};${group.AllowRequestToJoinLeave};${group.AutoAcceptRequestToJoinLeave};${group.Id};${limited}\r\n`;
            }
        } else
            csv += `${user.Title};${user.Email};${user.LoginName};${user.Id};${user.IsSiteAdmin};noGroup;;;;;;;;;noGroup\r\n`;
    }
    console.log("csv", csv);
}

let query = `/_api/web/siteusers?$select=IsSiteAdmin,Email,Id,LoginName,Title&$expand=Groups`;
await getUsersGroups(siteUrl + query);