Forum Discussion
MicrosoftIs there an API to read Microsoft forms data
waltksfc I abandoned my attempts at the time, but looking at it again now I see that I can get info on a specific form with:
https://forms.office.com/formapi/api/forms/{formid}Then I can get the questions or responses on that form with these:
https://forms.office.com/formapi/api/forms/{formid}/questions
https://forms.office.com/formapi/api/forms/{formid}/responses
Get the {formid} from the /forms API call.
ProtivitiDan - thanks for the reply. Yep - that works. Successfully retrieved JSON-formatted questions and responses - but only after logging in/authenticating in our O365 tenant. I can see that the Forms design page is making AJAX calls to the API, but it looks like it's passing some sort of access tokens in the request headers/cookie. It would be nice if Microsoft would publish some information about this, and allow some kind of persistent access token in the url/path for an API call - as they do when they provide a shared collaboration link.
At this point, I've already created a process to ingest the responses from a downloadable Excel sheet, so there's probably no advantage for me to use the API (since I have to be authenticated before it will work).
Thanks again - appreciate you taking the time to respond.
Is there any way you could let me view your code? I am trying to do the exact same thing you mentioned above.
Currently I manually click the "Open in Excel" button under "Responses" Tab. There is a macro that copies the data from sheet 1 into sheet 2 and does some re-formatting and parsing of image urls etc. Then it takes the values from each field and places them into specific bookmarked fields in a word template to build a standardized report. As of right now I have to manually dl the excel file after each submission then manually enter the store number to choose which row to use to build the correct report.
I would like to just be able to link the table in excel to the data produced by clicking the button. But even if I could just automate the process of "open in excel" it would help tremendously. I am stuck here and quite frustrated. I have tried power automate but get lost pretty quickly.
Could you share your methodology? I would truly appreciate it.
Thanks in advance!
At this point my need to use the Form has evaporated, so I haven't done anything with it lately. The process I ended up with still had a couple manual steps, and as they were very application-specific, I doubt that it would help you much.
I abandoned the attempt to use the API, because it would only work when I was already online and authenticated in a browser (and couldn't use it a server-based web app). There was another post in this thread that had a workaround for this, but it seemed too complicated for me based on the temporary nature of my app.
My manual process was to download the spreadsheet with form results, then import it into a SQL Server table using BCP. The download and BCP import were manual steps that only took a minute to run, but I had to do it every day to refresh the data (so I never did get to realtime form results). Once it was in a SQL table in raw form, I had some other automated steps that transformed the raw data, linked it with other tables in the database, and became the data source for a .Net Core web app.
Sorry I don't have more helpful advice. Honestly, it would have been easier to just develop a custom web app from scratch than try to incorporate the Forms data (but using Forms for my particular use case was "suggested" by management).
waltksfc I needed the exact same thing, and thanks to you and ProtivitiDan I was able to figure out the correct path.
If you want to make a custom connector for Forms in Flow, you'll need a way to handle authentication.
- Make a new app registration in Azure AD
- Use https://global.consent.azure-apim.net/redirect as the redirect
- Under Certificates & secrets, create a new client secret and save the value somewhere before closing the window because you won't see it ever again
- Under API permissions, add a permission. For this you'll want to go to the "APIs my organization uses" tab and find Microsoft Forms. Add it as a Delegated permission if you want the logged in user to only access their own forms in Flow. I haven't tried Application permission for Forms, but I imagine it will grant access to everyone's forms.
- If you don't want to have to approve every Delegated permission request, make sure after adding it that you press the "Grant admin consent" button.
- Take note of the Application ID.
- Open up Flow and create a custom connector using the App you just created.
There are a lot of guides out there for how to make one. However, the relevant fields for this connector to work with Forms are:
- Host: forms.office.com
- Base URL: /formapi/api
- Identity Provider: Azure Active Directory
- Client id: your App ID from above
- Client secret: the value of the client secret from above
- Resource URL: https://forms.office.com
- Scope: your permissions from above (I used Forms.Read Responses.Read.All )
When you make the connector definition, you insert any dynamic parameters in curly brackets in the request URL. For example, to make an action that requests all questions that are on a specific form, you'd use https://forms.office.com/formapi/api/forms/{formId}/questions for the URL and then formId will be a parameter that you'd fill in for the Flow's action.
I'm now able to authenticate to make web requests to Forms in a Flow so I know you'll be able to do it, too. Hopefully my little guide helps put you on the correct path!
