Answered

Export Sharepoint list to excel containing an array.

(0) Share

Report

Posted on by NotSwanky

I'm trying to export data from a Sharepoint list to excel. I have it all working except one column is an array. I'm believe after I build my table without the array value data then I should go an update a row that contains the array. Below is my setup, but it doesn't appear to work.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Suggested answer

Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You can't really store an array in a column in Excel Directly. You would need to convert it in some way. The most common way would be to turn the array into a comma delimited string. You can do that in Power Automate using the Concat() function.

Concat(array, ';') This would generate a semicolon delimited string.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
NotSwanky 29 on at

Like (0)

Report
Copy link

Link copied!

@pstadnyk

Sorry I should have mention I already did that in the compose above the Update row. I'm trying to update the row with the newly concat'ed string. I'm happy to share more screenshots if needed.

Was this reply helpful? Yes No
Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Looking at the screenshot it looks like you are doing this using a generic connection. To do that you need to format the input as an Item: record for the full row. So the output of the Compose should look something like this

{item: {columnName: 'concatenated value'}}

Just to be clear. You are trying to save the array in a single column in the table, not that the array is the row of the table?

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
NotSwanky 29 on at

Like (0)

Report
Copy link

Link copied!

@Pstork1

Each row should have the appropriate concat'ed string for that row.

My compose looks like this: join(body('Select_Counties'),',')

The compose looks correct in the raw output, but the update row keeps failing. I'm trying to put the county or counties in 1 column called County for each row.

Below is the list I'm trying to export. The column county can have more than one field like the last one on the list there.

Was this reply helpful? Yes No
Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Two issues.

1) The compose is the value of the column, but you are using it in a field labeled Item Properties. As I said that normally means that you are updating the whole row as a record rather than supplying a specific column value. To update the whole row it needs to be in the form of an item record.

2) For the key field you are using current Item, but current Item is the entire record, when it should be just the Index field for the row.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
NotSwanky 29 on at

Like (0)

Report
Copy link

Link copied!

@Pstork1 Could you give me a little more direction as to how I would correct this?

Was this reply helpful? Yes No
Verified answer

Pstork1 69,397 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

The Key Column is the Column in the Excel Spreadsheet that is used to uniquely identify the row to be updated. The Key Value is the value to use in that column to do the update. Then the item properties are the column values to be used for updating in JSON format. For example, In my sample Event is the unique value that identifies each row. In yours it may be something different. That value is the Title field in SharePoint in my example. The item properties are then the county delimited string, but it is insert as a JSON record with the column name {County: string value} Since you are building the Excel Table in the flow there may also be a timing issue. You'll probably need a delay after the creation of the file.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

1 people found this reply helpful.

Was this reply helpful? Yes No
NotSwanky 29 on at

Like (0)

Report
Copy link

Link copied!

@Pstork1 Thank you so much!

That is exactly what I needed!

Was this reply helpful? Yes No