Power Query Editor: Add “Grouped By” Column to existing query

 

 

Accessing other people’s mailboxes in Office 365

There is a topic for this: https://support.microsoft.com/hr-ba/help/10048/accessing-other-people-s-mailboxes-in-office-365

The basic steps are:

1. Go to https://outlook.office365.com/ecp/ if you have Office 365 tenant administrator credentials
2. Select Recipient and edit
3. 
4. Add yourself to “Send As”, “Send on Behalf” and “Full Access”
5. 
6. In your mailbox, add a new shared folder and enter the person that you want to see emails
7. 
8. Bem, you should be able to see his emails under shared folder
9. 

Happy administrator!

SharePoint online: Share your excel report to anonymous users

Sometime, you have an excel report located in your SharePoint Online, and you want to share it to anonymous user (not require to login), how can you do that?

First, you need to turn on “Allow sharing to authenticated external user and using anonymous access link”

Then, select the site collections that you want to share to anonymous, turn on this “Allow shareing with all external users, and by using anonymous access links”

Now, we go to the excel report that you want to share to external user and click share.

Then copy the link in the dialog.

So, the link should be similar this:

https://yoursite.sharepoint.com/:x:/g/publishing-report-center/publishing-sale-reports/ETflhztYjGdCljjlf-0nt8kBICp–iVrpej5m2b–sGmQA?e=7r7fRe

But wait, you don’t want to share the whole file, you just want to share the report in the file. Humm, let’s get the report link first:

In your excel online file, click to File -> Share -> Embed

Select the report area in the embed dialog, then you will have a script similar this:

< iframe width=”1024″ height=”1024″ frameborder=”0″ scrolling=”no” src=”https://yoursite.sharepoint.com/publishing-report-center/publishing-sale-reports/_layouts/15/Doc.aspx?sourcedoc={3b87e537-8c58-4267-9638-e57fed27b7c9}&action=embedview&ActiveCell=’OverviewTable’!B4&Item=TableOverview&wdHideGridlines=True&wdInConfigurator=True“></ iframe>

Now, you can combine 2 links above to have a final link for your report. The final link should be like this:

https://yoursite.sharepoint.com/:x:/g/publishing-report-center/publishing-sale-reports/ETflhztYjGdCljjlf-0nt8kBICp–iVrpej5m2b–sGmQA?e=7r7fRe&sourcedoc={3b87e537-8c58-4267-9638-e57fed27b7c9}&action=embedview&ActiveCell=’OverviewTable’!B4&Item=TableOverview&wdHideGridlines=True&wdInConfigurator=True

Use the tool online to make that url shorter, then from now on, you can send that link to anonymous user to see your report.

Good luck!

Add O365 account as a member of Visual Studio Online project

For testing purpose, follow this article to create a free 30-day trial of Office 365 Developer account.

When everything are ready, you should have an account and all the related apps like this:

In my case, my account is: admin@testingtableau.onmicrosoft.com

And, if you want to add this account to your Visual Studio Online project. Go to “Members” and add new user:

In the popup, type the account there, but unlucky, you see something like “No identities found”:

Don’t worries, just go ahead and click on “Save changes”.

Then, use another browser (or another user on same browser) and access to your project link, my case: https://mysub.visualstudio.com/myproject

The login form will display for you to enter your account, but ops! What the hell is this?

Most of us will give-up at this time, but why? Why don’t you just go ahead and create a Microsoft account similar with O365 account and use it for both?

Then, go with some other steps and you are ready to use that account for your Visual Studio Online project.

Annoying, because they are still 2 difference users. So you still need to select a correct type when you want to access to any of them.

How to get SPAppToken in Office 365 and use it in your Provider-Hosted app?

When you log in to your SharePoint site in Office 365, the site will generate a SPAppToken  and cache it somewhere for you to use in App. So, you need this token to open the ClientContext in your Provider-hosted app. When you add an app to your page, it will go to the appredirect.aspx with some parameters:

redirect_uri: App url with the formatting like this:

1:  https://yourproviderhosted.cloudapp.net/ProjectSite?SPHostUrl=https://youroffice365.sharepoint.com/sites/yoursitecollection&SPHostTitle=Your SharePoint Site Title&SPAppWebUrl=”&SPLanguage=en-US&SPClientTag=1&SPProductNumber=16.0.2016.1224&SenderId=E73DC26C0  

client_id: This is an app principle id with the formatting like this:

1:  i:0i.t|ms.sp.ext|48384dfb-52f9-4f04-9a53-dfad0028cbf2@dad0be63-dbfb-4bb2-ab26-ce292a09cff8  

But the problem here is the App only exist at the site where you add it, if you want to use this app again in sub site, you must add it again. In my scenario, I want to add an app only one time at root site collection, and can use it every where in this site collection, include sub site. How I do this?

Step 1: You need to know an app instance id via app product id. You can see the ProductId at the AppManifest.xml of App project, keep this id to javascript variable.

1. Declare some variables to store your context

1:  var clientContext = {};  
2:  var rootWeb = {};  
3:  var appInstance = {};  
4:  var yourAppProductId = '6d510a27-38f7-4d0f-9e67-dac426cc5a3d';

2. Load the root web in your site collection

1:  function loadRootWeb() {  
2:    clientContext = SP.ClientContext.get_current();  
3:    var site = clientContext.get_site();  
4:    clientContext.load(site);  
5:    rootWeb = site.get_rootWeb();  
6:    clientContext.load(rootWeb);  
7:    clientContext.executeQueryAsync(loadRootWebSuccessed, loadRootWebFailed);  
8:  };  

3. In the function “loadRootWebSuccessed”, get appPrincipalId by your AppProductId

1:  function loadRootWebSuccessed() {  
2:    appInstance = rootWeb.getAppInstancesByProductId('{' + yourAppProductId + '}');  
3:    clientContext.load(appInstance);  
4:    clientContext.executeQueryAsync(loadAppRedirect, errorHandler);  
5:  };  

4. Get appInstanceId and load appredirect.aspx with that instanceId

1:  function loadAppRedirect() {  
2:    var appEnumerator = appInstance.getEnumerator();  
3:    var hasNext = appEnumerator.moveNext();  
4:    if (hasNext) {  
5:      var oApp = appEnumerator.get_current();  
6:      var appInstanceId = oApp.get_id().toString();//     "6c2ca931-e661-4cff-bb71-66be022e1763"     String  
7:      var appPrincipalId = oApp.get_appPrincipalId().toString();//     "i:0i.t|ms.sp.ext|48384dfb-52f9-4f04-9a53-dfad0028cbf2@dad0be63-dbfb-4bb2-ab26-ce292a09cff8"     String  
8:      var hostWebUrl = _spPageContextInfo.siteAbsoluteUrl;  
9:      var iframeAppRedirectId = 'appRedirectFrame';  
10:      var iframeAppRedirectSrc = hostWebUrl + "/_layouts/15/appredirect.aspx?instance_id=" + appInstanceId;  
11:      $('body').append('

‘); 12: $(“#” + iframeAppRedirectId).load(iframeAppRedirectSrc + ” #frmRedirect”, function () { 13: getAppInformation(); 14: }); 15: } 16: }

Step 2: Get all needed information of an app in function getAppInformation, include SPAppToken

1:  function getAppInformation() {  
2:    var inputSPAppToken = $('#frmRedirect').find('input[name="SPAppToken"]')[0];  
3:    var formAction = inputSPAppToken.form.action;  
4:    var spAppToken = inputSPAppToken.value;  
5:    var spLanguage = getQuerystringParamValue(formAction, "SPLanguage");  
6:    var spHostUrl = _spPageContextInfo.webAbsoluteUrl;//getQuerystringParamValue(formAction, "SPHostUrl");  
7:    var spClientTag = getQuerystringParamValue(formAction, "SPClientTag");  
8:    var spProductNumber = getQuerystringParamValue(formAction, "SPProductNumber");  
9:    var appUrl = getAppUrlByAction(formAction);  
10:  }  

1:    function getQuerystringParamValue(url, paramName) {  
2:      paramName = paramName.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");  
3:      var regex = new RegExp("[\\?&]" + paramName + "=([^&#]*)"),  
4:        results = regex.exec(url);  
5:      return results == null ? "" : decodeURIComponent(results[1].replace(/\+/g, " "));  
6:    };  

1:    function getAppUrlByAction(url) {  
2:      return getRootUrl(url.split('?')[0]);  
3:    };  

That’s all!