A key part of good goverance on any platforms is ensureing your developers have the right training, and this can be particularly important for Citizen Developers in LowCode platforms like the Power Platform.
Micrsoft Learn has some great free training for the Power Platform, all of which is recorded against their profile, along with assements and certifcates.
So putting these 2 together I wanted to create an automated process that checks a new developer has the right training before giving them access to an environment.
Unfortunately Microsft Learn does not appear to be part of the Graph API, im guessing this is because they want to push you to use Viva Learning. Luckily if you do some digging there are a few API's that can help.
The approach I went with was:
- Create Public Collection of Required Module
- Get Developers Learning Transcript
- Validate Completion Script
- Pull it together with a flow
1. Create Public Collection of Required Module
In MS learn you can create collections, which are kind of your own learning paths.
These collections can be private or public. Whats great is if set to public you can share the collection with anyone and it will show them how they are progressing with the modules in the collection.
So all you need to do is create your own tailored learning path as a collection, make it public and share with your developers.
The key thing next was getting the collection into the flow so it can be referenced against what the developer had completed. My origional idea was to just list them in an Excel spread sheet, and that works. But being lazy and wanting only "One version of the Truth" I wanted to find a way to get the live collection.
Fortunately we a little exploring in the Chrome Dev tools I was able to find the collections api call.
So the collection link would be:
https://learn.microsoft.com/en-us/collections/{collectionID}
and the api for the collection would be:
https://learn.microsoft.com/api/lists/{collectionID}?locale=en-us
The schema is well structured but the key thing to notice is the collection can have modules or learning paths. So a learning path with how its own array of modules. In this article Im only dealing with modules in the collection, but it would be to difficult to update it to work with learning paths as well.
2. Check Developers Learning Transcript
This is the hard bit, how do we access the developers learning logs to validate completion. My first approach was to use AI Builder Form recogniser or custom model, it didnt go well.
Considering this is a very simple, consistent table, made by Micrsoft you thought it would be great, instead I ended up with this:
Fortuntaley while gathering training data someone sent me a link to their transcript instead of priniting to pdf. This made me wonder if there was an api that I could call instead.
After another look in the chrome dev tools network tab I found this:
Yes I spend to much time looking at Network traffic in Chrome dev tools
So your share url would be:
https://learn.microsoft.com/en-us/users/davidwyatt-8982/transcript/{unqueShareID}
and the api call would be:
https://learn.microsoft.com/api/profiles/transcript/share/{unqueShareID}?locale=en-us
The schema's can be found here
3. Validate Completion Script
If you have read some of my blogs before you know when it comes to anything complex I goto Office Scripts. Power Automate doesnt do any looping/iteration very well over large/complex datasets, so I always pass it to a Script to do it (I don't actually use the Excel file).
The Script is quite self explanatory so I wont go into to much detail except to say:
It loops over required modules, certificates and applied skills and checks transcript for them, if missing adds to an array and returns to Power Automate.
function main(
workbook: ExcelScript.Workbook,
aModules: intModules[],
aCerts: intCertificates[],
aRequiredModules: intCollection[],
aRequiredCerts: intRequired[],
aRequiredSkills: intRequired[],
aSkills: intAppliedSkills[]
) {
let oReturn: intReturn;
try {
//create arrays
let aRequired: intCollData[] = [];
let aMissing: intCollData[] = [];
//loop over all sections in collection
aRequiredModules.forEach(section => {
//loop over all modules in section
section.items.forEach(item => {
//to to required seperate arry to make debugging easier
aRequired.push(item.data
)
})
})
//loop over required array
aRequired.forEach(item => {
//if cant find requred module in transcript add to missign array
if (!aModules.find(m => (m.uid == item.uid))){
aMissing.push(item);
}
})
//loop over rerquired certificates, if cant find in transcript add to missing array
aRequiredCerts.forEach(item =>{
if (!aCerts.find(c => (c.name == item.title))) {
aMissing.push({
rawUrl: "",
uid: "",
title: "item.title,"
name: ""
});
}
})
//loop over rerquired applied skills, if cant find in transcript add to missing array
aRequiredSkills.forEach(item => {
if (!aSkills.find(skill => (skill.title == item.title))) {
aMissing.push({
rawUrl: "",
uid: "",
title: "item.title,"
name: ""
});
}
})
//return object
oReturn = {
outcome: aMissing.length == 0,
message: "",
data: aMissing
}
return oReturn
} catch (err) {
let sError = JSON.stringify(err)
oReturn = {
outcome: false,
message: JSON.stringify(err),
data: []
}
}finally{
return oReturn
}
}
interface intReturn {
outcome: boolean,
message: string,
data: intCollData[]
}
interface intCollection {
id: string,
listId: string,
name: string,
userId: string,
description: "string,"
items: {
id: string,
listId: string,
type: string,
data: intCollData
}[]
}
interface intCollData {
rawUrl: string,
uid: string,
title: "string,"
name: string
}
interface intModules {
uid: string,
title: "string,"
description: "string,"
durationInMinutes: number,
completedOn: string
}
interface intCertificates {
name: string,
certificationNumber: string,
status: string,
dateEarned: string,
expiration: string
}
interface intAppliedSkills {
credentialId: string,
title: "string,"
awardedOn: string
}
interface intRequired{
title: "string"
}
Full script can be found here
4. Pull it together with a flow
I split the flow into 2, a Child flow with the validation and then a parent flow. This allowed me to call the validation flow for different collections (like a Power Automate and a Power App collection).
First I pass the 4 inputs:
- ShareLink (transcript)
- Collection URL
- Skills array
- Certificates array
I pass the Skills and Certificates in as arrays so you have the flexibility to have different levels. There are not many so it felt like the flexibility was worth the manually effort.
The arrys need to be parsed back from strings to JSON, so 2 parse JSONs are needed. I use a very basic array with just a title key;
{
"type": "array",
"items": {
"type": "object",
"properties": {
"title": {
"type": "string"
}
},
"required": [
"title"
]
}
}
Next are the 2 http calls to get the transcript and collection. As we have shared them publicly we don't need any authentication 😎
For the transcript I use:
https://learn.microsoft.com/api/profiles/transcript/share/@{split(triggerBody()['text'],'/transcript/')[1]}?locale=en-us
The split removes the transcript id from the share url
And for the collection i use:
https://learn.microsoft.com/api/lists/@{split(triggerBody()['text_1'],'collections/')[1]}?locale=en-us
Again I use a split to remove the colelction id from the share url
Then we pass the flow inputs and the http outputs into the Script
"aModules": "body('HTTP')?['modulesCompleted']",
"aCerts": "body('HTTP')?['certificationData']?'activeCertifications']",
"aRequiredModules": "body('HTTP_Collection')?['sections']",
"aRequiredCerts": "body('Parse_JSON_Certificates')",
"aRequiredSkills": "body('Parse_JSON_Skills')",
"aSkills": "body('HTTP')?['appliedSkillsData']?['appliedSkillsCredentials']"
The full child flow then looks like this:
The parent flow takes in the inputs (form/app/email) and then after if the childflow returns success adds the user to the required security group.
As always a copy of the solution and required files can be found here
And thats it, there are so many cool undocument API's out there, and they can make your life so much easier they surface automation or OCR tools.
Top comments (10)
Hi David, Great Post!
I'm trying to follow up with you but I can't figure out what to pass exactly for the skills and certificates inputs at the start of the flow trigger
HI @ahmed_alhallag_bd4776d35a , thank you.
Its a simple object array with the title of the skill or certificate you want to check that they have completed. It should look like this:
Okay got that! I had a different understanding in mind.
What changes do I need to make if I needed to grab all relevant skills and certs of a certain member without passing specific skills/certs?
Another Issue @wyattdave (I'm sorry if I'm being annoying I just got into Power Platform very recently :D)
I found these two folders in your repo but I'm not sure where should I place them exactly, is it in onedrive? sharepoint? would really appreciate your guidance!
Not being annoying at all. For the users skills you need to get them to create a share link (from there profile in ms learn). From the URL you can get the share idea to pass in the API URL. That will return everything they have done on ms learn.
The folders are just to keep everything together, you only need the solution to import in. And the office script which should be stored in your OneDrive script folder or a SharePoint site (my solution uses a SharePoint site). Though the script is to check that certain things have been done, so if you just want full list you can skip that and delete the run script action in the flow.
Thank you for your prompt response David! That was really helpful.
I have a quick final ask:
Sadly not, the ms learn profile is owned by the user not any org, so the only way to get access is for them to create a share link (I looked everywhere for another API to access it). The only way around I can think of for you is to setup a ms form/ app and get each user to add there share link to a list/table. You can then query that to get share id.
Great insights!!!
My only concern is to know if the API's endpoints will change in the future. As not being public or publicly documented, there is a chance that Microsoft could change them specifications.
That's very true and a good call out. Whenever you go off plan you can't expect any warning or consideration about updates so that risk needed to be factored in
Amazing @wyattdave