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?
Solved! Go to Solution.
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:
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
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
@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!
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
@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.
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
@santee that is normal behaviour with multi-selection columns. To follow my approach, try these steps:
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
@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
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
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.
Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.
The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.