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
Super User II
Super User II

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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (3,574)