DEV Community

Cover image for Updating Power Platform Admin Settings With Office Scripts
david wyatt
david wyatt Subscriber

Posted on • Edited on

3 1 1 1 1

Updating Power Platform Admin Settings With Office Scripts

#powerplatform #powerautomate #powerapps #officescripts

With the new shiny Power Platform Admin Center (PPAC) Microsoft is working hard at 2 things:

  1. Getting Key Information in front of the admins easier
  2. Getting you to purchase Managed environments

When global admin on the security area we can see potential key issues (flags) in the overview section.

overview

This is great information to have and displayed in a way that is user friendly and helpful.

I'm not going into to much detail about each of the flags in this blog (maybe a future one), but wanted to talk through one and how to manage them, and the creative workaround I use 😎

  1. The Flag
  2. PPAC Fix Options
  3. Bulk Updating

1. The Flag

In this example im going to use 'Turn on IP address-based cookie binding', its a simple setting with (in my opnion) little downside to enabling.
Access tokens are stored in cookies, so if a hacker got access to the cookie and the token stored in it, they could in theory use it to log in as you (with no need for passwords or mfa). Cookie binding means that the token stored only works with the ip address it was requested from. So once used with a different ip address it doesn't work. The only negative I can see is if you go on/off a vpn, your ip address changes so you will need to re-authenticate.

There is one problem with the flag (and not an uncommon issue), the report will flag all environments, even Teams environments, and guess what, you can't enable it in Teams environments 🤷‍♂️

2. PPAC

The Cookie binding settings is under settings - Privacy + Security, new PPAC center has a quick link to the setting from the overview page.

But with one issue, it needs to be a managed environment. That's right it's going to lower your security score for not having managed features enabled, for me its a little scare mongering, but the good news is you can dismiss the waring, personally I would like a managed environment toggle to hide warnings.

managed environments only

So let's say you have managed environments, you are going to use the overview to identify it, click to the setting screen, update, save, go back. Fine if only a few environments, but what if you have hundreds. Sadly it's not even a feature in managed environment groups, and there isn't even a CLI or PowerShell script to use. Luckily there is a way.

3. Bulking Updating

So we want to bulk update all environments, and without a CLI the only real way to do this is through API's.

You may not know this but my original steps into the IT world was through Excel and VBA (if you want to know full story check this out From Shadow IT to Power Platform Developer, The Low Code Diary, so my first thought was to spin up a vba Macro to do all the API calls, and then I remembered Office Scripts, the kind of replacement for VBA.

The plan then is to create a Office Script that loops over every environment and sends API request to update the cookie setting.

We need to achieve the following:

  • Get API's
  • Get Tokens
  • List Environments
  • Update Environment
  • Create The Script

plan

Get API's

Finding the API you need can be really hard work, especially if its not documented, luckily there is a an easy way.
Your browser has developer options that allow us to see not jsut the code that runs locally, but also all the network traffic.

Press ctrl+shift+i and the developer window should open, click on the network tab at the top.

network traffic

Now goto an environment in the ppac and update the setting you want to update. There maybe a lot of traffic, so clear the traffic before hitting save. With a little looking you will find the api url and body.

For the cookie we have:
Url:

https:{yourDynamicsUrl}/api/data/v9.0/organizations({yourOrganizitionID})
Enter fullscreen mode Exit fullscreen mode

Body:

{
    "enableipbasedcookiebinding": true,
    "ipbasedstorageaccesssignaturemode": 0,
}
Enter fullscreen mode Exit fullscreen mode

For the environment list we had to do a little trial and error, but by going to the environment load screen and filtering for environment I found:

https://api.bap.microsoft.com/providers/Microsoft.BusinessAppPlatform/scopes/admin/environments?api-version=2021-04-01
Enter fullscreen mode Exit fullscreen mode

network details

We now have the API's setup, next we need to authenticate them.

Get Tokens

The proper way to do this is to register a spn with the right scopes, and then use it to generate your token. But that is a lot of effort and requires right access to Azure/Entra (rightfuly a privalged role in most organizations).

So in the proper way, Im going to bodge it 😎

Powershell has a command we can use, simply run the 2 commands below and copy the token into Excel ready to use.

List Environments
Get-JwtToken "https://api.bap.microsoft.com"

Update Environment
Get-JwtToken "{urDynamicsUrl}"

MS Learn - Power Shell- Getjwttoken

powershell

The tokens only have a limited life span and will expire so they will need to be updated regularly and you have to install powerapps module first.

If you install but can't run the command it could be Execution Policy is set to restricted (Check by running: Get-ExecutionPolicy). To fix run Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

List Environments

We have our https://api.bap.microsoft.com/providers/Microsoft.BusinessAppPlatform/scopes/admin/environments?api-version=2021-04-01 url and we can call it with a GET request, but that sends a lot of data as it does not send the required fields we need, so I updated the $select parameter and endup with:

https://api.bap.microsoft.com/providers/Microsoft.BusinessAppPlatform/scopes/admin/environments?api-version=2021-04-01&$select=name,properties/tenantId,properties/displayName,properties/creationType,properties/environmentSku,properties/isDefault,properties/linkedEnvironmentMetadata/resourceId,properties/linkedEnvironmentMetadata/uniqueName,properties/linkedEnvironmentMetadata/domainName,properties/linkedEnvironmentMetadata/instanceUrl,location
Enter fullscreen mode Exit fullscreen mode

There is a little future proofing including, but the fields we need are:

  • Environment Name - properties/displayName
  • ResourceId - properties/linkedEnvironmentMetadata/resourceId
  • Dynammics Url - properties/linkedEnvironmentMetadata/instanceUrl
  • Geo Location location
  • Environment Type - properties/environmentSku

Some of the above are obvious, but why the rest here, well:

ResourceId, this is also known as orgnaiziationId, the one we need in out Update API call.
Dynamics Url, yep that's the beginning of our Update API call
Geo Location, each environment token only works on environments in the same location, so we want to be able to filter our environments and use the right token (if we could do it properly we could build in the get token for each call, but remember, I'm lazy).
Environment Type, this is if its Production,Sandbox,Dev,Trial, or Teams, and not all environment features are available in every environment, so again we need to do some filtering.

Response

{value: [
   location: "unitedstates",
   name: "00f3993b-4444-4444-4444-4a303c5f4f31",
   properties: {
      tenantId: "2242945a-4444-4444-4444-cce1c66e31bb",
      displayName: "Daves demo environment",
      creationType: "User",
      environmentSku: "Production",
      isDefault: false,
   },
   linkedEnvironmentMetadata:{
      resourceId: "238302d2-4444-4444-4444-6045bd016a11",
      uniqueName: "unq238302d2bddded11a80b604444016",
      domainName: "us-demo",
      instance URL: "https://us-demo.crm.dynamics.com/",
    }
]}
Enter fullscreen mode Exit fullscreen mode

Creating the Script

We are going to set up our Excel workbook with 2 tabs, one will be the list to write the environments to, and the other will be out configs (urls, tokens, settings).

First lets look at setting those inputs from Excel into the script

const wsConfigs = workbook.getWorksheet("Configs");
const wsOutputs = workbook.getWorksheet("Outputs");
let sTokenList = wsConfigs.getRange("tokenBap").getText().replace("Bearer ","");
let sTokenPatch = wsConfigs.getRange("tokenDynamics").getText().replace("Bearer ", "");
const sApiPath = wsConfigs.getRange("apiList").getText();
const sApiPath2 = wsConfigs.getRange("apiPatch").getText();
const sMode = wsConfigs.getRange("mode").getText();
Enter fullscreen mode Exit fullscreen mode

We set the sheets, then get the text from the relevant cells. sTokenList and sApiPath are for List, sTokenPatch and sApiPath2 are for Update. sMode sets if List or Update.

Next we are going to do the api call to get the json response:

const response = await fetch(sApiPath, {
    method: "GET",
    headers: {
        "Authorization": `Bearer ${sTokenList}`,
        "Accept": "application/json"
    }
});
if (!response.ok) {
    throw new Error(`API call failed with status: ${response.status}`);
}
const data: resp = await response.json();
Enter fullscreen mode Exit fullscreen mode

If successful the environments are in the data variable, the schema has to be set by using a interface, in this case resp.

interface resp{
    value:items[]
}
interface items{
    name:string,
    location:string
    properties: properties
}
interface properties{
    tenantId:string,
    displayName:string,
    creationType:string,
    environmentSku:string,
    isDefault:boolean,
    linkedEnvironmentMetadata: linked    
}
Enter fullscreen mode Exit fullscreen mode

Finally we are going to loop over each environment and write to the List worksheet.

let i: number =2;
data.value.forEach(item =>{
    wsOutputs.getRange("a"+i).setValue(item.properties.displayName);
    if (item.properties.linkedEnvironmentMetadata != undefined) {
        wsOutputs.getRange("b" + i).setValue(item.properties.linkedEnvironmentMetadata.resourceId);
        wsOutputs.getRange("c" + i).setValue(item.properties.linkedEnvironmentMetadata.instanceUrl);
        wsOutputs.getRange("d" + i).setValue(item.location);
        wsOutputs.getRange("e" + i).setValue(item.properties.environmentSku);
        wsOutputs.getRange("f" + i).setValue(i);
    }               
    i++;
})
Enter fullscreen mode Exit fullscreen mode

We now have out list of environments, we switch the mode to Update and run the code to update each environment.

First we get some extra setting from Excel:

const sGeo = wsConfigs.getRange("geo").getText();
const sType = wsConfigs.getRange("type").getText();
const sBody = wsConfigs.getRange("body").getText();            
const payload: body = JSON.parse(sBody);         
const iRows=wsOutputs.getUsedRange().getRowCount();
Enter fullscreen mode Exit fullscreen mode

sType filters out the environment type, as some updates won't work on specific environments e.g Cookie on Teams.
We also get our body, as we can not just update the cookie but other settings to. And last we get number or rows of the environment sheet so we now what to loop over.

Finally we loop over every environment and send the API request:

for (let i = 2; i <= iRows;i++){
    if(wsOutputs.getRange("d"+i).getText()==sGeo){
        const sEnvironmentType = wsOutputs.getRange("r" + i).getText()
        if ((sType == "Not Teams" && sEnvironmentType!="Teams") ||
            sType==sEnvironmentType || 
            sType=="All"   
        ){
            const apiUrl = wsOutputs.getRange("c" + i).getText() + sApiPath2 + wsOutputs.getRange("b" + i).getText() + ")";
            const response = await fetch(apiUrl, {
                method: "PATCH",
                headers: {
                    "Authorization": `Bearer ${sTokenPatch}`,
                    "Accept": "application/json",
                    "Content-Type": "application/json"
                },
                body: JSON.stringify(payload)
            });
            wsOutputs.getRange("g" + i).setValue(response.status);
        }   
    }            
}
Enter fullscreen mode Exit fullscreen mode

The code also updates the table with the API status (204 for successful, 400/500 for failures).

When adding in error handling and run logic, the full code looks like this:

async function main(workbook: ExcelScript.Workbook) {
    const wsConfigs = workbook.getWorksheet("Configs");
    const wsOutputs = workbook.getWorksheet("Outputs");
    let sTokenList = wsConfigs.getRange("tokenBap").getText().replace("Bearer ","");
    let sTokenPatch = wsConfigs.getRange("tokenDynamics").getText().replace("Bearer ", "");
    const sApiPath = wsConfigs.getRange("apiList").getText();
    const sApiPath2 = wsConfigs.getRange("apiPatch").getText();
    const sMode = wsConfigs.getRange("mode").getText();

    if (sMode == "List") { 
        if (!sTokenList) {
            console.log("Bap Token is missing.");
            return;
        }
        try {
            const iRows = wsOutputs.getUsedRange().getRowCount();
            wsOutputs.getRange("a2:g"+iRows).clear();
            const response = await fetch(sApiPath, {
                method: "GET",
                headers: {
                    "Authorization": `Bearer ${sTokenList}`,
                    "Accept": "application/json"
                }
            });
            if (!response.ok) {
                throw new Error(`API call failed with status: ${response.status}`);
            }
            const data: resp = await response.json();
            console.log(data)
            let i: number =2;
            data.value.forEach(item =>{
                wsOutputs.getRange("a"+i).setValue(item.properties.displayName);
                if (item.properties.linkedEnvironmentMetadata != undefined) {
                    wsOutputs.getRange("b" + i).setValue(item.properties.linkedEnvironmentMetadata.resourceId);
                    wsOutputs.getRange("c" + i).setValue(item.properties.linkedEnvironmentMetadata.instanceUrl);
                    wsOutputs.getRange("d" + i).setValue(item.location);
                    wsOutputs.getRange("e" + i).setValue(item.properties.environmentSku);
                    wsOutputs.getRange("f" + i).setValue(i);
                }               
                i++;
            })        
        } catch (error) {
            console.log("Error calling the API:", error);
            wsOutputs.getRange("a2").setValue(error.message);
        }
    }
    if(sMode=="update"){
        try{
            if (!sTokenPatch) {
                console.log("Token is missing.");
                return;
            }
            const sGeo = wsConfigs.getRange("geo").getText();
            const sType = wsConfigs.getRange("type").getText();
            const sBody = wsConfigs.getRange("body").getText();            
            const payload: body = JSON.parse(sBody);         
            const iRows=wsOutputs.getUsedRange().getRowCount();
            for (let i = 2; i <= iRows;i++){
                if(wsOutputs.getRange("d"+i).getText()==sGeo){
                    const sEnvironmentType = wsOutputs.getRange("e" + i).getText()
                    if ((sType == "Not Teams" && sEnvironmentType!="Teams") ||
                        sType==sEnvironmentType || 
                        sType=="All"   
                    ){
                        const apiUrl = wsOutputs.getRange("c" + i).getText() + sApiPath2 + wsOutputs.getRange("b" + i).getText() + ")";
                        const response = await fetch(apiUrl, {
                            method: "PATCH",
                            headers: {
                                "Authorization": `Bearer ${sTokenPatch}`,
                                "Accept": "application/json",
                                "Content-Type": "application/json"
                            },
                            body: JSON.stringify(payload);
                        });
                        wsOutputs.getRange("g" + i).setValue(response.status);
                    }   
                }            
            }
        }catch(error){
            console.log(error)
        }
    }
}

interface token{
    expires_in:number,
    ext_expires_in:number,
    expires_on:number,
    not_before:number,
    resource:string,
    access_token:string
}
interface body{
    enableipbasedcookiebinding: boolean,
    ipbasedstorageaccesssignaturesMode: number,
    applicationbasedaccesscontrolsMode: number
    restrictGuestUserAccess: boolean
    auditretentionperiodv2: number
    isauditenabled: boolean    
}
interface linked{
    resourceId:string,
    uniqueName: string,
    domainName:string,
    instanceUrl: string
}
interface properties{
    tenantId:string,
    displayName:string,
    creationType:string,
    environmentSku:string,
    isDefault:boolean,
    linkedEnvironmentMetadata: linked    
}
interface items{
    name:string,
    location:string
    properties: properties
}
interface resp{
    value:items[]
}
Enter fullscreen mode Exit fullscreen mode

And it it runs right your Excel will look something like this:

excel table

But there is also a super cool benefit, see at the moment it turns out those managed environment features are actually only locked in the UI, not the API (over Microsoft,this is now a pattern). So when we run it on non-managed environments, it still works 😎:

cookie set

As always you can get the files, the Excel workbook and Script are in my GitHub Repo here: https://github.com/wyattdave/Power-Platform/tree/main/Office%20Scripts:

If you would like to get notified every new blog (I also do a few in the Power Platform Community), subscribe below

Subscribe to David Wyatts Blogs

favicon powerdevbox.com
profile
Warp
 Promoted

Warp.dev image

Warp is the highest-rated coding agent—proven by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

Top comments (1)

Collapse
 
balagmadhu profile image
Bala Madhusoodhanan •

this is wicked.. Ideally would have loved this as part of PPAC . But i love the way you have leveraged the most powerful data presentation software EXCEL !!!