A lemon


September 2020

PowerBI report
PowerBI report2020/09/16
Get field changes from API
Get field changes from API2020/09/16
Collapse adjacent non-changes
Collapse adjacent non-changes2020/09/16

Visualising changes in columns of a SharePoint document library

I discovered that I could query the version history and related column changes for any file in SharePoint via the api:

https://<subdomain>.sharepoint.com/sites/<site>/ _api_web/lists/GetByTitle('<listName>)/items(<itemID>)/versions

There's a bit of XML table expanding, but it seems everything is there. Unfortunately I have yet to figure out how to do these in bulk.

Once we have a list of every state of the column at a specific point in time, we can change it to a list of state changes by time period, as shown in the 3rd image. So the first table we get would be the nodes, and the table we need to create is one of all the edges between the nodes.

There's a bit of struggling with the permissions in PowerBI with the data privacy firewall, as we're pulling the itemIDs off from the SharePoint, then querying the individual version history for each item in a separate query. I couldn't figure out how to solve this properly, so I disabled the warning.

The benefit this report brings is that we can visualise our existing list of tickets with their workflow status, and track their state, or stage-by-stage movement, of each tickets on a detailed level over a period of time.

While before we could only see the current state of everything (which tickets are currently overdue or complete), now we can see *when* tickets were closed on a day-by-day (or even minute-by-minute) basis.

We could further improve this by using Time-Intelligence to be able to visualise the breakdown of tickets by status at any single point in time.

Mister "Time is but an illusion" Swift - 2020/09/16


September 2020

Agenda-style Adaptive Card
Agenda-style Adaptive Card2020/09/15

Inspired by the 'Agenda' sample Adaptive Card, I decided to make one that showed how far along an entity is in one of our workflows.

The primary key and title on top are pulled from a master data SharePoint list.

The Power Automate Flow then checks if a document set has been created in the paperwork document library, and for each stage it checks if any forms are present, and what their current status is.

Ideally I would have done it in a more programmatic and generalised manner, but I hardcoded it for the sake of simplicity and time (and resilience to failure).

— The Ghost of Lost and Forgotten Corporate Paperwork - 2020/09/15


September 2020

First query
First query2020/09/13
Another query
Another query2020/09/13

Put together yet another over-engineered and convuluted Microsoft Power Automate flow, this time for filing and cataloguing approvals.

tl;dr Outlook, Adaptive Cards, Teams, Sharepoint, Sharepoint Document Sets.

When a user flags an email in a specific folder, it tries to guess a primary key, and asks the user to confirm. It then checks if a document set has already been created for this entity on Sharepoint.

If it has, we save the flagged email and update the metadata accordingly. We also check if a matching form to the approval is present, and update that one as well.

The user can then file the documents on another system we do not control, and once they receive a ticket number, they can update straight from MS Teams.

The reason for this process is to save all the relevant emails and attachments in a centralized, managed location on Sharepoint, with the appropriate metadata for primary keys, status, and normalized form types.

— WTB F# to WDL transpiler - 2020/09/13


August 2020

Create document set
Create document set2020/08/25
Update document set
Update document set2020/08/25

Creating and updating Document Sets via MS Power Automate

Both using 'Send an HTTP request to Sharepoint':

Create: Method: POST Uri: _vti_bin/listdata.svc/<listName> Headers: Accept: application/json;odata=verbose Slug: /sites/<site>/<list%20name>/<documentSetName>|<contentTypeID>
Update: 'Send an HTTP request to Sharepoint' Method: POST Uri: _vti_bin/listdata.svc/<listName>(<documentSetId>) Headers: Accept: application/json;odata=verbose X-HTTP-Method: MERGE If-Match: etag Body: { "Field1" : "value1", "Field2" : "value2", ... "Fieldn" : "valuen" }

Some notes:

listdata.svc prefers (?) names with whitespace removed, while the slug prefers spaces encoded to %20. It also accepts the Id of a file or list item in parenthesis as an index accessor. MERGE will update the existing properties with the changed ones (instead of replacing the existing properties that were not specified with defaults(?)). Don't forget to include the etag of the item to be changed, or it will fail.

Mister "Learning from mistakes" Swift - 2020/08/25


August 2020

Create document set
Create document set2020/08/25

Updating visiblity of fields on Sharepoint's NewForm and EditForm

Connect-PnPOnline -Url https://<domain>.sharepoint.com/sites/<site> -UseWebLogin $list = Get-PnPList -Identity "Form Name" $ctx = Get-PnPContext $field = Get-PnPField -List $list -Identity "Field Name" $field.SetShowInNewForm($false) $field.SetShowInEditForm($false) $field.Update() $ctx.ExecuteQuery()

Some notes:

PowerShell Sharepoint PNP -Identity is okay with names with spaces in it. Sometimes the update/execute fails for certain fields, haven't had the time to figure out why.

Mister "Learning from mistakes" Swift - 2020/08/25


August 2020

Screenshot of Flow 1/6
Screenshot of Flow 1/62020/08/20
Screenshot of Flow 2/6
Screenshot of Flow 2/62020/08/20
Screenshot of Flow 3/6
Screenshot of Flow 3/62020/08/20
Screenshot of Flow 4/6
Screenshot of Flow 4/62020/08/20
Screenshot of Flow 5/6
Screenshot of Flow 5/62020/08/20
Screenshot of Flow 6/6
Screenshot of Flow 6/62020/08/20

Outlook to Sharepoint Document Set with Microsoft Power Automate

Some of our end users were presented with the problem that their current filing system (Plain Old Folders) wasn't scaling well to the modern-day influx of reports and forms, and needed an interim measure until a proper enterprise workflow was implemented.

Currently, they receive an email per customer, with up to three separate forms which need to be actioned via different workflows. Currently we were tracking this via Excel, and as you can expect, queries were getting stuck and forgotten about for months on end.

Our current prototype is as follows:

Email is moved to a designated folder in the user's Outlook mailbox. This triggers a Power Automate flow.

First, we store some data from the email into more convenient variables. The subject, the person who sent the email, the date, and so forth. We need to do some minor string manipulation on these to make them print friendly for humans.

Next, we check if the unique key for the customer is placed in the email subject. Ideally I'd use RegEx for this, but there does not seem to be a vanilla regex action or function available. We then take what we think might be the customer key, and search in a Sharepoint list containing all the keys. This both checks if the key is valid, as well as returns us the ID of the customer in the Sharepoint list, which we will need later on.

We then need a name for the document set we are going to create for this specific email. If we have the customer ID, we use that, otherwise we fall back to something like "Received from John Doe at 2020/08/20". We cannot use the customer's name yet as our master data still needs some clean up to disambiguate duplicates.

We then check if we already have a document set with this exact name in the document library. We shouldn't have duplicates, but we check in case somehow the flow gets triggered twice for the same email, or the user completing the form sends it twice by accident. If it does not already exist, we are free to create the document set. Unfortunately there is no built-in action, so we need to use 'Send an HTTP request to Sharepoint' along with some minor listdata.svc REST work.

Once this is complete, we Export the triggering email so that we have access to its' content as a binary variable, then we save it into the document set using 'Upload file to folder (Sharepoint)'. We then use a Control/for-each loop to do the same for each of the attachments whose content-type does not begin with 'image'. If any of these files have already been uploaded, the entire flow will stop and exit with a failed status.

Finally, we use Microsoft Teams to send the user a message informing them that the document set was created, along with a link to access it. I chose this over an email to avoid cluttering their inbox too much. They can now access the forms on Sharepoint, where they can update columns such as Status, Approver, Ticket number, and Date Completed.

All of this can then be queried via Export to Excel, or using PowerQuery to Load Data from Sharepoint List. We have created both a new Excel sheet with a few charts (Number of Forms per status, Number of Forms not completed older than 30 days, Forms awaiting on Approval by person, etc.), as well as integrated the live data from Sharepoint into the old tracking sheet.

Mister "Go with the Flow" Swift - 2020/08/21


August 2020

Visual Studio Code screenshot
Visual Studio Code screenshot2020/08/20
In-memory F# Record
In-memory F# Record2020/08/20
Resulting HTML TABLE
Resulting HTML TABLE2020/08/20

Spotify Playlist Scraper

I haven't been using F# for a month or two now so I decided to do a little one day project to keep the brain gears running.

The goal: extract all my public playlists from Spotify so I can dump them on this microblog. I could have probably achieved the same result using PostMan or some other REST tool, or heaven forbid, copy and pasting it from the pages, but it seemed like a neat little problem to tackle that wasn't completely out of my league.

So, I grabbed my profile URL from Spotify's share button, poked around a bit with the DevTools HTML Inspector. It looked simple enough to grab the song data via either DOM selectors or Regex, so away I went and attempted to open the URL via a HTTP request in F# and bam. 400 Bad Request.

Huh? Odd. I ran it through wget, and sure enough, everything loads fine. It's a public URL, I can open it in an Incognito window without logging in, I can download it via the terminal, what am I missing?

I copied the headers as-is from the Network tab in DevTools, to mixed result. Long story short — I completely neglected the fact that I first need to grab an (anonymous) authentication bearer token, and that the proper way to get the data was not to parse the HTML page, but to just poke the REST API.

I then used a naïve and lazy way of dumping it into an HTML TABLE, and voila, mission completed, but my scalp left severely scratched in confusion.

Mister "Please don't judge my music taste" Swift - 2020/08/20


December 2019

OLED lolcat
OLED lolcat2019/12/06
LED Bar Graph
LED Bar Graph2019/12/07

Raspberry PI and LED Bar Graph

Bought a Raspberry PI with the idea of tinkering around and making a bandwidth meter in the style of the old hi-fi's/stereos.

Got about as far as wiring up one (of two) LED Bar Graphs to a multiplexer, to the PI, and then running some rudimentary copy-and-paste'd Python, before I got bored and ended up using the Raspberry for the more mundane usual network activities of Pi-Hole, mini-DLNA, and a "micro NAS".

Mister "How the hell does solder work?" Swift - 2019/12/07