Help Requested With The Following Flow Creation: Insert HTML table into Excel, Sort data, Save File

[Geoghs]

Hi,

I'm using Shifts to keep track of my team's on-calls. I would like to run a flow automatically that runs on a Friday, pulls 2 weeks of on-calls from the following Monday onwards and emails that data to our departmental secretaries.

I have been able to do this by using Create HTML table which I then insert into an email. The problem is that the data appears to be sorted by shiftID (or possibly the time when the shift was last edited) rather than by date. Hunting around online it seems that I will not be able to sort the HTML table by date (happy to be proved wrong here) and I will need to somehow get this data into an Excel spreadsheet, sort it, then email the spreadsheet (easier I suspect) or create a table with the sorted data and insert that into an email (preferable).

This flow, as mentioned, runs every week and collects data for 2 weeks ahead which sometimes has changed in the interim. It would be preferable although not essential if I could simply keep the same file and just overwrite the data rather than create a new file each time.

The HTML data is pretty simple:
Date                              Name                     Comments
2021-02-22T08:00:00Z Person A
2021-02-23T08:00:00Z Person B
2021-02-24T08:00:00Z Person C
2021-02-25T08:00:00Z Person D
2021-02-26T08:00:00Z Person E
2021-02-27T08:00:00Z Person A
2021-02-28T08:00:00Z Person A
2021-03-01T08:00:00Z Person F
2021-03-04T08:00:00Z Person B
2021-03-05T08:00:00Z Person C
2021-03-06T08:00:00Z Person D
2021-03-02T08:00:00Z Person G
2021-02-23T08:00:00Z Person A        From 1700-0000
2021-02-23T08:00:00Z Person B        From 0000-0600
2021-03-03T08:00:00Z Person E


Also, is it possible to just have the date rather than the date_time? This data is derived from the dynamic content: "Shift Start Time" which appears to be hard coded to this format.

Thank you in advance.

James

[DamoBird365]

Hi @Geoghs ,

For the date, you can use formatdatetime('2021-02-22T08:00:00Z','dd-mm-yyyy') and it will drop the time.

For ordering your data this might help https://sharepains.com/2020/03/05/sort-an-array-in-power-automate/

But what is your data source and what steps do you use to collect it?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Geoghs]

Hi @DamoBird365 ,

Thanks for the reply. In reverse answer to your questions:

3) My data source is an array created from data pulled from Shifts. I've slightly altered the flow in this article https://buildbod.com/2020/06/05/daily-shift-reports-from-microsoft-shifts/ to suit my needs. I don't have permission to upload images but the changes aren't particularly big and don't alter the bulk of the work (I ignored the first 2 "Initialise variable" commands and trimmed a lot of the data pulled).

2) I'd come across the Sharepains article but couldn't quite work how to get the html table inserted into Excel or the new array of data inserted into Excel instead of into the html table. I guess I need to put something after the last "Set variable" but I'm not sure what.

3) Where should I put the formatdatetime command? Based on the article linked above, which box should it go in?

Thanks.

James

[DamoBird365]

Nice article... I will need to keep a note of that one 🙂

For the date, when you are mapping the array of items (properties with shift.....).  Use the dynamic expression builder when selecting the two dates to allow you to use formatdatetime.

You should also be able to use the shifts array at the end of the apply to each (before create html) in the sharepains example.

[Geoghs]

Thanks.

1) With the mapping as per the article linked to above, I have a row thus:
Date      formatDateTime
              Shift Start Time

Where formatDateTime is selected from the dynamic expression builder and added so it looks like this: 

formatDateTime('yyyy-MM-dd')

and Shift Start Time is selected from the dynamic expression builder as is.

Running the flow I get an error message at this step:
The execution of template action 'Select_-_Properties_from_Shift_combine_with_User_Details' failed: The evaluation of 'query' action 'where' expression '{
"Date": "@{formatDateTime('yyyy-MM-dd')}@{items('Apply_to_each')?['sharedShift/startDateTime']}",
"Name": "@outputs('Get_user_profile_(V2)')?['body/displayName']",
"Comments": "@items('Apply_to_each')?['sharedShift/notes']"
}' failed: 'In function 'formatDateTime', the value provided for date time string 'yyyy-MM-dd' was not valid. The datetime string must match ISO 8601 format.'.

2) I sure I can, I think it's going to be trial and error........