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.