Microsoft Sharepoint - Getting started in Peliqan

Microsoft SharePoint is a web-based collaborative platform that integrates natively with Microsoft 365. SharePoint is primarily sold as a document management and storage system, although it is also used for sharing information through an intranet, implementing internal applications, and for implementing business processes. This article provides an overview to get started with the Sharepoint connector in Peliqan. Please contact support if you have any additional questions or remarks.

Connect

In peliqan, go to Connections, click on Add new. Find Sharepoint in the list and select it. Click on the Connect button. This will open Sharepoint and allow you to authorize access for Peliqan. Once that is done, you will return to Peliqan. You can choose between following connectors for Sharepoint:

Sharepoint Site: to work with Sharepoint and lists in low-code Python scripts
Sharepoint List: to work with one specific list on Sharepoint, including an ETL pipeline
Sharepoint Excel 365 Writeback: to work with Excel on Sharepoint, writeback only (low-code Python scripts), no ETL pipelines. Click here for more info.
Sharepoint Excel 365 External Tenant: same as above but to connect to an external tenant, a tenant that you do not own but that was shared with you (using the “Grant access” setting under Sharing in Excel) or when you are a guest user to that Sharepoint site.

How to find your Site id (Sharepoint)

Using Sharepoint admin Go to: https://{tenant}-admin.sharepoint.com Replace {tenant} with your Sharepoint subdomain. Navigate to: Sites > Active Sites > Select your Site. The Site ID is in the URL on the right hand side. Using an _api URL Open this URL in your browser:

For the main (default) site: https://{tenant}.sharepoint.com/_api/site/id
For other sites: https://{tenant}.sharepoint.com/sites/{sitename}/_api/site/id

Make sure to remove spaces from the site name when inserting {sitename}. The Site ID is in the XML response, see the guid at the end of the XML.

Document Library Name (for Excel on Sharepoint)

In your Sharepoint site, go to “Site contents” to see the names of your Document Libraries. The default is “Documents”. Sharepoint Document Library Names page

Relative File Path (for Excel on Sharepoint)

For example “myfile.xlsx” for a file in the root folder. Or “myfolder/myfile.xlsx” for a file in a subfolder.

Explore & Combine

Wait a few minutes for the data to start syncing. Now you can view your Sharepoint data in tables in Peliqan’s built-in data warehouse (or in your own DWH if you connected e.g. SQL Server, Snowflake, Redshift or BigQuery), for connectors that have an ETL pipeline (e.g. “Sharepoint List”). Select “Explore” in the left navigation pane, expand “Data warehouse” in the left tree and click on Sharepoint. All tables from Sharepoint will now be shown. Sharepoint data in Peliqan grid view

You can explore the data in the gridview, and you can write SQL queries to transform the data and to combine the data from Sharepoint with data from other sources.

Activate

You can use Peliqan’s low-code Python scripts to interact with Sharepoint using the Sharepoint API. With a single line of code, you can for example add an item to a list on Sharepoint. In Peliqan, click on “Build” in the left navigation pane. Now add a new “App” or Python script. In the right pane, expand the “Connected SaaS APIs”, and expand Sharepoint. You will now see the available functions to interact with Sharepoint. Click on a function to insert it into your script.

Reading items from a list

Example reading all items from a list on Sharepoint, using the connector “Sharepoint List”:

sharepoint_list_api = pq.connect('Sharepoint List')
result = sharepoint_list_api.list('list_items')
items = result["detail"]
st.json(items)                               # Show list of items (contains ids only)

for item in items:
	item_details = sharepoint_list_api.get('list_item', { 'item_id': item["id"] })
	st.write(item_details)                     # Show item
	st.write(item_details["fields"]["Title"])  # Get title from item

Update a list item

Example updating an item in a list on Sharepoint, using the connector “Sharepoint List”:

import datetime, pytz

updated_item = { 
    'item_id': 1,
    'payload': {
        'Status': "OK",   # keys must match column names in the list on Sharepoint
        'Selected': True,
        'LastUpdate': datetime.datetime.now(pytz.timezone('Europe/Brussels'))
    }
}

result = sharepoint_list_api.update('list_item', updated_item)
st.write(result)

Upload a file to Sharepoint

Example uploading a file to Sharepoint, using the connector “Sharepoint Site”:

import base64
import requests

drive_id = "b!A9cWN8zgB0G1_ybchVg5W-tiz8Hr8ThFuRt4uBixzuc4eLKshz_cT7ngdcjONZaa"

sharepoint_site_api = pq.connect('SharePoint Site')

def download_file():
    url = "https://somedomain.com/somefile.pdf"
    response = requests.get(url)
    response.raise_for_status()  # raise an error if the download failed
    return response.content

def sharepoint_upload_file(file_bytes):
    file_content_base64 = base64.b64encode(file_bytes).decode("ascii")
    drive_item = {
        "path": "my_file.pdf",
        "drive_id": drive_id,
        "base64_content": file_content_base64
    }
    result = sharepoint_site_api.add('drive_item', drive_item)
    st.json(result)

def sharepoint_upload_text_file():
    text_content = "My test file.\nThanks for reading!"    
    base64_content = base64.b64encode(text_content.encode("utf-8")).decode("ascii")
    
    drive_item = {
        "path": "my_file.txt",
        "drive_id": drive_id,
        "base64_content": base64_content
    }
    result = sharepoint_site_api.add('drive_item', drive_item)
    st.json(result)
    
sharepoint_upload_text_file()

file_bytes = download_file()
sharepoint_upload_file(file_bytes)

Excel on Sharepoint

You can also interact with Excel files on Sharepoint, see: Microsoft Excel 365 - Getting started in Peliqan

​Connect

​How to find your Site id (Sharepoint)

​Document Library Name (for Excel on Sharepoint)

​Relative File Path (for Excel on Sharepoint)

​Explore & Combine

​Activate

​Reading items from a list

​Update a list item

​Upload a file to Sharepoint

​Excel on Sharepoint