How to Retrieve Start and Completion Time From Microsoft Forms

[sdedic]

Hi all,

Is there a way to retrieve Start and Completion Time of a Microsoft Form response directly into Power Automate flow?

I would like to create an item in SharePoint list and include these two values from Microsoft Form. However, inside Power Automate I don't get these two values available. On the other side, both Start and Completion Time are being generated inside Excel sheet when I download Form response as Excel file.

Here is the Excel file containing response data from MS Form:

Here is the output from Get Response Details action in Power Automate, and as you can see there are no Start and Completion Time values:

I could you Submission Time from the above output as Completion Time, but still I need Start Time somehow. If there is no way to use Start and Completion Time directly inside Power Automate, I will go and take them from Excel sheet. Anyhow, I will wait for someone to provide an advice how to approach and resolve this.

Ty

Best

[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.

[tantofaz]

Hello!

I'm having same problem but could solve using your feedback happyume

my flow (image 1) is quite simple as you can see but i'm not sure where i should insert your suggestion (image 2)

image 1

image 2

[sdedic]

Hi @happyume,

thank you for your help. I am not sure why, but 'Send an HTTP request to SharePoint' action doesn't recognize the Forms site address:

Any idea why's that happening? I double checked that I copied the address without additional spaces, I also retyped it, copied from my browser, but it doesn't recognize it at all.

Best

[sdedic]

In the meantime, I've found an alternative to this, and it takes fewer steps to accomplish what I am trying to get.

Since the Form and Excel sheet are synced and every new Form response is automatically added to the Excel, I used 'Get a row' action from Excel Online (Business) list of actions and as a 'Key value' entered 'Response Id' from 'Get response details' step.

This allowed me to retrieve Start and Completion Time from Excel and add it to the 'Create item' action.

Thnx

Best