sharepoint powershell csom paged caml
  1. Home
  2. Blog
  3. Powershell Csom Paged Caml Query

Powershell Csom Paged Caml Query

0

 


$list = Get-PnPList -Identity $listTitle -ThrowExceptionIfListNotFound

$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 200
Do{
    $stringBuilder = New-Object System.Text.StringBuilder
 
    $stringBuilder.Append("<View Scope='RecursiveAll'>") | Out-Null
    $stringBuilder.Append("<Query><Where></Where>")| Out-Null
    $stringBuilder.Append("<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>")| Out-Null
    $stringBuilder.Append("</Query>")| Out-Null
    $stringBuilder.Append("<ViewFields>")| Out-Null
    $stringBuilder.Append("<FieldRef Name='ID' />")| Out-Null
    $stringBuilder.Append("<FieldRef Name='Title' />")| Out-Null
    $stringBuilder.Append("</ViewFields>")| Out-Null
    $stringBuilder.Append("<RowLimit Paged='TRUE'>$($rowLimit)</RowLimit>")| Out-Null
    $stringBuilder.Append("</View>")| Out-Null
    
    $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $spqQuery.ViewXml = $stringBuilder.ToString();
    $spqQuery.ListItemCollectionPosition = $page


    $pageNumber ++;

    $spqQuery.ViewXml = $stringBuilder.ToString();
    $itemki=$list.GetItems($spqQuery); 
    $spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition

    $ctx.Load($itemki)
    $ctx.ExecuteQuery();


    Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
    Write-Host "processing query results. Recs: $($itemki.Count)"

    $Counter = 0;
    foreach($item in $itemki)
    {
        Write-Host "$($item["ID"]) title pageNumber '$($pageNumber)' : $($item["Title"])"
    }

    $page = $itemki.ListItemCollectionPosition
 }   
 Until($page -eq $null)
 
get script

Sharepoint CSOM powershell

Related posts

PowerApps Functions

On 30/09/2022

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

Getquerystringparam

 

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)

 

In SharepointOnLine

SharePont Keep Modified And Edited

On 17/09/2026 



console.clear();
let mContext = {
    // URL du site courant (contexte de la page SharePoint classique).
    // ⚠️ Limite : si _spPageContextInfo n'existe pas (hors page SP), prévoir une URL de repli (… || "https://…").
    siteUrl: _spPageContextInfo ? _spPageContextInfo.webAbsoluteUrl : "Shared%20Documents",
    query: "Id le 500",// and FSObjType eq 0
    //query: "Id le 500",// and FSObjType eq 0
    doclib: "Shared%20Documents",
    select: "",//"&$select=TotalSize,TotalFileCount,LastModified",
    //select: "?$select=FileDirRef,HasUniqueRoleAssignments,Id,Title,FileLeafRef,Modified,vCreated,FSObjType,ContentTypeId,FileSizeDisplay",
    list: null,
    listFields: null,
    listFieldsNotHidden: null,
    webServerRelativUrl: "/sites/FDI_SandBox",
    fetchGetOptions: {
        method: 'GET',
        headers: {
            'Accept': 'application/json;odata=verbose'
        }
    }
}

// get FormDigestValue
mContext.getRequestDigest = async function () {
    const response = await fetch(`${mContext.siteUrl}/_api/contextinfo`, {
        method: "POST",
        headers: { Accept: "application/json;odata=verbose" },
        credentials: "include",
    });
    const data = await response.json();
    return data.d.GetContextWebInformation.FormDigestValue;
}

mContext.ExecuteQuery = async function ExecuteQuery(req, fetchOptions, maxRetry = 3, wait = 1, trynum = 1) {

    console.log("ExecuteQuery", req, fetchOptions, maxRetry, wait, trynum);
    if (trynum >= maxRetry) {
        console.log("ExecuteQuery Error", req);
        console.log("ExecuteQuery Error", fetchOptions);
        throw new Error(`ExecuteQuery error! maxRetry >= trynum`);
    }
    try {
        let startDate = new Date();
        let diffMinutes = 0;
        respList1 = await fetch(req, fetchOptions);
        endDate = new Date();
        const diffMs = endDate - startDate;
        diffMinutes = Math.floor(diffMs / (1000));
        console.log(`seconds ${diffMinutes} queryNumber  ${trynum}`)

        //avoid 429 too much queries
        if (!respList1.ok && respList1.status == 429) {
            const errorDetails = await respList1.text(); // Get error details from the response
            let err = JSON.parse(errorDetails);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
            await sleep(wait * 10);// * trynum
            return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
        }

        //avoid 503 server unavailable / connections error
        if (!respList1.ok && respList1.status == 503) {
            const errorDetails = await respList1.text(); // Get error details from the response
            let err = JSON.parse(errorDetails);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
            await sleep(wait * 10);// * trynum
            //return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
        }

        //avoid 403 reload page in another tab
        if (!respList1.ok && respList1.status == 403) {
            //debugger;
            window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
            const errorDetails = await respList1.text(); // Get error details from the response
            let err = JSON.parse(errorDetails);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
            await sleep(wait * 10);// * trynum
            window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
            //return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
        }

        

        if (!respList1.ok) {
            console.log("ExecuteQuery Error", respList1);
            const errorDetails = await respList1.text(); // Get error details from the response
            const errorDetails2 = await respList1.json(); // Get error details from the response
            console.log("ExecuteQuery Error", respList1);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
            let err = JSON.parse(errorDetails);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
            console.log(err.error.message.value);
            // Find a validation error returned by SharePoint.
            const validationError = errorDetails2.find(
                function findValidationError(result) {
                    return result.HasException === true;
                }
            );

            // Throw the SharePoint field validation error.
            if (validationError !== undefined) {
                throw new Error(
                    "SharePoint rejected the field update. " +
                    `Field: '${validationError.FieldName}'. ` +
                    `Details: '${validationError.ErrorMessage}'.`
                );
            }
            throw new Error(`HTTP error! Status: ${respList1.status}`);
        }

        return respList1

    } catch (error) {
        console.log("req", req);
        console.log("fetchOptions", fetchOptions);
        console.log(error);
        throw new Error(`HTTP error! Status: ${error}`);
    }
}
function formatDateForValidateUpdateListItem(isoDate) {
    const date = new Date(isoDate);

    if (Number.isNaN(date.getTime())) {
        throw new Error(
            `La date SharePoint reçue est invalide : '${isoDate}'.`
        );
    }

    const pad = value => String(value).padStart(2, "0");

    let hours = date.getHours();
    const minutes = date.getMinutes();

    const ampm = hours >= 12 ? "PM" : "AM";

    // Conversion 24h -> 12h
    hours = hours % 12;
    hours = hours === 0 ? 12 : hours;

    return (
        `${pad(date.getMonth() + 1)}/` +
        `${pad(date.getDate())}/` +
        `${date.getFullYear()} ` +
        `${hours}:` +
        `${pad(minutes)} ${ampm}`
    );
}

// Get a readable error message.
/**
* Change the "Modified By" system field of a SharePoint list item.
*
* Internal SharePoint field name: Editor
*
* Important:
* - listUrl must be the root URL of the list or library.
* - The list must be in the Web identified by mContext.siteUrl.
*
* Examples:
* /sites/FDI_SandBox/Lists/MyList
* /sites/FDI_SandBox/Shared Documents
*
* @param {string} listUrl
* @param {number|string} itemId
* @param {string} userEmail
* @returns {Promise}
*/
mContext.setModifiedBy = async function (
    listUrl,
    itemId,
    userEmail,
    keepDate = true
) {
    // Check the list URL.
    if (
        listUrl === null ||
        listUrl === undefined ||
        String(listUrl).trim() === ""
    ) {
        throw new Error(
            "Variable 'listUrl' is null, undefined or empty."
        );
        // #TODO check fdi
    }

    // Check the item ID.
    if (
        itemId === null ||
        itemId === undefined ||
        String(itemId).trim() === ""
    ) {
        throw new Error(
            "Variable 'itemId' is null, undefined or empty."
        );
        // #TODO check fdi
    }

    // Check the user email.
    if (
        userEmail === null ||
        userEmail === undefined ||
        String(userEmail).trim() === ""
    ) {
        throw new Error(
            "Variable 'userEmail' is null, undefined or empty."
        );
        // #TODO check fdi
    }

    // Check the current SharePoint Web URL.
    if (
        mContext.siteUrl === null ||
        mContext.siteUrl === undefined ||
        String(mContext.siteUrl).trim() === ""
    ) {
        throw new Error(
            "Variable 'mContext.siteUrl' is null, undefined or empty."
        );
        // #TODO check fdi
    }

    // Convert the item ID to a number.
    const normalizedItemId = Number(itemId);

    // Check that the item ID is a positive integer.
    if (
        !Number.isInteger(normalizedItemId) ||
        normalizedItemId <= 0
    ) {
        throw new Error(
            "Variable 'itemId' must be a positive integer. " +
            `Received value: '${itemId}'.`
        );
        // #TODO check fdi
    }

    // Normalize the user email.
    const normalizedUserEmail = String(userEmail)
        .trim()
        .toLowerCase();

    // Perform a basic email format validation.
    const emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

    // Reject an invalid email format.
    if (!emailPattern.test(normalizedUserEmail)) {
        throw new Error(
            "Variable 'userEmail' does not contain a valid email address. " +
            `Received value: '${userEmail}'.`
        );
        // #TODO check fdi
    }

    try {
        // Normalize the current SharePoint Web URL.
        const siteUrl = String(mContext.siteUrl)
            .trim()
            .replace(/\/+$/, "");

        // Convert the provided list URL to an absolute URL object.
        const absoluteListUrl = new URL(
            String(listUrl).trim(),
            window.location.origin
        );

        // Decode the pathname to restore spaces and special characters.
        const decodedListPath = decodeURIComponent(
            absoluteListUrl.pathname
        );

        // Remove the trailing slash from the list URL.
        const listServerRelativeUrl = decodedListPath
            .replace(/\/+$/, "");

        // Check the resulting server-relative URL.
        if (
            listServerRelativeUrl === null ||
            listServerRelativeUrl === undefined ||
            listServerRelativeUrl.trim() === ""
        ) {
            throw new Error(
                "Variable 'listServerRelativeUrl' is null, undefined or empty. " +
                `Received list URL: '${listUrl}'.`
            );
        }

        // Escape apostrophes for the OData string value.
        const escapedListUrl = listServerRelativeUrl.replace(
            /'/g,
            "''"
        );

        // Build the OData alias value.
        const encodedListUrl = encodeURIComponent(
            `'${escapedListUrl}'`
        );
        let oldDate = null;
        //keep the original Modified date if keepDate is true
        if (keepDate) {
            // Get the current Modified date of the list item.
            const ensureUserFetchOptions = {
                method: "GET",
                headers: {
                    "Accept": "application/json;odata=verbose",
                    "Content-Type": "application/json;odata=verbose"
                }
            };

            const getRequestUrl =
                `${siteUrl}/_api/web/GetList(${encodedListUrl})` +
                `/items(${normalizedItemId})?$select=Modified`;
            const date = await mContext.ExecuteQuery(
                getRequestUrl,
                ensureUserFetchOptions);
            const ensureUserData = await date.json();

            console.log("oldDate", ensureUserData.d.Modified);
            oldDate = formatDateForValidateUpdateListItem(ensureUserData.d.Modified);
            console.log("oldDate", oldDate);
        }

        // Get a request digest before performing write operations.
        const requestDigest = await mContext.getRequestDigest();

        // Check the request digest.
        if (
            requestDigest === null ||
            requestDigest === undefined ||
            String(requestDigest).trim() === ""
        ) {
            throw new Error(
                "Variable 'requestDigest' is null, undefined or empty."
            );
            // #TODO check fdi
        }

        // Build the REST endpoint used to register or retrieve the user.
        const ensureUserRequestUrl =
            `${siteUrl}/_api/web/ensureuser`;

        // Build the ensureuser request body.
        const ensureUserRequestBody = {
            logonName: normalizedUserEmail
        };

        // Build the ensureuser request options.
        const ensureUserFetchOptions = {
            method: "POST",
            headers: {
                "Accept": "application/json;odata=verbose",
                "Content-Type": "application/json;odata=verbose",
                "X-RequestDigest": requestDigest
            },
            credentials: "include",
            body: JSON.stringify(ensureUserRequestBody)
        };

        // Ensure that the user exists in the SharePoint site user information list.
        const ensureUserResponse = await mContext.ExecuteQuery(
            ensureUserRequestUrl,
            ensureUserFetchOptions
        );

        // Convert the ensureuser response to JSON.
        const ensureUserData = await ensureUserResponse.json();

        // Extract the SharePoint user object.
        const sharePointUser = ensureUserData?.d;

        // Check the SharePoint user object.
        if (
            sharePointUser === null ||
            sharePointUser === undefined
        ) {
            throw new Error(
                "Variable 'sharePointUser' is null or undefined."
            );
            // #TODO check fdi
        }

        // Check the SharePoint user ID.
        if (
            sharePointUser.Id === null ||
            sharePointUser.Id === undefined
        ) {
            throw new Error(
                "Variable 'sharePointUser.Id' is null or undefined. " +
                `User email: '${normalizedUserEmail}'.`
            );
            // #TODO check fdi
        }

        // Check the SharePoint user login name.
        if (
            sharePointUser.LoginName === null ||
            sharePointUser.LoginName === undefined ||
            String(sharePointUser.LoginName).trim() === ""
        ) {
            throw new Error(
                "Variable 'sharePointUser.LoginName' is null, undefined or empty. " +
                `User email: '${normalizedUserEmail}'.`
            );
            // #TODO check fdi
        }

        // Build the ValidateUpdateListItem endpoint.
        const updateRequestUrl =
            `${siteUrl}/_api/web/GetList(@listUrl)` +
            `/items(${normalizedItemId})` +
            "/ValidateUpdateListItem" +
            `?@listUrl=${encodedListUrl}`;

        // Build the SharePoint person field value.
        const editorFieldValue = JSON.stringify([
            {
                Key: sharePointUser.LoginName
            }
        ]);
        let updateRequestBody = null;
        if (oldDate !== null && keepDate) {
            // Build the update request body.
            updateRequestBody = {
                formValues: [
                    {
                        FieldName: "Editor",
                        FieldValue: editorFieldValue
                    },
                    {
                        FieldName: "Modified",
                        FieldValue: oldDate
                    }
                ],
                bNewDocumentUpdate: true
            };
            console.log("updateRequestBody", updateRequestBody);
        } else {
            updateRequestBody = {
                formValues: [
                    {
                        FieldName: "Editor",
                        FieldValue: editorFieldValue
                    }
                ],
                bNewDocumentUpdate: true
            };
        }

        // Build the update request options.
        const updateFetchOptions = {
            method: "POST",
            headers: {
                "Accept": "application/json;odata=verbose",
                "Content-Type": "application/json;odata=verbose",
                "X-RequestDigest": requestDigest
            },
            credentials: "include",
            body: JSON.stringify(updateRequestBody)
        };

        // Execute the Editor field update.
        const updateResponse = await mContext.ExecuteQuery(
            updateRequestUrl,
            updateFetchOptions
        );

        // Convert the update response to JSON.
        const updateData = await updateResponse.json();

        // Extract the field validation results.
        const validationResults =
            updateData?.d?.ValidateUpdateListItem?.results || [];

        // Find a validation error returned by SharePoint.
        const validationError = validationResults.find(
            function findValidationError(result) {
                return result.HasException === true;
            }
        );

        // Throw the SharePoint field validation error.
        if (validationError !== undefined) {
            throw new Error(
                "SharePoint rejected the field update. " +
                `Field: '${validationError.FieldName}'. ` +
                `Details: '${validationError.ErrorMessage}'.`
            );
            // #TODO check fdi
        }

        // Build the function result.
        const result = {
            success: true,
            listUrl: listServerRelativeUrl,
            itemId: normalizedItemId,
            editor: {
                id: sharePointUser.Id,
                email: sharePointUser.Email,
                loginName: sharePointUser.LoginName,
                title: sharePointUser.Title
            },
            validationResults: validationResults
        };

        // Log the successful operation.
        console.log(
            "The Editor field was updated successfully.",
            result
        );

        // Return the operation result.
        return result;
    } catch (error) {
        // Get a readable error message.
        console.error("Erreur lors de la mise à jour de Modified By :", error);

    }
}


await mContext.setModifiedBy(
    "/sites/FDI_SandBox/Lists/FichierDePaiementDetails",
    2,
    "fdietrich@test.com",
    true
)
★ ★ ★ ★ ★
No ratings yet - be the first to rate this.

Add a comment

Anti-spam