Unanswered

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

(0) Share

Report

Posted on by Sidhant_02

986

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (36)

Answers (3)

SanmeshG 1,947 Moderator on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
Verified answer

SanmeshG 1,947 Moderator on at

Like (2)

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

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

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (1)

Report

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

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.

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
SanmeshG 1,947 Moderator on at

Like (0)

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

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,

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
SanmeshG 1,947 Moderator on at

Like (1)

Report

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

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.

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
Verified answer

SanmeshG 1,947 Moderator on at

Like (1)

Report

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

Hi @Sidhant_02 ,

Please separate the date expressions in the Header with comma , so that they are treated as separate header names.

To upload the file to the Teams Channel , use the SharePoint create file action before that get the content of the Newly created Excel file. of the the file to the SharePoint Document Library in the /Shared Documents/General and post the message in teams channel.
Else you can create the file in SharePoint itself initially before creating it in OneDrive and post the message to it after the team members are added in excel.

Hope this completes the answer , please mark this reply as a solution.

Thanks,
Sanmesh

Was this reply helpful? Yes No