cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sharepoint list auto export to excel

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? 

33 REPLIES 33

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:

Beat_0-1656077024098.png

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!

```

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.'.

```

Screenshot 2022-06-24 170453.jpg

 

 

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
Advocate V
Advocate V

santee
Helper I
Helper I

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
Advocate V
Advocate V

@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:

Beat_0-1656155911914.png

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.

Beat_4-1656157445791.png

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:

Beat_1-1656156632372.png

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:

Beat_2-1656156836625.png

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

Beat_3-1656157260334.png

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!

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
Advocate V
Advocate V

@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.

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.

 

 

1.jpg

 

2.jpg

This is what happens when i select Analyst DisplayName

3.jpg

 

With this error:

santee_0-1656351871016.png

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
Advocate V
Advocate V

@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
Advocate V
Advocate V

@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. 

Beat_0-1656398772891.png

 

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

santee_0-1656399543830.png

 

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

santee_1-1656399613907.png

 

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

4.jpg

 

Beat
Advocate V
Advocate V

@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

Beat_0-1656401329647.png

Good luck!

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,012)