Connecting PowerBi to an Access Web App in SharePoint Online

Hopefully this will help some others prior to the shutting down of existing web apps and web databases in Office 365 and SharePoint Online.

I have been working with a client in providing guidance in the migration / move of an existing Access Web Application and Database  from one tenant to another. A component of this application uses PowerBI reports and Dashboards that requires the use of an OLE DB Connection.

After moving the web app and web database the below error was encountered:

After scouring the Azure Portal for firewall rules or any other access restrictions cam up with nothing, I ended up opening the Web App in the Access client application and checking to see if there were any settings in access that may be causing this issue.

Turns out that there is a Connections setting in the Info of an Access Web Application called “From Any Location”, selected that saved and bingo, all worked and connections were established as expected.

As a side note, please be aware of the following:

This feature will be retired from Office 365 and SharePoint Online. We will stop creation of new Access-based web apps and Access web databases in Office 365 and SharePoint Online starting in June, 2017 and shut down any remaining web apps and web databases by April, 2018.

https://support.office.com/en-us/article/access-services-in-sharepoint-roadmap-497fd86b-e982-43c4-8318-81e6d3e711e8

Credit: Photo by Jason Leung on Unsplash

Improve Project Delivery with Office 365 Groups and Microsoft Flow

One of the great use cases for Office 365 Groups are for running Projects. They help with keeping my internal project team aligned to the outcomes we are working towards and also provides the foundation for improved communication and documentation with my clients.

As a general rule, most projects that I run with clients have either SharePoint, Office 365 Groups, Yammer and Microsoft Teams involved in some way shape or form depending on the business outcomes the client is working towards.

As a by product of using Office 365 Groups for running these projects and adding the client project team members (external to my tenant) as Guests of the Office 365 Group, the client then gets their first look at he “new” SharePoint, they become the “early adopters” if you will and form part of the Adoption and Change Management strategy and more often than not become part of the “champions” group.

One of the key things that I implement, and most others do as well is provide an issues and risks register for the client for the duration of the project. Extending this with Microsoft Flow has provided some great results and happy clients.

An example of a Flow that I have implemented is show below:

Very simple flow, but has great results.

When an issue is created or modified in the issues list, we check to see if the status is Resolved. If the item has been resolved we fire off an email to the Creator (generally the client) to notify as such.

If the issue is new, meaning not resolved we first check the priority. If the priority is set to high then we execute a push notification and also a high priority email notification to the Project Team. If the priority is anything other than high then a normal email notification is sent to the Project Team.

With this simple implementation of Office 365 Groups, combined with Guest Access and Microsoft Flow , projects are run smoothly, outcomes are achieved and clients are happy. A win for all.

Choosing a Content Type via the OneDrive for Business Sync Client

I have written previously about how you can set metadata via the OneDrive for Business Sync Client. I use the NextGen Sync Client to sync Project Document Sets to my Surface Pro. Using the new client allows me to sync only the document sets that I want and not the entire library.

One of the bug bears of syncing is that you don’t get all the document library features. Choosing Content Types for Documents is one of those. Now recently while working on a project with colleagues, I started a document from the Word 2016 Client. After starting the document I proceeded to File > Save As and browsed to my synced project document set on my PC, hit the save button and BOOM I was presented with the below dialog Box.

I can choose a Content Type !!! Combine that with setting metadata via the OneDrive for Business Client and the Office client (seen below) we have a good solution.

 

Azure Functions, Zapier, ConnectWise and SharePoint = Better together

In a previous post Integrating ConnectWise and SharePoint Online with Office PnP and Azure Functions I gave an example of how the awesomeness of Azure Functions combined with Office PnP Powershell can combine to integrate other line of business applications with SharePoint Online. In that particular case I was integrating ConnectWise with SharePoint Online.

As already mentioned in that post, ConnectWise has what they can “callback” functionality. Meaning we can send “payloads of information” to a specified location (an Azure Function). I have been working on an integration piece so that whenever an opportunity or project is created in ConnecWise an Opportunity Document Set or Project Document set is created in SharePoint with associated Site Column values set from data passed in from the payload sent by ConnectWise.

I have been having a few issued with the Project API from ConnectWise not firing and send that “payload” to my Azure Function endpoint. So I have been investigation alternatives and while looking through the ConnectWise Developer Forum I came across a post mentioning that ConnectWise Manage is in Beta as a Zapier Application. This sparked some interest.

After joining the Beta there are some initial setup procedures to complete, generating Public and Private keys from ConnectWise for authentication but that is all really straight forward, just follow the bouncing ball when setting up the Zapier App.

When we create the new Zap, the following triggers are available:

I am interested in the New/Updated Projects. After choosing the appropriate trigger we can then set some additional settings. I want it to trigger on all projects but only on new projects.

Then it comes time to choose an action, and what do you know Zapier has a webhook app.

I want to Post data to my Azure Function endpoint.

Then we are presented with the option to enter the URL we want to send the payload to and also the format. In my case my Azure Function endpoint and I want JSON as the format. I can then use the power of the Office PnP Powershell commands in my Azure function to consume the data and do the funky stuff in SharePoint Online.

On the Azure Function end the code I am using is fairly similar to the previous post except that the format of the JSON is slightly different so all I needed to do was change a few lines of code to accomodate. You can use something like this to have a look at the data that is being received:

I like using this because I like to examine the data from a file. Just personal preference. With the data now been sent and now consumed by my Azure Function I can use a combination of the Office PnP Powershell commands to do all the heavy lifting. In particular Connect-PnPOnline, Get-PnPListItem, Set-PnPListItem, and I even threw in Send-PnPMail to send email notification.

I am really loving combing Azure Functions and the Office PnP Powershell commands to achieve some great automation capabilities. It also proves that Azure Functions, Office PnP, SharePoint Online and third party applications are better together.

 

Set Metadata for a Document via the OneDrive Sync Client

I read a great article written by Jeremy Thake titled “Getting the most out of OneDrive for Business” over on the Hyperfish Blog (amazing product by the way !!!

I have heard many times before about the fact there are challenges working with the sync client is that the benefits of SharePoint (eg: versioning, views, content types etc) can’t be used. Granted there are some challenges, but when it comes to Metadata did you know that you can set metadata from the Office Client itself?

Below is a document that has been opened from the new sync client on my local machine from a SharePoint Document Library.

Associated to this Document Library is a Client column that is set to required. Notice the yellow warning bar…..”To save to the server, correct the invalid or missing required properties”.

If I click the Edit Properties button I am taken to the properties of the document where I find the culprit.

This is a metadata column that is coming from the SharePoint Document Library. If you had other columns associated to the library then they would appear as well.

Similarly, if you click the File Tab in Office 2016 from the open document you are taken to the same place.

As Jeremy mentioned there are some challenges, but at least we can set the metadata.

 

Integrating ConnectWise and SharePoint Online with Office PnP and Azure Functions

Azure Functions and Office PnP Powershell modules provide us with an extremely powerful platform to build some robust solutions. As mentioned previously I have been digging into the potential of Azure Functions in combination with the Office PnP Powershell modules for a while now and this post outlines another great use case. What I have manged to accomplish here is the integration of ConnectWise and SharePoint Online using Azure Functions and the Office 365 Powershell Modules.

For those unfamiliar with ConnectWise, it is a business process automation platform that allows your business to sell, service and support technology more efficiently and in a more streamlined way. The business management tool allows your business to centralize all information, automate business process, real-time visibility in operations, and provide better customer support.

We use ConnectWise internally, but what it does not handle very well is Document Management. SharePoint on the other hand well……

What if we could integrate, in an event driven way ConnectWise with SharePoint Online? Well with the power of Azure Functions, Office PnP Powershell modules and the ConnectWise “callback” functionality we can make this happen and make it happen really well!. ConnectWise callbacks are “payloads of information that are similar to webhooks”. So when a record is created or updated in ConnectWise, a payload is sent to a specified location (an Azure Function).

You can read more about the ConnectWise Callbacks on the ConnectWise Developer Website.

While building a Client Management Portal for internal operations, I had a few requirements around Clients, Opportunities and Projects. When a new Client is added to ConnectWise, create a new Client entry in our Client list in SharePoint. When an Opportunity is created in ConnectWise, create an Opportunity Document Set in an Opportunities Library. When a Project is created in ConnectWise, create either a Project Document Set or an Office 365 Group depending on requirements. You get the picture.

In ConnectWise we can enable callbacks via an Integrator Login Setup Table. Simply enable the respective API and provide a callback URL.

The only requirement from a ConnectWise perspective is that at the end of the callback url you must include an (=) sign after the URL. eg: https://azurefunction.azurewebsites.net/api/newOpp?code=53ltosoqitok26fw80mtwklnmiq0cd5ytqpecben6ig13fk1emixj7dgvuoy&id=

The Callback URL in my case will be the Azure Function URL.

Now as I have already written about, and so has MVP John Liu, we can very easily integrate the Office PnP PowerShell modules into an Azure Function. So what I have created is a HttpTrigger-PowerShell Function from the provided template.

With all this in place, we can write the Azure Function to consume the JSON Payload that is sent by ConnectWise. You can see a main parts of my function below.

So now what we have is a great automated solution to manage Clients, Opportunities and Projects from ConnectWise and using all the power that Office 365 and SharePoint give us and our Project Teams.

Use Azure Functions and Office PnP Powershell to Schedule SharePoint Online Reporting

Over the past few weeks I have been doing quite a bit with Azure Functions, and let me say that Azure Functions are amazing!

If you follow MVP @johnnliu you will have no doubt come across numerous articles on how you can use Powershell in Azure Functions. The catalyst for me was John’s article on Site Provisioning with Office PnP Powershell, Flow and Azure Functions (read it here). As John mentions this is very powerful.

I had a request to provide a recurring report on leave that had been requested from the past week. Enter the Azure Function Template Timer-Trigger-Powershell.

I won’t go over how you can add the required Office PnP Powershell modules to an Azure Function as John does a great job in his article above.

What this allows you to do is run a PowerShell Script on a Schedule, perfect. What I opted for was to use the Get-PnPListItem  with the -Query switch to query a Leave Request SharePoint List for any new entries for the past 7 Days. This is to be run weekly at the end of the week.

When using the TimerTrigger-Powershell Template you have the opportunity to provide a schedule

As you can see above you enter a cron expression to specify your schedule.

Below you can see the Powershell commands that I am using to

  1. Connect to the desired Site Connect-PnPOnline
  2. Create CAML Query
  3. Get Items using Get-PnPListItem and the -Query switch, passing in the CAML Query
  4. Looping through the items generating an array
  5. Exporting as CSV
  6. Then using Add-PnPFile, uploading the report to a Document Library in the same site

I am currently working on a solution to manage item level permissions with Azure Functions, FLOW and the Office PnP Powershell commands. More on that in a later post.

Azure Functions, FLOW and SharePoint = Brilliant !!!

Copy Documents from OneDrive for Business to a Microsoft Team

Microsoft Teams are hot right now and rightly so. This is a quick post to highlight another little but big feature that make things really easy for a team member to copy content from their own OneDrive for Business to a Microsoft Team Channel / Office 365 Group.

As mentioned in  my previous post, every Team Channel gets a folder in the underlying SharePoint Site Document Library. Now the lifecycle of a document may start in an individual users OneDrive for Business and then progress to the wider Team for review or additional collaboration and discussion.

The Files Tab of Microsoft Teams makes things really easy to copy a file from your OneDrive for Business to a specific Channel of the Team. When you enter the Files section of Microsoft Teams you are automatically presented with Recent Files that you have been working on and you also have the ability to access your OneDrive for Business Files and files from other Teams that you are a member of.

If you enter your OneDrive for Business and select a file, you have the option to copy that file to a Team that you are a member of.

screen-shot-2016-11-05-at-8-41-35-am

Once you click Copy you can browse your Teams and copy the file to the folder that represents the Channel, now all team members have access to the file and you can begin to collaborate.

Microsoft Teams and Office 365 Groups Integration

Microsoft recently announced Microsoft Teams. Microsoft states this is an “entirely new experience that brings together people, conversations and content, along with the tools that teams need.” I am all for providing organisations, teams and users choice when deciding on how they want to work and the introduction of Microsoft Teams gives just that. Remember, not every team or individual works the same so giving them different ways of working together is a must in this day and age.

I am a huge fan of Office 365 Groups and what they bring to organisations in the way of improved collaboration, allowing teams to work better and be more productive and simply get their work done easier and better. As with all new Microsoft services, the underlying fabric of Microsoft Teams is Office 365 Groups. They provide membership, access control and other services to a Microsoft Team. Did I mention that I love Groups?

During the process of creating a Team, a Private Office 365 Group is provisioned that provides the Team with file storage, task management via Planner, a OneNote Notebook, Team Calendar all the goodness of what Office 365 Groups bring. One good thing about the Team creation process is that you can add Teams functionality to an existing Office 365 Group, but this this group must be a PRIVATE Group.

screen-shot-2016-11-04-at-1-15-48-am screen-shot-2016-11-04-at-1-16-00-am

By default every Team gets a “General” Channel, now if you have added Teams functionality to an existing Office 365 Group then what that does is create a folder called “General” in the default document library of that group. When you create different Channels in a Team and upload files to that channel,  a folder for that channel is created in the document library also.

screen-shot-2016-11-04-at-1-20-02-am

While adding Team functionality to an exisiting Groups is a great feature, one little nuance that I have found is that you cannot see any existing content from the Group’s document library. You can only see files for each channel (which live in the channel folder in the library). You can however add a “SharePoint” tab that can allow you to point to another document library from another site and surface the content from that tab.

What I did notice is that if you point to the existing Office 365 Group Library, you get full visibility to the library and can see the channel folders, so you can upload files from the newly created tab into a channel folder as well and those files will appear in the files tab of the channel.

Scheduling Meetings.

When scheduling a meeting in a Team, again through the integration here with Office 365 Groups, the meeting is added to the Exchange Calendar of the Group and an invitation is sent to the mailbox of that Group. Nice.

Primary Team Platform?

I can certainly see the potential with Microsoft Teams and I can see this being the primary platform for a number of organisations as they quickly bring everyone in a team together to collaborate and communicate.

Something else that I think Teams can do is by making it easy for users to decide where to store their files. As mentioned earlier files a stores in the underlying Group Document Library.

One thing that I can see happening is users being confused about where to have discussions through. So with Teams we add another “option” to the email based conversations of an Office 365 Group and of course Yammer.

There is a good post over at Sharegate by Ben Niaulin on Microsoft Teams vs Yammer that is worth a read.

It will be interesting to see where this goes as Teams mature.