Blog
Power Automate add line to Excel
Copy an Excel file from a template with an Sharepoint Http request
/_api/web/getFileByServerRelativeUrl('/sites/test@{variables('TemplateFileName')}')/copyTo(strNewUrl='/sites/test@{variables('ExportFolder')}/@{variables('ExportFileName')}',bOverWrite=true)
Header
{
"Accept": "application/json; odata=verbose",
"content-type": "application/json; odata=verbose",
"X-HTTP-Method": "PATCH",
"If-Match": "*"
}
Create An Excel Table
Add your Datas in an Excel line
Line :
@{items('Appliquer_à_chacun_2')}
Connect Sharepoint With AppID Certificate
Connect to Sharepoint using app registration and certificate
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Graph;
using PnP.Core.Auth;
using PnP.Core.Auth.Services.Builder.Configuration;
using PnP.Core.Services;
using PnP.Core.Services.Builder.Configuration;
using System.Security.Cryptography.X509Certificates;
using System.Threading.Tasks;
namespace HGH.Buisiness4
{
public class SPTools2 : IDisposable
{
private IHost _host;
public SPTools2()
{
}
public async Task Connect(string appId, string tenantId, string siteUrl, string thumbprint, StoreName storeName, StoreLocation storeLocation)
{
try
{
_host = Host.CreateDefaultBuilder()
.ConfigureServices((hostingContext, services) =>
{
// Add the PnP Core SDK library
services.AddPnPCore(options =>
{
options.Sites.Add("SiteToWorkWith", new PnPCoreSiteOptions
{
SiteUrl = siteUrl
});
});
services.AddPnPCoreAuthentication(
options =>
{
// Configure an Authentication Provider relying on Windows Credential Manager
options.Credentials.Configurations.Add("x509certificate",
new PnPCoreAuthenticationCredentialConfigurationOptions
{
ClientId = appId,
TenantId = tenantId,
X509Certificate = new PnPCoreAuthenticationX509CertificateOptions
{
StoreName = storeName,
StoreLocation = storeLocation,
Thumbprint = thumbprint
}
});
// Configure the default authentication provider
options.Credentials.DefaultConfiguration = "x509certificate";
// Map the site defined in AddPnPCore with the
// Authentication Provider configured in this action
options.Sites.Add("SiteToWorkWith",
new PnPCoreAuthenticationSiteOptions
{
AuthenticationProviderName = "x509certificate"
});
});
})
// Let the builder know we're running in a console
.UseConsoleLifetime()
// Add services to the container
.Build();
await _host.StartAsync();
}
catch (Exception ex)
{
Console.WriteLine($"ERROR SPTools2 appId {appId} siteUrl {siteUrl} thumbprint {thumbprint} {ex}");
throw new Exception($"ERROR SPTools2 appId {appId} siteUrl {siteUrl} thumbprint {thumbprint}", ex);
}
return 1;
}
public async Task LoadWeb()
{
try
{
// Optionally create a DI scope
using (var scope = _host.Services.CreateScope())
{
// Obtain a PnP Context factory
var pnpContextFactory = scope.ServiceProvider
.GetRequiredService();
// Use the PnP Context factory to get a PnPContext for the given configuration
using (var context = await pnpContextFactory.CreateAsync("SiteToWorkWith"))
{
// Retrieving web with lists and masterpageurl loaded ==> SharePoint REST query
var web = await context.Web.GetAsync(p => p.Title, p => p.Lists,
p => p.MasterUrl);
Console.WriteLine($"Web Title {web.Title} Loaded");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"ERROR LoadWeb{ex}");
throw new Exception($"ERROR LoadWeb ", ex);
}
return 1;
}
public void Dispose()
{
if (_host != null)
_host.Dispose();
}
}
}
Powerautomate get folder by url
/_api/web/GetFolderByServerRelativeUrl('@{variables('WebserverRelativeUrl')}/@{variables('DoclibUrl')}/Fiche_@{variables('FicheId')}')Verify status (exists = 200, does not exists = 404)
@{outputs('HttpGetFolder')['statusCode']}
{
"Accept": "application/json; odata=verbose",
"content-type": "application/json; odata=verbose" "If-Match": "*"
}
Create folder
/_api/web/folders/add('@{variables('DoclibUrl')}/Fiche_@{variables('FicheId')}')
In Powershell
Powershell Import Export Fields
Write-Output 'Connexion Portail'
# Connect-PnPOnline -Url $siteUrl -Tenant $aadDomain -Thumbprint $certifThumbprint -ClientId $appId
function ExportFieldsToCsv {
Param(
[string]$listTitle,
[string]$csvFilename,
[boolean]$includeHidden = $false
)
#$list = Get-PnPList -Identity $list
if ([string]::IsNullOrEmpty($listTitle)) {
$fields = Get-PnPField
$aFieldToExports = @();
for ($i = 0 ; $i -lt $fields.Length ; $i++) {
$field = $fields[$i];
$aFieldToExport = New-Object -TypeName PSObject -Property @{
'Title' = $field.Title;
'InternalName' = $field.InternalName;
'Id' = $field.Id;
'TypeAsString' = $field.TypeAsString;
'Hidden' = $field.Hidden;
'StaticName' = $field.StaticName;
'Required' = $field.Required;
'Description' = $field.Description;
'TypeDisplayName' = $field.TypeDisplayName;
'Group' = $field.Group;
}
$aFieldToExports += $aFieldToExport
$aFieldToExports | Export-Csv -Path "$($csvFilename)" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
}
}
else {
<# Action when all if and elseif conditions are false #>
}
}
function ExportFieldsToXml {
Param(
[string]$listTitle,
[string]$xmlFilename,
[boolean]$includeHidden = $false
)
#$list = Get-PnPList -Identity $list
if ([string]::IsNullOrEmpty($listTitle)) {
$fields = Get-PnPField
$stringBuilder = New-Object System.Text.StringBuilder
$null = $stringBuilder.AppendLine("")
for ($i = 0 ; $i -lt $fields.Length ; $i++) {
$field = $fields[$i];
$null = $stringBuilder.AppendLine($field.SchemaXml);
}
$null = $stringBuilder.AppendLine("")
$stringBuilder.ToString() | Out-File -FilePath "$($xmlFilename)" -Encodin:UTF8 -Append
}
}
function ImportFields {
Param(
[string]$listTitle,
[string]$xmlFilePath,
[boolean]$includeHidden = $false
)
[XML]$xmlfile = Get-Content -Path $xmlFilePath -Encoding:UTF8
if ([string]::IsNullOrEmpty($listTitle)) {
foreach ($field in $xmlfile.fields.Field) {
Write-Host $field.Name
$exists = Get-PnPField -Identity $field.Name -ErrorAction:SilentlyContinue
if ($null -eq $exists) {
Write-Host "$($field.Name) is null"
Add-PnPFieldFromXml -FieldXml $field.OuterXml
Write-Host "$($field.Name) added"
}
else {
Write-Host "$($field.Name) exists"
}
}
}
else {
foreach ($field in $xmlfile.fields.Field) {
$siteField = Get-PnPField -Identity $field.Name -ErrorAction:SilentlyContinue
$listField = Get-PnPField -Identity $field.Name -List $listTitle -ErrorAction:SilentlyContinue
if ($null -eq $siteField -and $null -eq $listField) {
Write-Host "$($field.Name) is null"
Add-PnPFieldFromXml -FieldXml $field.OuterXml
Add-PnPField -List $listTitle -Field $field.Name
Write-Host "$($field.Name) added"
}
elseif($null -eq $listField){
Write-Host "$($field.Name) exists"
Add-PnPField -List $listTitle -Field $siteField.InternalName
Write-Host "$($field.Name) added to $($listTitle)"
}
}
}
}
Try catch in power automate
{
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"startTime": {
"type": "string"
},
"endTime": {
"type": "string"
},
"trackingId": {
"type": "string"
},
"clientTrackingId": {
"type": "string"
},
"clientKeywords": {
"type": "array",
"items": {
"type": "string"
}
},
"code": {
"type": "string"
},
"status": {
"type": "string"
},
"error": {
"type": "object",
"properties": {
"code": {
"type": "string"
},
"message": {
"type": "string"
}
}
}
},
"required": [
"name",
"startTime",
"endTime",
"trackingId",
"clientTrackingId",
"clientKeywords",
"code",
"status",
"error"
]
}
}
{
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"type": {
"type": "string"
},
"location": {
"type": "string"
},
"tags": {
"type": "object",
"properties": {
"flowDisplayName": {
"type": "string"
},
"environmentName": {
"type": "string"
},
"logicAppName": {
"type": "string"
},
"environmentWorkflowId": {
"type": "string"
},
"xrmWorkflowId": {
"type": "string"
},
"environmentFlowSuspensionReason": {
"type": "string"
},
"sharingType": {
"type": "string"
}
}
},
"run": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"type": {
"type": "string"
}
}
}
}
}
concat('https://emea.flow.microsoft.com/manage/environments/', body('Analyser_JSON')?['tags']?['environmentName'], '/flows/', body('Analyser_JSON')?['name'], '/runs/', body('Analyser_JSON')?['run']?['name'])
a href="@{outputs('LinkToWfHisto')}">@{workflow()?['tags']?['flowDisplayName']} a
formatDateTime('2009-06-15T13:45:30', 'M/dd/yyyy h:mm tt') 6/15/2009 1:45 PM
formatDateTime('2009-06-15T13:45:30', 'dd/MM/yyyy') 15/06/2009
formatDateTime('2009-06-15T13:45:30', 'd/M/yyyy') 15/6/2009
formatDateTime('2009-12-15T13:45:30', 'dddd/MMMM/yyyy') Tuesday/December/2009
formatDateTime('2009-12-15T13:45:30', 'ddd/MMM/yyyy') Tue/Dec/2009
Concat
concat(outputs('Compose'),'test')
Get user mail in REST results
items('Appliquer_à_chacun')?['MyUser']?['EMail']
Add 10 Days
addDays(utcNow(),10)
Get JSON data
body('parameterJSON')?['Relaunch1nbDays']
body('myGet')?['value']
Get variable value
variables('RelaunchDate1')
Check data is empty
if(empty(variables('userMail')), null, variables('userMail'))
Date formatting
formatDateTime(your-value, 'dd/MM/yyyy hh:mm tt')
image base64 to <img>
Get_file_content_using_path_2')['$content'] //the $ is important
<img class="logo" src="data:image/jpeg;base64, @{body('Get_file_content_using_path_2')['$content']}" alt="My Image" />
Add an edit button, in field json formatting
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"children": [
{
"elmType": "span",
"style": {
"padding-right": "8px"
},
"txtContent": "@currentField.title"
},
{
"elmType": "a",
"style": {
"text-decoration": "none"
},
"attributes": {
"iconName": "Edit",
"class": "sp-field-quickActions",
"target": "_blank",
"href": {
"operator": "+",
"operands": ["www.source=portal&screenColor=rgba%280%2C+176%2C+240%2C+1%29&skipAppMetadata=true&ID=",
"[$ID]"
]
}
}
}
]
}