Monday, May 3, 2021

On-premise data gateway for Power App, Power BI, Power Automate and Azure Logic Apps

 On-premise data gateway

There are many situations where you will use the on-premise data from different sources to be presented or modified or added from cloud applications like PowerApp, Power BI or Power Automate. On-premise data could be on SQL instance etc. At present there is no direct possible way to transfer the on-premise data to could directly and securely. 

On-premise data gateway allow safe data transfer between on-premise data source locations to cloud services/applications like PowerApp, Power BI or Power Automate. Idea behind using the on-premise gate way to keep the on-premise data on organization network and use online

On-premise data sources could be SQL server, SQL server analysis services, files or SharePoint and other data sources. Similarly Could applications which can use on-premise data could be PowerApp, Power BI, Power Automate, Azure Logic App and analysis services.

Installing and configuring On-premise data gateway

You can click here and visit Microsoft location to get related information like minimum requirement, where to download & how to configure the on-premise data gateway. I will try to cover few topics before planning the on-premise data gateway for your organization.

A) On-premise data gateway naming convention or usage:

You can install on-premise data gateway on your local computer or virtual / dedicated server having consistent network connectivity. You can use same data gateway connecting many data sources. It is good to have generic name used across your organization instead of using gateway name specific to any data source or group if people or team.

B) On-premise data gateway governance:

You can manage permission to gateway as Admin, Can use & Can use and Share. With Can use permission - one can only be able to use the gateway, Can use and share permission - use the gateway as well as share among other team members and Admin can delete gateway and provide access to other team members.

C) Rename or Delete On-premise data gateway:

You can delete the gateway by visiting the Data section in PowerApp online provided you have Admin permission. In addition, We do not see the direct options to rename the on-premise data gateway but deleting the on-premise data gateway and then recreating new one could be one option. If on-premise data gateway has used in many places and you have production usage, it is not advisable to follow this step. Below steps can be considered:

  • Take snapshot of permissions provided to users with current gateway. You will have to grant same level of permissions to users with newly created on-premise data gateway
  • Take another snapshot of connections where you have used this on-premise data gateway. You can visit PowerApp > Data section > Gateways > select current gateway > go to details > connection section
  • Optional step: You can send communication to your users about decommission of current gateway
  • Delete on-premise data gateway
  • Create new on-premise data gateway using correct naming convention and provide same level of permission to users as per step 1.
  • Visit all connections as per step 3 and update the connect with new on-premise data gateway & save it.
  • That is it. It take little while to reflect the changes. In my case, it tool 15 to 20 minutes.

D) New version of gateway:

Microsoft will be providing upgrades to on-premise data gateway. You can visit here to see how to upgrade the gateway.

Unable to delete SharePoint Online site

 There could be many reasons if you are not able to delete the particular SharePoint Online site from Office 365 tenant. I would like to discuss below error messages:

"The site can't be permanently deleted because it's connected to an Office 365 group"

This issue mostly identified with SPO team sites, as modern team site may associated with office 365 group in backend. Before deleting the SharePoint Online site you need to delete the office 365 group. Pease see below steps

1) User PowerShell to connect to SPO Service.

Connect-SPOService -Url https://<SPO Admin URL>

2) Run below command to identify the associated group for SPO team site

Get-SPOSiteGroup -Site https://<SPO Site Collection URL>

3) Delete office 365 group

Remove-UnifiedGroup -Identity "<Group Name>"

4) Delete SharePoint online site

 Remove-SPOSite -Identity https://<SPO Site Collection URL> -NoWait

"This site cannot be deleted because it is included in an eDiscovery hold or retention policy"

In most of the cases, this has been seen that organization may have applied the retention policy and as a result owners can not delete the SharePoint Online site. You can verify the retention policy at below location:

Office 365 portal > Security Compliance > Data governance > Retention

Recommendation is not to remove the retention policy for entire organization but you can add your site in exclusion list of retention policy and then try to delete the SharePoint Online site.

In addition, admins also cannot delete a site that is under a retention policy. In order to site be deleted, the site must first be excluded from the retention policy locations. You can do so by adding an exception through the UI in the Security and Compliance Center (SCC)>Data Governance>Retention>the specific policy or through PowerShell. 

Using PowerShell, you must first connect to the SCC and then use the cmdlet  below

Set-RetentionCompliancePolicy -Identity "<Name of exiting Policy>" -AddSharePointLocvationException "https://<SPO Site Collection URL>

Except External Users and AllUsers default groups?

This is a rear question, but sometime your organization may require to control the security of entire SharePoint online site and disallow default "Everyone",  "Everyone Except External Users" or "Allusers" to be used by respective site owners.

Currently, it is not possible to disable this group from office 365 tenant but you can hide this group to be visible so that when site owners are providing permissions to users on SharePoint online site, they actually can't see or search it.

You can use below PowerShell command to hide this group:

Set-SPOTenant -ShowEveryoneExceptExternalUsersClaim $false -ShowEveryoneClaim $false -ShowAllUsersClaim $false

For more information, you can refer below article: https://docs.microsoft.com/en-us/powershell/module/sharepoint-online/set-spotenant?view=sharepoint-ps

What will happen to exiting SharePoint online sites (after you hide this group)?

It won’t effect those currently using "Everyone",  "Everyone Except External Users" or "Allusers" claims. However, if site owner delete Everyone (for example) from the members group, they will not be able to re-add it because it won’t show up in the people picker. (Unless you set that parameter back to $true for the entire tenant.)

Microsoft Office 365 learning pathways solution

 About

Microsoft Office 365 learning pathways is learning solution to help any organization in learning office 365 and its application's working. In addition, it helps to increase the daily usage of these online services and drive user adaptation. It empower your users to do things. If your organization policy allow the customized solution implementation then this could be learning solution for your users.

You can find all related information about this learning solution, how to configure, usage and troubleshooting from Microsoft locations below:

Reference:

https://docs.microsoft.com/en-us/office365/customlearning/

https://docs.microsoft.com/en-us/office365/customlearning/custom_provision

https://docs.microsoft.com/en-us/office365/customlearning/custom_exploresite

https://docs.microsoft.com/en-us/office365/customlearning/custom_troubleshooting

In this blog we would like to discuss some more facts and finding about implementation and usage of this learning pathway solution.

Its benefits:

  • It's free solution, no additional fee
  • Provide modern experience to end user while learning and training
  • Solution can be modified based on your organization need. For example, you can hide Microsoft provided play list and create your own
  • Very easy to create various play lists based on your relevant scenario & target to specific audiences
  • Offer on-demand training for your office 365 users
  • It has custom web part which offer dynamic feed and content
  • Very easy to deploy and configure using provisioning service
  • Center place of all training and learning material organized by category or application wise.

Additional Information:

  • Require tenant admin rights to deploy this solution on tenant.
  • "App Catalog" site collection needed/created before you configure the leaning pathway solution. 
  • Make sure you are site collection administrator of "App Catalog" as you will use "Apps for SharePoint" section to upload custom learning pathway app
  • Require modern communication site to configure learning pathway apps/web part. This is going to be framework for custom solution. The communication template works best with this custom 365 learning pathways solution

Helpful Links:

  • Driving Adaptation Blog: Microsoft is updating the latest new or information about this solution on "Driving Adaptation Blog" site. You can follow and also get latest feed
  • Feedback and support: Microsoft also offer users to report any issues, ask questions, provide suggestions or provide feedback about this learning solution at "Feedback and Support" site

SharePoint Online list connection issue with Tableau desktop app

 About

Tableau desktop application is being used most popularly in various organizations for data analysis or reporting purposes. It allows connection between SharePoint Online list to get the data and display. This article will cover connection issue between SharePoint Online list and Tableau Desktop application. 

"An error occurred while refreshing tasks." 

"You must either set an auth cookies or specify a username and password to connect to SharePoint Online. Unable to cerate extract"
OR
"Unrecognized response when getting SharePoint online security cookies, please check your credentials."

If you connect Tableau desktop app with SharePoint online list and able to create extract, publish to tableau server and finally save the extract. No issue reported. But once you reopen the saved extract file and try to refresh the connection it gives you connection issue with below error message.

"You must either set an auth cookies or specify a username and password to connect to SharePoint Online. Unable to cerate extract"

Troubleshooting:

  • Make sure your are using correct version of drivers both on client side and server side. You can find latest driver information from Tableau site. click here
  • You can use Fiddler or WireShark apps to capture and compare the traffic data while connecting to SPO list when it was first working and then when it is not working. 
  • You can try to login to Tableau client desktop app and as well as web app in non-cached environment (using browser in private mode) and to see if issue is limited to desktop app or web app also have same trouble.
  • Try using same account email address to login to SharePoint online list directly to see if you can connect without Tableau app 
  • Resolution: If you are using Tableau version 2019.4 (or below) desktop client app, then you can consider upgrading it to higher version as this has been seen that if you try to open already saved Tableau extract file it will not allow you to re-enter the credentials and give above error messages. In higher version like 2019.4.3 (or latest) desktop client you can see new popup feature to re-enter the credentials and connect to SharePoint Online list again.

Reference:

You can visit below articles to know How to connect to Tableau with SharePoint Online list

https://help.tableau.com/current/pro/desktop/en-us/examples_sharepoint_lists.htm

https://www.tableau.com/support/drivers?_fsi=IXoDeruy&_ga=2.65582054.973423501.1589759769-1324713014.1589759769&_fsi=IXoDeruy

Highlighted Content web part missing Page Properties

Using SharePoint online modern pages, when you try to add Highlighted Content web part and try to filter the content by column values or page properties, it does not show you filter option to choose "Page properties" and further not allow you to choose any columns. This is not an issue but you need to choose the different source under "Content" section. 

Please follow below steps:

1. Add Highlighted Content web part on modern SharePoint page.

2. Go to web part properties 

3. Under section "Content", choose "The page library on this site"

4. Under section "Filter and sort", choose filter "Page Properties"

5. Now you can see option to select property name and text box to add the values.


For more help and information about Highlighted content web part you can also visit : https://support.microsoft.com/en-us/office/use-the-highlighted-content-web-part-e34199b0-ff1a-47fb-8f4d-dbcaed329efd

SharePoint Online list missing quick edit button

 About Quick Edit button

Quick Edit button is an another alternative option replacing datasheet view in modern SharePoint Online pages. In some scenarios, Quick edit button does not appear as expected. There could be many reasons, please follow below troubleshooting steps to resolve this issue.

1. Make sure "Allow items in this list to be edited using Quick Edit?" option is selected.

Go to List settings > Advanced Settings > Scroll down to "Allow items in this list to be edited using Quick Edit?" and select Yes.

2. Check if you have Edit permission on list level. Even if edit permission granted on Item level, you still need edit permission on list level to see the "Quick Edit" button.

3. Try using default list view without using options or filters like "Group By" or "Filter" or "Sort" etc.

4. Although "Quick Edit" button works fine on all browsers but try resetting your browser history / cookies or using browser in private mode to see if it work fine.

5. If list with datasheet view recently migrated from on-premise SharePoint to SharePoint online, then after migration try deleting the datasheet view and recreating new one


Reading JSON file using PowerShell

 

Introduction

Reading JSON file can be done using "ConvertFrom-Json" method which simply converts a JSON content to an object or a hash table. I would like to explain the reading / parsing concept by using below JSON format string. JSON can be downloaded from site or can also be get from file from local machine.

JSON content example:

'{"Chair":[ { "color":"white", "price":"50" },    { "height":"10", "width":"5" }]}'

Reading JOSN using site URL

$JSONRequest = 'http://<JSON SITE URL>'
$JSONContent = Invoke-WebRequest $JSONRequest | ConvertFrom-Json
$JSONContent | select -expand Chair | Select color
$JSONContent | select -expand Chair | Select price
$JSONContent | select -expand Chair | Select height
$JSONContent | select -expand Chair | Select width

Reading JSON file from local machine

$JSONContent = Get-Content -Path "C:\filename.json" | ConvertFrom-JSON
$JSONContent | select -expand Chair
$JSONContent | select -expand Chair | Select color
$JSONContent | select -expand Chair | Select price
$JSONContent | select -expand Chair | Select height
$JSONContent | select -expand Chair | Select width


Some time, you may get the error message below while running this cInvoke-WebRequest command. Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a send. This error be resolved by using below link of code in start of PowerShell script

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Reference:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/convertfrom-json?view=powershell-7

Edit User Permission or Remove User Permission greyed out in SharePoint Online Team site

In SharePoint Online modern experience, if you create SharePoint Online Team site it also create Office 365 group associated with it. In addition, three SharePoint groups like Owners, Members and Visitors created and if you try to change the permissions of these groups, you will find the "Edit User Permission" or "Remove User Permission" grayed out.

By default SharePoint associate these groups to Office 365 membership and use these groups to manage the owners, members and visitors of office 365 group membership as well. Hence you can not change the permission of these groups manually.

There are some alternative options like you can change the SharePoint Online Team site default/associated groups so that you can use the existing groups with required permission. In order to change the association, you can either create new SharePoint groups or select the existing groups by navigating below SPO URL.


https://<YOUR DOMAIN>.sharepoint.com/sites/<SITE NAME>/_layouts/15/permsetup.aspx