Pick data from an array to excel row

(0) Share

Report

Posted on by kilaj1

257

I have a SharePoint list that has multiple line items for one record:

I need to get this into a row within an excel table. In the Excel table I need the entries to be entered in different columns, i.e.

I'm using the "Update Row" action, but the problem is getting the second entry to go into the Amount 2 column. I've managed to exact the value out of the SPO table but that will just put it int eh same cell. I'm wondering if there is a way to index the array (from the SPO list) and then select the index number to add the records using select action.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

grantjenkins 11,063 Moderator on at

Like (0)

Report

What happens if you have three entries in your SharePoint list for the same Form, but only two columns for the data in Excel? Will it need to dynamically create new columns in Excel, or will there always be enough?

Also, what will your trigger be in your flow? Will you be doing this for When an item is created in your SharePoint List, or a manual trigger than runs across all items in your SharePoint List?

Was this reply helpful? Yes No
kilaj1 257 on at

Like (0)

Report

Thanks for the reply, at most I will have 5 columns. Ideally I would have it dynamically create the columns but, I will "hard code" in excel for now. I have a PowerApp Trigger (button) that will send the ID to power automate which will then filter the Get SPO list items.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
Given that you said you're using Update a row, I'm assuming the Excel Table already has a list of the Form IDs waiting on the amounts. Hopefully this gets what you're looking for.

For this example, I'm using the following SharePoint List.

And the following Excel Table that includes the Form IDs, but no amounts.

See full flow below. I'll go into each of the actions.

Manually trigger a flow has a single input for the Form ID. You would pass this in via your Power App.

Get items uses a Filter Query where the Title (my Form ID column) is equal to the Form ID input from the trigger.

Select retrieves the Amounts from the values returned from SharePoint. Note that for Map I've used Text mode (see arrow on screenshot).

Update a row uses FormID for the Key Column and the Form ID trigger input for the Key Value. It then uses the following expressions to retrieve the amounts.
//It's important that we include the ? in the expressions as this will ensure it returns null if there isn't an amount at that index. If we didn't include the ? then it would throw an error body('Select')?[0] body('Select')?[1] body('Select')?[2] body('Select')?[3] body('Select')?[4]

After running the flow, and passing in 335, we would get the following output in our Excel Table.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No