SharePoint resource listing with Python

Problem

You (a publisher) want to let your colleagues obtain resource identifiers within various SharePoint Sites owned by your organization for use in programmatically modifying data within SharePoint.

Solution

You are able to create a simple Streamlit application that takes in a SharePoint Site URL and queries its Lists endpoint using the logged-in user’s SharePoint access token retrieved by Posit Connect. If the access token is not found then the app checks if it is running locally or on Connect and then prompts the user to either log into the OAuth integration or supply the token via the SHAREPOINT_TOKEN environment variable, depending on the detected environment. In the event that the Lists request is not successful an error is displayed.

requirements.txt
streamlit
posit-sdk==0.5.0
app.py
# -*- coding: utf-8 -*-
# mypy: ignore-errors

import os
import requests
import streamlit as st
from posit import connect

# initialize the Connect python sdk client if running on Connect
if os.getenv("RSTUDIO_PRODUCT") == "CONNECT":
    client = connect.Client()

    # grab the user session token from headers
    user_session_token = st.context.headers.get("Posit-Connect-User-Session-Token")

    try:
        # fetch the access token using the session token
        access_token = client.oauth.get_credentials(user_session_token).get(
            "access_token"
        )
    # if the refresh token has expired prompt the user to login again
    except connect.errors.ClientError as e:
        if e.error_code == 219:
            st.write(
                "Your SharePoint refresh token has expired. Please log out and then log back into the integration."
            )
else:
    # grab access token from env var if running locally
    access_token = os.getenv("SHAREPOINT_TOKEN")

try:
    # check for the existence of the access_token to prevent failures later
    if not access_token:
        print("Checking for access token.")

    # only prompt the user for a site URL if you have an access token
    if access_token:

        # no trailing slash and schema required
        site_url = st.text_input(
            "Please input your SharePoint site URL to query Lists: ",
            placeholder="https://my-site.sharepoint.com or https://my-site.sharepoint.com/sites/example-site",
        )

        # only query if a site_url is present
        if site_url:

            # set authorization and accept headers
            headers = {
                "Authorization": f"Bearer {access_token}",
                "Accept": "application/json;odata=verbose",
            }

            # form the URL and make the request
            url = f"{site_url}/_api/web/lists"
            response = requests.get(url, headers=headers)

            # check that the request was successful and parse out lists title and ID if so
            if response.status_code == 200:
                resp_json = response.json()
                lists = resp_json["d"]["results"]
                for list in lists:
                    title = list["Title"]
                    id = list["Id"]
                    st.write(f"Title: {title}, ID: {id}")

            # output error on failure
            else:
                st.write("Lists query failed: ", response.status_code, response.text)

except NameError:
    st.write("Unable to obtain access token.")
    # depending on environment prompt the user to log in or pass the environment variable
    if os.getenv("RSTUDIO_PRODUCT") == "CONNECT":
        st.write("Are you logged into the OAuth integration?")
    else:
        st.write("Is the SHAREPOINT_TOKEN env var set?")

Running the app locally

Terminal
export CONNECT_SERVER=<connect-server>
export CONNECT_API_KEY=<connect-api-key>
SHAREPOINT_TOKEN=<sharepoint-token> streamlit run app.py