Delete rows from a Excel sheet where the date is not current

[Yacob_hard]

Hi All,

I'm working with a flow on a recurrence basis, the flow should;

1. Grab the file from the sharepoint drive

2. Attach the file to an email to be sent out

3. After the email has been sent, the excel spreadsheet should have all rows with dates older the present date removed

I've managed the getting and emailing of the file, however a bit stumped when looking at how to only remove the rows that have been added if the dates don't match up.

Here's what i'm working with now, any advice would be greatly appreciated!

The spreadsheet where i want to remove all data other than the headers;

Following the Emailing of the file;

formatDateTime(utcNow(), 'YYYY-MM-DD')

Hope this makes sense!

[v-xida-msft]

Hi @Yacob_hard,

Could you please share a full screenshot of your flow's configuration?

Do you want to remove rows whose date (Date & Time column value) is older than current date?

I have created a Excel file within my SharePoint library and the data structure of it as below:

I have made a test on my side and please take a try with the following workaround:

• Add a "Recurrence" trigger, Interval set to 1 and Frequency set to Day.
• Add a "List rows present in a table" action, specify your Excel file.
• Add a "Apply to each" action, input parameter set to output of the "List rows present in a table" action.
• Within "Apply to each" action, add a Condition, click "Edit in advanced mode", type the following formula:

@less(formatDateTime(item()?['Date & Time'], 'MM/dd/yyyy'), utcNow('MM/dd/yyyy'))

Within "If/yes" branch of Condition, add a "Delete a row" action, Key Column field set to Date & Time, Key Value field set to Date & Time dynamic content of the "List rows present in a table" action.

• Under "Apply to each" action, add a "Get file metadata" action, specify Site Address and File Identifier field set to your Excel file.
• Add a "Get file content" action, specify Site Address and File Identifier field set to Id dynamic content of the "Get file metadata" action.
• Add a "Send an email" action, Attachments Name - 1 field set to DisplayName dynamic content of the "Get file metadata" action, Attachments Content - 1 field set to File Content dynamic content of the "Get file content" action.

Image reference:

The flow works successfully as  below:

Best regards,

Kris

[Yacob_hard · Accepted solution]

Hi @v-xida-msft,

Yes, i'd like to remove all rows where the Date is not current after the days email has been sent out.

Rather than have the delete action before the email goes out, i'm instead looking at having the delete action at the end of the flow, would that be possible?

I've just tested this flow which seems to work as expected but would be good to get another pair of eyes across this.

The Conditional expression was a great help though!

Thanks!

[Yacob_hard]

Thanks, I moved the deletion of data above the actual sending of the report to after the rows were deleted which seems to be working better now.

@v-xida-msft I was wondering if it's possible to return a numerical value within the email that is sent out based on the amound of rows present in each table -1 (headers row).

[GabrielStJohn]

Hello, @Yacob_hard!

Have you had a chance to apply @v-xida-msft‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel

Flow Community Manager

[Yacob_hard]

Hey @GabrielStJohn,

This did work well, however because we're processing 700+ emails a day, i often found that the AWS services would throw a 400 error and be unable to find the sheet in sharepoint.

I've since abandoned this as it doesn't seem this is a scalable solution to tracking inbound and outbound mails from a shared mailbox.

Any alternate suggestions would be most welcome! 🙂