Menu

How to query a SharePoint List

Introduction

Lists can be maintained in SharePoint they can no longer be extracted and refreshed in Office 365 Excel because the native export relies on Microsoft Query which has been deprecated for Power Query. Alternatively, a List can be exported to a CSV file but it must then it must be manually opened and saved in Excel.

The Sharperlight System datamodel has a “Web Page Table, CSV, JSON, XML or ODATA” query object that can directly query a SharePoint List using the Microsoft Graph API. Graph provides a secure web service address (https://graph.microsoft.com/v1_0/) where the path to the SharePoint Site and the list can be uniquely identified using their respective internal ID’s. Identify the Site and List internal ID’s can be challenging because they are not visible within SharePoint but there is a Graph Explorer web site which can assist in the identification. This exercise requires technical skills because it requires running web service queries and analysing the returned JSON payloads. Once the ID’s are identified, the web service call that extracts the List records, is relatively easy to setup in the Query Builder. It will need an App registration in Microsoft Azure because the web service call to the Graph API needs to use a Shared Authentication record.

NoteThe App registration in Microsoft Azure is a mandatory requirement when using the Microsoft Graph API, and the registration will only work on a Sharperlight Service that is configured for https and internet facing. The Sharperlight Service has to communicate with Microsoft and receive an authorisation token; this can only be achieved through the internet, on a secured http connection.

Identifying the SharePoint Site and List ID’s

To query a SharePoint List using the Microsoft Graph API, you need to identify the Site ID of the relevant SharePoint Site or SubSite where the SharePoint List exists and the ID of the target List. These ID fields are not visible within SharePoint but the Graph Explorer can be used to extract the relevant ID’s. Open the Graph Explorer in a web browser and sign-in using an account in the same tenancy as the SharePoint List. The default Graph Explorer web page uses a Sample Tenant without a user signed in, left mouse click on the user login icon (circled in red below) and sign in using the Microsoft Online login.

Navigate to the SharePoint Sites folder and double left mouse click on the GET “my organization’s default SharePoint site”; this will copy the https://graph.microsoft.com/v1.0/sites/root command into the query bar and use the Run query button to execute the command. In the Response preview pane it will return a JSON payload contain the ID of the default SharePoint Site. The root site id is the comma delimited string displayed in quotes after the “id”: label. In the illustration below the root Site id would be contoso.sharepoint.com,1237fb41-6f1a-417b-b51b-97956a1fca7,7ad22344-52c1-431d-8bf2-187439e87077 with the delimiting commas included.

If the List was created in the root SharePoint Site, we could jump directly to running another GET that would extract all the Lists and their ID’s which exist in the root site. However, it is more likely that Lists are create in Subsites below the root site. The URL address of the SharePoint List will indicate where it exists. If the URL path to the List was https://contoso.sharepoint.com/TeamSite/Project/Lists/; this indicates the List exists in the Project Subsite which is a child site of the TeamSite Subsite which is a Subsite of the contoso.sharepoint.com SharePoint Site.

We cannot directly deduce the ID of the Project Subsite but the Graph Explorer has sample GET queries that can be used to interrogate the SharePoint content structure. In the same SharePoint Sites folder, select the GET “enumerate subsites of the root site”, this will copy the https://graph.microsoft.com/v1.0/sites/root/sites command into the query bar and the response JSON payload will contain the ID of the TeamSite, eg contoso.sharepoint.com,1237fb41-6f1a-417b-b51b-97956a1fca7,86e40134-55a2-4631-988b-c38656f65d01

To identify the ID of the Project Subsite we then modify the GET command from about and replace the root placeholder with the Subsite ID of the TeamSite. The new command would be https://graph.microsoft.com/v1.0/sites/contoso.sharepoint.com,1237fb41-6f1a-417b-b51b-97956a1fca7,86e40134-55a2-4631-988b-c38656f65d01/sites with the JSON payload response identify the Project ID as contoso.sharepoint.com,1237fb41-6f1a-417b-b51b-97956a1fca7,0bedc83d-00f4-4731-9727-5ec3cc3a368c

Now we have identified the Project ID we can navigate to the SharePoint Lists folder and use the GET “enumerate the lists in the root site” to create the command to extract the available lists. The default command will be https://graph.microsoft.com/v1.0/sites/root/lists but replace the root reference with the ID of the Project Subsite, making the command https://graph.microsoft.com/v1.0/sites/
contoso.sharepoint.com,1237fb41-6f1a-417b-b51b-97956a1fca7,0bedc83d-00f4-4731-9727-5ec3cc3a368c/lists. The JSON payload response will document all the lists that have been created in the Subsite, showing their unique ID and the List names. In our example we want to query the MITJournals list with the ID of e8304619-0575-4396-953a-fb329a91e537.

Shared Authentication

To query the Microsoft Graph API in the Query Builder we require a valid App registration with SharePoint Site read privileges. The Application (client) ID, Directory (tenant) ID and the Client Secret Value from the App registration to be added as a Microsoft Azure Graph authentication record in Sharperlight, using the Shared Authentication Names window. This can be accessed from within Site Setup, right mouse click on any username in the Users folder and the pop-up menu has an item for Shared Authentication Names.

The Application (client) ID and Directory (tenant) ID can be copied from the App registration Overview page.

The Authentication for the App registration must include a Web Redirect URI for the Sharperlight Web Service address suffixed with AzureAD. It will also require Mobile and Desktop application Redirect URI enabled for the oauth2/nativeclient and the oauth20_desktop.srf authentications whilst the URI should have an AzureLogin suffix. The Front-channel logout URL should have the same Sharperlight Web Service address with an AzureLogoff suffix.

The API Permissions for the Microsoft Graph API include both Delegated and Application Sites.Read.All privileges. Make sure the Status has been Granted (with a green tick box).

The Client Secret Value needs to be created in the Certificates & secrets section and make sure the Value is copied into a text file on creation. Navigating away from the Certificates & secrets folder will hash the Value with * asterisks.

Web Page… Query

To query the identified SharePoint List, open the Query Builder then select a Detailed Report with the System Product and the Web Page Table… query object. The URL Link will be the lists command used to identify the List ID, with the addition of the ID and an /items?expand=fields parameter. Using the Site address and the ID’s from the previous example it would like below.

https://graph.microsoft.com/v1.0/sites/contoso.sharepoint.com,1237fb41-6f1a-417b-b51b-97956a1fca7,0bedc83d-00f4-4731-9727-5ec3cc3a368c/lists/e8304619-0575-4396-953a-fb329a91e537/items?expand=fields

To provide the authentication credentials, select the Shared Authentication record in the Authentication Type, and add the word fields to the JSON Expand Fields filter. If you don’t want to cache the dataset on the Sharperlight application server, drag the Cache Date for (n) Minutes filter from the Options folder in the Selection pane and set the filter value to 0.01. Output the required fields that are prefixed with the fields:: label; these are the fields in the List.

The URL Link syntax for this query is going to be long and unwieldly. The full URL Link could be broken up into separate prompt only filters (pseudo filters) and then concatenated together in the URL Link using there names. For example, URL Link {@MicrosoftGraphURL}/{@SiteID}/lists/
{@ListID}/items?expand=fields might be easier to manage with {@MicrosoftGraphURL}, {@SiteID} and {@ListID} stored into different filters.

Related Posts

Privacy Preference Center

Consent Management

It is Sharperlight’s policy to respect your privacy regarding any information we may collect while operating our website. Accordingly, we have developed this privacy policy in order for you to understand how we collect, use, communicate, disclose and otherwise make use of personal information.


Sharperlight Terms of Service and Privacy Policy

1. Terms

By accessing the website at http://www.sharperlight.com/, you are agreeing to be bound by these terms of service, all applicable laws and regulations, and agree that you are responsible for compliance with any applicable local laws. If you do not agree with any of these terms, you are prohibited from using or accessing this site. The materials contained in this website are protected by applicable copyright and trademark law.

2. Use License



Permission is granted to temporarily download one copy of the materials (information or software) on Sharperlight's website for personal, non-commercial transitory viewing only. This is the grant of a license, not a transfer of title, and under this license you may not:


modify or copy the materials;
use the materials for any commercial purpose, or for any public display (commercial or non-commercial);
attempt to decompile or reverse engineer any software contained on Sharperlight's website;
remove any copyright or other proprietary notations from the materials; or
transfer the materials to another person or "mirror" the materials on any other server.


This license shall automatically terminate if you violate any of these restrictions and may be terminated by Sharperlight at any time. Upon terminating your viewing of these materials or upon the termination of this license, you must destroy any downloaded materials in your possession whether in electronic or printed format.


3. Disclaimer


The materials on Sharperlight's website are provided on an 'as is' basis. Sharperlight makes no warranties, expressed or implied, and hereby disclaims and negates all other warranties including, without limitation, implied warranties or conditions of merchantability, fitness for a particular purpose, or non-infringement of intellectual property or other violation of rights.
Further, Sharperlight does not warrant or make any representations concerning the accuracy, likely results, or reliability of the use of the materials on its website or otherwise relating to such materials or on any sites linked to this site.


4. Limitations

In no event shall Sharperlight or its suppliers be liable for any damages (including, without limitation, damages for loss of data or profit, or due to business interruption) arising out of the use or inability to use the materials on Sharperlight's website, even if Sharperlight or a Sharperlight authorized representative has been notified orally or in writing of the possibility of such damage. Because some jurisdictions do not allow limitations on implied warranties, or limitations of liability for consequential or incidental damages, these limitations may not apply to you.

5. Accuracy of materials

The materials appearing on Sharperlight's website could include technical, typographical, or photographic errors. Sharperlight does not warrant that any of the materials on its website are accurate, complete or current. Sharperlight may make changes to the materials contained on its website at any time without notice. However Sharperlight does not make any commitment to update the materials.

6. Links

Sharperlight has not reviewed all of the sites linked to its website and is not responsible for the contents of any such linked site. The inclusion of any link does not imply endorsement by Sharperlight of the site. Use of any such linked website is at the user's own risk.

7. Modifications

Sharperlight may revise these terms of service for its website at any time without notice. By using this website you are agreeing to be bound by the then current version of these terms of service.

8. Governing Law

These terms and conditions are governed by and construed in accordance with the laws of Australia and you irrevocably submit to the exclusive jurisdiction of the courts in that State or location.

Privacy Policy

Your privacy is important to us.

It is Sharperlight's policy to respect your privacy regarding any information we may collect while operating our website. Accordingly, we have developed this privacy policy in order for you to understand how we collect, use, communicate, disclose and otherwise make use of personal information. We have outlined our privacy policy below.


We will collect personal information by lawful and fair means and, where appropriate, with the knowledge or consent of the individual concerned.
Before or at the time of collecting personal information, we will identify the purposes for which information is being collected.
We will collect and use personal information solely for fulfilling those purposes specified by us and for other ancillary purposes, unless we obtain the consent of the individual concerned or as required by law.
Personal data should be relevant to the purposes for which it is to be used, and, to the extent necessary for those purposes, should be accurate, complete, and up-to-date.
We will protect personal information by using reasonable security safeguards against loss or theft, as well as unauthorized access, disclosure, copying, use or modification.
We will make readily available to customers information about our policies and practices relating to the management of personal information.
We will only retain personal information for as long as necessary for the fulfilment of those purposes.


We are committed to conducting our business in accordance with these principles in order to ensure that the confidentiality of personal information is protected and maintained. Sharperlight may change this privacy policy from time to time at Sharperlight's sole discretion.