How I converted a table in an email to a graph on a website? Hint: Azure Functions and Logic App

This mini-project started as a necessity. 

I get an email like this everyday, which gives daily numbers for different metrics. The problem with such an email is that it is difficult to keep track of or gauge the rate of growth of these metrics. If you want to know how what the previous day's numbers were you need to go to the previous day's email. 

The better option any day is to have a graph that shows the values of the metrics every day. Something like this:

With this goal I set out to make this system but with as less coding and expenses as possible. 

First obvious step in the process was to get hold of the email and extract its text. The easiest way to do this is to use Azure Logic App. 

Here is what my logic app looks like:

When any new email arrives in my Outlook 365 account, with a subject that contains "System usage Stats as on", it passes the body of the email to an Azure function named 'EmailTextCleanerFunction' (code below), whose job is to clean any HTML text.  

The Azure function returns cleaned-up text, which is then written to a file in the "attachments" folder. The name of the file is "<sender email id>.txt". 

Congratulations! We have crossed our first hurdle. We have got our source data neatly written in a text file.

Before we proceed further, here is our EmailTextCleanerFunction. 

Now that we have the email body written in a file, we need to start our next step: actually reading the text and extracting the data to keep it in a form that we can use to create the graph. 

And this has to be done every time a file is written in the 'attachments' folder so we create a new Blob Trigger function, which gets triggered whenever a new file drops in this folder.

This blob trigger function, named very simply BlobTriggerFunction, lives in EmailBodyProcessorFunctionApp. There is another function named 'HttpTriggerFunction' as well, we will talk about it later.

Here is the function definition. In essence, this function is the heart of the whole system. It reads the text file, extracts the dates, metrics, and values, and writes all of this in a file named "<metrics>.txt" in a folder named "data-files".

Following values need to be set up in the application insights to ensure the function runs properly. Just Azure things!

Instead of writing the data in files, we could have used a database. But obviously, files are much cheaper so I used the files.

Here are all the different files in the "data-files" folder:

As I said earlier, we need the data in a format that can be used to plot the date-wise chart. So we have to write the values along with the dates.

Now we have our data stored in a datewise format in different files so anytime we want the data for a specific metric, we need to just read the file. And this is where the HttpTriggerFunction comes into the picture.

This is, as the name suggests, an HttpTriggerFunction which, when gets a GET request for a specific metric, returns the data read from the file. Our HTML file actually makes use of these GET requests to render the data as a chart.

This brings us to the last piece of the puzzle, the front end. A simple JS library (CanvasJS) in a static HTML file is hosted as a 'static website' in Azure Blob Storage. 

Every time we select a metric in the dropdown in the HTML file, it sends an API call to /api/HttpTriggerFunction?name=<metric> and the data is returned to be displayed by the JS library. Actually, I set the format of the data in the files according to what was needed by the library.

So this is all that there is to it!