Flow and Microsoft Form start and end time

[mnemonicator]

Hi there. I am looking possibility to retrive start time and end time from Microsoft Forms. Start time is time when respondent started to fill in form, End time is when respondent press submit button.

These data ia available in a Microsoft Forms when results are exported to Excel, but i didn't see such data in a Get Response Details action.

Any idea?

thank you.

[v-yuazh-msft]

Hi @mnemonicator,

Do you want to get the start time when respondent started to fill in form and the end time when respondent submit the form in microsoft flow?

Microsoft flow support to get the end time when respondent submit the form currently,you could choose the "Submission time" dynamic content of the "Get response details" action to get the end time.

However, getting the start time when respondent started to fill in form is not supported in microsoft flow currently,there is no any way to achieve your needs in Microsoft Flow currently.

If you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Best regards,

Alice

[GabrielStJohn]

Hello, @mnemonicator!

If you have already posted your idea to the Flow Community Ideas Forum then you need to post the URL to your idea’s thread as a reply to this thread and accept it as the solution so that other users may vote for your Flow Idea and identify the information more easily.

Thank You!

-Gabriel

Flow Community Manager

[arunramanathan]

HI ,

i dont see the solution for this yet , i can see more and more users asking and raising this idea via the url since 4 years but no solution yet. if someone has found a way please post it.

[IJeff]

Un-tested Idea: I notice that the Start Time IS listed in the spreadsheet that the Form uses to store its info in OneDrive.  You could potentially use the ResponseID field to lookup the correct row in that Excel sheet and copy the value in the Start time column directly.

-Jeff

[RobElliott]

@IJeff unfortunately the flow cannot access the spreadsheet directly, only the data that is surfaced in the dynamic content box after the get response details action.

[IJeff]

Not even like this?

[RobElliott]

Apologies, I assumed you were originally thinking of the spreadsheet behind the form itself in Forms. Yes, if you have used Forms in Excel to create the spreadsheet it does save the start time and you can just use a compose action to then use the start time in further actions.

[kelleher]

So, just checking... is there no way to pull the 'Start Time" like we can w/ 'Completion Time' as dynamic content when building the Power Automate flow?

I have a Microsoft Form that I am feeding to an Excel doc that further feeds a Data Set for a Power BI dashboard. So I guess I will have to manually copy in the start time from the Microsoft Form export...

[happyume]

You can get this using Power Automate as below:

1 - Get the form ID from the URL of the form to be used in step 4 and 5

2 - Get Tenant ID from Azure to be used in step 4

3 - After get response details, add Get my profile (v2) action ... this will give us our own id to be used in step 4

4 - Send an HTTP request to SharePoint

Site Address: https://forms.office.com/

Method: GET

Uri: formapi/api/[tenantId]/users/[userId]/light/forms('[formId]')

This will give you a lot of metadata about the form in JSON including rowCount (number of responses) - but this will not give you details of those responses (yet)

5 - Create another Send HTTP request to SharePoint

Site Address: https://forms.office.com/

Method: GET

formapi/DownloadExcelFile.ashx?formid=[formId]&minResponseId=1&maxResponseId=[rowcount]

6 - Output from step 5 gives you detail of each response (as an array) with Start Time and End Time

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.

[shalem120kjh]

Hi @happyume ,

I have been using this exact same method for a couple of months now, and it worked well until yesterday: It seems like the "formapi/DownloadExcelFile" doesn't have the Start time and Completion time values populated any more.

The values are still present in the manually downloaded Excel file (through the form itself).

I would be grateful if you could verify that you see the same kind of behavior at your end as well and advise if you see any alternative for getting these values in another way somehow.

Thanks,

Gil.

[kelleher]

Tracking, I'm stuck not being able to use 'Site Address: https://forms.office.com/' when adding the "Send an HTTP request to SharePoint." I'm only able to select from a predefined list of Sharepoint sites that I assume are within my environment. Although, the form I am using that is active is hosted with a URL starting with https://forms.office.com.

Am I missing something? Thoughts?

[shalem120kjh]

The last option in the "Site Address" dropdown is "Enter Custom Value". You need to choose this option, and then you will be able to enter "https://forms.office.com".

[kelleher]

Thank you. Why would I be receiving this error:

"We are unable to find the site address. Please try selecting a site address from the dropdown."

[shalem120kjh]

@kelleher , 

I have just tested it in my environment, and you are right. I also get the "We are unable to find the site address. Please try selecting a site address from the dropdown." error message. But it does not prevent me from inputting the required Uri, and then this error message just disappears, and you can save the flow. In other words, ignore this error message.

Another option (which is actually what I am currently doing) is using the "Http with Azure AD" connector, with the "Invoke an HTTP request action". like explained here: 

https://powerusers.microsoft.com/t5/Building-Flows/Pull-MS-Forms-Question/td-p/255326 

(but I think you need to combine the newer API syntax version from this post).

Good luck,

Gil.

[kelleher]

Yes! I'm getting closer... what part of the API are you pulling for the date and time the user begins filling out the form in your flow? So, of course, there is no API documentation! Ha.

[shalem120kjh]

From this point I'm using the method @happyume suggested you a week ago. I'm creating an Excel file in OneDrive and query it for the Start Time. 

I suggest you watch Reza's video: How to Get Forms Responses using Power Automate | Download Excel, Filter, PDF of Quiz Results - YouT...

By the way, you can see that I have posted myself a question to @happyume a couple of days ago, because those 2 columns (Start Time and Completion Time) disappeared from the resulting Excel, but today I have checked again, and they are back. Very strange.