Creating a site specific SharePoint REST API to work with lists and list items without Azure or Microsoft Graph.

1. App Registration

Go to the site where you want to create API access, then add /_layouts/15/appregnew.aspx to the end so that your entire URL looks like this:

https://[tenant].sharepoint.com/sites/TestCommunication/_layouts/15/appregnew.aspx

Screenshot of the app registration sharepoint page

  1. Click the two generate buttons to generate a Client ID and Client Secret. Take note of them as we'll need it later on.
  2. Fill in the other details on the page.
  3. Click Create

2. App Permissions

Now we need to assign permissions for this app. In the same site URL, add /_layouts/15/appinv.aspx so that your entire URL looks like:

https://[tenant].sharepoint.com/sites/TestCommunication/_layouts/15/appinv.aspx

Screenshot of the app permisison request page

In the App ID text box, enter the Client ID generated from the previous step and click the Lookup button. Then, in the permission request XML text area, paste the following:

<AppPermissionRequests AllowAppOnlyPolicy="true">
  <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="FullControl" />
</AppPermissionRequests>

Click Create and a confirmation page will ask if you want to trust the app you just created. Click Trust It.

Screenshot of the app permission trust page

3. Obtaining the Tenant ID

In your browser, navigate to the following URL (remember to put your own tenant name):

https://login.microsoftonline.com/[tenant].onmicrosoft.com/.well-known/openid-configuration

You should get the JSON response back in the browser window. Take a look at the URL value for "token_endpoint" and the tenant_id is between .com and oauth2

Screenshot of the json response for getting tenant ID

We have just about everything we need to start constructing our HTTP requests. We'll be using the requests Python library to get an access_token and start the fun stuff.

4. Python 🐍

Access Token

We have to construct a POST request to get an access token to start working with the API. This includes all of the data we obtained in the previous steps.

client_id = 'xxxx-xxxxx-xxxxxx-xxxx'
client_secret = 'xxxxxxxxxxxxxxxxxx'
tenant =  'tenant' # e.g. https://tenant.sharepoint.com
tenant_id = 'xxxx-xxx-xxxxx-xxx-xxxxx'  
client_id = client_id + '@' + tenant_id

data = {
    'grant_type':'client_credentials',
    'resource': "00000003-0000-0ff1-ce00-000000000000/" + tenant + "[email protected]" + tenant_id, 
    'client_id': client_id,
    'client_secret': client_secret,
}

client_id is being overwritten. It is the client ID we got from the first step AND the tenant_id we got in the last step, joined by an '@'

With this data, we need to make a POST request to

https://accounts.accesscontrol.windows.net/tenant_id/tokens/OAuth/2

headers = {
    'Content-Type':'application/x-www-form-urlencoded'
}

url = "https://accounts.accesscontrol.windows.net/tenant_id/tokens/OAuth/2"
r = requests.post(url, data=data, headers=headers)
json_data = json.loads(r.text)

print(json_data)
{
'token_type': 'Bearer', 
'expires_in': '86399', 
'not_before': '1605580031', 
'expires_on': '1605666731', 
'resource': '00000003-0000-0ff1-ce00-000000000000/[email protected]_id',
'access_token': 'eyJ0eyJhdWQiOiIwMDAwMDAw......'
}

The json_data line loads the JSON text response as a dictionary where we can grab the access token value by it's key json_data['access_token']. The access_token gets added in all subsequent API calls. The response also gives you some expiration data at which time you'll need to re-authenticate. The white-space after Bearer is important!

headers = {
    'Authorization': "Bearer " + json_data['access_token'],
    'Accept':'application/json;odata=verbose',
    'Content-Type': 'application/json;odata=verbose'
}

Get List Items

We're finally ready to make a GET request to the SharePoint list endpoint URL which will return all items in a list. In this site there is a List called Customers with a few items:

Screenshot of the SharePoint Site Customers List

With a list named 'Customers' the endpoint to get the list items looks like this:

url = "https://tenant.sharepoint.com/sites/TestCommunication/_api/web/lists/getbytitle('Customers')/items"
l = requests.get(url, headers=headers)

Nestled in the JSON response is an array of JSON objects which contains all of the List item data, including meta data from SharePoint.

print(l.text)
{...
    [
        {   
            ...    
            "FileSystemObjectType": 0,
            "Id": 3,
            "ServerRedirectedEmbedUri": null,
            "ServerRedirectedEmbedUrl": "",
            "ContentTypeId": "0x01006F79CB39F79A2E4AA6B7C3B0B782C524",
            "Title": "UI",
            "ComplianceAssetId": null,
            "Name": "John Doe",
            "Message": "Please call later",
            "ID": 3,
            "Modified": "2020-11-17T01:52:52Z",
            "Created": "2020-11-16T18:38:45Z",
            "AuthorId": 7,
            "EditorId": 7,
            "OData__UIVersionString": "1.0",
            "Attachments": false,
            "GUID": "d9c-xxxxx-cx-x-c"
            ...
        },
    ]
...}

Add List Item

To add data to the list, POST to the same URL and include the necessary data:

# If your list name is Products then your value
# will be "SP.Data.ProductsListItem"
data = '''{ "__metadata": {"type": "SP.Data.CustomersListItem"},
    "Title": "PythonAPI", 
    "Name": "Mr Snake", 
    "Message": "Hello from Python"
}'''

url = "https://tenant.sharepoint.com/sites/TestCommunication/_api/web/lists/getbytitle('Customers')/items"
p = requests.post(url, headers=headers, data=data)

> print(p)
> <Response [201]>

Other Ideas

  • Need to post simple data to a SharePoint list? Use a PowerAutomate Webhook response instead! Once you create the flow and define JSON schema it is expecting, Microsoft will give you a URL.


Comments

comments powered by Disqus