PowerApps: API Queries

PowerApps: API Queries

Going Beyond the Power Apps UI

·

4 min read

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 organization

  • Access to the Power Platform Admin Center

  • Administrator or System Customizer rights on the Environment 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

By no means comprehensive
// 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!