Project Oakdale and Azure API Management

Microsoft Dataverse for Teams and Azure API Management

I think that Microsoft Dataverse for Teams (old name Project Oakdale) is the most important Power Platform announcement of the year, especially you are coming from a canvas apps background like myself. Teams are starting to be more and more of a platform for the business than a replacement for Skype. Now, with Microsoft Dataverse, users will have a real data capability and a route to future upgrades by transferring their application on top of Common Data Service if needed. Teams and Microsoft Dataverse for Teams apps offer the needed simplicity to build those small or even large everyday business apps that truly matter for the users.

There are multiple posts and how-to guides to learn Microsoft Dataverse for Teams and canvas apps development, so there is no need to go deeper. One thing that got my attention during the Ignite was the announcement of using Azure API Management with Dataverse for Teams solution through the existing Teams licensing!

This means that your professional developers can create API services to process data and connect to almost any enterprise service. Then the citizen or IT pro developers can leverage those functionalities on their application. These functions will be technically published as custom connectors to the Power Platform environment related to the Dataverse in Teams.

Earlier, this meant that you needed an extra license because a custom connector is a premium level connector, but now that is not needed with Dataverse for Teams environments. Let us see how to use this in real action. Again, we can use something easy for even non-developers and create an Azure Function with PnP PowerShell (I did write about this earlier).

Create Azure Function

Let us keep things simple and create an application that asks for some data from a user and then creates a new News page to SharePoint. We will also fetch some additional information from an “enterprise” service with a rest call during the creation process. The idea is to ask the title and the body from the user and then fetch some data from the Bacon Ipsum service and add it to the news page.

The source code of the function can be found from my GitHub. It is a lot easier to read the code from there, but I will cover the most important parts here: PowerShellCore / CreateBaconPage.

When I am creating PowerShell scripts, I have a habit of using the following type of structure. I think this helps to read and maintain the code.

  1. Start by reading the Azure Function request parameters if developing a function.
  2. Set the main internal parameters, like connection related, used in the function.
  3. Read the possible modules if needed.
  4. Then inside the first try-catch, open the necessary connection, for example, to SharePoint.
  5. Then check that all mandatory parameters are available. I have a parameter called $haveMainParameter that I update while checking the other parameters.
  6. If we have everything available and connections are open, we can start to run the main section of the program.
  7. In the last section, I am closing all the connections.
  8. If developing a function, I am pushing the return details so that the callers can continue their process.
  1. Create a new Azure Function with Visual Studio Code.
  2. We need to fetch three parameters from the request.
    • News title
    • Body of the news
    • Paragraph amount (int value) to be used in our service call to Bacon Ipsum
$newsTitle = $Request.Query.newsTitle
$newsBody = $Request.Query.newsBody
$meatParas = $Request.Query.meatParas
  1. Next, add the necessary parameters used to connect to SharePoint.
    • This time we will need to authenticate against SharePoint with users’ credentials because you cannot create pages with an app-only connection.
    • Make sure to save the credentials securely. I did use the application settings in this example, but Azure KeyVault is a better option.
  2. Now you can make a connection to SharePoint.
    • As a best practice, it is recommended to return the connection to a parameter and use every PnP function call.
    • This helps you overcome a possible mixing of the connection context that can happen when executing the parallel function. You will see ‘The object is used in the context different from the one associated with the object.’ error message when mixing happens.
$spConn = Connect-PnPOnline -Url $siteURL -Credentials $credential -ReturnConnection
  1. Now it is time to check that we have all the necessary parameters and connections available.
#Check the parameters necessary for the application
Write-Host " "
Write-Host "*Check the parameters necessary for the application"

If($spConn -and $newsTitle -and $newsBody -and $meatParas){
    #Parameters are available
    $haveMainParameters = $true
}
else {
    #Missign some parameters
    $haveMainParameters = $false
}
  1. If the parameters are OK, we can continue building the logic.
  2. First, let us create a basic page with a title and content.
    • As you can see, I like to write messages to the PowerShell host console a lot. I think this helps you with debugging and testing.
Write-Host " "
Write-Host "*Create the new page"

#Create basic section
Write-Host "..create basic section"

$newsPage = Add-PnPClientSidePage -Name $newsTitle -PromoteAs NewsArticle -Connection $spConn
Add-PnPClientSideText -Page $newsPage -Text $newsBody -Connection $spConn

#Connect to enterpise service
$baconText = GiveMeBacon -meatParas $meatParas #Save for later -meatType $meatType
  1. At this point, remember to save the function and test the logic by hitting F5.

Connecting to Enterprise Service

Now, let’s look at an example where we fetch some data from an enterprise service outside of Office 365 scope. As a test, let’s get some random text from the Bacon Ipsum service. We will do this by adding a custom module with the necessary logic into our Azure Function.

  1. I like to add a folder for my custom folder.
    • Add CustomModules -folders and EnterpiseAPI.psm1 file into it.
  2. Inside the module, we only need to add one function that is then exported to function logic.
function GiveMeBacon{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true, HelpMessage="Amount of meat")]
        [string] $meatParas
    )

    #**Give me bacon from - baconipsum.com
    Write-Host "#*#Give me bacon from - baconipsum.com"

    $response = ""

    try {
        $queryURL = ("https://baconipsum.com/api/?type=all-meat¶s={0}&start-with-lorem=1&format=text" -f $meatParas)
        
        $response = Invoke-RestMethod -Uri $queryURL -ContentType "application/json; charset=utf-8" -Method Post -UseBasicParsing
    }
    catch {
        $ErrorMessage = $_.Exception.Message
        Write-Host "**ERROR: #*#Give me bacon"
        Write-Error $ErrorMessage
    }

    return $response
}

Export-ModuleMember -Function GiveMeBacon
  1. Here is a quick overview of the function:
    • First, read the parameters for the paragraph and text type.
    • We need to construct the URL that we are calling and make a rest call against that URL.
    • As a response, we will get a random text set that we then return to the caller.
  2. Of course, in real life, an enterprise service connecting is most likely a lot complex, but this gives you an idea of building one.
  3. Next, let’s use this new logic in our original function. The first thing to do is to add a reference to the module.
    • Add the following text line at the beginning of your Azure function somewhere after the Input binding section.
#Get custom modules
$SP_ModulePath = $PSScriptRoot + "\CustomModules"
Import-Module "$SP_ModulePath\EnterpiseAPI.psm1" -Force
  1. Now we can extend the page creation by calling the enterprise function and adding the returned text into a separate section on the page. Add the following logic after the initial page creation section.
#Connect to enterpise service
$baconText = GiveMeBacon -meatParas $meatParas #Save for later -meatType $meatType

#Add related section
Write-Host "..add related section"

Add-PnPClientSidePageSection -Page $newsPage -SectionTemplate OneColumn -ZoneEmphasis 2 -Connection $spConn
Add-PnPClientSideText -Page $newsPage -Column 1 -Section 2 -Text "<h3>Related Info</h3>" -Connection $spConn

Add-PnPClientSideText -Page $newsPage -Column 1 -Section 2 -Text $baconText -Connection $spConn

#Add related section
Write-Host "..publish the page"
Set-PnPClientSidePage -Identity $newsPage -Publish -Connection $spConn

Again, you can test the function to make sure a correct type of page gets created to the SharePoint. When everything is working correctly, you can publish the function in Azure.

Microsoft Dataverse for Teams Application

Now let us go teams open the Power App application so that we can create and Dataverse application. The application is simple, with some data fields and a button. You can see the structure from the image below.

  1. I added a variable called EnableSendBtn to the OnStart setting of the App. We will use this parameter to enable and disable the Send button to send the form details only when all the necessary details are given.
  2. I added the following elements to the screen.
    • A one-line text box for the title of the news page.
    • A multiline text box for the body.
    • A number field used to give the number of paragraphs fetch from our enterprise service.
  3. Here are a few important things to notice from my example:
    • Remember to give a unique name for each element on the screens. This will help you to build and maintain the logic.
    • First, we set the EnableSendBtn as false to disable the button for the API call time.
    • The last two sections will enable the button again after the call and resets the form controls.
    • The DisplayMode setting of the Send button has the following logic:
If(
    EnableSendBtn And Not(IsBlank(txtNewsTitle.Value)) And Not(IsBlank(txtNewsBody.Value)) And Not(IsBlank(txtDetailsParagraph.Value)),
    DisplayMode.Edit,
    DisplayMode.Disabled
)

But how to call the Azure Function we made earlier?

Configuring and Using Azure API Management

If you have not used or created API Management before, you can start exploring the service with this simple documentation: Quickstart – Create an Azure API Management instance | Microsoft Docs. I will cover the Project Oakdale related basic settings in the next steps. I assume that you have Azure Function that you want to publish an Azure API Management instance created.

  1. The documentation link above also has details on how to add your first API to the management instance.
  2. In this example, you need to add an Azure Function.
    • You will see a form that you can use to find the necessary API details.
    • Click Browse from the form.
  1. Next, click the “Function App.”
    • You will see a list of available Azure Function Applications.
    • Select the one that holds the function you want to publish.
  1. A list of available functions is shown, and you can select those you want to publish.
    • Select the correct one and click Select.

  1. You will see the details of the function in a form.
    • I recommend giving a meaningful name for the details of the details because it will help you find and use the API in Project Oakdale.
    • Sure, these settings can be updated later also.
    • Finally, click create.
  2. An important thing to notice here is that every Azure API Management API is protected with a subscription key by default. This key needs to be added to the API call, or otherwise, the user is getting access denied error.
    • It is possible to turn the key usage off, but then the whole API would be public and don’t want that.
    • You can find the keys from the Azure API Management portal under Subscriptions.
    • Copy the necessary key, like the build-in primary key, because we will need that in the Power Apps side.
  1. In our function, there were three attributes that we need from the users. Those won’t be asked automatically unless we update the schema of OpenAPI details of the function and tell what we need.

     

  1. Select the post-call of our API and then click the edit link of the frontend section.
    • We want to add new Query parameters to the function.
    • You could write the JSON settings manually, but using the editor makes your life a lot easier.
    • Create parameters for all the necessary ones used in your function. In my case, I only needed a string or integer type of attributes.
    • Also, create one extra parameter for the subscription key called ‘subscription-key,’ type ‘String.’
    • Remember to save the changes.
  1. Now we can export the function. In the export, there is an option for Power Apps and Power Automate available.
    • In the export form, you see a dropdown box to select the Power Platform environment were to publish the connector.
    • Ensure that you have at least one Power App created in Teams because otherwise, you will not see the environment in the list. Also, makes sure to select the correct environment (been there, done that).
    • Give the connect a meaningful name and click export.
  1. It will take a few minutes to publish the API fully, but at this point, you can go to Teams and open the Project Oakdale application.
    • In case the App is open, I recommend hitting refresh for the browser.
  1. In the App, you need to add a new Data Connection.
    • Select Data from the left menu and click Add data.
    • Find the API with the name you gave during the publishing time and click it from the list.
    • A right popup menu will open, and you can click the Connect button from the form.
    • At least for the current preview version for Project Oakdale, you will see a warning about the Premium connection. Based on Microsoft documentation, there is no need to take any extra action based on that.
  1. Now we are ready to use the API. Go to the OnSelect setting of the button in our App and start to add a line after the CALL AZURE API MANAGEMENT comment in our example.
    • Remember to select the post-call of your function.
    • Then you need to give the custom parameters and associate the values to the form elements.
    • The final parameter, called ‘subscription-key,’ is the subscription key copied earlier. Without this key in the query, your API call will not be processed.
  2. My final logic of OnSelect of the Send button looks like this:
Set(
    EnableSendBtn,
    false
);
//CALL AZURE API MANAGEMENT
GiveMeBaconAPI.postcreatebaconpage(
    {
        newsTitle: txtNewsTitle.Value,
        newsBody: txtNewsBody.Value,
        meatParas: Value(txtDetailsParagraph.Value),
        'subscription-key': "YOUR_KEY_GOES_HERE"
    }
);
Set(
    EnableSendBtn,
    true
);
Reset(txtNewsTitle);
Reset(txtNewsBody);
Reset(txtDetailsParagraph);

PS. When writing this post, I have seen a couple of different setting options for the subscription key during the past week. This might because Project Oakdale is still in preview. Here I used one of the current working methods, but I will keep watching the progress and update my post if necessary.

  1. Now, we can save the application and make a test in the preview window.

When everything goes as planned, a new page is created to SharePoint with some enterprise service data. You can now continue to future develop the Project Oakdale app and publish it to the users. In case something goes wrong, you can check the possible errors in the Power App side after closing the preview windows. You can also debug the Azure Function by opening the monitoring and making a test call from the Power App. At this point, you will thank your-self for writing enough comments to the host inside your code.

PnP.PowerShell Is Out and Here's Why it Matters

PnP.PowerShell Is Out and Here’s Why it Matters

Good things are worth waiting for. It took around two years but finally, PnP PowerShell (@PnpPowershell) for PowerShell Core, aka PnP.PowerShell is out in a preview, and GA is expected to be realized this year. Big kudos to Erwin (@erwinvanhunen) and his team. More about the journey and roadmap ‘Cross Platform PnP PowerShell Released

I am a huge fan of PowerShell and especially the PnP PowerShell library. I still use it almost daily for something, and I think scripting is something that every IT Pro should master at least on some level. As we know, things are evolving quickly, and there is always something new in the IT world. One important thing around PowerShell was PowerShell Core 6 that was announced back in 2014. The PowerShell Core is a cross-platform, free, and open source. For an old-school SharePoint geek (like me), the lack of API and modules against SharePoint Online has been the thing to keep using the Windows PowerShell. Sure, you have been able to use REST before, but the PnP PowerShell module has just been more comfortable.

But now the future is finally here for the oldies everyone. You can find the source code of this post from my GitHub repository.

Things to Consider on PnP PowerShell

Installing the new PnP PowerShell is easy, as usual. Just open the PnP Management Shell and give Install-Module PnP.PowerShell -AllowPrerelease command. Things still are in preview mode, but new cmdlets are added regularly to GitHub. Using the module is almost similar to using the classic version. Here’s an excellent place to start if needed: PnP PowerShell Overview | Microsoft Docs

On a high -level, things to remember for us who have been using the earlier version are:

  • The roadmap
    • Once the new PnP PowerShell (v4) goes to the GA (scheduled end of 2020), the old v3 version’s development will stop.
    • The plan is to archive the v3 repo during Q1 2020
    • Start to plan the migration NOW! I just did.
  • PnP PowerShell will be available for SharePoint Online only in the future!
  • PnP PowerShell runs on top of .NET Core 3.1 / .NET Framework 4.6.1. This means that you need the new cross-platform version of PowerShell to use. You can find the installation instruction from here: Installing PowerShell – PowerShell | Microsoft Docs
  • All *-PnPProvisioningTemplate cmdlets have been renamed to *-PnPSiteTemplate. This means that Get-PnPProvisioningTemplate is now, for instance, called Get-PnPSiteTemplate.
  • Classic credential-based authentication has changed.
    • More info from GitHub (link above)
    • WebLogin functionality is not available anymore.

Regarding the login, if you are not familiar with connecting to SharePoint Online using Application Permission, I think this is an excellent time to check out that option. Giving the necessary permission through an Azure AD application will give you more robust tools to control the access for many scripting and integration use cases. Application permission is also crucial to master for a reliable and secure connection for automated tasks and service scenarios. Here’s a basic walkthrough on how to get started.

PnP PowerShell with Azure Functions

I still have not covered the “Here’s Why” section of the title, so here it goes. I think (for me) the major thing of the new release is that moving the PnP library on top of .NET Core means that PnP PowerShell now works natively with Azure Functions type of services. You were, and I have, able to use it before, but the development experience was not good, and there was not an easy way to manage the ALM process for your PowerShell functions. With the new version, all the complexity is gone, and PowerShell is starting to be a real option for service development in many cloud business cases.

As an old developer, I know this will raise the hairs upright among many of my friends, but you should not underestimate PowerShell when creating integrations or services. Yes, by coding, you can do things effectively, and there are multiple cases when PowerShell is not enough.

In many organizations, the situation is that there aren’t too many people who know modern code techniques but there are many who know PowerShell. Now with PnP PowerShell and another available cmdlet and modules, people are able to do even more complex useful programs for their organization.

At the same time, we, the consultants, are responsible for developing services that our clients can use AND maintain. In many cases, there are multiple IT Pro’s that do know PowerShell, but they do not have coding experience. I always try to build my solutions to take whole control of the solution when I am gone. One of my mentors in consulting had one rule; as a consultant, do your job every day so that you may be unnecessary the next day. Therefore, I see PowerShell as a viable option in many cases. By using it, I know multiple people can maintain the solution. This is the same reason why I find Power Platform as an essential platform in the Microsoft ecosystem.

As a consultant, do your job every day so that you may be unnecessary the next day.

To get back to the point, you can now use the PnP PowerShell module easily when building Azure functions. I recommend using a Visual Studio code from the beginning so that you will adopt better ALM practices. If necessary, start your journey by creating a basic Azure function with PowerShell. Here’s a guide for that (select PowerShell as the programming language): Create your first function in Azure using Visual Studio Code | Microsoft Docs

  • You should also go through the Application Permission section mentioned above to connect to SharePoint Online in a managed way.
    • Maybe the easiest way to connect to SharePoint Online from the automated process is to use Connect-PnPOnline -Url YOUR_URL -ClientId “ClientId” -ClientSecret “Secret.”
    • Remember not to add the client id, secret, cert details, etc. straight to your code.
    • Use the Application settings of the function to store these values during the development time.
    • In production, you should store these values to Azure Key Vault and use Managed Identity to access values, but this goes over this post’s topic.
  • For this example, I did save the connection variable to the application settings of the Function App.
  • A valuable thing to learn for Azure Function development is Dependency Management.

I assume that you have a working local Azure Function development environment based on the “Create your first function in Azure using Visual Studio Code” article above. Here are the steps you need to take to have PnP PowerShell working in your function.

Using PnP PowerShell with Azure Function

  1. Create a new PowerShell based function with Visual Studio Code.
    1. I did name mine as Basic-PnPPowerShellCore.
  2. Then, and this is the most enjoyable part, you need to load the PnP.PowerShell module on your function’s environment.
    1. Open the requirements.psd1 file and add PnP.PowerShell a dependency on the project.
    2. When writing this post, the version was 0.xxx, but you should check the current one from GitHub (link above).
    3. AND THAT’S IT. No more uploading the module manually or anything like that. Things are just rolling natively.
  1. Create a new Azure AD application for your target environment.
    1. Give the necessary permission to the application. I did give full control to all sites.
    2. Also, create a secret that can be used when authenticating against the application with PnP PowerShell.
  2. I prefer creating an Azure Function App before I publish the function to make the necessary settings, but you can choose to create the application while publishing the function.
    1. Save the application id, aka client id, and the secret to your function application settings (note what was said before about production).
  3. Download the Application Settings to your local development environment.
  4. For a test function, let us do a simple service that returns the site’s URL to the questioner.
  5. Open the run.ps1 of the function created earlier.
  6. Add the necessary parameters.
    1. $env is referring to the Azure Function Application Settings that holds the necessary details to connect to SharePoint Online.
    2. Of course, you can give these values straight to your code but in production, I recommend using other options.
$connClientId = $env:ConnClientId
$connSecret = $env:ConnSecret
$siteURL = $env:O365DemoURL
  1. My simple function looks like this:
$response = ""

try {
    Connect-PnPOnline -Url $siteURL -ClientId $connClientId -ClientSecret $connSecret

    $ctx = Get-PnPContext

    $response = $ctx.Url

    Disconnect-PnPOnline
}
catch {
    $ErrorMessage = $_.Exception.Message
    Write-Host "**ERROR: *Basic-PnPPowerShellCore"
    Write-Error $ErrorMessage
}

if ($response) {
    Write-Host ("Response: " + $response)

    # Site details found!
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = [HttpStatusCode]::OK
        Body = $response
    })
}
else {
    # No site details found
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = [HttpStatusCode]::NoContent
    })
}
  1. Next, you can test the function by pressing F5.
    1. Because we added a new module as a dependency, the first start will take a few minutes when the module is loaded to the local environment.
  2. After the loading is done, you can test the function by calling the URL of the function.
  1. When everything goes as planned, you will see the URL of the test site in the browser.
  2. After this, you can publish the function to Azure Function app from the Visual Code Extension
  1. I recommend going into Azure Portal and open the Azure Function where you deployed the function.
    1. From the App, select Functions, and then the function you just created.
    2. Run a Test against the function to make sure it is working.
    3. Running the test will also load the dependencies for the first time so that the calls after the initial call are quicker.
  1. Finally, you could push your code to the git repository or any source control system your organization uses.

Now, imagine what type of scenarios and possibilities the integration to Azure Function is giving to a user who knows PowerShell. In my next post, I will show something interesting regarding Power Platform and PowerShell, but hopefully, this post gives you ideas to continue developing useful services quickly.

Connecting to Microsoft Graph with PowerShell

Nowadays, I find myself self-working with PowerShell almost as much as with Power Platform. Not even mention that I used to work as a software developer in the web stack world, like jQuery, and React. I remember that my first large PowerShell project was related to SharePoint 2013 site provisioning years ago. Back then PnP PowerShell library was only a dream I had to write the modules and extension our self. Today, I’m a big fan of PnP PowerShell, and I’m using it almost daily. And we should not forget those multiple other PowerShell modules available to different services of Office 365.

Of course, there are situations and times when there isn’t a built-in command available for the needed task. In many cases, you can use CSOM capabilities to close the cap (I’ll come back to this in the upcoming posts). And then there’s Microsoft Graph you can use to do a lot of things. To get us rolling, we need to authenticate against it to get AccessToken we need to use in HTTP calls against the Graph API. Here are three different scenarios on how to get the AccessToken with PowerShell.

You find all the scripts from my GitHub: https://github.com/MikkoKoskinen/SP-Poweshell/tree/master/ConnectToGraph

General Variable

The first thing you need is to register the application and set the necessary permissions for the service you won’t use. Here are the steps on how to do it: https://docs.microsoft.com/en-us/graph/auth-register-app-v2. Remember to copy the application ID and secret because we need those in the scripts.

In all the scripts below, we will need several information from the tenant and the registered application. Most importantly, we need the application id, tenant id, and the created secret id. With these, we can design and application-level connection to the Graph. For delegated permission, we will also need the credentials of the users that we want to use for the connection.

#*** Initialize variables
Write-Host "Initialize variables"
Write-Host " "

[string]$graphApp_AppId = "APPLICATION_ID"
[string]$graphApp_AppSecret = "APPLICATION_SECRET"
[string]$tenantId="TENANT_ID"
[string]$spAdminURL = "SPADMIN_URL"
[string]$graphVer = "v1.0"

#Credetential iformation to be used in delegated connections
[string]$username = "USERNAME"
[string]$password = "PASSWORD"

Connection with Application Permissions

In my example script, the needed variables are given as plain text in the script file, but for production, you might want to consider other options, like using Credential Manager or Azure Key Vault, to hide this information. At least, don’t take a straight copy from my script, add the information, and post that to some public source.

To connect against Graph with application permission we will need to build a JSON variable with application id and secret. We also need to pass a grant type of ‘client_credentials’ and scope with a value of ‘https://graph.microsoft.com/.default&#8217;.

try{

    #Connecting with Application Permissions
    Write-Host " "
    Write-Host "Connecting with Application Permissions"

    $body=@{
        client_id=$graphApp_AppId
        client_secret=$graphApp_AppSecret
        scope="https://graph.microsoft.com/.default"
        grant_type="client_credentials"
    }

    $response = Invoke-WebRequest -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -ContentType "application/x-www-form-urlencoded" -Body $body -Method Post

    $accessToken=$response.content | ConvertFrom-Json
    $AppAccessToken = $accessToken.access_token
	
	if($AppAccessToken){
		Write-Host ("...got AppAccessToken")
	}
	else{
		Write-Error -Message$ "Application access error" -Category AuthenticationError
	}
}
catch {
    $ErrorMessage = $_.Exception.Message
    Write-Host "**ERROR: Connecting with Application Permissions"
    Write-Error -Message $ErrorMessage
}

This JSON in then send with Invoke-WebRequest Post against the login API. If succeeded, we should get a JSON response back and we can parse the authentication token from it.

Connection with Delegated Permissions

A delegated connection can be done in almost similar way than the connection with application permission. Again we need the Json variable with application id and secret. Then we need to add the username and password for the user account we want to use in the connection. This time the grant type for the call is ‘password’.

try{
    #Connecting with Delegated permissions
    Write-Host " "
    Write-Host "Connecting with Delegated permissions"

    $body=@{
        client_id=$graphApp_AppId
        password= $password
        username= $username
        client_secret=$graphApp_AppSecret
        grant_type="password"
        scope="https://graph.microsoft.com/.default"
    }
$response = Invoke-WebRequest -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -ContentType "application/x-www-form-urlencoded" -Body $body -Method Post

    $accessToken=$response.content | ConvertFrom-Json
    $UserAccessToken = $accessToken.access_token
	
	if($UserAccessToken){
		Write-Host ("...got UserAccessToken")
	}
	else{
		Write-Error -Message$ "Delegated access error" -Category AuthenticationError
	}
}
catch {
    $ErrorMessage = $_.Exception.Message
    Write-Host "**ERROR: Connecting with Delegated permissions"
    Write-Error -Message $ErrorMessage
}

Again, if everything goes as planned, we should get the access code as a return value.

Connection with PnP PowerShell

For the application connect there’s also a shorter version available through PnP PowerShell module. We can call the Connect-PnPOnline function with application id and secret and get the access token with Get-PnPAccessToken call after that.

try{
    #Connecting with PnP PowerShell
    Write-Host " "
    Write-Host "Connecting with PnP PowerShell"

    Connect-PnPOnline -Url $spAdminURL -AppId $graphApp_AppId -AppSecret $graphApp_AppSecret
    $pnpAccessToken = Get-PnPAccessToken

	if($pnpAccessToken){
		Write-Host ("...got pnpAccessToken")
	}
	else{
		Write-Error -Message$ "PnP PowerShell access error" -Category AuthenticationError
	}
}
catch {
    $ErrorMessage = $_.Exception.Message
    Write-Host "**ERROR: Connecting with Delegated permissions"
    Write-Error -Message $ErrorMessage
}

Change the display value of list column

Every now and then my customers are asking me to hide some list column from different views. Because every time I have to think that what is the best way to fulfill this requirement I decided to write this post that basically tells the different options. There are ways x to hide the column in different list views. And by views here I mean DisplayForm, EditForm and NewForm.

  • With list level setting
  • With JavaScript or actually JQuery here

As you can see I left out the options for doing this with server side code or declaratively during the wsp installation for example. These solution can be used to make the change in existing environment and especially these are working in Office 365. Actually the examples have been developed in my Office 365 test tenant. For these examples I created an OOTB announcement list and added one new column called “Hide Me”.

Change the list level setting

This shouldn’t be a new thing to you but just for reference. You can change the list column visibility from list settings. But this setting will change the visibility in all forms. There is no form level setting available through UI.

  1. Go to list setting from the ribbon
  2. Activate the content type management if necessary
  3. Open the content type that holds the field
  4. Click the field you would like to hide
  5. Select Hidden from column settings

Here’s a video that shows you the steps if needed.

Hide the column with JavaScript

This method is for maybe more advantage users because you need to insert some script to the pages. With this method you can hide the column form any list form separately.

  1. You can find the list form edition option from the ribbon. Open one you want to edit. I use display form here because that’s the most common form one have to edit.

  2. This will open the form (or actually a page). The form is opened in edit mode.
  3. Add a Script Editor web part to main zone.

  4. Click the Edit Snipped link and add the code below to editor.
  5. Remember to change the “Hide Me” text to match your field title.
  6. Click Stop Editing to save the changes.
  7. Your field should now been hidden in display view.

I decided to use JQuery in my script because this way it’s more flexible to search the right HTML element from the page. The code below includes a link to JQuery but you can leave it away if that is already loaded on your environment.

In the page the list columns are actually shown in table format. What we will do here is that we will find the title of our field that we want to hide. Then we will get the closest row and hide it. This will hide both the title and value columns. To get field back simply delete the script editor web part from the form. There is one small draw back when you use this method. For a blink of an eye it’s possible that users are actually seeing the field when they load the form. This is because the scripts in editor web part are run only after the form has been fully loaded.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js" />
<script type="text/javascript" language="javascript">
$(document).ready(function() { $('h3:contains("Hide Me")').closest('tr').hide(); });
</script>

Using PowerShell

Because of the delay there I decided to seek still one option. If you know there is actually SetShowIn setting for field. This setting is available only through object model or you can set it declaratively in field xml settings. Because we already had the environment running and we are using Office 365 those options wasn’t available to us. For me PowerShell is the tool make this kind of coding necessary operations.

You can actually use SharePoint client side code with power shell also. I won’t go through the requirements and basics of that because a link original source for how to do it is a lot easier. Thanks for Chirs O’Brien for posting this tip http://www.sharepointnutsandbolts.com/2013/12/Using-CSOM-in-PowerShell-scripts-with-Office365.html

  1. Make sure you have the SharePoint client dll available on the machine you are running the PowerShell script.
    1. If you don’t have SharePoint server available from where you can get them, you have to download and install SharePoint Server 2013 Client Components SDK.
    2. http://www.microsoft.com/en-us/download/details.aspx?id=35585
    3. The path in my script is showing the place where you can find these dll:s in SharePoint server or after you have installed the SDK
  2. Change the path of the dll files to the script if needed.

  3. You can run this code from basic Windows PowerShell command prompt. You should run the command prompt as administrator.
  4. Navigate in the folder where the script is and run it with the following parameter.
  5. .\ChangeColumnFormVisibility.ps1 -Url “https://tenant.sharepoint.com/sites/spdevsamples/&#8221; -ListName “Events” -FieldName “Hide Me” -ListView “DisplayForm” -ShowInForm:$False
    1. Url = Url of the site where the list is
    2. ListName = Title of your list
    3. FieldName = Title or internal name of your field
    4. ListView = form which view you want to edit. This value has to be DisplayForm or NewForm or EditForm
    5. ShowInForm = Boolean ($true or $false) value that tells should the field be hidden or shown
  6. The script will prompt and ask you for credential during the run.
  7. The details of the changes are shown after the changes are done.

After the script is run you can go and see the changes in your list view.

 

 

 

 

 

 

 

The code is actually quite simple. First we connect to the site. After that we have to get the right list and it’s fields. We have to load the field collection before we can make the necessary changes. After that we can get the right field based the given name. Then we have to check what form value user wants to change and make the right changes and update the field settings.

# connect/authenticate to SharePoint Online and get ClientContext object..
$credential = Get-Credential
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName, $credential.Password)
$clientContext.Credentials = $credentials

if (!$clientContext.ServerObjectIsNull.Value)
{
#This gets Microsoft.SharePoint.Client.List
$oList = $clientContext.Web.Lists.GetByTitle($ListName);
#This gets Microsoft.SharePoint.Client.FieldCollection
$fieldColl = $oList.Fields;

$clientContext.Load($fieldColl);
$clientContext.ExecuteQuery();

$field = $fieldColl.GetByInternalNameOrTitle($FieldName);

$clientContext.Load($field);
$clientContext.ExecuteQuery();

switch ($listView) {
"DisplayForm"
{
$field.SetShowInDisplayForm($ShowInForm);
$clientContext.ExecuteQuery();

$message = "Fields '" + $field.Title + "' DisplayForm visibility changed to: " + $ShowInForm
write-Host $message -ForegroundColor Green
break
}
"NewForm"
{
$field.SetShowInNewForm($ShowInForm);
$clientContext.ExecuteQuery();

$message = "Fields '" + $field.Title + "' NewForm visibility changed to: " + $ShowInForm
write-Host $message -ForegroundColor Green
break
}
"EditForm"
{
$field.SetShowInEditForm($ShowInForm);
$clientContext.ExecuteQuery();

$message = "Fields '" + $field.Title + "' EditForm visibility changed to: " + $ShowInForm
write-Host $message -ForegroundColor Green
break
}
}
}

You can find the whole script from my GitHub:

GitHub Mikko Koskinen