Streamlit: connection to a SharePoint folder — Part 2

6 min readMay 8, 2023

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 second 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).

Read and display the content of a file in the folder

Starting from the previously developed script, inside the if statement (which checks if the access to SharePoint is successful), we need to add a part to get a file from the SharePoint folder. To do that, we need the Relative URL of the file that can be retrieved in this way

# Take the Relative URL of the file
Filename = "Test_File.xlsx"
path_to_the_file = dataframe[dataframe["File name"] == Filename]["Relative url"].iloc[0]

The script reads the column “File name” in the dataframe we defined in the previous article and looks for the file “Test_File.xlsx”. Then, it takes its Relative URL. This latter will be something like

/username_of_the_sharepoint/path_to_the_folder/Test_File.xlsx

At this point, we can use the object st.session_state[“ctx”] defined in the previous article to open the file and read its content. Then we can save this latter in a pandas dataframe and display it:

# Useful libraries
import io

# Read the file content
response_reports = File.open_binary(st.session_state["ctx"], path_to_the_file)
bytes_file_obj_reports = io.BytesIO()                    # Save data to BytesIO stream                            
bytes_file_obj_reports.write(response_reports.content)
bytes_file_obj_reports.seek(0)                           # Set file object to start
File_content = pd.read_excel(bytes_file_obj_reports)     # Save content in a pandas dataframe

# Display the dataframe
st.write("This is your file:")
st.table(File_content)

At this point, we have imported the date in the Streamlit session and we can manipulate it.

Save a dataframe as a file in the folder

Now, suppose we have manipulated the dataframe (or have created a new one). We want to save its data in a file and put this file in the SharePoint folder. For this example, I chose to manipulate the previous dataframe in this way:

# Manipulated dataframe (example)
File_content_new = File_content[(File_content["Field1"].isin(["A","B"])) & (File_content["Value"]>=5)]

Now, we want to save it in a file “File_content_new.xlsx”. To do that, we need to add this script:

# Save data into a file when the button is clicked
if st.button("Save new data") :
  # Create "writer" object
  output = BytesIO()
  writer = pd.ExcelWriter(output, engine = 'xlsxwriter')
  
  # Convert data into Excel and choose a sheet name
  File_content_new.to_excel(writer, index = False, sheet_name = 'New data')
  writer.save()
  output.seek(0)
  workbook = output.read()
  
  # Upload the file in folder, choosing a name
  target_folder.upload_file("File_content_new.xlsx", workbook).execute_query() 
  st.write("**File uploaded successfully!**")

What it does is the following: it creates a “writer” object, useful to create .xslx files, converts the dataframe File_content_new (defined above) into an Excel file, and finally uploads the new file in the SharePoint folder using the object st.session_state[“ctx”]. The script includes a button to perform this procedure: after you pressed the button, the app will appear like this:

If we check our SharePoint folder, we will notice the new file there!

At this point, we can use that file or we can import it inside the app. The whole Python script (using also the first one developed in the first article) is the following:

# Useful libraries
import io
import pandas as pd
import streamlit as st
from io import BytesIO
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 dataframe
    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)

    # Take the Relative URL of the file
    Filename = "Test_File.xlsx"
    path_to_the_file = dataframe[dataframe["File name"] == Filename]["Relative url"].iloc[0]

    # Read the file content
    response_reports = File.open_binary(st.session_state["ctx"], path_to_the_file)
    bytes_file_obj_reports = io.BytesIO()                   # Save data to BytesIO stream                            
    bytes_file_obj_reports.write(response_reports.content)
    bytes_file_obj_reports.seek(0)                          # Set file object to start
    File_content = pd.read_excel(bytes_file_obj_reports)    # Save content in a pandas dataframe

    # Display the dataframe
    st.write("This is your file:")
    st.table(File_content)

    # Manipulated dataframe (example)
    File_content_new = File_content[(File_content["Field1"].isin(["A","B"])) & (File_content["Value"]>=5)]
    
    # Save data into a file when the button is clicked
    if st.button("Save new data") :
      # Create "writer" object
      output = BytesIO()
      writer = pd.ExcelWriter(output, engine = 'xlsxwriter')
      # Convert data into Excel and choose a sheet name
      File_content_new.to_excel(writer, index = False, sheet_name = 'New data')
      writer.save()
      output.seek(0)
      workbook = output.read()
      # Upload the file in folder, choosing a name
      target_folder.upload_file("File_content_new.xlsx", workbook).execute_query() 
      st.write("**File uploaded successfully!**")
    
  # 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 update the app we published previously with the new code lines. In the next part, we will explore how to upload and modify a file in a SharePoint folder :D

Streamlit: connection to a SharePoint folder — Part 2

Written by Sam Campitiello