How to get raw data from PFX certificates?

Summary

This article will show the step-by-step PowerShell commands to read the raw data from the certificate.

Prerequesite is you need to create a sample PFX file. It is very easy to create such files using the PnP Powershell module.

## create C:\Certs and run the following command.

New-PnPAzureCertificate -OutPfx wildcard.pfx -OutCert wildcard.cer -CertificatePassword (ConvertTo-SecureString -String "pass@word1" -AsPlainText -Force)
# the following command will be used to read the PFX file and get the cert value.

$pfxPath = "C:\Certs\wildcard.pfx"
$pfxPass = "pass@word1"
$stsCertificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 $pfxPath, $pfxPass, 20
$binCert = $stsCertificate.GetRawCertData()
$credValue = [System.Convert]::ToBase64String($binCert)

Now using the above $credValue you can use to connect to the PnP Online. Assuming you have created an Azure AD app, added the required permissions, and added the above-created cert to the app.


$HashArgs = @{
    Url = "https://contoso-admin.sharepoint.com"
    ClientId = "GUID-OF-AAD-APP"
    CertificateBase64Encoded = $certValue
    Tenant  = "contoso.sharepoint.com
}
$conn = Connect-PnpOnline @HashArgs -ReturnConnection

Conclusion

An easy and simple way to securely connect to the SPO tenant or site using PnP

Posted in PnP.PowerShell, SharePoint, Technical Stuff | Leave a comment

Application Lifecycle Management (ALM) for Power Platform (PP) 2 of 3

Application Lifecycle Management (ALM) for Power Platform (PP) 1 of 3

Application Lifecycle Management (ALM) for Power Platform (PP) 3 of 3

You will have two build pipelines and one release pipeline.

1. Create two build pipelines

Follow the following steps to create a pipeline.

Click on Newpiple

Select the “Use the classic editor” link

Select Azure Repos Git. Select the Team project, Repository, and branch main.

Select “Empty Job”

Name the pipeline

2. Add SolutionName as Build variable

Set the variable as SolutionName for build and release pipelines.
Select “Selectable at queue time” and “Selectable at release time”

3. Create “Export Dev Solution” Build pipeline

Export Dev Solution Build pipeline

Please make sure you have “Allow scripts to access the OAuth token” is checked for the Agent Job.

** Use the values from here to put in the actions input **

Power Platform Export Solution 
Solution Name : $(SolutionName)
Solution Output File : $(Build.ArtifactStagingDirectory)\$(SolutionName).zip
UNSELECT EXPORT AS MANAGE SOLUTION

Power Platform Unpack Solution 
Solution Input File = $(Build.ArtifactStagingDirectory)\$(SolutionName).zip
Target Folder to Unpack Solution : $(Build.SourcesDirectory)\$(SolutionName)

Command Line Script

Display name: Command Line Script

Script :
echo commit all changed
git config user.email "pankaj_surti@hotmail.com"
git config user.name "Automatic Build"
git checkout main
git add --all
git commit -m "solution init"

echo push code to new repo
git -c http.extraheader="AUTHORIZATION:bearer $(System.AccessToken)" push origin main

NOTE: 
To make the above script work you must give the contribute allow access to the Build service under project settings. 
Please refer to 1 of 2 (Prerequisites) step # 6 "Add Build Service as Contribute Allow permission for the new repository"

4. Create “Build QA Environment FilesBuild Pipeline

Build QA Environment Files
Power Platform Pack Solution 

Source Folder of Solution to Pack = $(Build.SourcesDirectory)\$(SolutionName)
Solution Output File = $(Build.ArtifactStagingDirectory)\$(SolutionName).zip
Type of Solution : Unmanaged

Power Platform Import Solution 
Solution Input File = $(Build.ArtifactStagingDirectory)\$(SolutionName).zip

Power Platform Export Solution 
Solution Name : $(SolutionName)
Solution Output File : $(Build.ArtifactStagingDirectory)\$(SolutionName)_managed.zip
SELECT EXPORT AS MANAGE SOLUTION

Publish Artifact: drop

Path to publish = $(Build.ArtifactStagingDirectory)
Artifact name = drop
Artifact publish location = Azure Pipelines


Reference: Predefined variables – Azure Pipelines | Microsoft Docs

5. Create Release to production

To create release pipeline follow the steps.

Click on Pipelines->Rlease.

Click on “New pipeline”

Select “Empty Job”

Select “Add an artifact”

Select Project.

Select Source (build pipeline) as “Build QA Environments”

Select Default version as “Latest”

Type Source alias as “Build”

Add an artifact

Now select Stage 1 and add two shown actions.

Release to production
Power Platform Import Solution 
Service connection: PROD


Solution Input File = $(System.DefaultWorkingDirectory)/Build/drop/$(SolutionName)_managed.zip

The following code can be used in your Power Apps to extract the environment variable.

UpdateContext(
    {
        txtEnvVar: LookUp(
            'Environment Variable Values',
            'Environment Variable Definition'.'Schema Name' = "new_RuntimeEnvironment"
        ).Value
    }
)

To Change the environments variables for the managed solution.

The managed solution will be in locked mode and you will not be able to change the environment variables. The following are the steps to change the environments variables.

Navigate to the PROD environments using Power Platform Admin Center

Click on the Tables under Dataverse on the left side menu

Search for ‘Environment Variable Value’

‘Environment Variable Value’ table

Click on the ‘Environment Variable Value’ table and navigate to the ‘Data’ tab (as shown below)

Click on the Data tab
Click on Edit to change the value and click Save & Close

For more details follow the following lab materials.

Microsoft Power Platform Build Tools for Azure DevOps


Microsoft Power Platform Build Tools for Azure DevOps – Power Platform | Microsoft Docs

Application Lifecycle Management (ALM) for Power Platform (PP) 3 of 3

Posted in Power Apps, Power Apps, Power Automate | 1 Comment

Application Lifecycle Management (ALM) for Power Platform (PP) 1 of 3

This is a three-part series of blog articles. Part1 (this one) article covers prerequisites to set up ALM in Azure DevOps. Part2 article covers the Power Platform Build Tools to build and deploy the solution. Part3 article covers the  Microsoft Power Platform CLI to build and deploy the solution.

For both Part2 or Part3 approaches, you will need the following steps completed as a prerequisite. From numbers 1 to 5 you will need as a prerequisite for both approaches.

If you are following the Part2 approach you will additionally need numbers 6 and 7 as a prerequisite.

Prerequisites

1. Create a free Azure DevOps subscription.

Follow this article to get a free five-user Basic Plan Azure Subscription.

Azure DevOps Basic Plan is free for up to 5 users

2. Create three Power Platform Environments for DEV, PRE PROD, and PROD.

The three power platform environments.

3. Create an Azure AD app

Create an Azure AD app with the permissions

Need following delegated permissions and consented by Global Admin

  • Dynamic CRM / user_impersonation / Access Common Service as organization users
  • PowerApps Runtime Service / user_impersonation / Common Data Service
  • PowerApps.Advisor / Analysis.All

Note: Save the ClientId, ClientSecret & TenantId information of the Azure AD app. You will need this later.

4. Add Application users to the Power Platform environment

Navigate to Power Platform Admin Center using https://aka.ms/PPAC

Click on the environment name. Click on Settings

Select Users + permissions and click on Application users

Click on New app user then click on Add an app

Select the Azure AD app created from the above step. Click Add and next screen click Create.

Add application users to Power Platform Environments

Using the above created Azure AD app add the application user for the Power Platform. Set the role as System Administrator. This application user will be used in the automation of the build and release pipelines.

5. Add Build Service as Contribute Allow permission for the new repository

Click on Project Settings

Click on Repositories under Repos

Select the repository in this case CRM446838

Select Security tab

Select the “{Project name} Build Service (pankajsurti)” Note: your project name and user name will be different.

Select Contribute as Allow/

Add Contribute allow permission for the repository

6. Add Power Platform Build Tools extension for Azure DevOps. (Part2)

NOTE: ONLY needed for PART2.

Power Platform Build Tools

7. Add Service Connection in Azure DevOps (Part2)

NOTE: ONLY needed for PART2.

Click on Project Settings

Click on Service connections under Pipelines

Click on Create service connection

Select “Power Platform” and click Next

Add Service URL, Tenant ID, Application ID, Client secret of Application ID, and provide a name of the service connection such DEV, PREPROD or PROD depending on whatever environment URL you are connecting to.

Add service connection

Conclusion

This completes this blog post. Once executing prerequisites you can refer to Part2 or Part3 depending on what tool you want to use for your need.

The ALM is documented in detail here.

Posted in Power Apps, Power Apps, Power Automate | 1 Comment

How to find {sitesId} to pass to MS Graph API call?

Summary

SharePoint related MS Graph API call requires {sitesid}, for example for the following API call we need {sitesId}

Create permission – Microsoft Graph v1.0 | Microsoft Docs

POST /sites/{sitesId}/permissions

Solution

Login to your SharePoint site. Go to Graph Explorer, https://aka.ms/GE, enter the following request call, assuming tenant as m365x162783 and site as Test1.

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

You will get a response something like the following.

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites(id)/$entity",
    "id": "m365x162783.sharepoint.com,2e0ad342-19b2-4116-b6a5-2dc4867cb498,0efde304-9646-43d8-8eee-5b1a55ab17f1"
}

What you get back in the id is in this format:

{hostname},{spsite.id},{spweb.id}
The Graph Explorer request-response

Conclusion

This is a simple way to get this sites-id information for any future MS Graph API calls if you may want to explore using PostMan or RESTClient.

However, if you want to make this call in your code you will need an access token and make a similar call as above to get the information.

$tenantPrefix = "m365x162783";
$clientId = "Client-ID";
$clientSecret = "Client-Secret";
$tenantName = $tenantPrefix +".onmicrosoft.com";
$tenantDomain = $tenantPrefix +".sharepoint.com";

#Provide site url
$sitePath = "https://m365x162783.sharepoint.com/sites/Test1"
$siteName = $sitePath.Split("/")[4]

# use splatting
$ReqTokenBody = @{
Grant_Type    = "client_credentials"
Scope         = "https://graph.microsoft.com/.default"
client_Id     = $clientID
Client_Secret = $clientSecret
} 
$TokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$TenantName/oauth2/v2.0/token" -Method POST -Body $ReqTokenBody

$apiUrl = 'https://graph.microsoft.com/v1.0/sites/'+ $tenantDomain +':/sites/'+ $siteName +'?$select=id,displayName'
try {
  $spoResult = Invoke-RestMethod -Headers @{Authorization = "Bearer $($Tokenresponse.access_token)"} -Uri  $apiUrl -Method Get 
  Write-Host "Site:" $spoResult.displayName
}
catch {
  Write-Output "Failed to enumerate the site"
  Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__ 
  Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription 
  Exit
}    

# NOTE THE SITES-ID is passed for the next call.
$apiUrl = 'https://graph.microsoft.com/v1.0/sites/'+ $spoResult.id +'/permissions'
Posted in MS Graph, SharePoint | 2 Comments

How to add custom banner to alert “SharePoint 2010 workflow retirement” to site users and owners?

Summary

The SharePoint 2010 workflow will retire. Microsoft announced it, see the following link.

SharePoint 2010 workflow retirement

For my customer, even after multiple messages to the Admins and Owners there was not an immediate action taken by the owners. To alert ‘SharePoint 2010 workflow retirement’ to all the identified sites my customer wants to add a banner similar to the following.

Banner for site with SharePoint 2010 Workflow

Solution

Prerequisite

  1. You should have the PnP PowerShell installed.
  2. You should have the SPFx development environment to create the .sppkg SPFx package.
  3. You should have configure and consented for PnP PowerShell on your tenant. Please follow this article.

Step # 1 : Create and install the SPFX application customizer for the modern site or page

Please click here to go to the GitHub Repo for the SPFx Application Customizer Extension.

Step # 2 : Add the JavaScript Link for the classic page or site

The JSLink file will allow to get the messages on the classic site or page.

https://github.com/pankajsurti/sp2010-alert-banner-app-extention/blob/master/DeploymentScript/AddSP2010BannerMessage.js

Create a folder c:\SP2010RetirementBanner copy the above JSLink file.

Step # 3 : Using the PnP PowerShell script add the banner for the list of sites in CSV file.

Copy the Process-AddRemoveBanner.ps1 in c:\SP2010RetirementBanner.

Create site2process.csv file in c:\SP2010RetirementBanner folder with the header as Url. Add your tenant’s site URL per line.

From c:\ SP2010RetirementBanner, run the following PowerShell command using

https://github.com/pankajsurti/sp2010-alert-banner-app-extention/blob/master/DeploymentScript/Process-AddRemoveBanner.ps1

# make sure the PS1 and js files are in the same directory i.e. c:\SP2010RetirementBanner

.\Process-AddRemoveBanner -ActionType "Add"

Step # 4 : If SP2010 workflow are removed, using the PnP PowerShell script Remove the banner for the list of sites in CSV file.

Run the following PowerShell command using

https://github.com/pankajsurti/sp2010-alert-banner-app-extention/blob/master/DeploymentScript/Process-AddRemoveBanner.ps1

# make sure the PS1 and js files are in the same directory i.e. c:\SP2010RetirementBanner

.\Process-AddRemoveBanner -ActionType "Remove"

Conclusion

This approach is only an awareness to the end users to think about the issue of the SP2010 workflow will retire soon so they can migrate to modern workflow using Power Automate.

Posted in SharePoint, SharePoint 2010, SharePoint 2013, PnP.PowerShell | Leave a comment

Error loading debug manifests (debugManifestsFile)

Summary

I was creating a simple hello world application customizer using the following documentation.

Build your first SharePoint Framework Extension (Hello World part 1)

After following the steps and I tried to debug I got the following error.

When I searched for the solution on the web I saw Vesa mentioned with a tip that the web server may not be running. I did run “gulp server” and it should run the local web server.

I did more search and I got some suggested posts to delete the browser cache, log out from the SPO site.

Nothing worked.

Resolution

I went back to the documentation step by step, I have missed the following step.

gulp trust-dev-cert

For more details follow Trusting the self-signed developer certificate

Trusting the self-signed developer certificate

Posted in SharePoint, SharePoint 2010, SharePoint 2013 | Leave a comment

How to associate full screen Power Apps Canvas App with SharePoint List?

Summary

To customize a SharePoint list or library form by using Power Apps is well documented. You can visit this link to see the details. But the issue with this approach is the form does not work as the full-screen mode app. You may want to create a blank canvas app in full-screen tablet mode.

If you want to associate your existing full-screen canvas app with the SharePoint list, please read further…

Step By Step Solution

The solution is very simple, you still need to create a custom form as mentioned in the above link. You will change the SharePointIntegration Controls OnNew, OneEdit, and OnView properties to launch your full-screen canvas app. You would use the Launch method.

Step # 1 Get the Launch URL for your Full-Screen Canvas app. Follow here

Step # 2 In your customize app go to SharePointIntegration Control. Modify OnNew with the code as defined below.

Note: there are four query strings passed to the launch.

hidenavbar -> is to hide the navigation bar for your full-screen app.

SelectedListItemID -> This will be used in the full-screen app to navigate used to NewForm, EditForm or ViewForm

Env and AppVer -> These are optional if you want to pass some environment or version information. You can skip if you like.

SharePointIntegration Control’s OnNew property
//NewForm(SharePointForm1)
Launch(
    "/providers/Microsoft.PowerApps/apps/002a5c25-0b35-4d7d-89c7-653f8c0d1ea6",
    "hidenavbar",
    "true",
    "SelectedListItemID",
    "-1",
    "Env",
    varEnvironment,
    "AppVer",
    varAppVersion
);
RequestHide();

Step # 3 In your customize app go to SharePointIntegration Control. Modify the “OnEdit” event with the code as defined below.

Everything is same as the Step # 2 except the SelectedListitemID property is different.

SharePointIntegration control’s OnEdit property.
//EditForm(SharePointForm1)
Launch(
    "/providers/Microsoft.PowerApps/apps/002a5c25-0b35-4d7d-89c7-653f8c0d1ea6",
    "hidenavbar",
    "true",
    "SelectedListItemID",
    SharePointIntegration.SelectedListItemID,

    "EditMode",
    "yes",
    "Env",
    varEnvironment,
    "AppVer",
    varAppVersion    
);
RequestHide();

**** NOTE for OnView event ****
Repeat the above code for the OnView with "EditMode" as 'no' or just don't pass it, the default can be coded as 'no'

Step # 4 Modify the OnStart Event of your Full-Screen App.

OnStart event for the Full-Screen Canvas App
// Read all query string parameters
Set (
    varSelectedListItemID,
    Value(Param("SelectedListItemID"))
);


Set(
    varEditMode,
    Coalesce(
        Param("EditMode"),
        "No"
    )
);
// These variables are optional, you can use to display the version and env.
Set(
    varAppVersion,
    Coalesce(
        Param("AppVer"),
        "17.0"
    )
);
Set(
    varEnvironment,
    Coalesce(
        Param("Env"),
        "Development"
    )
);

Step # 5 Act on the value of the SelectedListItemID property in the full-screen canvas app. The following code is still in the OnStart event. This will make SharePoint List record new or edit based on the varSelectedListItemId.

/// You need to change your DataSource to your SharePoint List
// If the ID is greater than zero and editmode is yes then just
//      get the record for that ID
// If ID is -1 means user clicked on New
If (
    varSelectedListItemID > 0 && Lower(vaEditMode) = "yes",
    Set(
        varCurrentlySelectedRecord,
        LookUp(
            [[[ YOUR DB SOURCE ]]],
            ID = varSelectedListItemID
        )
    );
    If ( 
        Lower(vaEditMode) = "yes",
        EditForm([[[ YOUR Edit form ]]]);
        Navigate(
            [[[ YOUR ADD/EDIT SCREEN],
            ScreenTransition.Cover
        );
        , // NOTE: This is the else part, assume this means View.
        ViewForm([[[ YOUR Edit form ]]]);
        Navigate(
            [[[ YOUR View SCREEN],
            ScreenTransition.Cover
        );
    );
);
// if the ID is -1 that means the user wants to create the record.
//   
If(
    varSelectedListItemID = -1,
    Set(
        varCurrentlySelectedRecord,
        Defaults([[[ YOUR DB SOURCE ]]])
    );
    Set(
        varEntryType,
        "New"
    );
    ResetForm(AESForm);
    Navigate(
        AddEditScreen,
        ScreenTransition.Cover
    );
    
);

Conclusion

In the above few steps, you can associate the full-screen PowerApps to the customize SharePoint List. The technique is simple the customized form will launch the full-screen PowerApps.

I hope this is helpful to you. Please post any comments you may have.

Posted in Power Apps, SharePoint | 3 Comments

How to validate the Date control in Power Apps to only allow selection of Tuesday and Thursday?

Summary

I needed to restrict the date selection in the Power Apps form to only allow Tuesday and Thursday.

Step By Step Solution

I assume you know how to create Power Apps form app.

Step # 1 I the BorderColor property of the Date control (in my case DateValue2) put the following formula. The Weekday function will provide the number for Tue and Thu.

// if parent error is blank or
//    selected date is Tue or Thu the border color is normal
If(
    Coalesce(
        (Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) = 3) ||
        (Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) = 5),
        IsBlank(Parent.Error)

    ),
    Parent.BorderColor,
    Color.Red
)
BorderColor

Step # 2 In the Text property of the ErrorMessage Control of the DataCard add the following formula.

Coalesce(
    Parent.Error,
    If(
        ((Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) <> 5) && (Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) <> 3)),
        "Date must be for Tuesday or Thursday only"
    )
)
Text property

Conclusion

Currently, the out of the box date control does not provide any validation, this is an alternative approach to validate and restrict the days. I hope it is useful to you for your scenario.

Posted in Power Apps | Leave a comment

How to get List-Field’s information such as display name and static name map?

Summary

During the development of PowerShell Script or Power Automate Flow with REST calls you will need a Static Names of the List. The Display name of the list is not same as static name.

Please read here for more information about the Display Name Vs Static Name.

Step By Step Solution

To simplify let’s create a simple Manually Triggered flow. Using the REST API make a call to the get Fields information in JSON. Map using the Select action to get only required field properties. After that apply the Create HTML action to create nice tabular data to see the map.

Flow to extract the Static Name information
https://github.com/pankajsurti/PowerAutomateSamples/blob/main/Export-ListFields-Information_20210704172208.zip

You can download the sample Power Automate from the above URL or by clicking here.

Conclusion

I use this flow to get the information of the list to get the static names. I hope it is useful for you as well.

Posted in Power Automate | Leave a comment

How to get a list of all SharePoint list names that were Customized with PowerApps?

Summary

The SharePoint List can be customized. It is as shown here. But question is how to find out all the list names which are customized for PowerApps?

Answer

It is very simple, I saw multiple places and I want to documented here.

Using the PnP.PowerShell connect to the site using Connect-PnPOnline.

Execute the following command to get the PowerApps customized list names.

Get-PnPList -Includes RootFolder.Properties | ? { $_.Hidden -eq $false } | ? {$_.RootFolder.Properties.FieldValues.Keys.Contains('PowerAppFormProperties')}

Conclusion

Posted in Uncategorized | Leave a comment