Forum Discussion
Microsoft office form add Time duration field
MSCIDM it is not a good idea to try to manipulate the spreadsheet behind the form. You can use a flow in Power Automate to get the start and completion times of your form responses, save them to a SharePoint list and then you can get the duration with a calculated column.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
Rob_Elliott I understand your advice. Though there is a growing need to use these worksheets behind forms as active tools for SharePoint and even teams channel active status and record keeping. Adding formula and column categories is only a natural progression of the tool. Can you provide a power automate example that scrapes the original workbook to output to another workbook or channel?
MSCIDM an example that gets your forms responses and saves them to the SharePoint documents library as a new excel spreadsheet containing the start and completion time is shown below.
I've used a manual trigger followed by the Get my profile (v2) action.
Next, add a Send an HTTP request to SharePoint action with the following parameters. I've renamed it to FormInfo.
Site Address: https://forms.office.com/Method: GET
Uri: formapi/api/3d234255-e20f-4205-88a5-9658a402999b/users/@{outputs('Get_my_profile_(V2)')?['body/id']}/light/forms('VUIjPQ_iBUKIpZZYpAKZm2tQtOivi1hPg51lh-vO1rRUQ1RUTzExRjNMTVYwVjNZUTEwSFRYUVhLNi4u')
So for the the first part in blue you need your Tenant ID.. For the Users/../ select ID from the dynamic content box, and for the last part in blue paste in the ID of your form inside single apostrophes as shown below:
Next, add another send an HTTP request to SharePoint action with the following parameters. I've renamed it to FormResponses.
Site Address: https://forms.office.com/
Method: GET
Uri: formapi/DownloadExcelFile.ashx?formid=VUIjPQ_iBUKIpZZYpAKZm2tQtOivi1hPg51lh-vO1rRUQ1RUTzExRjNMTVYwVjNZUTEwSFRYUVhLNi4u&minResponseId=1&maxResponseId=@{body('FormInfo')?['rowCount']}
For the part in blue paste in the form ID (this time without single apostrophes) and for the row count select it from the dynamic content box. In Headers enter Content-Type then application/json as shown below:
Finally, add a SharePoint create file action and select your site and library. We need the file name to be unique so in the File name field enter an expression of utcNow() followed by .xlsx
For the File Content field select Body from the FormResponses section of the dynamic content box.
Run the flow and the new Excel file will be created in the library:
You can now open the spreadsheet and do whatever calculations you want on it.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)