cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kuznip
New Member

How to send all Forms responses for the last week to a specific email?

Hi,

 

I'm having a trouble building the flow mentioned in subject. I'm using Form connected to Excel table. The main issue points: 1) how to select all responses for the last week only (I'm trying to use getPastTime function in KeyValue field in getRow (Excel Online for business element) 2) how to transfer those rows into email (any format - spreadsheet, pdf will be OK).

 

Thanks!

8 REPLIES 8
ChristianAbata
Community Champion
Community Champion

hi @kuznip  please use this expresion to get one week ago date

getPastTime(1,'Week','yyyy-MM-dd')

 

the problem with excel is thatwhen you use get items the dates will be converted into a numbers like this.

 

date.PNG

Like date in the example, so I recomend you to use sharepoint list to save your date.



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA
yashag2255
Dual Super User II
Dual Super User II

Hi @kuznip 

 

The date time type columns get converted to numbers when getting data from excel to Flow. You can refer to this post to understand how to work with date time type data in excel in Power Automate: https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

 

Once you have the excel rows, you can use the action create html table and create a table from the data and then add that table to the email body and send it. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

hi @yashag2255  I found another way could you try this

 

https://www.christianabata.com/2020/04/how-to-get-dates-from-excel-files.html



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

Hi @ChristianAbata 

 

Are you using any calculation?

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

@yashag2255  nop just changing the fotmat column in excel to text.



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

@ChristianAbata  gotcha! So it would be difficult if there are other columns depending on the date and time type data in the sheet. I remember vaguely mentioning about the same on my post but in my opinion it is not a best practice. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Thanks, it was a good advice to format columns with dates as text. However, as a novice, I had many issues on other preceding steps (i.e. - where to enter formula, I didn't understand what are exact outputs, etc.

As for selecting dates, I simply selected ticks for the date, entered in 'Completion_time' column of Excel (ticks(item()?['Completion_time'])), which were greater or equal to  ticks for last 7 days (ticks(getPastTime(7, 'Day')).

So, here's the images with the workflow, with some refinement (needed to select specific columns, because @odata.etag and ItemInternalId fields were automatically added, so I got rid of them) and some decoration of emailed table (improved HTLM table visual style):

 

 

General flowGeneral flow

 

 

HTML table decorationHTML table decoration

 

Selecting necessary columnsSelecting necessary columns

Hi, @ChristianAbata 

Thanks, it was a good advice to format columns with dates as text. However, as a novice, I had many issues on other preceding steps (i.e. - where to enter formula, I didn't understand what are exact outputs, etc.

As for selecting dates, I simply selected ticks for the date, entered in 'Completion_time' column of Excel (ticks(item()?['Completion_time'])), which were greater or equal to  ticks for last 7 days (ticks(getPastTime(7, 'Day')).

So, here's the images with the workflow, with some refinement (needed to select specific columns, because @odata.etag and ItemInternalId fields were automatically added, so I got rid of them) and some decoration of emailed table (improved HTLM table visual style):

General flowGeneral flowSelecting necessary columnsSelecting necessary columnsHTML table decorationHTML table decoration

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,020)