cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JW_TH
Regular Visitor

Flow from an excel spreadsheet with expiry dates

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @JW_TH ,

 

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

 

Excel table:

vJessicaGmsft_2-1643165282535.png

Flow:

vJessicaGmsft_0-1643164804850.png

vJessicaGmsft_1-1643165165559.png

vJessicaGmsft_3-1643165340387.png

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

View solution in original post

JW_TH
Regular Visitor

@v-JessicaG-msft 

 

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

View solution in original post

12 REPLIES 12
MarvinBangert
Super User
Super User

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

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'.

 

Flow.png

 

 

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.

 

Email output.png

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:

vJessicaGmsft_0-1641983511238.png

vJessicaGmsft_2-1641983630808.png

vJessicaGmsft_1-1641983602676.png

 

Best Regards,

Jessica Gu

JW_TH
Regular Visitor

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

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:

MarvinBangert_0-1642102544772.png

 

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
Regular Visitor

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

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

Hi @JW_TH ,

 

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

vJessicaGmsft_0-1642476956281.png

vJessicaGmsft_1-1642476997104.png

vJessicaGmsft_4-1642477286044.png

 

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

vJessicaGmsft_2-1642477125319.png

vJessicaGmsft_3-1642477236560.png

 

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

 

Best Regards,

Jessica Gu

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

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

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!).

 

SS1.pngSS2.png

Hi @JW_TH ,

 

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

 

Excel table:

vJessicaGmsft_2-1643165282535.png

Flow:

vJessicaGmsft_0-1643164804850.png

vJessicaGmsft_1-1643165165559.png

vJessicaGmsft_3-1643165340387.png

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
Regular Visitor

@v-JessicaG-msft 

 

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

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (2,226)