Workarounds needed to use the Excel Connector in Microsoft Flow

/

There are two Excel Connectors in Microsoft Flow. There’s a quirk to work with them.

Plan

  • Two Connectors

  • How do we use them? What’s the problem we need this workaround for?

  • Extra tips


Two Connectors

In Microsoft Flow, we now have two connectors for Excel. We have Excel Online (OneDrive) and Excel Online (Business).

There used to be a third Excel connector - which has been deprecated.

How do we use them?

The Excel Connectors call Microsoft Graph under the hood. We need to provide a folder to the file, the file itself, read tables from that file, and then get rows from a table in the Excel file.

To use these connectors, the Excel file must have the data that we want to read in a table.

To use the Excel Online connectors with dynamic file name, we need to find the MSGraph ID of that file. It looks something crazy like:

01BUSAX432CKPX6HWHFJBI7CHAXKHKDHSS

So we build a carefully crafted Send HTTP Request to SharePoint, using the v2.0 endpoint to pull back the MSGraph ID for the selected file.

This then allows us to work with the Excel Online connector.

To use List Rows Present in a Table - we need the Table ID, since this is dynamic now - I’m fetching this with Get Tables (need the file ID).

Then I use an expression to fetch the ID of the first table in the excel 

first(body('Get_tables')?['value'])?['id']

Extra Tips

By default, Excel Online connector retrieves 250 items. We can change the top query param to return 500 or 1000 items.

But to truly get back All the items, we need to switch on Flow’s pagination policy.

See the result - my meteorite.xslx has 49998 rows.

Extra Tips 2

Excel Connector can only retrieve rows via a ‘table’. So if you have to read rows from an Excel file that doesn’t have a table, we can call the Excel Connector action “Create Table” to insert one, then read the rows with that.

This is originally Flow lifehack 96.

https://twitter.com/johnnliu/status/1129715544712597504



Flow Studio 1K users - time for a roadmap update

/
ezra-comeau-jeffrey-77199-unsplash.jpg

In the past week - total number of Flow Studio users crossed over 1000.

We have 1000 users!

We want to thank you, Flow makers, for your support. When you share a feature you loved, or suggest a feature that was missing or tell me a bug that we’ve never seen before.

We really enjoyed the journey, there is much we didn’t know when we started, but your support made sure we keep going on.

If you don’t know what John does - check out

https://flowstudio.app

What’s next in Flow Studio?

It’s perhaps a good time to talk about what’s coming next on the Flow Studio roadmap.

We have been very busy.



Flow Studio

Flow Studio’s key feature today is a better way to manage a maker’s own inventory of Flows, with a suite of tools to help a maker with making more successful flows.

Along the way, makers relied on:

  • #hashtag grouping

  • Sort by failed runs and last modified runs

  • Run sparklines

  • Edit JSON

  • Runs with Context

  • Bulk run resubmit

  • Flow version history

  • Full definition text search

  • … just to list some of our favourite features.

rail_25_62_8_web.jpg

Our next big roadmap milestone is a Flow deployment wizard

  • Create a deployment plan to copy a template Flow, with variable, URL or connection replacements, to a set of destinations.
    Ideally, the target scenario is for 1 Flow to be copied to 50 SharePoint sites.

  • Keep the deployment plan to perform future updates. Because deployment is never a one-off process.

  • We will be able to copy between 1 to many sites

  • Between multiple Flow environments

  • And eventually, between different accounts - for user migration (and even tenants)

This work is progressing, you can support us right now by trying Flow Studio - and reach out and tell us what scenarios you need.

Get started with Flow Studio for free: https://flowstudio.app/

Flow Studio for IT

Flow Studio is designed for an individual maker, or even a consulting company creating Flows under a single account. It helps us manage the Flows in that one account.

It turns out, an inventory management tool for an individual maker and a fast way to see the runs immediately, is actually a scaled down version of what an IT management and governance portal needs.

IT wants to know what’s in every maker’s toolbox. What is running across the enterprise. What’s successful and keep them that way, and what’s not successful and needs to be mitigated.

Picture ourselves across the fog of war that is today’s IT - how many Flows are we able to see? Are they successful? If they aren’t successful, do we know? Are they business critical? If they are business critical but not successful… Is that a risk?

To begin to answer all these questions, we think we need to understand the landscape - let’s produce a Map. Is there a way we can get a bird’s eye view of the entire map of Flows across the entire company.

This is a picture of London underground. The FLOWS IN YOUR ORG LOOKS LIKE THIS.

This is a picture of London underground.
The FLOWS IN YOUR ORG LOOKS LIKE THIS.

Our second roadmap milestone - is to create a governance dashboard.

We want to know

  • All the Flows and the runs, are they successful, are they failing, when have they been last modified?

  • All the Users - who are our Makers, what Flows do they do?

  • All the Systems - what systems do they touch - not just SharePoint, which Site? Not just Exchange - which email account?

    • You can use SharePoint connectors. But you can’t use it on any site URL

    • You can use Exchange connectors. But you can’t use it on certain email addresses

  • All the solutions - we understand

    • Flow solutions (managed and unmanaged)

    • Teams Flows

    • Personal Flows

    • Resource Owner Flows

    • We also understand if a maker is using Flow Studio tags.

  • All the connectors

  • Monitor, Alert Agents, Reports

  • Bring the bulk editing tooling from Flow Studio and scale it to IT

We want to ensure

  • All your business critical processes are automated and monitored

  • Maintain Flow success - we want green ticks across the map

We are in the process of building this out with several partners that has expressed interest. Please do reach out if you think your IT needs this. We are looking out for early pilot companies that wants to see and be on top of the Flows that are being created across their business. We think we have the perfect product you might want to try and build it with us.

Contact us: support @ flowstudio.app

Business

I leave this one at the end - Flow Studio is supported by a separate business entity Flow Studio Solutions, which is also John’s first company. So, aside from writing all that wonderful code (code is so wonderful), I’m also learning everything that I loved about learning. From the next Australian financial year forward, Flow Studio invoices will come out of a company entity.

There is much uncertainty of what I just don’t know. Sales, tax, marketing, support. My problems have their own problems. Like IT needs a MAP to see their Flows, John also needs a mental map to see his business.

If you feel hey John’s cadence has slowed down - I’m sorry. My efforts are split between several major roadmap milestone features. These are bigger pieces that needs more work to get right before I can push them out the door. Hope you understand.

Reminding myself to be more kind. Scary is the unknown path I have chosen, but I need to remind myself to be more kind, there’s enough stress in the world, I shall not add more.

We hope to bring you the latest in Flow Studio very very soon! Drop us a comment or note about anything!



Flow Studio features in April that will help us mitigate a disaster

/
eric-muhr-1146201-unsplash.jpg

In the best scenario, nothing ever fails. Your code and logic doesn’t fail, flows don’t fail, SharePoint doesn’t fail, Azure doesn’t fail.

Some days like yesterday aren’t as wonderful. I’m sure everyone “went home” on Thursday, but there’s a daunting task on Friday - how do we know business continuity has been maintained? Where do we even start?

I hope you are reading this while coming into your work on a Friday morning. Because I’m offering a great solution with Flow Studio, and I think you will have a wonderful Friday.

We can’t perform miracles every day, but today, the Flow Studio team thinks we have a good shot at give you back a “great Friday”

Flow Studio free feature update for March ~ April

This blog post is about several new features that were added to Flow Studio during March to April. Flow Studio is a power tool we’ve built for Power Users to work with their Microsoft Flows. We believe in Flow automations, and we want your Flows to be successful. It is an coincidence that many of the features we paid attention to during March and April forms a complete set of tools when it comes to disaster mitigation. Let us explain how these work together.

Sparklines

We introduced Flow Run Sparklines in March, and improved the performance over April. In Flow Studio - your runs are not second tier citizens - they are top tier. We want you to see immediately on Friday morning, which ones of your Flows have failed.

This is important. Because this was my task this morning. I reviewed all the Flows in my environment, and at a glance - I could identify which ones have failed and needs immediate migitation.

Flow Runs, with Context

Clicking on Sparklines takes me right into the latest set of Flow runs - many of them have failed. Flow Studio understands the trigger data (SharePoint, Dynamics or CDS) and brings the context of each trigger to the front. We don’t see just random Run IDs - instead we see the trigger item id, title or filename. These to help makers select the correct runs to re-submit.


Bulk re-submit

When we are sure the Flow is ready to be re-submitted, Flow Studio gives us the ability to bulk select our failed runs and re-submit them.

Flow Run deletion (future)

Deleting Runs is a feature we are expecting to appear (since it has been announced for Logic Apps) - so we have already added support for this feature. As far as we are aware - this API has not been available on any environment.

In the future, when Flows have been successfully re-submitted, we expect the Maker may choose to delete the old failed run and keep the successful, resubmitted run.

Deleting failed runs so we only have successful runs is sort of cheating, but we LOVE SUCCESS! 100% allowed!

Flow Studio subscription feature update

Sparklines for admins

Flow Subscription allows a maker (with Flow P2 license) to see all the Flows made within that environment.

The new Sparklines is also available here - this allows us to observe all the Flows are successful in the entire environment.

There are Flows that may belong to a solution, belong to a team (that excludes you), or even belong to a Resource owner like SharePoint (so they don’t have an actual Flow user owner). To see those Flows - you’ll need to get Flow Studio subscription, because we can only see them via the P2 admin API.

Approvals (v2) cancellation

In the latest Approvals V2 update - Approvals can now be cancelled. Flow Studio provides bulk Approval cancellation.

We feel this is a situational feature - it may be useful if a lot of Flows have been accidentally ran and created a lot of duplicate Approvals that we might want to cancel in bulk.

But it is very possible that during the disaster of May 2019 - we have a lot of approvals created and then the Flow failed. So in re-submitting these Flows again, we wouldn’t want to have a lot of duplicated approvals. Bulk approval cancellation would be very useful in this scenario.


Thank You

Thank you for your support of Flow Studio App - we hope your business processes have not been impacted, and we hope you were able to use Flow Studio to restart your Flows.

We think the combination of Sparklines, Runs with Context and Bulk Resubmit is a timely reminder of the Flow Studio mission.

Flow to Success!

https://flowstudio.app

A Power User friendly method of connecting hundreds of sites, list and libraries to a single Flow

/
Photo by Sebastian Boring on Unsplash

Photo by Sebastian Boring on Unsplash

We need to get this disclaimer out of the way first - this approach uses SharePoint Designer workflow as a glue - the main reason for this is because SharePoint Designer workflows (being old generation technology) can be deployed across site collections via PowerShell scripting without too much trouble, as long as they remain small and don’t need regular maintenance.

This approach isn’t necessary for deploying a Flow to a dozen sites. But if we are talking potentially hundreds of project sites. Those become scenarios that aren’t currently covered well, and would need workarounds such as this one.

Let me know what you think in the comments.

The Problem

There are many ways we can view this problem of “deploying Flows across many lists, libraries, sites and site collections”.

This problem has several issues we may not need to consider:

  • First, we have Export and Import, as well as Send Flow as a Copy. These are simple, manual steps. That can’t be automated.

  • But at some point - we will hit the 600 Flow limit (this isn’t per environment, this is per account)

  • We will need to reconfigure URLs specific to each Flow.

  • As these copied Flows are customized from the master version which makes updating and maintaining them very tricky.

  • When redeploy a Flow - existing connections must be maintained.

  • When deploying Flow to a new site - new connections must be configured (or existing connections re-used).

  • If we are ever in a situation where we need to deploy a new update an existing Flow but now with additional new Connections. God have mercy.

  • If we think perhaps just delete existing Flows and re-deploy as new, we’d kill any existing runs.

So there are alternate approaches as well… what if we just don’t deploy hundreds of copies of the same Flow?


A new solution?

The method in this blog post is a different idea - we will borrow SharePoint Designer workflow’s various tools for deploying cross site collections and have it invoke the same Flow to perform the main task.

This was posted as #FlowNinja hack 91

https://twitter.com/johnnliu/status/1121055884749053953


Let’s begin

Warning: this hack involves the use of SharePoint Designer. Now before you all run off screaming, there's a good reason why we want to try this method: mass deployments. So here we go

1. Make a Request trigger Flow. With a simple compose.
2. make SPd 2013 reusable workflow

3. first action - add SPd HTTP web request to the Flow request trigger
4. need to configure both request body and request header

5. If we don't blank out authorization this happens DirectApiAuthorizationRequired: The request must be authenticated only by Shared Access scheme

Add SPd Workflow to a Library and a List

6. now publish and add this to a List and a Document library
7. upload a file see SPd workflow trigger
8. see HTTP Request flow trigger

The Test is successful. Next copy the JSON we received in Flow

9. add Request schema from the previous successful run
10. change Compose to match request body

11. add a new item to a sharepoint list (that has the same SPd workflow attached)
12. the same Flow re-triggers.

Take a breather, have some tea

13. at this point, there's several more things to do - consider whether we want excessive logging. SPd workflow is simple - once it is working there's not a lot more to tweak, perhaps we want more context values. While still within this site collection, republish will update it.

Package the SPd Workflow for cross site collection

Let's package this workflow for cross site collection warp jump.

14 remove workflow association to list Achievements - because we don't have that list everywhere, only Documents. 15 export WSP to desktop

16 go to new site collection - this can be SharePoint or group sites or project sites.
17 site settings - upload solution to gallery - activate solution
18 site settings - site features -activate feature (this associates with Documents)

19 test with upload new file in new site collection
20 flow triggers, context of Flow is new site url.

21. note there was an error with the SPd workflow after it called HTTP - because I didn’t activate the SharePoint Workflow feature, Workflow History list isn’t available in the new site collection.

Consider Step #13 and whether you’d want to delete all the logging steps before exporting Flow

21. now we are in Flow. Re-fetch the item and go crazy.

Yes, in Step #4 we deliberately chose the context values that will allow us to re-configure Flow to pick up the item

Conclusion

Con:

  • The pain of writing a SharePoint Designer workflow again

  • Difficult to update deployed SPd workflows - but the one we have here is very simple (two build dictionary and a HTTP request)

Pros:

  • A simple lightweight SharePoint Designer workflow can be deployed across many site collections via PnP-PowerShell, or as part of PnP-Provisioning.

    Activate the solution, and then activate the feature (this will create the workflow associations)

  • All the events from associated SharePoint libraries will call a single Microsoft Flow - this is where we can customize to our heart’s content.

I don’t think this approach should be written off - it should be evaluated and may suit your situation well. I also think in a possible future when cross-site Flow deployments can be done easier - we may have different Flow triggers invoking our one Flow. So there exists an upgrade path forward.

Azure Global Bootcamp Sydney - this Saturday!

/
Photo by James Ree on Unsplash

Photo by James Ree on Unsplash

I’m presenting a talk on Azure Logic Apps (and Microsoft Flow) this Saturday at the Azure Global Bootcamp Sydney.

https://www.meetup.com/en-AU/Azure-Sydney-User-Group/events/256253065/


[Update 2019-04-28 Video livestream]

I start at 1:15
https://www.youtube.com/watch?v=_kZZbKsHrGI&feature=youtu.be&t=4543

Our day of developer talks streamed live on Saturday 27 April 2019.

LogicApps & Flow for Developers - insane low-Code Serverless Automation

We make the case that every developer must understand what LogicApps and Microsoft Flow are, because they will make us rethink how we really write code.

There is always more code to write, but what if we can

  • Use out of the box actions when we don't need to write code

  • Connect to new systems painlessly

  • Connect to APIs that we have never used before

  • Not worry about how much it all costs

  • Build microservice architecture solutions

  • Fall into pit of success


Join this session to learn about how to write code, fast, without writing code.


This is a similar session (with more focus on Azure Logic Apps - there’s always room for tweaking) from a talk I’ve done previously, most notably during the MS Ignite Roadshow.

The talk is aimed at developers. There’s no assumption that you would know much about Logic Apps or Microsoft Flow, but think of this as a baptism by fire - an mid-level introduction that goes right into parallelism and HTTP requests.

These are what one would often consider to be difficult developer concepts - and as we will all see, these are extremely easy to achieve in Logic Apps / Microsoft Flow.

Join us this Saturday - how to write code fast without writing code.