Displaying a Replicon Time-Off Calendar in SharePoint (or wherever!)

Our team just started using Replicon to record our time off, but wanted to have a nice calendar widget on our SharePoint page (or wherever) rather than having to log into Replicon each time.

At first I tried their API, but struggled with permissions and getting any time-off data out of it.

However, I did find a more straight forward way.. through the iCalendar feed! I’m posting my solution here for anyone else who might want to do this with the minimum of effort (but it does need some effort! ;).

First off, you need to proxy the iCal data feed through something due to web (CORS) security.. so I’m doing that with a very simple Azure Logic App that grabs the feed & responds with the data.

Proxying the iCalendar Feed

This can then be consumed by our calendar JavaScript code, which takes the iCalendar data, reformats it to JSON, then tidies it to feed into a free JavaScript calendar called FullCalendar.

The full source for this is in a CodePen, which makes it easy for you to try this out yourself. https://codepen.io/mattc_uk/pen/bGYLBpP?editors=0010

(you’ll need to edit the URL in the code to point at your iCalendar data proxy).

Once you’re happy with the code (in CodePen) you’ll need to host it on a webserver, then you can use the Embed component in SharePoint to get it onto your site page. To do this, I actually used another quick Azure Logic App, since it means I don’t need to have an actual web server.

I pasted all the code from the CodePen into a Response blog & boom, we have an easily-embedded calendar containing our team’s holidays!

Hosting the code in a Logic App

Hopefully this will help anyone else trying to do something similar, so you don’t have to start from scratch.

Advertisement

The Modern Yahoo Pipes Alternative … Azure Logic Apps!

Back in 2015, Yahoo! shut down a service I used to love called Yahoo Pipes. It allowed you to combine and filter XML-based RSS feeds to create a new RSS feed containing the data you wanted.

At the time, I looked for a decent alternative and didn’t find anything.. until recently, when I realised that Logic Apps (which I’d been creating for over a year at work) were actually comparable to a next-gen version of Yahoo Pipes, with A LOT more functionality.

Since Logic Apps / Power Automate (used to be called Flow) is a lot more feature-rich, it does mean it’s a little harder to do what you want with the data, but once you’ve developed a few, you’ll see how much more flexible they are, especially when the world has moved to JSON-based APIs, with XML-based RSS taking a backseat.

While Pipes would just output RSS, Logic Apps can output the result into pretty much anything you want.. JSON, email, other APIs, Twitter, spreadsheets (Office 365 and Google Sheets).. the list goes on and on!

You can get started with Logic Apps for free.. and they’re very low cost to run beyond the Azure trial period. You’re talking pennies to run them monthly, depending on run-frequency & complexity. The pricing calculator can help you if you’re worried about cost. If you created 10 Logic Apps, each containing 10 basic actions and 10 standard actions, and ran them all once a day for 30 days it’d cost you 34p a month;

Actions in Logic Apps are the blocks you see in a flow;

It starts with a ‘Trigger’.. so in this case I’ve chosen a Recurrence (schedule), but you could set one up to trigger on a HTTP call from an RSS reader app, something happening on Twitter, or pretty much anything.. here’s a few of the triggers to choose from…

Just some of the Trigger actions available!

Non-Microsoft products are well represented, so don’t go thinking you’re limited to Sharepoint or Office 365, that’s not the case.

You then design the flow how you want it to behave, e.g. get data from X and Y, merge it together, filter it, query an API, persist some data in a sheet, then send back a piece of JSON.

Choose from Built-In, Standard or Enterprise actions.. you can even build your own custom connectors for systems that aren’t currently listed.

List of the Built-In actions (April 2020)

As you’ll see in the video I put together, to replace Yahoo Pipes, you need to be able to merge, filter, manipulate, and sort JSON. You can do a lot of this with the  built-in actions, but you can use Inline Code to achieve anything else.

Inline Code runs NodeJS and has access to standard built-in Javascript objects.

The one thing that’s Logic Apps aren’t great for compared to Yahoo Pipes is sharing what you’ve created.. it was really easy to adapt what someone else made to suit your needs. Logic Apps don’t work like that at all, and you’ll need to roll your own, which takes some trial-and-error & patience.

Basic filtering (etc) is quite straightforward …

… but Inline Code might take you longer to figure out, so to get you started, here are some Inline Code snippets you might find useful;

Simple Text Filter

	const filtered = workflowContext.actions.Current_JSON.outputs.filter(item => item.display_name=='My search string');
	return filtered;

view raw

simpleTextFilter.js

hosted with ❤ by GitHub

Multi-Filter Example Including Regex

	var titles = workflowContext.actions.Filter_array_on_year.outputs.body;
	var newTitles = [];
	const regex = /(.*)(2019|2020|2021).*/i; // Regex for year filtering
	titles.forEach(function(item) {
	// Additional filter for titles containing 1080p only
	if (item.title.indexOf('1080p') > –1) {
	// Extract the title and year only
	var titleMatch = regex.exec(item.title);
	if (titleMatch!=null && titleMatch.length>0) {
	var tidyTitle = titleMatch[1]+titleMatch[2];
	var pubDay = item.pubdate.substr(5, 2); // Get the day number
	tidyTitle = pubDay + " – " + tidyTitle;
	// Simple dedupe
	if (!newTitles.includes(tidyTitle)) newTitles.push(tidyTitle);
	} else {
	newTitles.push(item.title);
	}
	}
	});
	return newTitles;

view raw

multiFilter.js

hosted with ❤ by GitHub

Generate HTML for an Email

	var titles = workflowContext.actions.Execute_JavaScript_Code.outputs.body;
	var html = '';
	titles.forEach(function(fulltitle) {
	var title = fulltitle.substr(5); // Strip off the first 5 characters.. '12 – Headline' becomes 'Headline'
	html += fulltitle +
	' | <a href="https://www.somesite.com/find?q=&#39;+title+'">Somesite</a>' +
	' | <a href="https://anothersite.net/search/&#39;+title+'">Another</a>' +
	'<br>';
	});
	html += '<br><br>Footer of email goes here';
	return html;

view raw

emailBody.js

hosted with ❤ by GitHub

This hasn’t been a step-by-step guide, but has hopefully shown you enough to spark your interest.. get yourself a free trial of Azure, and give it a go!

 

How to list Azure Resources that don’t have Alerts enabled

Alerts are great for letting you know when Logic Apps or Function Apps fail to run for any reason.. the problem is, while you can list out which resources have Alerts, you can’t get a list of resources which don’t have Alerts!

So how do you find out which resources you need to add Alerts to? Well, the CLI has the tools you need, but it’s a bit more complicated than it should be!

First you need a list of resources which have alerts.. you can do that with a command like this;

az resource list --output tsv --resource-type "Microsoft.Insights/metricAlerts" --query [].id

You can run that as part of this command, which is supposed to take the Alert ID list, pipe it into az resource show, and output the value in properties.scopes[0], which is the ID of the resource the alert is set up to monitor.

az resource show --ids $(az resource list --output tsv --resource-type "Microsoft.Insights/metricAlerts" --query [].id) --query properties.scopes[0] --output tsv

However, this fails miserably when the Alert name has a space in it; there ends up being a space in the Alert ID, which makes our command throw an error. UPDATE – Microsoft are figuring out if they can fig it in this bug report I raised.

What I ended up having to do is break up the commands inside a Bash script & use a loop.

	alerts=$(az resource list –output tsv –resource-type "Microsoft.Insights/metricAlerts" –query [].id)
	alerts=${alerts// /SPACEFIX}
	for alert in $alerts
	do
	az resource show –ids "${alert//SPACEFIX/ }" –query properties.scopes[0] –output tsv
	done

view raw

listAlertIds.sh

hosted with ❤ by GitHub

This will assign all the Alert IDs to a variable, then replaces spaces with ‘SPACEFIX’. We then loop over the Alert IDs, querying the Alert to find what resource they’re monitoring. ‘SPACEFIX’ is replaced within the loop. It’s somewhat of a hack to get round this silly issue!

There might be a better way to handle this, but I didn’t find it in a hurry.

Now we’ve got a list of resources that have alerting enabled, we need to list out all the resources we want to check.. for this example I’m just interested in Logic Apps, and this script will list them out, one resource per line;

	logicapps=$(az resource list –output tsv –resource-type "Microsoft.Logic/workflows" –query [].id)
	for logicapp in $logicapps
	do
	echo "$logicapp"
	done

view raw

listLogicApps.sh

hosted with ❤ by GitHub

Now we can redirect the output of the 2 scripts into temporary files, and use the ‘comm’ command to show us the lines that exist in one file that don’t in the other.. here’s how it looks;

	echo Finding what Alerts exist already …
	./listAlertIds.sh > listAlertIds.tmp
	echo Finding what Logic Apps exist …
	./listLogicApps.sh > listLogicApps.tmp
	echo Logic Apps without an Alert are …
	comm -23 <(sort -u ./listLogicApps.tmp) <(sort -u ./listAlertIds.tmp)

view raw

listMissingAlerts.sh

hosted with ❤ by GitHub

This gets us what we want.. a list of Logic Apps that don’t have Alerts set up for them. You can pipe the results through grep to check certain Resource Groups, or whatever you need.

Hope this helps save you some time, and gives you a few ideas for useful scripts.

I’ve published some videos on YouTube showing various Azure tips, which you might want to check out!

 

Autopilot for Cosmos db – The Cost of Convenience

In the October 2019 update of Azure, Microsoft added ‘Autopilot’ that automatically controls the throughput of a Cosmos d/b. This is handy for unpredictable workloads.. like irregular imports, when you’ll hit the 400 RU maximum and have a Data Factory Pipeline cut out part way.

This can’t be retroactively set on existing Cosmos db containers.. only new ones.

We compared the cost to a d/b with a manual setting of 400 RUs and ran them for a couple of days with no usage.

This it how it looked in Cost Analysis:

Throughput	Daily Cost	Yearly Cost
400 RUs	$0.75	$273
600 RUs	$1.15	$419
Autopilot 4000 RU max	$1.13	$412

As you can see, the standing charge is more expensive for Autopilot… $138/yr more expensive than 400 RUs. But equivalent to running at 600 RUs.

If you have a 400 RU container with predictable high-throughput bursts you can run a script to temporarily increase the RUs, then set them back when you’re done.. that’ll save you money, especially if you have a lot of similarly configured containers.

How to Automate PageSpeed Insights for Multiple URLs on a Schedule using Logic Apps or Flow

For the website I’m responsible for, I was interested in capturing the data from the Google PageSpeed Insights tool, and having the data recorded somewhere on a schedule. There’s a blog post on Moz.com that talked about doing this with a Google Sheet, but it wasn’t quite what I was after; I wanted the data to be collected more regularly.

Instead of using Google Sheets (and a fair amount of code), I decided to use an Azure Logic App (you can use this or Microsoft Flow), which is part of Microsoft’s Cloud platform.

The Logic App is run on a Recurrence trigger which I set to every 6 hours. By collecting the results automatically over time, you’ll see how the changes you’re making to your site affect your PageSpeed scores.

The first step simply defines the URLs you want to check, then it’ll loop over each one & call the PageSpeed API. Go get an API key, and make sure PageSpeed API is enabled.

Results from the API call are parsed out and pushed into a new row in an Excel Online sheet.

If you’re interested in setting this up yourself, I recorded a short video which shows how it works in more detail.

There are a few foibles in Logic Apps which caught me out, first, getting the list of URLs into an Array didn’t work as expected. I had to switch to Code View to correct the escaping of the return character to read;

@split(variables('urlList'), '\n')

The JSON payload from the PageSpeed API is pretty large, so I’ve listed the path to the elements you’ll be interested in below. I’m using split (on space) purely to get at the numerical value, which is more useful in the spreadsheet;

First Contentful Paint

@{split(body('HTTP')?['lighthouseResult']?['audits']?['first-contentful-paint']?['displayValue'], ' ')[0]}

First Meaningful Paint

@{split(body('HTTP')?['lighthouseResult']?['audits']?['first-meaningful-paint']?['displayValue'], ' ')[0]}

Speed Index

@{split(body('HTTP')?['lighthouseResult']?['audits']?['speed-index']?['displayValue'], ' ')[0]}

Time To Interactive

@{split(body('HTTP')['lighthouseResult']['audits']['interactive']['displayValue'], ' ')[0]}

Time to First Byte

@{split(body('HTTP')?['lighthouseResult']?['audits']?['time-to-first-byte']?['displayValue'], ' ')[3]}

Overall, this was quite easy to put together and shows the power of Azure Logic Apps. Being able to do this without any code or (your own) servers, and getting things live in a couple of hours is a fantastic tool to have at your disposal.