PowerAutomate Daily email sending from Excel

Question

Hey all!

We have a nightly supervision report that i'd like the info automatically emailed out to our leadership team. I'm about 70% there but am missing a piece that I cannot figure out.

Currently we're using an iPad to fill out a SharePoint Form that dumps the info into an Excel sheet. This sheet lists date/time/name of the person submitting and spits the data into 17 different areas (Member services, Safety, Facilities, etc). My current flow is

Recurrence > List Rows present in a table > Apply to Each / "value" / Send an email (V2)

This is properly pulling and formatting the data from Excel but is sending one email per row. I'd like all data in the collum to be listed at once and only send one email instead of (like last night) 7, due to 7 reports being submitted.

Thanks!

Answer 1

Dear JC,

You may use Rest API to only extract the responses of daily data instead of all Excel rows and send an email with html table to the corresponding team in Power Automate.

Here is a flow for your reference.

To get all form responses, use Rest API.

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

Method: Get

Uri: formapi/api/forms('formid')/responses

Filter Array: outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['value']

formatDateTime(item()?['submitDate'],'yyyy-MM-dd') is equal to formatDateTime(utcNow(),'yyyy-MM-dd')

Compose2: items('Apply_to_each')?['answers']

Compose3: json(item()['answers'])[0]['answer1']

Compose4: json(item()['answers'])[1]['answer1']

Compose5: items('Apply_to_each')?['responderName']

Compose6: formatDateTime(items('Apply_to_each')?['startDate'],'yyyy-MM-dd')

Append to array variable:

Value:

{

"submitDate": "@{outputs('Compose\_6')}",

"responder": "@{outputs('Compose\_5')}",

"Question1": "@{outputs('Compose\_3')}",

 "Question2":"@{outputs('Compose\_4')}"

}

More information: Power Automate: MS Forms responses to Sharepoint - Microsoft Community

Welcome to share any updates when you have time.

Thank you for your effort and time.

Sincerely

Cliff | Microsoft Community Moderator