none
How to import data from Other Organization External SharePoint site RRS feed

  • Question

  • Hi All,

    One of our client provide access to there SharePoint online site to me.

    I want to create a Dashboard using Power Query which will import/consolidate etc.. data from client's SharePoint location and show the live summary of the reports.

    This template will be used by both the organizations. Now the issue is that as soon as I am trying to import data from client SharePoint site, Power query automatically login to my organization and I am getting error like Access forbidden.

    For temporary solution I have sync client SharePoint folder with my local desktop folder but the issue is that I can only use that Template due to link to my local desktop sync file.

    Can anybody suggest me the better approach in this case ?

    Regards,

    Rajender


    Rajender

    Tuesday, February 4, 2020 7:01 PM

Answers

  • Hi Ehren,

    Thanks a lot for your valuable time. its just solved now.

    Client given us the folder level access.

    And below point I have noticed

    1) In first step I was using complete folder level path while selecting SharePoint folder.

    2) When next message box pop up where we have to select "Anonymous option"or Microsoft account login option " then their only main root path was showing that's why I was unable to connect.

    After your last post I just realized the same and manual change the path to folder level and all working fine :-).

    Thanks once again for your valuable time and support.

     


    Rajender

    Thursday, February 13, 2020 7:43 AM

All replies

  • Hi Rajender,

    Have you tried clearing your SharePoint credentials in Data Source Settings before you access the client's SharePoint site? It sounds like maybe you need to have specific credentials for the different sites/clients. Usually this would happen by default. Do all the various clients' SharePoint sites share a common root URL? If so, this could explain the behavior you're seeing.

    Ehren

    Thursday, February 6, 2020 7:27 PM
    Owner
  • Hi Ehren,

    I tried to delete the credentials from Data source setting as well. Also SharePoint root url is different.

    for clients side its like : https://XXXXXX.sharepoint.com/sites/external/Unknown/

    for ours : https://XXXXXX.sharepoint.com/sites/

    Still confused now how to solve this issue...…:-(

    temporarily solution which I tried to sync SharePoint folder with local desktop and then link that path with Power Query. But it I fix this approach then I have to ask every user(i.e 30-40 now) to manually sync the SharePoint folder to their local drive. and update the query which will pick dynamically the folder path.

    which is not a good approach I think because as soon as new users join than again manual we have to sync SharePoint folder to their desktop. 

    Looking forward for your expert suggestion & support. 


    Rajender

    Friday, February 7, 2020 12:40 PM
  • Hi Rajender,

    If both SharePoint sites start with https://XXXXXX.sharepoint.com/, then they do share the same root URL, and thus they may also be sharing credentials.

    Are the values represented by XXXXXX the same or different for the two cases listed above?

    Ehren

    Friday, February 7, 2020 5:48 PM
    Owner
  • Hi Ehren,

    Both XXXXXX are different. they do not share the same root URL.


    Rajender

    Monday, February 10, 2020 11:03 AM
  • Ok. What credentials do you have saved in Data Source Settings? Are there separate creds specified for each site?

    Ehren

    Monday, February 10, 2020 5:59 PM
    Owner
  • Hi Ehren,

    No credential saved for Client SharePoint site. I received only one email as soon as they provided access to my email ID. if I direct open client's external SharePoint site than it automatically open. its not asking for any password.

    Also I tested same link in one other users who don't have access to client site and in his case only message box pop up in internet browser that "user not authorized to access".

    And in our SharePoint case I tried to test by deleting the password even from browser history but not successful.


    Rajender

    Monday, February 10, 2020 6:58 PM
  • Hm. I'm not sure I understand the sequence of events you're performing when you hit this issue. Can you paste screenshots to clarify what you mean by the following (starting with the Get Data dialog)?

    as soon as I am trying to import data from client SharePoint site, Power query automatically login to my organization and I am getting error like Access forbidden.

    Ehren

    Monday, February 10, 2020 7:47 PM
    Owner
  • Hi Ehren,

    Below are snapshots for your easy reference. Please let me know if in case any other detail required :-


    Rajender

    Tuesday, February 11, 2020 3:34 AM
  • I don't understand the flow. Are you saying you get prompted for credentials for site A and choose Microsoft Account, and then you get re-prompted for credentials for site A and choose Anonymous, and then after that you get an access denied error?

    In any case, if you're picking Anonymous auth then it makes sense that you'd get an access denied error. Are you expecting something different?

    Ehren

    Tuesday, February 11, 2020 5:59 PM
    Owner
  • Hi Ehren,

    Sorry I was not able to clearly mention the issue.

    In first step I tried to access the client SharePoint using Anonymous option and as soon as I clicked ok below error message pop up. where as if I directly try to open the same client external sharepoint using internet explorer then its open.

    When above step fails then I tried to use and Microsoft Account option but in this case also I received same error message box.

     


    Rajender

    Wednesday, February 12, 2020 1:12 PM
  • Ok. Opening the site in IE is not the same as using Anonymous, since IE will do automatic authentication behind the scenes. You should be able to sign out in IE and then sign in again, which should allow you to see the account being used to connect to the site. This is the account (the Microsoft Account) you should be using to connect to the site in Power Query.

    Also, which SharePoint connector are you using? SharePoint Folder? SharePoint List? Something else?

    Ehren

    Wednesday, February 12, 2020 5:34 PM
    Owner
  • Hi Ehren,

    I am using SharePoint folder option. I tried to delete the saved password in browser as well but not successful.

    its giving me same error as posted in my post.


    Rajender

    Wednesday, February 12, 2020 6:30 PM
  • IE is giving you the same error ("Access to the resource is forbidden") when you sign out?

    Ehren

    Wednesday, February 12, 2020 6:39 PM
    Owner
  • when I directly open IE no error and I am able to login as soon as I enter my current organization email id.

    but when Power Query when I am selecting sign in under Microsoft account than this error message pop up.


    Rajender

    Wednesday, February 12, 2020 6:49 PM
  • Ok. As long as you're specifying the same Microsoft Account in PQ that you're using to log into the site in IE, it seems like the connection should work (unless there's something wrong with the URL you're providing). I've contacted the owners of the SharePoint Folder connector. Hopefully they'll be able to assist you.

    Ehren

    Wednesday, February 12, 2020 7:00 PM
    Owner
  • Hi Ehren,

    Thanks a lot for your valuable support. I will wait for your feedback. till the time being I will go further with manual sync of SharePoint folder to local desktop.


    Rajender

    Wednesday, February 12, 2020 7:35 PM
  • Hi Rajender,

    Here's one more thing to try in the meantime. It looks like you might only be able to pass in the root level site URL to the SharePoint Folder connector. Are you entering https://XXXXXX.sharepoint.com/sites/external/Unknown/ or https://XXXXXX.sharepoint.com/sites/ when connecting to the client's SharePoint site?

    Ehren

    Wednesday, February 12, 2020 7:42 PM
    Owner
  • Hi Ehren,

    Thanks a lot for your valuable time. its just solved now.

    Client given us the folder level access.

    And below point I have noticed

    1) In first step I was using complete folder level path while selecting SharePoint folder.

    2) When next message box pop up where we have to select "Anonymous option"or Microsoft account login option " then their only main root path was showing that's why I was unable to connect.

    After your last post I just realized the same and manual change the path to folder level and all working fine :-).

    Thanks once again for your valuable time and support.

     


    Rajender

    Thursday, February 13, 2020 7:43 AM