Connectors

Contact support

Microsoft Sharepoint - getting started in Peliqan

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.

Contents

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 onky (low-code Python scripts), no ETL pipelines
  • 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.
image

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.

image

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

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

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)