Sending a file at the start of every week in MS Teams

[Sidhant_02]

Hi everyone,
As we are in hybrid mode structure (post COVID), to know the work mode of employees  in my organization at the start of every week (Monday) an excel sheet is shared in Teams to fill the work mode for the week which looks like this:

So currently I am sharing this kind of file at the start of the week in my MS teams group with my colleagues (for record purpose), but is there a way we can automate the following process by using Microsoft Power Apps or Power Automate by integrating it in the MS Teams groups.
So what I am expecting is at the start of every week (for all 12 months) an automated excel sheet should be generated with all the names of the employees (for which it can refer to the list of employees in the group that I want this list to be shared along with the correct weekday dates for the current week) like the date for this weekday are: 17/07/23 to 21/07/23 and the next week it should update the dates and send the file in the teams group.

This is the group in which I want to share the excel sheet to enter the working mode for the week (so for names it can refer to the group, so instead of defining a list of employee's manually it should be automated so if a new employee joins in they can bed added to the next week list automatically using automation)
I have checked the Attendance Bot plugin but I want to check if it can be done using basic components in Power Apps and using flow. Do let me know on how to achieve the following.

Thanks & Regards,
Sidhant.

[SanmeshG]

Hi @Sidhant_02 ,

This can be achieved using Power Automate. A few questions though , 

1. You want to update the same Excel Every week ? (I am not sure if the headers can be updated)

2. Is it okay to share a new Excel File at the beginning of every week with the List of all Employees? 

Also here I am assuming that Employees will update the WFH or WFO status manually in the File.

[Sidhant_02]

Hi @SanmeshG ,
To answer your queries:
1. You want to update the same Excel Every week ? (I am not sure if the headers can be updated)
Yes, because the headers will be containing the Sr. No column (which will remain the same for most of the cases until a new employee is added to the group MS Teams  which in my case will be the 'All Team Members') and the only part that should be changing at the start of every week will be the dates part
(Current week)
17/07  18/07  19/07 20/07 21/07

Next week:
24/07 to 28/07

The reason to retrieve the dates to be auto-populated is to reduce the manual input
(we can discuss about this later as well to update the headers part after we get the functionality running)

2. Is it okay to share a new Excel File at the beginning of every week with the List of all Employees? 
Works for me (as currently I am just sharing the same file at the start of the week i.e. Monday) but to differentiate each file for the name of the file can we add a like date timestamp (like the file generated for this week will be named Attendance_17-07-2023 

And your assumption is right employees will manually filling their work mode status for the current week and it will be WFO or WFH.

If you any questions that you want to be answered do let me know.

Regards,
Sidhant.

[SanmeshG · Accepted solution]

Hi @Sidhant_02 ,

I have created a flow for this.

Please refer the below steps.

1. Create  scheduled flow which runs every Monday.

2. Initialise a counter variable to keep the count of No. of Employees

3. Create a blank file in excel in onedrive or SP document library and use it to create a new .xlsx file.

4. Create the Headers dynamically as shown with SNo , Employee Name and Dates(which are dynamic)

5. Create the table in the file and pass the above header(created in step 4 as a parameter)

6. List the team members and add them to the newly created file using the add a row action.

7. Share the file from OneDrive or use the below thread to send the file from Power Automate(as there doesn't seem to be a direct action to send  a file in team group chat).

https://www.damobird365.com/custom-action-post-email-attachment-to-teams/

Some expression used in the flow are as below , the rest of them are dynamic and can be found from the Dynamic Content while creating the flow.

formatDateTime(utcNow(),'dd/MM/yyyy')
formatDateTime(addDays(utcNow(),1),'dd/MM/yyyy')
formatDateTime(addDays(utcNow(),2),'dd/MM/yyyy')
formatDateTime(addDays(utcNow(),3),'dd/MM/yyyy')
formatDateTime(addDays(utcNow(),4),'dd/MM/yyyy')

If this helps , please give this a thumbs up and accept this as a solution.

Thanks,

Sanmesh

[Sidhant_02]

Hi @SanmeshG ,
Thanks for the quick solution, I will try this on my side and reply if there is any more help that I may need. 

I am just not getting the file name which you have enclosed in the guid string

So is the correct format:

Expression:

guid(Attendance_formatDateTime(utcNow(),'dd/MM/yyyy')).xlsx

And the other issue is that I am able to retrieve the Table name defined earlier:

(Here Table1)

And also @SanmeshG , for the File content in the create file, you have mentioned to pass the Get_File_Content there was no option of Body:

Regards,
Sidhant.

[Sidhant_02]

Also I am using a OneDrive Business (so do we have to use OneDrive) and in the Create File (OneDrive for Business, the only option that I got was File content no body option which you have shown  in your steps.

So what I did was selected the Add Custom Table option and entered data in the required fields:

So is it correct @SanmeshG ?.

Regards,
Sidhant.

[SanmeshG]

Hi @Sidhant_02  ,

You can use either OneDrive for business or SharePoint Document Library.

You don't need to use guid for File Name the below expression will be fine in your case. 

Attendance_formatDateTime(utcNow(),'dd/MM/yyyy').xlsx

You can select the Enter Custom name option and enter the name that you have given in the create table action

 Also , currently I am not sure if you are using a group chat or a teams channel to post the file to Teams.

I would suggest you to go ahead with teams channel approach as this creates Document library in SharePoint and the document uploaded there would reflect in the teams.

Else you can create the file in SP/OneDrive and share the link and post that link every week.

If this helps , please accept this answer as a solution.

Thanks,

[Sidhant_02]

Hi @SanmeshG ,
Yes I selected the Custom Table option and entered the same name which I used when the table was created and it is ok now.
Is this correct:

I used the file name convention that you mentioned but it is not valid:

And I am using a Teams Group chat and was thinking of sending an automated message along with the file in Teams like this:

Also you have recommended to use a Teams channel in that how will the file be posted every Monday (could you explain the process for same; are there any changes) and now after this flow is built. Will I need to share the file link every time (On Monday), can't be automated?. 

Regards,
Sidhant.

[SanmeshG]

Hi @Sidhant_02 ,

Attendance_formatDateTime(utcNow(),'dd/MM/yyyy').xlsx

Here , the one that is written in bold is an expression and Attendance_ and .xlsx at the end is a hard-coded value.

It can be automated ,

If you are using a Teams Channel, Upload the file to a document library and it should reflect in the Files Section(Tab) in the Teams Channel.

[Sidhant_02]

Hi @SanmeshG ,
Now the flow is running successfully after fixing the file name issue

But now the date fields are getting populated in the same column instead of populating in different column (as you had shown in your response)

And you also mentioned that the process of uploading to a Teams Channel can be automated, so could just explain are there any additional steps that need to be added in the existing flow (and we can add a message that is shared to the Teams channel like : Please fill your Timesheet for this week).
Assume this is the Teams Channel where I want the file to be uploaded: (Power Apps Activities)

Regards,
Sidhant.