Streamlit: connection to a SharePoint folder — Part 1

Very recently, I had the opportunity to explore some features and capacities of Python, in particular with the library Streamlit for creating apps. Moreover, I also took a look at how to connect an app with a SharePoint folder and interact with its content.

This article is the first of a short series that I would like to share, where I will cover the following topics:

• Connect to a SharePoint folder and read its content (Part 1)
• Read and save a file from a SharePoint folder (Part 2)
• Upload and modify a file in a SharePoint folder (Part 3)

Streamlit: Brief introduction

Streamlit is an open-source Python library that can be used to develop and publish interactive apps for a very large number of use cases. It is very simple to use and, if you have a basic knowledge of Python, then you will find it a very useful tool. I used it for building from very simple apps (for some of my daily routines like saving useful links or organizing my calisthenics training) to more advanced ones, to practice and explore some of its capabilities.

One of those advanced apps was the implementation of a pipeline to clean and use an imported dataset with a Machine Learning model to make predictions in a guided process (this may be a future article).

This small intro is just to give a very broad idea of how to use this fantastic library. Now, let’s jump into the main topic of the article. Note: some topics, Python libraries, and Streamlit modules will be taken for granted and not discussed further (please refer to the official documentation for details).

Introduction: connection to a SharePoint folder

The app that we want to build has to connect to a SharePoint folder and read its content. To do that, we need two pieces of preliminary information:

SharePoint URL

like → https:/name_here.sharepoint.com/username_of_the_sharepoint

and

Folder URL

like → /username_of_the_sharepoint/path_to_the_folder/

In some SharePoint links, there might also be the string /personal/ before the username_of_the_sharepoint part of the URL. However, if we have access to a SharePoint folder, it will be very easy to build these two connection strings. Now, let’s write a script!

SharePoint access configuration

We will build an app with two sections: the first one will be the SharePoint authentication section, where it is possible to enter e-mail and password, and the second section will display the folder content.

1. First section: credentials and connection

In this first section, we define a placeholder (initialized to empty) which is a sort of container that can be filled or emptied. This will contain this section at first, and then it will be emptied to leave space for the second section (i.e., the table showing the folder content). Moreover, in order to center the text of this section, we will create three columns and use the middle one to print the elements.

Next, we create two text_input objects to get and store e-mail and password that will be used for the authentication. For the password, we add the property type=”password” to replace characters with *.

Finally, a button to connect. For this latter, if some data interaction (through buttons, slicers, checkboxes) will be part of your app, it is necessary to save its status (True if clicked, False otherwise) in the session: this is crucial because an app built with Streamlit will refresh its content (from the top of the script) at each interaction. If the status is not saved, at each interaction you must enter your e-mail and password again.

# Useful libraries
import streamlit as st

# First section: e-mail and password as input
placeholder = st.empty()
with placeholder.container():
  col1, col2, col3 = st.columns(3)
  with col2:
    st.markdown("## **SharePoint connection with Streamlit**")
    st.markdown("--------------")
    email_user = st.text_input("Your e-mail")
    password_user = st.text_input("Your password", type="password")

    # Save the button status
    Button = st.button("Connect")
    if st.session_state.get('button') != True:
      st.session_state['button'] = Button

Now, we have to configure the connection to the SharePoint folder. First of all, we need to check the status of the button defined above: only if True, the authentication process will take place. After that, the connection to SharePoint can be done by using the library office365 in a custom function: this latter will read the e-mail and password provided before, and the SharePoint URL. It will return an object that can be used in the Python script to retrieve data from the folder (and for other operations). As for the button above, also this object must be saved in the Streamlit session to avoid the custom function being called again at each interaction: each time we need to use it, it will be already there for us!

# Useful libraries
from office365.sharepoint.files.file import File
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.authentication_context import AuthenticationContext

# Authentication and connection to SharePoint
def authentication(email_user, password_user, sharepoint_url) :
  auth = AuthenticationContext(sharepoint_url) 
  auth.acquire_token_for_user(email_user, password_user)
  ctx = ClientContext(sharepoint_url, auth)
  web = ctx.web
  ctx.load(web)
  ctx.execute_query()
  return ctx

# Second section: display results
# Check if the button "Connect" has been clicked
if st.session_state['button'] :                              
  placeholder.empty()
  if "ctx" not in st.session_state :
      st.session_state["ctx"] = authentication(email_user,   
                                               password_user, 
                                               sharepoint_url)
  
  st.write("Authentication: successfull!")
  st.write("Connected to SharePoint: **{}**".format( st.session_state["ctx"].web.properties['Title']))

Notice the line where the placeholder is emptied: this will make the authentication section disappear. If the e-mail and password are correct, then you will be able to see this:

Of course, in the place of “Samuele Campitiello”, you will see the name of your SharePoint.

2. Second section: read and show files in a folder

And finally, the last section (under the previous if statement). Here we want to display the files contained in the folder. For this purpose, we need to use the st.session_state[“ctx”] object created before: the object will take a look at the Folder URL provided and load all the items in it; these latter will contain the information for each file.

Once retrieved, this information can be saved in a pandas dataframe. In the example below, for each file, we will save “Name”, “TimeLastModified”, “ServerRelativeUrl” (this is the URL related to each file that can be used for further operations — see Part 2).

# Useful libraries
import pandas as pd

# Connection to the SharePoint folder
target_folder = st.session_state["ctx"].web.get_folder_by_server_relative_url(folder_in_sharepoint)

# Read and load items
items = target_folder.files
st.session_state["ctx"].load(items)
st.session_state["ctx"].execute_query()

# Save some information for each file using item.properties
names, last_mod, relative_url = [], [], []
for item in items:
    names.append( item.properties["Name"] )
    last_mod.append( item.properties["TimeLastModified"] )
    relative_url.append( item.properties["ServerRelativeUrl"] )
 
# Create and display the final data frame
Index = ["File name", "Last modified", "Relative url"]
dataframe = pd.DataFrame([names, last_mod, relative_url], index = Index).T
st.write("")
st.write("")
st.write("These are the files in the folder:")
st.table(dataframe)

The final output of our script will be something like this:

One final comment: in the first section, if we enter the wrong e-mail and password, an error will raise. This eventuality can be managed with a try-except configuration.

Below is the final updated script:

# Useful libraries
import pandas as pd
import streamlit as st
from office365.sharepoint.files.file import File
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.authentication_context import AuthenticationContext

# Page setup
st.set_page_config(layout = 'wide')

# SharePoint and Folder urls
sharepoint_url = your_sharepoint_url
folder_in_sharepoint = your_sharepoint_folder_url

# First section: e-mail and password as input
placeholder = st.empty()
with placeholder.container():
  col1, col2, col3 = st.columns(3)
  with col2:
    st.markdown("## **SharePoint connection with Streamlit**")
    st.markdown("--------------")
    email_user = st.text_input("Your e-mail")
    password_user = st.text_input("Your password", type="password")

    # Save the button status
    Button = st.button("Connect")
    if st.session_state.get('button') != True:
      st.session_state['button'] = Button

# Authentication and connection to SharePoint
def authentication(email_user, password_user, sharepoint_url) :
  auth = AuthenticationContext(sharepoint_url) 
  auth.acquire_token_for_user(email_user, password_user)
  ctx = ClientContext(sharepoint_url, auth)
  web = ctx.web
  ctx.load(web)
  ctx.execute_query()
  return ctx

# Second section: display results
# Check if the button "Connect" has been clicked
if st.session_state['button'] :  
  try :                            
    placeholder.empty()
    if "ctx" not in st.session_state :
        st.session_state["ctx"] = authentication(email_user, 
                                                 password_user,
                                                 sharepoint_url)
    
    st.write("Authentication: successfull!")
    st.write("Connected to SharePoint: **{}**".format( st.session_state["ctx"].web.properties['Title']))
  
    # Connection to the SharePoint folder
    target_folder = st.session_state["ctx"].web.get_folder_by_server_relative_url(folder_in_sharepoint)
    
    # Read and load items
    items = target_folder.files
    st.session_state["ctx"].load(items)
    st.session_state["ctx"].execute_query()
    
    # Save some information for each file using item.properties
    names, last_mod, relative_url = [], [], []
    for item in items:
        names.append( item.properties["Name"] )
        last_mod.append( item.properties["TimeLastModified"] )
        relative_url.append( item.properties["ServerRelativeUrl"] )
     
    # Create and display the final data frame
    Index = ["File name", "Last modified", "Relative url"]
    dataframe = pd.DataFrame([names, last_mod, relative_url], index = Index).T
    st.write("")
    st.write("")
    st.write("These are the files in the folder:")
    st.table(dataframe)
  
  # Handle the error in the authentication section
  except :
    col1, col2, col3 = st.columns(3)
    with col2:
      st.write("**Authentication error: reload the page**")

At this point, we can publish the app (we must have a Github repository to do that — please refer to the Streamlit documentation). In the next part, we will explore how to read and save a file from a SharePoint folder :D