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

[kuznip]

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!

[ChristianAbata]

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.

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

[yashag2255]

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!

[ChristianAbata]

hi @yashag2255  I found another way could you try this

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

[yashag2255]

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!

[ChristianAbata]

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

[yashag2255]

@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!

[kuznip]

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 flow

HTML table decoration

Selecting necessary columns

[kuznip]

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 flowSelecting necessary columnsHTML table decoration