Adding Holidays and Notifying users

[Sidhant_02]

Hi everyone,
So few months back I had designed a flow (Power Automate flow) that collects all users from a Microsoft Teams group (with headers in the excel sheet: Name, Email, Dates of the week) this file is created at the start of the week (i.e. Monday at 9:30 am IST) and a message is shared on the Teams channel that the file is being created for the week please enter your work mode for the current week (WFO - Work From Office and WFH - Work From Home).
But there were some issues like if a holiday comes in (like on Monday like for New year 1st Jan) in this case no user will login and fill in the timesheet (as it's a Holiday 😉) so in such cases I wanted the flow to fill it as 'Holiday'.
So my flow looks like this:

This flow creates an Excel which looks like this:

So I created a HolidayList excel which has all the list of holidays as per my region

So now I was thinking how to refer this (holiday list sheet) and if there is any date that is present and matches with one of the dates in the week (like 26th Jan is a Republic Day which is on Friday) so for that week on Friday the flow should add the value to all rows for that day (for all users) as 'Holiday'/ 'Holiday_name (Republic Day) Holiday'.

And the next thing that I was thinking of sending a reminder (at-least for two days like Tuesday,Wednesday: For users who have not filled the time sheet 'Please fill your work mode time sheet for the week' and next week (the previous week timesheet should not be editable for users)

So if anyone has any idea about this please let me know it would help and if any more information is needed do let me know.

Regards,
Sidhant.

[Sidhant_02]

Hi @BCBuizer ,
Thanks for the response I will try the approach that you have suggested. Before doing that I did check your point 1 wherein you did confirm that the expression is right only I need to remove the double quotes ("") for the value part and use single quotes (''), but that resulted in Invalid json message in Power Automate

So I replaced it with double quotes again and then there were no errors, then when I tried tested the flow it failed at the Compose action inside the loopCompose action fail
And when I checked in the OneDrive folder as well the file was created (the headers were displayed properly) but they had no values i.e.Employee Name, Employee email (which is because the compose action i.e. loop did not execute)

Anything that I have missed?.

Regards,
Sidhant.

[Sidhant_02]

Hi @BCBuizer ,
I did try the new approach wherein you suggested to add a 'Select' operation just before the loop like:
For the From: used this

{You had written:  @{outputs('List_rows_present_in_a_table')}}
Then in Compose action used the following Expression:

"split(outputs('Header'),',')[0]" :  "if(contains(join(body('Select'),','),split(outputs('Headers'))[0]),'Holiday','')",


"split(outputs('Header'),',')[1]" : "if(contains(join(body('Select'),','),split(outputs('Headers'))[1]),'Holiday','')",

"split(outputs('Header'),',')[2]" : "if(contains(join(body('Select'),','),split(outputs('Headers'))[2]),'Holiday','')",

"split(outputs('Header'),',')[3]" : "if(contains(join(body('Select'),','),split(outputs('Headers'))[3]),'Holiday','')",

"split(outputs('Header'),',')[4]" : "if(contains(join(body('Select'),','),split(outputs('Headers'))[4]),'Holiday','')"

//Here I have used double quotes on both sides as when I tried to use just single quotes for the value part it displayed invalid JSON message.

//In Flow (Actual Expressions)
{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
 "@{split(outputs('Header'),',')[0]}" : "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[0]),'Holiday','')}",
"@{split(outputs('Header'),',')[1]}"  : "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[1]),'Holiday','')}",
"@{split(outputs('Header'),',')[2]}"  : "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[2]),'Holiday','')}",
"@{split(outputs('Header'),',')[3]}"  : "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[3]),'Holiday','')}",
"@{split(outputs('Header'),',')[4]}"  :  "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[4]),'Holiday','')}"
}

And on Save it gave the following error message:

Is there anything that we might have missed?.

Regards,
Sidhant.

[Sidhant_02]

Was able to solve the issue it was due to:

Before:
"@{split(outputs('Header'),',')[0]}" : "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[0]),'Holiday','')}"

//It was Headers not Header:
 "split(outputs('Headers'),',')[0]": 'if(contains(join(body('Select'),', '),split(outputs('Headers'))[0]),'Holiday','')'

So now I was able to save the flow but now was facing a new issue:

(I guess it might be the indexing that I was talking about in my yesterday's reply as [0] might refer to SNo: which is populated by varCounter), so I did make some modifications to the expression in the Compose action.

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
  "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[3]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[4]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[5]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[6]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[7]),'Holiday','')}"
}

So any idea what is going wrong (in both the approaches : 1st without the select action and the other with Select action) @BCBuizer 

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

I would suggest to only keep the dates in the header as per point 3. in my previous reply. It seems somehow the Sno key is duplicated.

[Sidhant_02]

Hi @BCBuizer ,
Something like this:

Updated:
{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
  "@{split(outputs('Headers'),',')[0]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[0]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[1]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[1]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[2]}": "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[2]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[3]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),','),split(outputs('Headers'))[4]),'Holiday','')}"
}

But even after doing the changes:

Still facing the same issue.

Regards,
Sidhant

[BCBuizer · Accepted solution]

Hi @Sidhant_02 ,

It seems the second argument in the right set of split functions was missing:

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
  "@{split(outputs('Headers'),',')[0]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[0]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[1]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[1]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[2]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[2]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[3]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[4]),'Holiday','')}"
}

[Sidhant_02]

Hi @BCBuizer ,
I did the updated expression that you have mentioned in your latest response which is:

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
  "@{split(outputs('Headers'),',')[0]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[0]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[1]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[1]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[2]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[2]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[3]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[4]),'Holiday','')}"
}

So now there is no error but it generates the following file:

So here the Email and SNo are missing (not populated; as they are no longer present in the Headers action) & there additional 3 columns Column6,Column7 & Column8 and the user data is not populated (from the Teams group), also I had made one change to the 'Holidays.xlsx' that is mentioned 18/04/2024 as a Holiday :

so I expected for 18/04/2024 column to be populated with 'Holiday' but that did not happen. Is the date format in the Holiday.xlsx sheet correct?.

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

If adding those columns back to Headers, please do so, but then update [0]-[5] to [3]-[7].

For the dates I see a difference in the date formatting for the month: 04 vs. 4

[Sidhant_02]

Hi @BCBuizer  ,
After adding back the required Headers that are SNo, Employee Name, Employee Email and made the indexes change in the compose action:

{ "SNo": "@{variables('varCounter')}", "Employee Name": "@{items('Apply_to_each')?['displayName']}", "Employee Email": "@{items('Apply_to_each')?['email']}", "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[3]),'Holiday','')}", "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[4]),'Holiday','')}", "@{split(outputs('Headers'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[5]),'Holiday','')}", "@{split(outputs('Headers'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[6]),'Holiday','')}", "@{split(outputs('Headers'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[7]),'Holiday','')}" }

The file was generated properly only the Holiday was not being populated, you mentioned the difference of 04 vs 4 so for single digit dates like 9th April was Gudi Padwa even if I tried using 04 it took 4 as final o/p in the sheet

and when I checked the inputs after reading the HolidaysList it was something like this

(I also had added tomorrow's date as a holiday to test if Holiday is populated or not)

Regards,
Sidhant.

[BCBuizer · Accepted solution]

Hi @Sidhant_02 ,

In the Excel file, try to format the Date column as text instead of a date. I think that should resolve the issue.