Unanswered

Building Flow to fill Excel template from list, no office scripts available.

(0) Share

Report

Posted on by vaultRchick

Hi everyone,

I may be trying to do something that is impossible given the constraints of Power Automate and Excel, but I had to try to make life easier for our staff. Here is the situation.

First off, the agency I am working for does not have a Microsoft subscription that includes Office Scripts, so all of those solutions are out.

We have grant applications that will be coming in that need to have a budget included. Last year an Excel file was provided for the applicants to fill in, but many of them had such limited Excel use that an excessive amount of staff time was spent answering questions about how to complete the Excel file's worksheets. To try and eliminate that headache, I had the idea to create a flow for a Form I created where the applicant can fill in the data and then take the information from the Form straight into a new Excel file for that applicant. After trying and failing to make that work I set the Forms data to use Power Automate to update a SharePoint list since lists seemed to talk better with Excel. I need to take the list and fill in an Excel template.

The flow for updating the list works perfectly. I have an Excel template created with the necessary tables. The problem comes when trying to update the Table with the information from the list.

Here is the table I need to fill.

Here is my current try at updating the file.

Get tables and List Rows both are working, but I am stuck on actually filling in the template without the use of Office Scripts.

Here is the first part of my compose action for filling in the table with the list data:

I seem to have all the pieces, but am struggling to put it all together. I really need this to work to save our staff time by filling out the Excel sheet for the applicant and thus eliminating hundreds of questions about how to use an Excel workbook.

Like I said at the beginning, I might be trying to do something impossible, but if there is any way to get this Excel template filled it would save my agency tons of man hours when we don't have the staff to walk each applicant through Excel.

Thank you so much in advance for any help you can provide.

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

Are you creating a new Excel workbook for each submission/SP Item? I don't understand how you are using the same workbook or table for all SP list items.

Second, why do you need the Excel formatted that way? You already have the data in a SharePoint list, what else is this Excel workbook doing?
What I'm trying to think about is, why not make a new way to display this info instead of trying to push it into an old excel template?

Was this reply helpful? Yes No
vaultRchick 14 on at

Like (0)

Report

1. I hope to create a new Excel file for each submission, but when I add that in at the beginning, I get an error when I go to list rows having to do with literal strings. So I decided to focus on getting the information into the Excel file and then going back to creating a new file with the data. I even played around with having the Grant Funds list create the file with the Grant Applicant's name as part of the title, then after the Cash Funds list is updated use a variable file name to retrieve the file that was already created but ran into the same error. Basically the conclusion was that I needed to pull from the template to be able to read the rows.

2. The Excel sheet is formatted to add the values of each section, and then produce a total for all funds at the bottom. I have limited knowledge of SP Lists, but from what I can tell, that is not something that it can do. There are three worksheets in the file that all need to be filled in for the different types of funds (Grant Funds, In-Kind Funds, Cash Funds) a fourth worksheet then takes all the totals across all funding areas and types and puts it all together for the total cost of the project. The application committee wants to be able to see the breakdown of how money will be spent in each area as the applicant has to match funding amounts from the combination of other sources with what they are requesting in grant funds. The Excel file s a much better view of how the funding comes together than the SP List and is already formatted to combine each of the other worksheets totals.

Does that make sense?

Was this reply helpful? Yes No
vaultRchick 14 on at

Like (0)

Report

Good questions.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Yes, I kind of figured that was going on with number 1, just get it to work and then worry about how to make new files or whatever.

The second part I would like more information about to help understand. I think you have options to get the same results, but I need some more information.

You said there are 3 worksheets "to be filled in for the different types of funds (Grant Funds, In-Kind Funds, Cash Funds) a fourth worksheet then takes all the totals across all funding areas and types and puts it all together for the total cost of the project."

What worksheet is the table that you are trying to fill in shown in your photo? How are these 3 other worksheets getting filled in with data?

What and Why are you getting with the Apply to Each - "Get Cash Funds List Data" and the "Get Item" action? I don't know why that is in the flow within an Apply to Each.

When it comes to the final output, any data calculation that the Excel workbook is doing, you can do with power automate. It is just a matter of getting the data organized. You could even do all of the calculations in Power Automate and then populate a Word document table that is in the same format for the committee to review.

Was this reply helpful? Yes No
vaultRchick 14 on at

Like (1)

Report

Here is a screenshot of the workbook tabs and the table for the 4th tab that pulls in the combined totals from the other three sheets. With this particular flow, I am trying to fill Sheet 3 - Cash Funds. Once I had the first worksheet in, I planned to use a control to pull the data from the responses of the other two funding areas to fill those sheets by using the applicant's Organization name as an 'is equal to' to get the data I needed. (I also attached the Excel file for you, so hopefully that will help.)

I honestly don't know why the Get Item is in an Apply to Each. When I added the Get Item action to the flow Automate put the Apply to Each in automatically and when I tried to remove the action from Apply to Each told me I couldn't with this message "This action cannot be dragged out of foreach as it depends on an action inside foreach."

The function of Get Item is to pull the information from the list for the newest item created into the flow so that it is available to fill the table in the Excel sheet.

I am open to the idea of completing the calculations in Power Automate and then putting it into a Word document. The key would be that if the applicant were to make changes to their numbers on the document, it would automatically recalculate for them.

Child-Care-Budget...

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Ok, thank you, I downloaded the workbook (password locked, but I can see the different tables and everything.) Just a couple more questions.

1.For the trigger "When an item is created" what is the name of the source sharepoint list for this action?

2.For the action "Get Item" what is the name of the source SharePoint list for this action? (I'm guessing it is the Cash Funds list?)
Also can you show a screenshot of this action completely opened and whatever dynamic content or filter you have for this Get Item action.
I'm trying to understand why that Get Item is inside of an Apply to Each. It doesn't make sense that it is in one, so I want to see what is prompting the automatic Apply to Each. It must be something that you are using from the trigger to filter or identify the specific Get Item.

3.In the Excel workbook on the tab Cash Funds, can you highlight the fields that will be populated by values from the Trigger and also highlight/identify the fields that will be populated with values from the Get Item. (You can paint the fields green or blue or something and then screenshot and say which fields are which color.)

I'm trying to do a basic recreation of your data so I need to know where values should go.

Was this reply helpful? Yes No
vaultRchick 14 on at

Like (0)

Report

Here is the List name.
Here is the Get Item to retrieve the new list data. I used the dynamic ID refers back to the item ID from the previous step. When I added the ID from the dynamic content is when PA added the Apply to each piece.

I may not completely understand question 3.

I thought that the Trigger, When an item is created, does not actually pull the data from that item, just that its new existence is what starts the Flow. The Action, Get item, is what actually pulls the new data values into the flow making those values available to use. Do I have a misunderstanding of how those two pieces work?

Based on that understanding, I have been operating under the assumption that I am only working with one set of data values at this point in the Flow. Therefore, the different responses in the List columns are what I am trying to fill in the yellow spaces below.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Ah ok, that's why I asked. The trigger "When an item is created" will carry all of the data the same as if you used "Get Item" to get a single item.

You don't need that Get Item step because it is basically duplicating the results of the trigger.

Was this reply helpful? Yes No
vaultRchick 14 on at

Like (0)

Report

Good to know. I will take that part out.

Was this reply helpful? Yes No
vaultRchick 14 on at

Like (0)

Report

Taking that out, everything still worked up until the Update a row step. The error I am getting is "Resource not found".

Here is a screenshot of the raw inputs.

Was this reply helpful? Yes No