Retrieve data from SharePoint Online without user interaction

I guess many of us have looked at the following requirement: Repeatedly read a portion of data from SharePoint Online and write it to some output stream e.g. a (json) file. In other words: you would need to come up with a simple script that can run repeatedly as a batch job and without user interaction (for example because it is triggered by a timer such as Windows Task Scheduler). Not difficult, right? Wrong! At least, if you don’t want to save another username and password in yet another config file. Especially not, if usernames and passwords are regularly updated by policy in your company.

Disclaimer

The ideas presented here are not new. After trying to solve this issue myself, I found at least one post from Wictor Wilen [http://www.wictorwilen.se/sharepoint-online-app-only-policy-powershell-tasks-with-acs] where he describes at least part of the solution I am presenting here. However, in a future “O365 Recipe” I will build on this one and extend the concept(s) presented here to show how this can be used in an Azure Function.

Creating a SharePoint App (Principal)

For starts, we need an App Principal. This is quite similar to an application registered in Azure AD. However, the difference with an ordinary application that you would have registered in Azure AD is that you cannot use it to connect to (Office 365) online without user interaction (also see the last paragraph of this O365 Recipe).

So let’s assume that you’d like to read all list items from a list titled “Tasks” and that this list can be found in a Site Collection with the following URL: “https://my-tenant.sharepoint.com/sites/my-site-collection”. To get an App Principal that would have the permissions to do so without any user interaction, do as follows:

  • Navigate to https://my-tenant.sharepoint.com/sites/my-site-collection/_layouts/15/appregnew.aspx.
  • Register a new SharePoint Addin and when doing so, let SharePoint generate both the Client Id and Client Secret. Since our App Principal won’t redirect users to a provider hosted backend, you can basically fill out what ever you would like for domain and redirect URL, but I suggest you use “localhost” as the domain and “https://localhost” as the redirect URL. However, you will see an error if you use your tenant’s domain e.g. your-tenant.sharepoint.com so better don’t even try.
  • Click “Create” and make sure to copy the app identifier information that is shown, as you won’t see this again. We need this for the next step.
  • Now navigate to https://my-tenant.sharepoint.com/sites/my-site-collection/_layouts/15/appinv.apsx.
  • Enter the “Client Id” from the app identifier information into the “App Id” field (is it just me, or is SharePoint really trying to make us scream here by using multiple names for the same ID) and click “Lookup”.
  • This will load the App that we just created but it now allows us to update the app’s manifest and define the permission that we require in the “Permission Request XML” as follows:
<AppPermissionRequests AllowAppOnlyPolicy="true">
  <AppPermissionRequest
    Scope="http://sharepoint/content/sitecollection/web"
    Right="Read"/>
</AppPermissionRequests>
  • Click “Create” and then “Trust it” to grant the app permission to access SharePoint when no user is connected (AllowAppOnlyPolicy) with a scope limited to read items in the current Site Collection.

Testing our SharePoint access without user interaction

To test our “offline access” to SharePoint online, we can now use the SharePointPnPPowerShellOnline Cmdlets, for example as follows:

Connect-PnPOnline -Url https://my-tenant.sharepoint.com/sites/my-site-collection -AppId your-client-id -AppSecret your-app-secret

Get-PnPList -List "Tasks"

Final thoughts

If you would try and repeat this using an “ordinary” Azure AD application, you may notice that even though you can successfully connect to (Office 365) online, you cannot use the connectionand access SharePoint online (even when you granted the application permissions to do so). Reason for this is that you would need to have created an application secret using a certificate (instead of a plain password). I will (hopefully) cover this in a future O365 Recipe. However, as rightfully pointed out by Wictor Wilen in his blog I referenced before, Azure AD applications would allow users to access any Site Collection in SharePoint Online and not just the one where you registered the app. In other words: The approach presented here would give you more fine-grained control over how permissions are granted for SharePoint.

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: