Flow from an excel spreadsheet with expiry dates

[JW_TH]

Hello

I'm new to Power Automate and am trying to build a flow that sends an email to me 30 days before a date in a column in an excel file setting out the content of that row. The file has a list of a bunch of sites with an expiry date for a certain thing - the email is designed to be a reminder.

Based on reading suggestions from elsewhere, so far I think I need to use:

• Recurrence (would be daily)
• List rows present in a table (select the table to be used)
• Filter array - I set the 'From' to 'value' and then 'Expiry date' is less or equal to (but then don't know how to say 30 days in the future, so temporarily set it to now)
• Create HTML table
• Send an email

However, I get lost around the Create HTML table, as PA starts to do odd things (like repeatedly inserting 'Apply to each' stages). I've tried various things, and managed to get it to send emails, but either they just list every site (and the date is garbled as 44290 or something), appear as a complete mess, or are blank.

Would anyone be able to explain how such a flow should be laid out? Many thanks in advance!

[MarvinBangert]

Hi @JW_TH 

regarding the Filter array: Please check the date column within your Excel in which format this is. To calculate the future date, you can use the "addDays()" function in combination with "utcNow()", so your Filter array should look something like:

<dynamic value from Excel> is less than or equal to addDays(utcNow(),30,'MM/dd/yyyy')

"utcNow()" uses the current timestamp, using the "addDays()" you add 30 days to "utcNow()" and in the end you need to give the same format as within your Excel table. MM = month, dd = day, yyyy = year, so in this example '01/10/2022'.

Regarding your HTML table, I guess you are using the dynamic value "Item" from your "Filter array", please try "Body". This shouldn't add any "apply to each".

Can you please share a picture of the output of your "Create HTML table" and the "Send an email" from a run? Like when does the date column loses it's format? Please also check, that the date column is formatted as a date column within excel.

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[JW_TH]

Thanks for the response @MarvinBangert 

The date column in the spreadsheet in question is formatted to be a date format (dd/mm/yyyy as I'm in the UK).

I have used body in the Create HTML table. However, if I try to switch the columns to 'Custom' and insert 'Site' in the value section, it turns the 'Create HTML table' into a subset of an 'Apply to each', so I have to leave it as 'Body'.

If I run the flow as is shown above, I get an email in which all sites are shown, there is an additional column added named ItemInternalId, and all the dates are garbled as below.

[v-JessicaG-msft]

Hi @JW_TH ,

You could change the format of the date column in excel to text, so that the date can be displayed correctly in the Create HTML table.

I have test for your reference, please try the following workaround:

Best Regards,

Jessica Gu

[JW_TH]

Hi @v-JessicaG-msft 

Thank you very much for your help. This has worked - however, still a couple of issues/questions:

1.  I don't want the first two column (@odata.etag and ItemInternalID) to display in the email (I don't know where they come from, as they are not in the original file) - how do I get rid of them? If I try to change the columns displayed in the 'Create HTML table' by going to the 'Custom' bit, PA automatically inserts an 'Apply to all' level. I would like to be able to customise which columns display (as in the file I will eventually use there are a lot, but I only want site and expiry date).

2. Why was the workaround needed? Does PA not recognise dd/mm date formats, or does any type of date not get properly transferred into an html table ? I don't really want to have a column with manually inputted dates as it relies on people inputting them correctly when updating the column (whereas having the date format set would automatically adjust any errors) - for example, I changed one date from 09/02/2022 to 9/2/2022 and it didn't get picked up when I tested again.

[MarvinBangert]

Hey @JW_TH 

To your first question: Please add a "Parse JSON" action, the "Content" is the Body from "Filter array", you can generate the "Schema" by using an old run, getting the body from "Filter array", click within the "Parse JSON" on "Generate from sample", paste the copied body from "Filter array" and submit. Then try again using "Custom" Columns within "Create HTML table". Please notice to change your "From" Body also to the "Parse JSON" action. The parse will let you select the values within dynamic values and it shouldn't create an "apply to each" anymore:

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[JW_TH]

Thanks @MarvinBangert - that has worked exactly as suggested - thank you!

If anyone has any idea about the dates issue, do let me know.

[v-JessicaG-msft]

Hi @JW_TH ,

1.You could use this expression " addDAYS('1899-12-30', int(outputs('Compose')),'dd-MM-yyyy') " to convert dates.

2.You could also try to select ISO 8601 for DateTime format when getting rows in table excel:

Please refer to this link: https://powerusers.microsoft.com/t5/General-Power-Automate/Manipulating-Flow-Excel-Date/td-p/365876

Best Regards,

Jessica Gu

[JW_TH]

@v-JessicaG-msft 

Thanks again for the response. Unfortunately neither of these solutions worked. In both cases the outputs I got were every date, regardless of whether it was more or less than 30 days ahead. Plus I also seemed to be unable to add the site column to the output table.

[v-JessicaG-msft]

Hi @JW_TH ,

Could you show me a screenshot of the your flow? Provide more information to help me solve your issue.

Best Regards,

Jessica Gu

[JW_TH]

Hi @v-JessicaG-msft and sorry for the delay in responding. Please see below for a screenshot of the flow, and the output in the email. As you can see, the email output is just a list of all the expiry dates in the file, and does not only list the ones coming up in the next 30 days (and there are no site names!).

[v-JessicaG-msft · Accepted solution]

Hi @JW_TH ,

I have a test for your reference, you could refer to the screenshot:

Excel table:

Flow:

JSON:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Site": {
                "type": "string"
            },
            "Expiry date": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Site",
            "Expiry date"
        ]
    }
}

 Expression:

addDays(utcNow(), 30)
formatDateTime(item()['Expiry date'],'dd-MM-yyyy')

Best Regards,

Jessica Gu

[JW_TH · Accepted solution]

@v-JessicaG-msft 

Thank you - it finally works! Your help (and @MarvinBangert ) is really appreciated!!