Powerautomate Caml Query

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)

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

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

function sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
}

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

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

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

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

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

        if (!respList1.ok) {
            console.log("ExecuteQuery Error", respList1);
            const errorDetails = await respList1.text(); // Get error details from the response
            console.log("ExecuteQuery Error", respList1);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
            let err = JSON.parse(errorDetails);
            console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
            console.log(err.error.message.value);
            throw new Error(`HTTP error! Status: ${respList1.status}`);
        }

        return respList1

    } catch (error) {
        console.log(error);
        throw new Error(`HTTP error! Status: ${error}`);
    }
}
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const fetchOptions = {
    method: 'GET',
    headers: {
        'Accept': 'application/json;odata=nometadata'
    }
};

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

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

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