Is Flow a solution for auto-update excel from multiple excel files in multiple Microsoft Teams?

(0) Share

Report

Posted on by cravenmc

I am looking for an automated solution to update one excel workbook from 31 Microsoft Teams weekly workbooks.

I'm new to flow and so I'm not sure if this is a viable solution for my problem.

Each team has four members, a captain and three members. Each week a new workbook is created with 5 worksheets. The first worksheet is for Team Points. It gives daily totals for each member and then a combined team total for the week. The remaining four sheets are identical in design where it tallies daily points and automatically calculates the daily total as it also automatically accumulates a total for the week. The calculated totals from each member sheet are automatically updating the Team Points sheet in summary form.

I know how to do this outside of Microsoft Teams but the whole purpose of the MS Teams was so that we didn't have to get IS involved in setting up shared folders.

The program coordinator has owner permissions to all 31 teams.

I'm trying to look for a simple and quick way for the program coordinator to get or have all 31 weekly team points so she can create an email to send out to everyone on the top three teams for the week.

I am open to any creative way to resolve this challenge. If you have any questions, let me know.

Thank you,

Michelle Craven

Categories:

General topics

I have the same question (0)

All responses (11)

Answers (0)

ScottShearer 25,290 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@cravenmc:

As I understand your process, you'll need to add actions to connect to all 31 workbooks, open them and extract the team totals. There may/may not be a way to do this with an api call via the http action where you could loop through all of the sites.

Is it practical to ask the teams to copy the workbook to a central SharePoint library? That library could be exposed within Teams for each group. If so, there is a reasonably simple solution to your issue.

Scott

Was this reply helpful? Yes No
cravenmc 10 on at

Like (1)

Report
Copy link

Link copied!

Hi Scott: thanks for your reply. I had thought about having each captain copy their final workbook into a shared location for just the captains and program coordinator but the pc doesn't want them to be able to see the other teams workbooks. That is why we set up the Microsoft Teams where only the four members could she their team workbook.

I was thinking this might be one of those things that just can't be solved thru automation. I just hate giving up and know someone more creative and resourceful than me will have a possible solution. Just need to find them and the solution.

I'm not sure how to look into the api via http at this point but I'll go digging to learn how that works. thanks again!

Was this reply helpful? Yes No
Back2Basics 68 on at

Like (1)

Report
Copy link

Link copied!

Rather than have them copy and paste, could you set up an excel file that looks at particular cells in the seperate workbooks. This should update as you open the 'overall' workbook or when you refresh the data in that workbook?

Are they updating the same rows and columns each time? If so this should be fairly simple to effectively to get a copy of this by pointing the 'overall' cells to the same ones in the other workbooks? Just a thought?

Was this reply helpful? Yes No
GabrielStJohn Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello, @cravenmc!

Thank you for posting on the Flow Community Forum! Have you had an opportunity to apply @Back2Basics‘s or @ScottShearer's recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel
Flow Community Manager

Was this reply helpful? Yes No
ScottShearer 25,290 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Easy fix:
Create folders for each team in a library
The boss has access to all folders
Each team has access only to their own folder
Teams won't even see the other folders
Add columns to the library for the data you need
Make these columns required
Create a Flow that runs when a new document is added that collects the data you need from the Excel workbooks
You might store this data in a separate SharePoint list
Creat a view that summarizes the data for the boss
Let me know if you need an example

Scott

Was this reply helpful? Yes No
cravenmc 10 on at

Like (0)

Report
Copy link

Link copied!

Hi Scott, I thought about a shared library but the coordinator doesn't want them to be able to see other workbooks. So I don't think that is a solution. Thank you for sharing your ideas!

Was this reply helpful? Yes No
cravenmc 10 on at

Like (0)

Report
Copy link

Link copied!

Hello Back2Basics:

This is exactly what I want to do. I have done this very thing outside of MS Teams with another project but I don't know how to make it work inside of Teams and yes the totals are in the same cell address on all 31 workbooks. Actually they are all calling it the same workbook name, which I might have to ask them to put their team name and then date at the end.

Do you know how to find the external location inside of teams? Thanks for your help.

Was this reply helpful? Yes No
cravenmc 10 on at

Like (0)

Report
Copy link

Link copied!

I have not found a solution yet. Thank you all for responding. Once I get required access I'm going to try the library one. I'll let you know if it works, thanks!

Was this reply helpful? Yes No
Back2Basics 68 on at

Like (0)

Report
Copy link

Link copied!

Sorry, I don't know about MS Teams. If I find anything I will let you know.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

@cravenmc:

I believe the solution that Scott provided is close to what you are asking for. I think is was just missing the idea of using a schedule for starting the steps.
You have stated that each is a Microsoft Team. Every Team channel by default has a files tab. The files tab is actually a SharePoint Online library.
1. Establish a convention for naming the workbook so the latest document can be identified.
2. Have each team chose which Team channel library they will store their workbooks in.
3. Have the program coordinator create a folder in his OneDrive for Business to store the weekly workbooks.
4. Create a Flow with the Recurence trigger set to 1 week.
a. The flow would connect to the workbook in a teams library and copy the data needed to a workbook in the program coordinator's OneDrive. You may be able to add a step that will run this from all one flow. The issue with this is that it is harder to troubleshoot if there are any errors such as URLs and may fail part of the way through if for any reason one of the workbooks is not there. It may be better to create spearate flows for each team.

Was this reply helpful? Yes No