Today I was faced with an enjoyable challenge. I was asked to determine if it was possible to create an entry form within Office 365 that could collect data and then email the data collected within the entry form to someone as an attachment. The attachment had to be in a Excel compatible file format with column headers.
I started off by creating a simple list in SharePoint Office 365. In this test case, my list is titled; Job Requisition. Here I have a few columns of different data types titled “Job Title” (Single Line of Text), “Job Description” (Multi-line Text Box), “Department” (Choice) “Hire By Date”(Date), “Salary” (Number)
I created a mobile PowerApp form to modernize the user input experience, however a regular list input forms work just as well. Once a user fills out the input form, data is saved to the list.
In this example, I decided to use Flow as my programming platform, though in production, Logic Apps may be the preferred solution of choice.
The Flow has 4 steps:
- CSV Creation
- Send Email
Step 1 – launches the Flow based around a new item being created
Step 2 – is a data operation step where we extract the data from the current item list entry into an array. I must admit this step was a bit tricky as the syntax isn’t well documented.
I decided to use the Data Operation – Compose action and then utilize the expression builder to extract the array from the trigger output of the current item that the Flow is running against.
Here we can see how the expression builder is being used to create the array for the object.
Here is the syntax for the expression.
Step 3 – Next we use this object created from the array to create a CSV table using another data operation action.
You are also able to create column headers and determine the “values” under the header from the object created by the array.
This allows us to select the display value and not the stored value for drop downs. We can also use this to help strip out html and markup from our data results
Step 4 – We use the Send an Email action to build our email. The trickiest part here is specifying the file format we are using and defaulting this to an Excel friendly format. Since we are using the Create CSV Table action in Step 3, we are able to give our file a generic name with the “.csv” extension for Microsoft Excel to automatically open the file.
Here is the email that is sent.
Here is the CSV / Excel file that is generated.
Pretty cool and useful. This is also functionality that was not available in InfoPath unless code behind was used.