Part of my job is to stay on top of the products, features and APIs Microsoft is releasing, and to correlate these to the needs of customers expressed through various channels, including the different online communities. Every now and then I end up looking into some topic that is of no great interest to me, or the company, but we still need to get some better understanding of. Reporting on Microsoft Forms is one such example.
If you ask me about reporting on Microsoft Forms, I’d probably just point you to the built-in report in the Admin Center, or the corresponding Graph API endpoint. I might also point out that there is no officially supported API to use on your own, or that your best bet for building more robust reports is the Unified Audit Log activities captured for events related to Forms. In this article however, we will talk about the unsupported solution, namely taping into the undocumented Forms API.
Here’s the “executive summary” on it: we can leverage the API to report on all forms created by users, and what’s even more important, we can do so via application permissions. The bad news is that if we want to also report on forms owned by (or stored in) Microsoft 365 Groups, application permissions are not supported. Reporting on group forms is only possible via delegate permissions, but keep in mind that the user must have access to any of the groups we are interested in.
Prerequisites for the script
As usual, I’ve prepared a “proof of concept” script that illustrates the process, which you can find over at my GitHub repo. To use the script, you will need to register an application with the Forms.Read.All scope (application permission). Follow the standard steps for creating such an app, then proceed over to the API permissions page > Add a permission and switch to the APIs my organization uses tab. Therein, search for the Microsoft Forms resource entry (with a GUID of c9a559d2-7aab-4f13-a6ed-e7e9c52aec87). Note that the resource will only appear if your organization is licensed for Forms, which most Office 365/Microsoft 365 plans do include.
To complete the process, click on said entry > select Application permissions > check the box next to Forms.Read.All and finally hit the Add permissions button. If you plan to also report on Group forms, you will need to repeat these steps, but this time select Delegate permissions > check the box next to Forms.Read. After this is all done, you will also need to grant admin consent, as with all other application permissions.
Now, these are the requirements on the Forms API side, but if we plan to enumerate forms for all users within the company, we also need to be able to enumerate the users. To facilitate this, we can leverage the Graph API, so make sure that your app also has been granted the User.Read.All (or equivalent) permission. And yes, we will need multiple tokens, and even some additional shenanigans if we want to cover Group forms in a (somewhat) automated manner.
Apart from the scopes, we also need to know which endpoint(s) to use, including the audience for the access token. Since the Forms API is undocumented, your primary source of information here will be the browser’s F12 button/Developer tools. You can also find articles and code samples online. The important details are as follows: use https://forms.office.com/.default for the scope, and https://forms.office.com/formapi/api/ for requests. If you’re lucky, you might even stumble into an endpoint that returns the metadata 🙂
How to cover Group forms
Remember when I said that only delegate permissions are supported for covering Group-based forms? Well, as you are likely aware, delegate permissions require you to obtain a token interactively, thus they are not a good solution when you require a fully automated script. There is one workaround – using the ROPC flow, which when enabled for an application, allows you to obtain a token via username and password. I cannot stress this enough, do NOT do this for any of your production scripts. I only provide such example as a demonstration, do not mistake it for recommendation!
Anyway, in order to leverage the ROPC flow, the application needs additional configuration. Go to Authentication and scroll down to the bottom of the page and toggle the Allow public client flows switch. Confirm the changes, and the app is now much less secure than before… don’t say I didn’t warn you! And yes, you can see this as requirement for many Power automate flows, PowerShell scripts and code samples… none of them should be considered secure. Better use something like MSAL.PS and accept the inconvenience of having to authenticate interactively.
How to run the script
Now that we have covered all the application requirements, let’s also talk about the other prerequisites for the script. Before you run it, make sure to fill in the authentication-related variables (lines 69-71). Pay attention to the $tenantID variable, as its value must be a GUID – the Forms API does not accept tokens issued otherwise. Also, make sure to have the ImportExcel module installed, as the script leverages it for output (and I was too lazy to craft two variations of that).
And that’s all, you can now run the script. The first thing it will do is obtain an access token for the Graph API, as well as one for the Forms API. After that, it will enumerate all users within the tenant and cycle over each of them to cover any Forms. A call to the https://forms.office.com/formapi/api/$tenantId/users/$($User.Id)/light/forms endpoint is made for each user, but as said call doesn’t return details about the number of responses the form has, we follow it up with a separate call to fetch the form’s details. This is probably an overkill, but I though having the count of responses visible in the report is handy.
#Run the script (user forms only) .\Report_Forms.ps1 -Verbose -IncludeGroupForms #Use the -Verbose switch to show additional information as the script progresses .\Report_Forms.ps1 -Verbose -IncludeGroupForms #Use the -IncludeGroupForms switch to include forms owned by Microsoft 365 Groups #NOTE: Using the -IncludeGroupForms switch requires interactive authentication .\Report_Forms.ps1 -IncludeGroupForms
By default, only user forms will be covered. The script exposes an additional parameter, –IncludeGroupForms, which you can use if you want to also cover Group forms. Remember what we talked above: only delegate permissions are supported for these calls. Thus, when you use the IncludeGroupForms switch, the script will try to obtain a token in delegate context. It will prompt you to enter a username and password using PowerShell’s built-in Get-Credential cmdlet. Make sure you use the credentials for a user that has access to all the Groups you are interested in!
As a side note here, you can certainly hard-code the credentials for said user in the script. This is a very obvious bad practice, even if the user himself doesn’t need to have any admin roles assigned. Please DO NOT do this in your code!
Back to the script. After we obtain the “delegate” access token, the script will enumerate all the groups the user is privy of. To facilitate this, we can leverage the Forms API itself, namely the https://forms.office.com/formapi/api/groups endpoint. The result will give a list of all groups that can potentially host forms (that the user has access to), which we can then iterate over. Not having to use the Graph API here saves us from obtaining yet another token (and another interactive login). Sadly, this method does not work with application permissions, thus we can only cover forms in Group the given user has access to, in turn meaning the report’s coverage might not always be accurate
After all the forms data has been gathered, we utilize the ImportExcel module to produce some better looking output. Apart from the Overview sheet that will contain the count of forms per location, the generated Excel file will also include a separate sheet, listing all discovered forms per “container” (either a given user or a group). And just because I can, I’ve included some clickable links to each of the added sheets. Here’s how the report looks like (with the Overview sheet’s content layered over):
Closing remarks
And that’s it in a nutshell. There’s of course a lot more you can do with the Forms API, for example fetch the content of any responses, or even fetch the Forms-related tenant settings. But as the Forms API is undocumented and unsupported, your best bet of incorporating any such methods in your solutions is to capture the network trace when working with Forms in the browser… not a pleasant experience. Maybe one day we will live to see a Graph API implementation.
Speaking of the Graph API, there are some obvious improvements we can made on that front in the script. For example, we can filter out users that are unlicensed, or have their sign-in status blocked, instead of iterating over each user in the tenant. But that’s an exercise I will leave to the reader. The demo script can also benefit from proper error handling, especially if you intend to run it against production tenants. Which you should probably not do, considering the ROPC-based functionality.
Then, there’s the question on actual usage of the Forms. While the number of responses gives you some indication, a proper coverage would rely on incorporating the UAL entries into the script. Not that hard to do, and we actually had such example in my previous article, but querying the UAL datamart synchronously is only possible via Exchange Online PowerShell… yet another token to handle. But if reporting on Forms activity is that important to you, it’s certainly doable 🙂
As always, let me know if you run into any issues with the script!
1 thought on “Primer: reporting on Microsoft Forms in use within your organization”