VIDEO – Fully Automatic Page Translation with SPO, Azure Fn, PNP, and Cognitive Services

Wanted to share video demo of the configuration, testing, and debug for ASPX Page Translation of “flat” text modern content leveraging SharePoint Online (SPO), Azure Functions (Fn), PowerShell PNP, and Azure Cognitive Services.

Now users create Modern SPO pages with embedded text and simply click Microsoft “convert” to languages button. Background job handles Translation API for text updates.

Key technical design components are:

  1. SPO Modern page source text
  2. SPO site settings > Enable multi-language
  3. Azure Translation API
  4. Azure Function PowerShell
  5. MS Flow to automatically execute Azure Function

Comments and suggestions always welcome. Cheers.

VIDEO

SCREENSHOT

CODE

AZURE FUNCTION KUDU

# This file enables modules to be automatically managed by the Functions service.
# See https://aka.ms/functionsmanageddependency for additional information.
#
@{
    # For latest supported version, go to 'https://www.powershellgallery.com/packages/Az'. 
    # To use the Az module in your function app, please uncomment the line below.
    # 'Az' = '7.*'
    
    'PNP.PowerShell' = '1.*'
}

MS FLOW COMPOSE

replace(replace(triggerBody()?['{Path}'], 'SitePages',''),'/','')

MS FLOW COMPOSE2

{
  "siteURL": "https://spjeff.sharepoint.com/sites/portal",
  "language": "@{outputs('Compose')}",
  "pageTitle": "@{triggerOutputs()?['body/{Name}']}"
}

REFERENCES

VIDEO – PNP Site Directory JSON expanded with AzureAD site owner

Demo how to create JSON inventory of full tenant SPO site collections. Lookup Site Owner in Azure AD and expand JSON with AD properties such as user, manager, department, and more. Cheers.

CODE

https://github.com/spjeff/office365/blob/master/office365-site-directory/PnP-Site-Directory-JSON.ps1

VIDEO

SCREENSHOTS

REFERENCE

Auto Detect PowerShell Azure Runbook (or Local) Runspace

Wanted to share quick code snippet for how to detect if current PS1 host environment is Azure RUNBOOK or LOCAL console like POWERSHELL_ISE or POWERSHELL.EXE. Cheers.

SCREENSHOT

CODE

if ($host.Runspace) {if ($host.Runspace[0].GetType().Name -eq “LocalRunspace”) {$local=$true; “LOCAL”}} else {“RUNBOOK”}

REFERENCE

https://docs.microsoft.com/en-us/powershell/scripting/developer/hosting/windows-powershell-host-quickstart?view=powershell-7.2

Tip – How to tell M365 host location

Wanted to share quick trick on how to detect where your M365 tenant is hosted. Simply open URL below with tenant URL in the middle. Response JSON contains property “tenant_region_scope”:”NA” showing the physical host location. Cheers.

NOTE – For tenants hosted on GCC or you will see an extra property “tenant_region_sub_scope”:”GCC”

CODE

https://login.microsoftonline.com/SPJEFF.COM/.well-known/openid-configuration

{“token_endpoint”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/oauth2/token”,”token_endpoint_auth_methods_supported”:[“client_secret_post”,”private_key_jwt”,”client_secret_basic”],”jwks_uri”:”https://login.microsoftonline.com/common/discovery/keys”,”response_modes_supported”:[“query”,”fragment”,”form_post”],”subject_types_supported”:[“pairwise”],”id_token_signing_alg_values_supported”:[“RS256″],”response_types_supported”:[“code”,”id_token”,”code id_token”,”token id_token”,”token”],”scopes_supported”:[“openid”],”issuer”:”https://sts.windows.net/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/”,”microsoft_multi_refresh_token”:true,”authorization_endpoint”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/oauth2/authorize”,”device_authorization_endpoint”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/oauth2/devicecode”,”http_logout_supported”:true,”frontchannel_logout_supported”:true,”end_session_endpoint”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/oauth2/logout”,”claims_supported”:[“sub”,”iss”,”cloud_instance_name”,”cloud_instance_host_name”,”cloud_graph_host_name”,”msgraph_host”,”aud”,”exp”,”iat”,”auth_time”,”acr”,”amr”,”nonce”,”email”,”given_name”,”family_name”,”nickname”],”check_session_iframe”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/oauth2/checksession”,”userinfo_endpoint”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/openid/userinfo”,”kerberos_endpoint”:”https://login.microsoftonline.com/0a9449ca-3619-4fca-8644-bdd67d0c8ca6/kerberos”,“tenant_region_scope”:”NA”,”cloud_instance_name”:”microsoftonline.com”,”cloud_graph_host_name”:”graph.windows.net”,”msgraph_host”:”graph.microsoft.com”,”rbac_url”:”https://pas.windows.net”}

REFERENCES

PNP Connect to SharePoint Online with 3 DEMOS – Classic, Certificate, and Runbook

Wanted to share step-by-step procedures for how to connect PNP.PowerShell console to SharePoint Online. Three major methods are outlined below, each slightly more advanced than the previous. Drop any questions or comments at bottom of post. Cheers.

VIDEO 1 – Client ID and Client Secret plain text

Demo how to connect with Client ID and Client Secret plain text running PNP.PowerShell.

Steps are included for

  1. Register application with SharePoint Online (SPO) by opening “appregnew.aspx”
  2. Grant permission with SharePoint Online (SPO) by opening “appinv.aspx”
  3. Connect-PNPOnline using Client ID and Client Secret plain text Cheers

CODE

# PNP Client Secret
# https://medium.com/ng-sp/sharepoint-add-in-permission-xml-cheat-sheet-64b87d8d7600
# https://www.koskila.net/fastest-way-to-verify-your-client-id-and-client-secret-are-valid-with-powershell/
<#
The app identifier has been successfully created.
Client Id:  	12306f98-2d2f-49b8-88b3-0eddd71ec25f
Client Secret:  OhYnQV2Hq888LoZOz7C8QSKr81VCNyOWQG9XEjQP111=
Title:  	PNP-PowerShell
App Domain:  	localhost
Redirect URI:  	https://localhost
#>
# Scope
$tenant = "spjeff"
$clientId = "1236f98-2d2f-49b8-88b3-0eddd71ec25f"
$clientSecret = "OhYnQV2Hq888LoZOz7C8QSKr81VCNyOWQG9XEjQP111="
# Connect
Connect-PnPOnline -Url "https://$tenant.sharepoint.com/" -ClientId $clientId -ClientSecret $clientSecret
Get-PnPWeb | Format-Table -AutoSize

VIDEO 2 – PFX Certificate running PNP.PowerShell locally

Demo how to connect with PFX Certificate running PNP.PowerShell locally given PFX input file.

Steps are included for

  1. Register Application with Azure AD
  2. Generate certificate (PFX and CER) with private key saved locally
  3. Connect-PNPOnline using local PFX input file and private key password

PNP-Register.ps1

# PNP Register
# https://pnp.github.io/powershell/articles/connecting.html
# https://pnp.github.io/powershell/articles/authentication.html
# https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/register-pnpazureadapp?view=sharepoint-ps
# https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps
# https://mmsharepoint.wordpress.com/2018/12/19/modern-sharepoint-authentication-in-azure-automation-runbook-with-pnp-powershell/
# Scope
$tenant = "spjeff"
$clientFile = "PnP-PowerShell-$tenant.txt"
# Register
$password = ConvertTo-SecureString -String "password" -AsPlainText -Force
$reg = Register-PnPAzureADApp -ApplicationName "PnP-PowerShell-$tenant" -Tenant "$tenant.onmicrosoft.com" -CertificatePassword $password -Interactive
$reg."AzureAppId/ClientId" | Out-File $clientFile -Force

PNP-Connect.ps1

# PNP Connect
# https://pnp.github.io/powershell/articles/connecting.html
# https://pnp.github.io/powershell/articles/authentication.html
# https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/register-pnpazureadapp?view=sharepoint-ps
# https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps
# https://mmsharepoint.wordpress.com/2018/12/19/modern-sharepoint-authentication-in-azure-automation-runbook-with-pnp-powershell/
# Scope
$tenant = "spjeff"
$clientFile = "PnP-PowerShell-$tenant.txt"
# Connect
$clientId = Get-Content $clientFile
$password = "password"
$secPassword = $password | ConvertTo-SecureString -AsPlainText -Force
Connect-PnPOnline -ClientId $clientId -Url "https://$tenant.sharepoint.com" -Tenant "$tenant.onmicrosoft.com" -CertificatePath '.\PnP-PowerShell-$tenant.pfx' -CertificatePassword $secPassword
Get-PnPTenantSite | Format-Table -AutoSize

VIDEO 3 – PFX Certificate in Azure Automation Runbook

Demo how to connect with PFX Certificate running PNP.PowerShell in Azure Automation Runbook given PFX input file.

Steps are included for

  1. Register Application with Azure AD
  2. Generate certificate (PFX and CER) with private key saved locally
  3. Upload PFX into Azure Automation with [Exportable=Yes] and password
  4. Runbook code to download PFX at runtime (Get-AutomationCertificate)
  5. Connect-PNPOnline using Azure temp PFX file and private key password

CODE

# PNP Connect
# https://pnp.github.io/powershell/articles/connecting.html
# https://pnp.github.io/powershell/articles/authentication.html
# https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/register-pnpazureadapp?view=sharepoint-ps
# https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps
# https://mmsharepoint.wordpress.com/2018/12/19/modern-sharepoint-authentication-in-azure-automation-runbook-with-pnp-powershell/
# Scope
$tenant = "spjeff"
# Azure Certificate
$password = "password"
$secPassword = $password | ConvertTo-SecureString -AsPlainText -Force
$cert = Get-AutomationCertificate -Name 'PNP-PowerShell-$tenant'
$pfxCert = $cert.Export("pfx" , $password ) # 3=Pfx
$certPath = "PNP-PowerShell-$tenant.pfx"
Set-Content -Value $pfxCert -Path $certPath -Force -Encoding Byte 
# Connect
$clientId = Get-Content $clientFile
$password = "password"
$secPassword = $password | ConvertTo-SecureString -AsPlainText -Force
Connect-PnPOnline -ClientId $clientId -Url "https://$tenant.sharepoint.com" -Tenant "$tenant.onmicrosoft.com" -CertificatePath '.\PnP-PowerShell.pfx' -CertificatePassword $secPassword
# Display
Get-PnPTenantSite | Format-Table -AutoSize

What’s in that patch? November 2021

NOTE – PDF format updated to include both SharePoint 2013, 2016, and 2019 notes. Ever wondered what fixes are inside of a given CU?   Please see attached PDF with full detail. I wanted a new format for easy reading.   Show management and make the business case for why downtime should be taken to apply CUs.  Also posted at https://sharepointupdates.com/ If you found this helpful, please leave a comment.    shades_smile_thumb_thumb_thumb_thumb[2]

Download

What’s in that patch – Nov 2021.PDF

Demo – MS Flow History – Track each run by SharePoint Item ID for quick lookup

Demo how to create “Flow History” list for tracking each MS Flow run by SharePoint Item ID for quick lookup, troubleshooting, and support. Below formula creates dynamic HTTPS endpoint URL to show single MS Flow execution for the current instance. Writing to SharePoint list with “Create Item” step ensures history we can track for support.

Cheers.

BENEFITS

  • Granular history
  • Hyperlink URL to view full screen MS Flow run
  • Filter by SharePoint ID number
  • Filter by MS Flow name
  • Exact time stamps
  • Exact steps taken

VIDEO

CODE

concat('https://us.flow.microsoft.com/manage/environments/',
workflow()?['tags']['environmentName'],
'/flows/',
workflow()?['name'],
'/runs/',
workflow()?['run']['name'])

Use MS Dataflow to Import Northwind XLS to Dataverse tables

Step-by-step demo for MS Dataflow. How to import Northwind XLS to MS Dataverse tables with column mapping, primary query, Power Query mapping, and strong data types.

Includes “[X] Delete” option to remove rows in destination which no longer exist in source. Cheers.

VIDEO

REFERENCES

Return to Top ▲Return to Top ▲