Unanswered

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

(0) Share

Report

Posted on by 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!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

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

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.

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

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

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!

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (1)

Report

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

hi @yashag2255 I found another way could you try this

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

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

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

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!

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
kuznip 5 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
kuznip 5 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No