Sharepoint list auto export to excel

[Anonymous]

Would anyone have a flow suggestion that will, in Powerapps, when a button is pressed, that the data from my SharePoint list could be exported to an excel workbook and then send the excel file as an attachment in Outlook? 

[Beat]

Hi @santee I try to avoid multiple selection columns as much as I can (too much headaches) and have no example at hand. With regards to the datetime format, you may find the solution here.

I often use a formatDateTime() expression in Compose actions, then use the Output of the Compose action to display the date in the desired format. Example:

Expression: 

formatDateTime(outputs('Get_GCC_item')?['body/endDate'],'dd.MM.yyyy')

You can construct the expression by selecting Expression, type formatDateTime( and then switch to dynamic content and insert the date to be converted, then type ,'<your date format>').

You will find all the possible formats under the link I provided above. Important to note is that MM is month while mm is minutes. Good luck!

[santee]

```

InvalidTemplate. Unable to process template language expressions in action 'Add_a_row_into_a_table' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string 'Created' was not valid. The datetime string must match ISO 8601 format.'.

```

The error seems obvious, i've added string 'Created' (the name of the column) instead of linking the actual column...but i can't link the actual column, if i switch to dynamic content i can only choose body or value

I tried placing the Compose before the Get items (as per your example) but it doesn't let me chose the column i'm interested in.

I know i must be infuriating (a complete n00b) ...but i do try to google before coming here first. Power Automate seem very logic, up to a point. I was expecting this to be really easy ...like: add a row, for column X convert time from whatever you find in whatever i want ... but noooo

[Beat]

@santee that's yet another topic. Maybe this helps Solved: The datetime string must match ISO 8601 format - Power Platform Community (microsoft.com)

[santee]

Yeah... it doesn't want to cooperate.

So the column which has a list/array object doesn't want to provide me with the value i'm interested (i tried mapping first('column_name')?['name']  ... no good)

The column with the iso8601 refuses to give me the "normal" time (I tried to mapp directly with formatdatetime('Get_items', 'yyyy/MM/dd HH:mm') ... i get same error as above ...; I also tried to do the convert time zone right after Get_items ... the moment i select the column i'm interested it converts into a apply to each loop ... and still gives the same errors).

I am ready to let go of this issue though, as I can probably do the transformation inside SQL (the data i pull from the Sharepoint List, and dump into an excel ... will get pushed through a Python script into a PostgreSQL DB).

So, i'm left with the that colum that doesn't want to provide me with the value I need (DisplayName) but it return a list

[Beat]

@santee with the FormatDateTime function I can't help you much (I don't see why it wouldn't work) but I made a little test on my side for the multiple selection column.
I created a SharePoint list with two columns, Team and Person, where Team is a single line of text and Person is a Person column that allows multiple selections. I want their emails, but not send 3 emails if there are 3 people picked, only one with the 3 email addresses. For the first part it's quite simple:

Note that we initialize the variable varEmails to store the email addresses. In the Get items action I added an ODATA filter 'Person ne null' as I don't want to process any item that has no email address (or no DisplayName in your scenario). Since we are going to use the variable within a for each loop, we must set the Concurrency Control of the flow to 1 (as otherwise, if a flow is started while the previous one is still running, it may pick up the values from the other flow). You do this in the settings of the trigger itself.

Then I added a 'Get item' action, from the same list, with the ID from the 'Get items' outputs. This automatically wrapped it into the first Apply to Each:

Next I added an action 'Append to string variable', which was wrapped in Apply to each 2, nested within the first for each loop when I added the Person field. I used a semicolon to separate the values, as this way the variable can be directly used to send one message to multiple emails:

Then, below and outside that Apply to each 2, back in the first Apply to each, I added the 'Add a row into a table' action, using the varEmails variable. At this point I could as well send an email, using the variable on the TO: line

And finally, I set the variable to null, in order to clear the email addresses and start from scratch for the next item.
Please give me a thumbs up if you find this useful. Thank you!

[santee]

Same issue as before, the moment I'm trying to replicate your apply to each 2 and select the column that piece of flow changes into an apply to each 3, at select output it changes into value, and then the apply to each 2

[Beat]

@santee you don't need to create the Apply to each 2, it will create it automatically when you select the field (e.g. Person DisplayName or Person Email) from dynamic content. Put a screenshot of your flow here.

[santee]

Currently my flow is like this:

Manual trigger, search in an Excel Template and delete ever row (i went with this as doing an "update when new items" is not consistent... most of the times it triggers the flow but doesn't pull the new data). Next is getting the items from the SP list.

Next is a for each loop, going through all values (the Compose + Current Time + Converttimezone + formatDate Times is just to fix my previous issue with dates formatting).

In this 'Apply to each' loop, one of the columns is the one causing the issue. If i write its name (lets say Analyst) in the Dynamic Content i get:  Analyst, Analyst Item, Analyst Claims, Analyst DisplayName, AnalystEmail, Analyst Picture ... so on.

IF i chose (just) Analyst ... my excel will return [] 

IF i chose any other variant (I am interested in Display name), then the whole 'Apply to each' flow changes by itself by adding a (within) 'Apply to each 2' and changing the AnalystDisplay into value.

I tried doing as per your input (and other solutions on Google) with initializing a variable, compose and other stuff ... all those solutions require me to select Analyst DisplayName (at some point)... as soon as i do that it changes that flow with an Apply to each loop, with value as values.

[santee]

This is what happens when i select Analyst DisplayName

With this error:

As you can see, by selecting Analyst DisplayName in the Analyst Column .. changed the flow, forcing a new apply to each 3 to appear. I ran it anyways, just so you can see the above error

[Beat]

@santee that is normal behaviour with multi-selection columns. To follow my approach, try these steps:

1. Right after the trigger, initialize the string variable varAnalysts.
2. In the Get Items advanced options, add an ODATA filter Analyst ne null to exclude any items that have no person selected.
3. Right after the Get Items action, insert a Get Item action and fetch the current record via its ID. This will create an apply to each loop.
4. Within this apply to each loop, append to string variable and append Analyst DisplayName from dynamic content. Now the multiple names are in one single string, and it won't create another apply to each loop when you use the variable instead of the multiple selections.
5. Don't forget to clear the variable after your Add a row into a table loop as otherwise it would be cumulative and append all the Analysts in the list, and ensure concurrency of the flow is set to 1.

[Beat]

@santee I think you miss the Initialize Variable and the Get Item steps. Then you use the variable instead of the dynamic value for Analyst DisplayName. 

[santee]

At step 2, if i select Analyst then type ne null, it will create an each loop with this error:

If i just type "Analyst ne null", the flow behaves, but still errors out:

At step 4, the moment i select the Analyst DisplayName it does create an each loop

[Beat]

@santee the error 'The expression "CG Analyst ne null" is not valid is likely due to the space in the column name. 

Try CG_x0020_Analyst ne null or simply CGAnalyst ne null. If that doesn't work, it's likely that this column name has been changed at some point in time. Go to the SharePoint list, List Settings, edit the column and look at the last parameter of the URL in the browser's address bar, for example

Good luck!

[santee]

I'm back ... with more disappointment ...tried all above, still same 2 issues: data not getting pulled, the moment i select my column in question it creates an each loop (where it was not supposed to). At this point I'm at: if you want to have a remote meeting, I'm up to it