Hi!
It is no news that Developers and Business Users speak different languages. This is why, sometimes when I ask a business User to update a configuration file, it usually does not ends well. It happened to me when sharing [.json], [.config], or [.xml] files,
these users do not know which tool to use to edit the values, and also happens that a plain text format, is often not intuitive for them.
That’s why a while ago, I took a different approach: I decided to adapt myself to their tools and ways to work. In a nutshell the working model with these files is as follows
- Business Users: Microsoft Excel
- Developers: Json
And as we are in a time where [Serverless] is the premise, I challenged myself to see if I could do it without writing a single line of code. To do this, use Microsoft Flow as the base integrating information store in OneDrive with Excel files and interacting with the flow using HTTP requests.
The first thing was to create a spreadsheet with information to share, in this case a list of users, email accounts and twitter.
The, I saved the file in OneDrive and I grant access to the file to my Business Users. They can happily edit the file now.
And then I created a Flow to access to this configuration file but in a developer mode. The Flow is using an HTTP Request as the trigger, then it reads the information from the Excel file and it closes with an standard HTTP Response including the content of the Excel file in Json format.
There is only one tricky step here, and is when we need to build the Json response. To do this, I use a simple Flow Expression working with the main body of the Excel file.
When we save the Flow, we can access to the public URL where the flow is published. This is the URL we can use in our Apps to access to this information. To test this without a full .Net App, we can use Postman (I wrote about this some time ago). 1 Url copy and 2 clicks later, and we can see the HTTP response with the configuration information in Json..
As soon as Flow uses this Excel file, we will see a new column in the excel file [__PowerAppsId__]. We should not delete this column. The easy way to explain this is: Flow uses this column as base to check where / when a file was accessed and to see the changes on this.
However we can add more information to the file, and if we want, an extra column with new information, in example:
As soon as the changes are reflected and stored in OneDrive, a new request to our Flow will return a new complete Json response with the new column and the new rows.
As a ZERO CODE solution, this rocks! and it rocks twice if you also gives your users the chance to use Excel as a edition tool for this.
Happy Coding!
Greetings @ Toronto
El Bruno
References
Tools
- Postman, home page
My posts on Logic Apps
- Working with Visual Studio 2017 and Logic Apps
- Let’s create our first Logic App in Azure
- Logic Apps, Introduction and main differences with Microsoft Flow
My posts on Flow
- Check your Apps health in Azure using Application Insights and Microsoft Flow
How To iterate through collections of elements (Excel, Sharepoint, Cognitive Services and more…)Trigger a Flow associated to an item in a #SharePoint list - New input options when using Flow Button: list of choices
- Working with Expressions
- Export and Import Flow definitions with advanced options
- How To emulate a Button for a Flow using a Browser
- Advanced options to work with an HTTP Action in Microsoft Flow, Headers, Authentication and more
- Working with Variables in Microsoft Flow
- Handle exceptions, errors, timeouts and more in a Microsoft Flow definition
- Buttons Widgets in iOS and Android
- New action definition to use Computer Vision APIs from Microsoft Cognitive Services
- Schedule a Flow execution, recurrence supported!
- “Save as ” and interaction with physical buttons FLIC and BTTN
- Email Sentiment Analysis on a PowerBI dashboard, and Flow
- Support for Team coauthoring and sharing
- Calling a Flow from a C# ConsoleApp
- Push Notification Support
- How to review a Flow process
- How to invoke a Flow from an external app using HTTP Request
6 comments