Excel REST API in SharePoint Online to PowerBI

Let’s says that you have an excel file located in a SharePoint library, and you know where to get data like this, and you want to display them in Power BI:

ExcelTableName

Yes, PowerBI can get data from an excel file, but how can we do that for excel online? Luckily, there are some example URL from excel services that we can use to fetch data from Excel:

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/sample-uri-for-excel-services-rest-api

For PowerBI, Odata maybe the best one to selected

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/using-odata-with-excel-services-rest-in-sharepoint

1. Get data from web (past odata link to the URL)

DataFromWeb

2. Sign in by your organization account

SignIn

3. Make sure you have data in Power Query Editor

PowerQueryEditor

Like this

QueryResult

Silent Sign In on SharePoint hosted app

This is for you to embed sharepoint hosted app to your main web with silent login:

Let’s say your app start page is like this:

https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/YourApp_ViewerStartPage.aspx?entrypoint=https://editor.YourApp_.com/intra/portal#/model/787e8934a8184c4a99152cca823a08cd&SPHostUrl=https://your_sp_ab.sharepoint.com&SPHostTitle=your_sp_ Intranet&SPAppWebUrl=https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App&SPLanguage=en-US&SPClientTag=2&SPProductNumber=16.0.19131.12021&SenderId=543305360

Then you need to sign-in from your app

https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/_layouts/15/silentSignIn.aspx?redirect_uri=https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/YourApp_ViewerStartPage.aspx?entrypoint=https%3A%2F%2Feditor%2EYourApp_%2Ecom%2Fintra%2Fportal%23%2Fmodel%2F787e8934a8184c4a99152cca823a08cd&SPHostUrl=https%3A%2F%2Fyour_sp_ab%2Esharepoint%2Ecom&SPHostTitle=your_sp_%20Intranet&SPAppWebUrl=https%3A%2F%2Fyour_sp_ab%2De231aa523fe845%2Esharepoint%2Ecom%2FYourApp_Viewer365App&SPLanguage=en%2DUS&SPClientTag=2&SPProductNumber=16%2E0%2E19131%2E12021&SenderId=543305360

then sign-in from your main web

https://your_sp_ab.sharepoint.com/_layouts/15/silentSignIn.aspx?redirect_uri=https://your_sp_ab-e231aa523fe845.sharepoint.com/YourApp_Viewer365App/_layouts/15/silentSignIn.aspx?redirect_uri=https%3A%2F%2Fyour_sp_ab%2De231aa523fe845%2Esharepoint%2Ecom%2FYourApp_Viewer365App%2FYourApp_ViewerStartPage%2Easpx%3Fentrypoint%3Dhttps%253A%252F%252Feditor%252EYourApp_%252Ecom%252Fintra%252Fportal%2523%252Fmodel%252F787e8934a8184c4a99152cca823a08cd%26SPHostUrl%3Dhttps%253A%252F%252Fyour_sp_ab%252Esharepoint%252Ecom%26SPHostTitle%3Dyour_sp_%2520Intranet%26SPAppWebUrl%3Dhttps%253A%252F%252Fyour_sp_ab%252De231aa523fe845%252Esharepoint%252Ecom%252FYourApp_Viewer365App%26SPLanguage%3Den%252DUS%26SPClientTag%3D2%26SPProductNumber%3D16%252E0%252E19131%252E12021%26SenderId%3D543305360

you should encode the redirect_uri to be able to use it in your iframe.

https://your_sp_ab.sharepoint.com/_layouts/15/silentSignIn.aspx?redirect_uri=https%3A%2F%2Fyour_sp_ab%2De231aa523fe845%2Esharepoint%2Ecom%2FYourApp_Viewer365App%2F%5Flayouts%2F15%2FsilentSignIn%2Easpx%3Fredirect%5Furi%3Dhttps%253A%252F%252Fyour_sp_ab%252De231aa523fe845%252Esharepoint%252Ecom%252FYourApp_Viewer365App%252FYourApp_ViewerStartPage%252Easpx%253Fentrypoint%253Dhttps%25253A%25252F%25252Feditor%25252EYourApp_%25252Ecom%25252Fintra%25252Fportal%252523%25252Fmodel%25252F787e8934a8184c4a99152cca823a08cd%2526SPHostUrl%253Dhttps%25253A%25252F%25252Fyour_sp_ab%25252Esharepoint%25252Ecom%2526SPHostTitle%253Dyour_sp_%252520Intranet%2526SPAppWebUrl%253Dhttps%25253A%25252F%25252Fyour_sp_ab%25252De231aa523fe845%25252Esharepoint%25252Ecom%25252FYourApp_Viewer365App%2526SPLanguage%253Den%25252DUS%2526SPClientTag%253D2%2526SPProductNumber%253D16%25252E0%25252E19131%25252E12021%2526SenderId%253D543305360

See more here:

https://splucy.wordpress.com/2014/11/20/getting-the-host-page-url-in-an-app/

http://giancode1.blogspot.com/2013/06/get-host-query-string-parameters-in-app.html?m=1

Cool.

 

Wildcard SSL for SP and high-trust-certificate

NOTE: The certificate forĀ  high trust does not support CNG enabled. Have to create with a legacy option.

I. Follow this to create a new certificate using for SSL:

https://support.jetglobal.com/hc/en-us/articles/235636308-How-To-Create-a-SHA-256-Self-Signed-Certificate

Example:

$cert = New-SelfSignedCertificate -DnsName “*.spdev.com” -CertStoreLocation “cert:\LocalMachine\My”

Then, go to iss web application -> bindings -> edit -> https -> SSL certificate: *.spdev.com -> host name: http://www.spdev.com -> port 443

launch the web application, on address bar, click to install the certificate to the computer

II. Follow this to create a new certificate for SP high trusted app:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/create-high-trust-sharepoint-add-ins

Example:

  1. Create a self-sign-certificate
  2. Export to pfx
  3. Export to cer
  4. Open MMC, install certificate to “Trusted Root Certificate Authority” and “Personal”
  5. On Personal, right click to the certificate -> All Tasks -> Manage Private Keys…-> Add user in your wep application pool to here
  6. Share certificate folder to everyone
  7. ……………………
  8. New-SPTrustedRootAuthority (remove first if already existed)
  9. New-SPTrustedSecurityTokenIssuer (remove first if already existed)
  10. AllowOAuthOverHttp
  11. Update OnPrem.config for all web applications
  12. IIS reset all

 

Get User Profile Properties with REST API

Thanks Vardhaman Deshpande for this tips:

1) Get all properties of current user:

http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties

2) Get single property of current user:

http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties/PictureUrl
OR
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties?$select=PictureUrl

3) Get Multiple Properties for the current user:

http://siteurl/_api/SP.UserProfiles.PeopleManager/GetMyProperties?$select=PictureUrl,AccountName

4) Get all properties of Specific User:

For Office 365/SharePoint Online:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)?@v=’i:0%23.f|membership|vardhaman@siteurl.onmicrosoft.com’

For SharePoint 2013 On-Premises:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetPropertiesFor(accountName=@v)?@v=’domainusername’

5) Get Specific UserProfile Property of Specific User:

For Office 365/SharePoint Online:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName=’LastName’)?@v=’i:0%23.f|membership|vardhaman@siteurl.onmicrosoft.com’

For SharePoint 2013 On-Premises:
http://siteurl/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName=’LastName’)?@v=’domainusername’

6) Get Multiple UserProfile Properties for Specific User:


http://siteurl/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertiesFor

Script to activate SharePoint feature on all site collection – Combine PnP and CSOM scripts

The prerequisite for this script is you need to be global admin on your tenant or you you have app installed with full permission on tenant scope

Let’s assume that your app have full permission on tenant scope, and you know the client id/client secret of your app

1. If your powersell script is not support for the execution policy, add this to your powershell windows first:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

2. Import SharePointPnPPowerShellOnline and Microsoft.Online.SharePoint.Powershell

Import-Module SharePointPnPPowerShellOnline -Scope “Local”
Import-Module Microsoft.Online.SharePoint.Powershell

Add-Type -Path “$path to client dlls\Microsoft.SharePoint.Client.dll”
Add-Type -Path “$path to client dlls\Microsoft.SharePoint.Client.Runtime.dll”

3. Declare the parameters

$global:appId = “your-client-id-guid-string”
$global:appSecret = “your-client-secret”

$global:adminUrl = “https://yoursharepoint-admin.sharepoint.com”

$global:SPFeatureId = “any-sharepoint-feature-id-fbace37b4a34”;

4. Connect to admin URL to get all site collection

Connect-PnPOnline -AppId $global:appId -AppSecret $global:appSecret -Url $global:adminUrl

$adminConnection = Get-PnPConnection

$allSitecollections = Get-PnPTenantSite -Connection $adminConnection

$sitecollections = New-Object System.Collections.ArrayList

5. For each site collection, connect again and activate the feature

foreach($siteCollection in $allSitecollections)
{
$targetUrl = $siteCollection.Url
Connect-PnPOnline -AppId $global:appId -AppSecret $global:appSecret -Url $targetUrl
$connection = Get-PnPConnection

#Activate SPFeature
$ctx = Get-PnPContext #This is important to get client content and use in CSOM
$site = $ctx.site
$Ctx.ExecuteQuery()

$Site.Features.Add($global:SPFeatureId, $true, [Microsoft.SharePoint.Client.FeatureDefinitionScope]::None) | Out-Null
$Ctx.ExecuteQuery()

Disconnect-PnPOnline -ErrorAction SilentlyContinue
}

That’s all!!!

Add a custom javascript file to SharePoint library using csom

var list = Ctx.Site.GetCatalog((int)ListTemplateType.MasterPageCatalog);
Ctx.Load(list);
Ctx.ExecuteQuery();

var rootFolder = list.RootFolder;
Ctx.Load(rootFolder);
Ctx.ExecuteQuery();

bool displayTemplateExists = DisplayTemplateExists(Ctx, list, ProjectStatusAll.Title);
string destFileName = rootFolder.ServerRelativeUrl + “/Display Templates/Content Web Parts/” + “Item_ProjectStatusAll.js”;
string content = ProjectStatusAll.Content;
string ctId = “0x0101002039C03B61C64EC4A04F5361F38510660500E478F12A266BD24B842D0B2128BDDAAB”;

var fields = new List<KeyValuePair<string, string>>();

fields.Add(new KeyValuePair<string, string>(“Title”, ProjectStatusAll.Title));
fields.Add(new KeyValuePair<string, string>(“TargetControlType”, ProjectStatusAll.TargetControlType));
fields.Add(new KeyValuePair<string, string>(“DisplayTemplateLevel”, ProjectStatusAll.DisplayTemplateLevel));
fields.Add(new KeyValuePair<string, string>(“TemplateHidden”, ProjectStatusAll.TemplateHidden));
fields.Add(new KeyValuePair<string, string>(“ManagedPropertyMapping”, ProjectStatusAll.ManagedPropertyMapping));

if (!displayTemplateExists)
{
CreateFile(Ctx, list, content, Encoding.UTF8, destFileName, ctId, fields, false);
}

 

public File CreateFile(ClientContext Ctx, List list, string content, Encoding encoding, string destFileName, string ctId, List<KeyValuePair<string, string>> fields, bool overwrite)
{
// Verify encoding
if (encoding == null)
{
throw new ArgumentNullException(“encoding”);
}

if (list == null)
{
throw new ArgumentNullException(“list”);
}

FileCreationInformation fileCreateInfo = new FileCreationInformation();
fileCreateInfo.Url = destFileName;
fileCreateInfo.Content = encoding.GetBytes(content);
fileCreateInfo.Overwrite = overwrite;

File createdFile = list.RootFolder.Files.Add(fileCreateInfo);
Ctx.Load(createdFile);
Ctx.ExecuteQuery();

ListItem item = createdFile.ListItemAllFields;

item[“ContentTypeId”] = ctId;

foreach (var field in fields)
{
item[field.Key] = field.Value;
}

item.Update();
Ctx.ExecuteQuery();

return createdFile;
}