Month: May 2020

Excel pro tip: Locate and reset the last cell on a worksheet

Posted on by Peter Geil

I had the weird situation, that an Excel source at a SSIS job did produce almost 100k output rows whereas the read Excel sheet did only contain around 5k rows of data. After dissecting the results of enabled Data Viewers I found out that the XLSX documents seems to have some internal metadata which also keeps a pointer to the last data cell. Or at least what Excel thinks what this last cell might be. This pointer however, can be read and interpreted (for a number of valid reasons) which would lead to the behaviour I observed.

Last data cell in line 93146? Nope

Deleting all seemingly empty rows did not do the trick, so I consulted Google which lead me to the following knowledge article which finally cleared everything up (literally): https://support.office.com/en-us/article/Locate-and-reset-the-last-cell-on-a-worksheet-C9E468A8-0FC3-4F69-8038-B3C1D86E99E9

TL;DR;

Pressing CTRL + END directly jumps the the last populated cell according to Excels internal pointer. In order to reset this, select all the rows or, even easier when applicable in your situtation, select the whole sheet, and finally clear the (visual) formattings. Done.

Oh, for the case your are also a curious one: I peeked into the XLSX file (which is an ordinary ZIP archive if you didnt know) and found the internal “pointer” I was suspecting. Inside the sheet XML files which are located under /xl/worksheets directory, there is a <dimensions ref="A1:D93146"/> right as first child element under the <worksheet/> root element that holds the misleading information. 😉

Thoughts about automated API Connection resource deployments as part of Logic App ARM templates

Posted on by Peter Geil

Recently I did re-evaluate the current possibilities when tasked with fully automated ARM template deployments of Logic Apps which contain API Connections. TL;DR; as of today there is still no sophisticated method for automating the authorization process of OAuth secured API connections. Microsoft still recommends the same custom PowerShell approach as back in 2018 when I first did evaluate this deployment aspect. Of course this should be understood as a general inspiration, rather than the ultimate solution. But in the year 2020 I would expect that there are already more sophisticated approaches, which ideally should have been integrated to Azure DevOps release pipelines, similar as the Azure Key Vault release tasks or the neat parameter mapping features of the Azure Resource Group Deployment task.

You might ask, why this is of relevance in the days of Service Principal access methods (aka App Users, AAD App Registrations asf.), Azure managed resource odentities or even simpler static API keys. Short answer: not all API Connections do support these advanced authentication mechanisms yet.

How to authorize OAuth connections (as of today)

Quick recap: exemplary OAuth 2.0 authentication flow (Source)

In essence I see three choices:

  1. implement some semi-automated OAuth login process using f.e. PowerShell which needs manual intervention for doing the actual “login” (example) → moderate initial effort; can be re-used
  2. implement automated login process which f.e. follows the common OAuth 2.0 authentication flow. Login credentials could for example be stored and retrieved from Azure Key Vault in order to avoid security leakage → rather complex and relatively high initial invest, but can be re-used
  3. just open the affected API Connection ressources after they have been deployed and manually authorize them → low effort

Keep in mind, that #1 and #2 also need to be integrated to the automated deployment process with, for example, Azure DevOps releases. For #1 this will impose additional complexity, since the OAuth login dialog must somehow be invoked on the user’s client machine in order for him to take action. And strictly speaking, #1 and #2 do contradict the philosophy of truely automated releases (CI/CD) since they involve manual intervention.

My comment

In the end, one needs to weigh out the costs/savings of following a strict philosophy (automation; CI/CD) vs. following a rather pragmatic approach, where both sides have the same goals: streamlining the whole release process and making it as efficient as possible. For me, following choice #2 would represent that most elegant solution, which will only amortize in the mid-/longterm and is best suited for very mature customer/project contexts, where this very specific optimization would imho pay off the most. While choice #3 is the most pragmatic and also cheapest (in many ways) way to accomplish the same goal.

Logic App Finding: Multiple run-after configurations when joining parallel branches

Posted on by Peter Geil

Here is my finding of the day: When working with parallel branches, after the execution paths have been joined again, the next action step can have different run-after configurations for each of the previously joined parallel branches! This ability is hidden behind some bad UI/UX, so I discovered it just today and rather “accidentially”. This might not be needed very often, but when, it can help to keep the LA execution paths simpler, since no bulky if-else branching and redundant actions would be needed.

Click on the previous action first, in order to configure individual state constraints in the run-after settings

Since Logic Apps and Power Automate (aka Flow) practically share the same platform fundament, differen run-after configurations can also be created in Flows.