Taking PDF snapshot of any SharePoint list item for approvals with Flow

/

This is a post about combining two steps I've blogged recently.  Combined they form a technique that can be used with approval workflows to provide a point in time snapshot.

I have been building up a library of really useful Flow blocks.  Because you can combine them in pretty interesting and important ways.

Flow: List Item to PDF

  • Using Flow-Each - we iterate through each property of the SharePoint List Item
  • Use Select to map each property and its value to an array
  • Use Create HTML table and we have a table
  • Sprinkle some CSS for decoration
  • Send to PDF

PDF Result

This isn't too bad - definitely workable.  Need some cleanup.  But right away, we can already do:

  • Send pre-approval list item (or document metadata) as a PDF attachment along with the approval email
  • Save a post-approval list item as part of the record keeping process.

Bonus Points: Clean up HTML

  • Create new JSON with just Author and Editor's email (you can also use DisplayName)
  • Union that with Get List Item
  • Use the new result as the Object to continue.
  • Add Filter-Array after Flow-Each
  • Create new Array, keep only elements that doesn't contain _x
  • Use this in the Pivot arrays to table

PDF Result: round two

pdf-second.png

A successful run

This is how everything looks at the end.  This doesn't show the details of each step, but is intended to show you how all the pieces look together at the end. 

Final thoughts:

  • Special thanks to Andrew Jolly who commented in an earlier blog post that we can use PDF to create records
  • I use this as a Nested Flow as part of our Approval workflows to create PDF snapshots of list items. 
  • Create PDF as attachment along with the pre-approval email
  • Create PDF as snapshot after approval
  • If you want to use SharePoint's Records Management features - you can declare the PDF as records.  You will need a simple AzureFunction to do this.
  • Lots of techniques for manipulating JSON - filter, select and union
  • You can use For Each as well, if you are using that - then it's better to use Array Variable, and use the Append action.

Oh, I went out of my way to not use variables - they are overrated if you don't want to use For-Each

  • Special mention to the Culmsee's that you don't have to use variables if you want to keep things pretty ;-)
no-variables.png

You must copy all your Flows to SharePoint - simple ideas are the most brilliant

/

Another crazy idea while travelling on the train home.  This one is so simple, in hindsight, I reckon others will come up with this soon.  So I'm writing this first.

When I was testing the idea on Friday night, I did a livestream hack and that's on Mixer https://mixer.com/Sousily?vod=12283142 - skip to 13:50 that's when we started the hack.

Plan

Copy all your Flow definitions to SharePoint
On File Change, push your change from SharePoint into Flow
(TODO warning: make sure you don't create an infinite loop)

Why SharePoint

Primary benefits:

  • Backup
  • Versioning - see changes
  • Kick-ass built in JSON Editor for advance hacks
  • For your team to collaborate
  • And by the end of this blog post - you can push change back into Flow, you have both Backup AND Restore.

Copy all your Flow definitions to SharePoint

some notes:

  • You don't need the Filter array if everytime you run the Flow you will copy everything into SharePoint
  • I only want to do "changed in the last day" filter, because I want to put this Flow into a Schedule
  • Then in SharePoint, because it has versioning controls, I will only see an updated version if the Flow was changed that day 

SharePoint JSON Experience

A big thank you to Sohail Merchant who told me how to get to JSON Editor via the Preview Panel in SharePoint.  I was going to write this whole post about using OneDrive for Business.

In May 2015 - I put up a SharePoint User Voice request to allow .json as an allowed file type in SharePoint and OneDrive.  This was done and rolled out in October 2017.  

But the SP/OD team went beyond and included a JSON editor.  Which is why we have this kind of amazing integration between products.

Wonderful.

On File Change, push your change from SharePoint into Flow

The dance with Update Flow action is very delicate.  The note is:

  • Set definition to string variable first
  • Update Flow will now 'see' this variable, and let you assign it
  • Then change the object definition back to "Object" type

You must do these steps or it won't work

Nearly

Make sure you check On Change - Upload.  I suggest you use a different account to write Flows to SharePoint.

Then On Change - if the Modified By is this Flow account, skip the upload.

Otherwise the schedule trigger will download the Flows, and trigger the File change which will upload the Flows and tomorrow the schedule trigger will re-download the Flows.  It would be quite silly.

Now we are in an Awesome place

Look at all the Versions

 

Simple ideas are the best ideas.  You need to build one of this.

Immediate Next Self-Inception-Hack

Any time, you think, hey I have a tool that can update itself.  Then you realize Flow is Self-Hosting.

And you can use it to improve itself.

I have a For-Each in the Copy Flows to SharePoint

So I immediately hacked parallel settings into the first Flow above.

scan-flows-faster.png

Scanning changed Flows is now faster.  (slightly, since the for-each is in parallel).

 

 

I'm speaking about Serverless Flow and Azure Functions at Collab365 Free Online Conference

/
collab365-watch-my-session.jpg

Have you heard about the virtual Collab365 Global Conference 2017 that’s streaming online November 1st – 2nd?

Join me and 120 other speakers from around the world who will be bringing you the very latest content around SharePoint, Office 365, Flow, PowerApps, Azure, OneDrive for Business and of course the increasingly popular Microsoft Teams. The event is produced by the Collab365 Community and is entirely free to attend.

Places are limited to 5000 so be quick and register now.

During the conference I'd love you to watch my session which is called : 

'Serverless with Microsoft Flow and Azure Functions'

Level up your mastery of Microsoft Flow. Switch to Azure Functions only when you need to. No doubt there will be many sessions on Microsoft Flow, introducing you to its wonderful merits and rough edges. This session is for the advanced users - we will see what Microsoft Flow really is, and bend it to our will.

If you join me, you will learn:

  • Master JSON in a Flow
  • Combining Azure Functions with Flow
  • Failure Recovery, in a Flow
  • How to handle Binary in a Flow
  • How to write HTML and generate PDF in Flow

Topic(s):

  • Azure Functions
  • Microsoft Flow

Audience :

  • Developer
  • Power User

Time :

  • Thursday, November 2 2017 10:00 AM (UTC)
  • Thursday, November 2 2017 09:00 PM (ADST)

How to attend :

  1. Register here.
  2. At the time listed above go here to watch my session. (you can also add me to your own personal planner from the agenda.
  3. Enjoy the demos and ask me questions, I'll put the templates up for download after the session.

From Office 365 to Azure to Minecraft, connected with Flow

/

"John, what's this headline."
This is just a love serenade to Microsoft's many engineers and teams.  Thank you, for making these products that makes these things look easy.

Thank you for showing the world what One Microsoft might look like when everything works together.

Plan

  1. Minecraft Windows 10 edition in the latest 1.2 (better together update) included web sockets previously only in the Minecraft Education version.  Web Sockets lets you connect to a Minecraft game, and remotely execute commands.
  2. Minecraft Code Connection is an external application that hosts a friendly REST API and translates JSON to web socket.  Previously this was only for Minecraft Education Edition.  An update in early October allows this to work with Minecraft Windows 10.
    https://makecode.com/blog/minecraft/10-18-2017
  3. Microsoft Data Gateway allows Flow, PowerApps, PowerBI to talk to on-premises environments.  It also recently gained the ability to execute custom connections.  It is essentially, an enterprise data gateway / reverse proxy that connects your local environment to an Azure Service Bus.  The ability to call your custom local REST endpoint was released in September.
    https://flow.microsoft.com/en-us/blog/q3-2017-update/
  4. Cloud based services like Flow/LogicApps, PowerApps, and PowerBI can talk to this connection online, via the magic of Azure Service Bus.
  5. And because I'm a SharePoint MVP - we are triggering this Flow from SharePoint Online.  Because the world needs this.

Setting up Minecraft & Code Connection

https://www.microsoft.com/en-au/store/p/minecraft-for-windows-10/9nblggh2jhxj?rtc=1 
Buy or download Minecraft for Windows 10

https://education.minecraft.net/get-started/download/
Download Minecraft Code Connection

https://education.minecraft.net/support/knowledge-base/code-connection-api-documentation/
The API for the JSON messages to send to Code Connection is here.
 

Code Connection uses Minecraft commands - the option is called "activate cheats" - do this in a creative world.

cc-1.png

Code Connection is a separate executable.  Run it outside of Minecraft - it'll ask you to enter the command into Minecraft

mc-2.png
cc-2.png
cc-post-1.png

Use Postman to test your localhost:8080 and send a REST request.  You'll see your agent bot move.

Congrats - you now have a REST endpoint that can send game commands to your Minecraft game.

 

Setting up Flow Data Gateway

https://flow.microsoft.com/en-us/documentation/gateway-reference/ 

The data gateway also calls your local REST endpoints via a custom connector.  This was a feature that was silently released in the deluge of news from MSIgnite.  I need to thank @pratapladhani (PowerApps PM) for sending me the link of the announce.  It is REALLY obscure.

https://flow.microsoft.com/en-us/blog/q3-2017-update/

“On-premises connectivity to any HTTP API - Finally, users can now connect their own on-premises APIs with Custom Connectors - leveraging the On-Premises Data Gateway. For example, if you have a service that’s only available on your local network, you can now author a custom connector that can read data from or push data to that local service.”

 

 

data-gateway.png
gateway-2.png

Congrats - this is your reverse proxy.

 

Setting up custom connection via this Swagger File I've prepared for you

https://github.com/johnnliu/flow/blob/master/mc-cc.swagger.json 

Grab this swagger.json and add a custom connection to Flow

custom-connection-0.png

Note the host is localhost:8080
Tick: Connect via on-premise data gateway

custom-connection.png

Note the custom connection swagger file points to localhost:8080 - this will work if your data gateway is on the same machine as the Code Connection exe.  Otherwise, you should use the LAN IP Address of the Code Connection server.

Add a  connection, and select On-Premises data gateway

data-gateway-2.png
connection.png

Note the connection type is "On-premises"

 

Write my Flow

flow.png

These wonderful methods with dropdowns are defined in Swagger file.  It's hard work.  Contributions welcome!

Trigger it from Office 365 / SharePoint

flow-run-1.png
flow-run-2.png

 

Hi bot agent

Uploaded by John Liu on 2017-10-25.

 

Code-Less

I want you to understand, all these are done, without me writing a single line of C# or JavaScript.

Cost?

Office 365 E3+ license allows you to have:

  • 2000 Flow runs per user
  • 1 Custom Connection
  • On-Premises data gateway allowed

https://australia.flow.microsoft.com/en-us/pricing/ 

So there's no extra cost for an Office 365 customer E3+

Summary Key Points

  • Minecraft Win10 has websockets
  • Code Connection does JSON to websocket
  • Data Gateway is amazing, also calls your local REST endpoints
  • Microsoft Flow just unlocks it to the rest of the world, if you like, LogicApps will work just as well.
  • You can have a PowerApp calling the function via the Custom Connection, without Flow.
  • Extremely code-less

Where do we go next?

Well, when I detect an Active Directory User deletion via webhook - I'm going to teleport this Zombie Villager into lava.

 

Update: I can't see a use case for this

So, some people have commented to me directly - John this is very cute, but there's no business use case for this.

Let me explain the head-fake

Flow can reach into your organization and call any of your REST endpoints with no code

Microsoft Flow blog posted https://flow.microsoft.com/en-us/blog/on-premise-apis/ explaining how you can build your own on-premises API service and call it from Flow via the data gateway.  I was bothering them with explanations for the data gateway and I think they were ready to publish anyway, they told me they'll share it in a few days.

I beat them slightly to publish since I worked out how to do the Minecraft bit.

I also think they need more imagination.  Minecraft is a hundred times better example and I didn't even have to write a REST service ;-)

<3

Generate Any PDF Documents from HTML with Flow

/

This is a crazy one, and if you have read ALL my Microsoft Flow blog posts, you'll be familiar with all these pieces.

We are going to connect them a different way though, and the result is still awesome.  Lets begin.

In this blog post:

  • Try to convert image files to PDF
  • Use a workaround to convert image files to PDF
  • Ultimate power: create Any HTML and convert to PDF. 
  • Effectively, we arrive at PDF-gen with templating.

Try to convert Image Files to PDF with Flow

  • Get File Content (Binary) from SharePoint. 
  • Write to OneDrive for Business
  • Use Convert to PDF to convert JPG to PDF.

This doesn't actually work.  But it's good to try and see the error.  Not Acceptable.

This isn't the end though, we have workarounds.

Use a workaround to convert image files to PDF

So even though Convert File doesn't work on image files directly, it does work on HTML.  And this is the heart of our workaround.

Remember several blog posts ago we used dataUriToBinary() to convert PowerApps camera image to a Binary file to store into SharePoint?

Today, we are going backwards.  We are taking a SharePoint image (as Binary), and converting that to dataUri format.  Lets put that in a variable dataUriJPG

We then create another string for <img src=variable(dataUriJPG) />.  You'll need concat() expression to combine the string and variable.

concat('<img src="', variable('dataUriJPG'), '" />')

See, browsers can render images with dataUri directly embedded.  Lets write that to a HTML file (pic.html)

  1. Get File Content from SharePoint
  2. Convert to dataUri string
  3. Concat within an HTML IMG tag
  4. Write to a HTML file
  5. Convert File from HTML to PDF

And hit it again with convert-file to PDF

Ta-da!

jpg-to-pdf-around-result.png

 

End with the ultimate power: create Any HTML and convert to PDF. 

  1. Try multiple headings,
  2. And repeat that image twice.
concat('<html>', '<h1>heading 1</h1>', variables('html'), '<h1>heading 2</h1>', variables('html'), '</html>')

Read and include some live data.

  1. Use SharePoint List Folder action and get a list of all the files
  2. Use Data Operations - Select to remap just the Path and Size properties (this is the same as Array.Map)
  3. Create HTML Table - with automatic columns.  We only have two fields.
  4. Concat into our HTML


Effectively, PDF-gen with templating

In this blog post, we covered some new and old techniques:

  • DataUri is our friend again
  • Convert-File to PDF
  • Select, Create HTML table

I leave the last example as a thought exercise for you, the reader.

  1. Store the HTML template in a HTML file.  You really don't want to be typing HTML in concat within a tiny formula box.
  2. Use Replace expression to replace REPLACE_ME words with values you plan to fill from a live data source.
  3. Insert images as DataUri strings to easily get your logo, headers into the PDF report.
  4. Consider using PDF to snapshot list item upon workflow completion, and then email that as PDF attachment to the manager.

Thank you for reading

I have a favour to ask.  See, I told @paulculmsee sneak peek about this post and he's like oh that's it?
If you think gosh this one's awesome, please tell him he's wrong :-)