There are many under-the-hood tools available to PowerApps developers if they provision the correct resources. Resources not made readily available to those accessing PowerApps strictly via the UI such as:
Power Automate Flow Run History
Entity Metadata Queries
- (Such as listing all of the entities in your environment, their columns, and being able to perform a universal column search!)
and more!
To access these resources you'll need to perform some API Queries and to do that you'll need the following access:
portal.azure.com
access for your organizationAccess to the Power Platform Admin Center
Administrator
orSystem Customizer
rights on theEnvironment
you wish to perform queries against.
With this access you will be:
Creating an Azure Service Principal.
- This is an Azure Application Registration (similar to a User in many respects) and outfitted with a Secret (effectively our password) for programmatic access to Azure resources.
Establishing an Application User within your PowerApps Environment.
- This gives the Azure Service Principal access to various resources within the environment.
Select your App Registration by it's name from the list. Then add, at a minimum, the Service Reader and Service Writer security roles.
Now we're ready to start querying!
Getting the Token
const env = "myenvname";
const urlBase = "https://${env}.api.crm9.dynamics.com"
const url = `${urlBase}/api/data/v9.2`;
/**
* @typedef {Object} AzureAppRegistration
* @property {string} client_id
* @property {string} client_secret
* @property {string} tenant_id
*/
/**
* @type {AzureAppRegistration}
*/
const AzureAppRegistration = {
client_id: "1234a123-ab12-12ab-1abc-1234567890",
client_secret: "12aBC~aBc1-AB1abA12Ab1aB-Ab1Abcd1a12-abc",
tenant_id: "a1a123a1-1a1a-1a1a-a123-1a1a12a1a123",
scope: `${urlBase}/.default`
}
/**
*
* @param {AzureAppRegistration} azureServiceDetails
* @returns {Promise<*|null>}
*/
const getToken = async (azureServiceDetails) => {
const tokenEndpoint = `https://login.microsoftonline.com/${azureServiceDetails.tenant_id}/oauth2/v2.0/token`;
try {
const params = new URLSearchParams();
Object.entries(azureServiceDetails).forEach(([key, value]) => params.append(key, value));
params.append('grant_type', azureServiceDetails.password ? 'password' : 'client_credentials');
const response = await fetch(tokenEndpoint, {
method: 'POST',
headers: {
'Content-Type': 'application/x-www-form-urlencoded'
},
body: params
}).then(response => response.json())
.catch(error => console.error('Error obtaining token:', error));
return response.access_token;
} catch (error) {
console.error('Error obtaining token:', error);
throw error;
}
};
Using the Token (Example)
const report = [];
let counter = 0;
const run = async () => {
const token = await getToken(AzureAppRegistration);
const fetchOptions = {
"headers": {
"accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
"accept-language": "en-US,en;q=0.9",
"cache-control": "no-cache",
"pragma": "no-cache",
"Authorization": `Bearer ${token}`
},
"referrerPolicy": "strict-origin-when-cross-origin",
"body": null,
"method": "GET"
};
const response = await fetch(url, fetchOptions);
if (!response.ok) {
const text = await response.text();
console.error(`[${response.status}]: Error fetching data: ${text}`);
process.exit(1);
}
const json = await response.json();
const entries = json.value.filter(x => x.url.toLowerCase().includes('notif'));
const totalEntries = entries.length;
for (const entry of entries) { // Now, for each entry, we await the inner fetch
const countUrl = `${url}/${entry.url}/$count`;
// Await for each fetch to complete before moving to the next.
// await new Promise(resolve => setTimeout(resolve, 2000)); // Respect rate limits
const recordCount = await fetch(countUrl, fetchOptions).then(response => response.json());
report.push({[entry.name]: recordCount ?? 0});
console.log(`Progress: ${++counter}/${totalEntries}: ${entry.name} has ${recordCount} records`);
}
// Reduce this Object to re-sort its keys by largest count to smallest count
const sortedReport = Object.entries(report)
.sort((a, b) => b[1] - a[1])
.reduce((acc, [key, value]) => {
acc[key] = value;
return acc;
});
console.log(JSON.stringify(sortedReport, null, 2));
}
run();
Other Possible API Uses
// Find all Entities that have a `modifiedon` attribute.
//https://myenvname.api.crm9.dynamics.com/api/data/v9.2/EntityDefinitions?$expand=Attributes($filter=LogicalName eq 'modifiedon')
// The above url fetches too much information. We don't need the actual attributes in our results and we only want the logical name of the EntityDefinition.
// The better url to meet our needs here is:
// https://myenvname.api.crm9.dynamics.com/api/data/v9.2/EntityDefinitions?$select=LogicalName&$expand=Attributes($filter=LogicalName eq 'modifiedon')
// And if we wanted to only include the LogicalName from the attributes we would update our url to:
// https://myenvname.api.crm9.dynamics.com/api/data/v9.2/EntityDefinitions?$select=LogicalName&$expand=Attributes($select=LogicalName;$filter=LogicalName eq 'modifiedon')
// On another note
// https://myenvname.api.crm9.dynamics.com/api/data/v9.2/workflows
// We want to project and only get name,statecode,statuscode,modifiedon, and we want to filter to only include workflows where iscrmuiworkflow is false
// To do this we would use:
// https://myenvname.api.crm9.dynamics.com/api/data/v9.2/workflows?$select=name,statecode,statuscode,modifiedon&$filter=iscrmuiworkflow eq true
// To order this by `name` we would add the following to the url:
// https://myenvname.api.crm9.dynamics.com/api/data/v9.2/workflows?$select=name,statecode,statuscode,modifiedon&$filter=iscrmuiworkflow eq true&$orderby=name
I hope this helps you. Forgive the rough nature of the guide. I've got to omit most sensitive details and this entry is half to document it so I don't forget it myself!