Forum Discussion
Doranarni
Microsoft
Nov 28, 2018Is there an API to read Microsoft forms data
is it possible to read Microsoft forms submitted data . we are exploring concepts and would like to get data of submitted forms using API.
waltksfc
Aug 17, 2021Copper Contributor
ProtivitiDan Any more information about this? I can get JSON responses as you did, but was not successful getting form responses. Wondering if any documentation has appeared anywhere.
ProtivitiDan
Sep 01, 2021Copper Contributor
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.
- Oh_No_Its_MicrosoftOct 06, 2023Brass Contributor
Unfortunately, now in 2023 the useful calls to forms API to get responses, now just doesn't work.
This works to get ID and other bumpf:
https://forms.office.com/formapi/api/forms
However, to get very useful form and responses:
https://forms.office.com/formapi/api/forms/{form id}
https://forms.office.com/formapi/api/forms/{form id}/reponses
.....using the "form id" from the https://forms.office.com/formapi/api/forms
.... now does not work. I tried all sorts of variants methodically.
I guess Microsoft is desperate for people to get tied into their very old asynch/dynamics infrastructure and go through "PowerAutomate" aka "Flow" instead, to output raw data to an incredibly slow SharePoint list, or an archaic Excel with all the bugs!
Ah! Why is so much in Microsoft-land so disappointing!
Data Scientists want the raw data on-tap!
Why force developers into another inefficient Microsoft infrastructure that's built to tie the customer into a poor solution?
- GideonHuangOct 08, 2023
Microsoft
Oh_No_Its_Microsoft Hi Dan, thanks for being interested in using Forms api to get form and response raw data. https://forms.office.com/formapi/api/forms/... are not designed for programmed retrieving data and it would be changed by other scenario needs and infrastructure changes. We're working on publishing microsoft graph api for Forms which will be used for the programmed retrieving data. These Forms Graph APIs will be released in coming monthes.
FYI Ruoying Liang
- Oh_No_Its_MicrosoftOct 09, 2023Brass Contributor
Hi GideonHuang,
Thank you for your reply.
In reply to your email/message: “ https://forms.office.com/formapi/api/forms/... are not designed for programmed retrieving data “
An API (Application Programming Interface) from Microsoft is not designed for programmed retrieving data?
Please can you help me understand why a Microsoft link that contains the word API in the URL above, isn’t an API?
It was a working API according to people in this thread - they posted it as a solution.
We programmers want to work with Microsoft to achieve our goals. Sometimes, we don’t understand why Microsoft develops cost effective solutions which work, only to subsequently replace them with either very expensive Microsoft solutions that don’t work well at all, or we have to wait 5 years for the basics. I’m sure the vast majority of developers and engineers at Microsoft are great and really want to develop the best they can.
It’s the erratic management of the Microsoft products, the illogicality and the bugs that really are hard to understand and deal with.
An API that’s now not an API, is a completely new one on me. I can only assume the development on this Forms API has been deliberately stopped by Microsoft to ensure that PowerAutomate is used as a tie-in to extract data?
Thanks for letting us know about forthcoming developments. However, how long will it be before we can interrogate MS Graph? You mention months –this could be 60 months which is 5 years.
Does extracting batches of 100 records every 5 seconds from MS Graph ultimately sound an efficient method for data retrieval compared to your original simple JSON API? Is this scalable as a data pipeline?
I thought I should express my concerns on behalf of organisations who are genuinely confused with Microsoft’s management of this data pipeline.
Assuming I'm utterly misguided - Please can you help suggest a straight-forward and efficient solution to extract Microsoft Forms data automatically via an online call to an API? (A simple method that has a persistent secret tokens so it doesn't require a manual authentication every time?)
Such as:
https://forms.office.com/formapi/api/forms/{form id}responses
So many other non-Microsoft Forms solutions providers have such easy to digest web APIs. I've done it in about 15 minutes with non-Microsoft solutions it's so very very easy!
After hours and hours of investigations and trying, I wonder why automated data extraction from Microsoft Forms is so oddly challenging and requires so much infrastructure and workarounds? Why prohibit easy automated extraction to a data science application?
I'm sure a simple API solution would be very useful for 100% of people who have tried to work this out within this thread who utilise Python, R, Java, MATLAB or C++ for their data science.
Many thanks
- aura_sanDec 06, 2021Copper Contributor
Hello, ProtivitiDan,
Do you know where can I find API documentation or any kind of guide?
I'm trying to retrieve responses for a specific QuestionId but can't seem to find the combination for the GET request.
Thank you!
- ProtivitiDanDec 06, 2021Copper ContributorSorry @aura_sun, but I don't know of any official documentation from Microsoft yet. JR2021 may have one of the best guides to date in the thread above.
Dan- aura_sanDec 06, 2021Copper ContributorThank you, ProtivitiDan!
- waltksfcSep 02, 2021Copper Contributor
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.
- marshall smithJan 24, 2022Copper Contributor
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!
- waltksfcJan 24, 2022Copper Contributor
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).
- JR2021Sep 27, 2021Brass Contributor
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!