Microsoft SharePoint file gem

This gem runs in .

Overview

Use a Source or Target gem to read from or write to SharePoint in Prophecy pipelines. This page covers supported file formats, how to create the gem, and how to configure connection details and paths for both Source and Target gems.

Supported file formats

Format	Read	Write
CSV	✔	✔
Fixed width	✔
JSON	✔	✔
XLSX	✔	✔
XML	✔	✔

Add a SharePoint Source or Target gem

To add a SharePoint Source or Target gem to your pipeline:

Add a Source or Target gem to the pipeline

Open your pipeline in Studio.
Click Source/Target in the canvas.
Select Source or Target from the dropdown.
Click the gem to configure it.

Select SharePoint format

In the Type tab, select SharePoint. Click Next.

Set location details

In the Location tab, set your file format and connection details. To learn more, see Location below.

Set file properties

In the Properties tab, set file properties. These vary based on the file type that you are working with.

See the list of properties per file type, such as CSV.

Preview data (Source only)

In the Preview tab, load a sample of the data and verify that it looks correct.

Grant service principal access to SharePoint sites

The connection you select or create in the Location tab authenticates to SharePoint as a service principal (an Azure AD app registration). Before that connection can read from or write to a site, the service principal needs explicit access to it. Microsoft recommends the Sites.Selected permission over tenant-wide access, since it scopes the service principal to specific site collections only.

Adding Sites.Selected in Azure alone does not grant access to any site. You must also complete Step 2 and Step 3 below for each site the connection needs.

Prerequisites

An Azure AD app registration to use as the service principal.
SharePoint Administrator or Global Administrator role on the tenant.
Access to Microsoft Graph Explorer.

Add the Sites.Selected permission

In the Azure Portal, go to App Registrations and select your app.
Click API permissions in the left menu.
Click Add a permission > Microsoft Graph > Application permissions.
Search for and add Sites.Selected.
Click Grant admin consent for your tenant.

Permission	Type	Description
`Sites.Selected`	Application	Access selected site collections

Get the SharePoint site ID

Use Graph Explorer to look up the ID of the site you want to grant access to.

Go to Graph Explorer and sign in with a SharePoint Administrator or Global Administrator account.
Take the site’s normal URL (for example, https://prophecy.sharepoint.com/sites/testsite) and convert it to the Graph format: replace the first / after the hostname with a colon.
Set the method to GET and run:

GET https://graph.microsoft.com/v1.0/sites/{hostname}:/sites/{siteName}

For example:

GET https://graph.microsoft.com/v1.0/sites/prophecy.sharepoint.com:/sites/testsite

Copy the id value from the response. This is the site ID used in the next step.

The hostname and site path are joined with a colon, not a slash.

Wrong: prophecy.sharepoint.com/sites/testsite
Correct: prophecy.sharepoint.com:/sites/testsite

Grant access to the site

Use a POST request to assign the service principal a role on the specific site.

In Graph Explorer, set the method to POST and use the site ID from Step 2:

POST https://graph.microsoft.com/v1.0/sites/{site-id}/permissions

In the Request body tab, paste the following, replacing <app-client-id> and <app-client-id> with your service principal’s values:

{
  "roles": ["read", "write"],
  "grantedToIdentities": [
    {
      "application": {
        "id": "<app-client-id>",
        "displayName": "<app-client-id>"
      }
    }
  ]
}

Click Run query. A 201 Created response confirms the permission was granted.

Repeat this step for every additional site the connection needs access to.

Role value	Access level
`read`	Read-only
`write`	Read and write
`fullcontrol`	Full site control

Grant both read and write together, as shown above. This is the only combination Prophecy has tested; granting read alone for Source-only connections is untested and not currently recommended.

Sites.Selected grants access at the site level only. It does not support scoping access to individual folders or libraries within a site.

Troubleshooting

403 Forbidden when running the POST request

Confirm you’re signed in to Graph Explorer as a SharePoint Administrator or Global Administrator. In Graph Explorer, open Modify permissions and consent to Sites.FullControl.All, then re-run the request.

Invalid hostname error

Make sure that you have a colon : before /sites in the hostname. Correct: prophecy.sharepoint.com:/sites/testsite (uses colon before /sites/) Wrong: prophecy.sharepoint.com/sites/testsite (uses slash)

Permissions don’t seem to take effect**

Sites.Selected grants nothing by default; you need to grant access per site explicitly via Step 3.
Confirm Step 3 was repeated for each site the connection needs, not just the first one.
Permission grants can take a few minutes to propagate. If a connection test fails immediately after granting access, wait a few minutes and retry before troubleshooting further.

Check what’s already been granted Run a GET on the site’s permissions to see existing grants, including any stale or duplicate entries from a previous app registration:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/permissions

Source location

Parameter	Description
Select or create connection	Select an existing SharePoint connection or create a new one. See Grant service principal access to SharePoint sites if the connection needs new site permissions.
File Path	Path to the file in SharePoint. Example: `/sites/sitename/SharedDocuments/file.csv`
Examine	Automatically determine the file format, compression type (if any), properties, and schema of the file to read.
Format type	Type of file to read, such as `csv` or `json`.
Compression	The compression type of the file to read. Supported types: `uncompressed`,`gzip`, `zstd`, `lz4`, `zlib`, `snappy`, `lzop`

Target location

Parameter	Description
Select or create connection	Select an existing SharePoint connection or create a new one. See Grant service principal access to SharePoint sites if the connection needs new site permissions.
File Path	Path to the file in SharePoint. Example: `/sites/sitename/SharedDocuments/file.csv`
Examine	If an existing file exists at the target path, click Examine to automatically determine the file format and compression type (if any).
Encryption Algorithm	The encryption algorithm to use when writing the file. Supported algorithms: `AES-192`, `AES-296`, `BlowFish`
Format type	Type of file to write, such as `csv` or `json`.
Compression	The compression type to use when writing the file. Supported types: `uncompressed`,`gzip`, `zstd`, `lz4`, `zlib`, `snappy`, `lzop`

Configure encryption in a Target gem to encrypt an entire file. Use the DataEncoderDecoder gem to encrypt individual columns.

Overview

Sources and targets

Data processing

Reporting

Advanced

Overview

Supported file formats

Add a SharePoint Source or Target gem

Grant service principal access to SharePoint sites

Prerequisites

Troubleshooting

403 Forbidden when running the POST request

Invalid hostname error

Permissions don’t seem to take effect**

Source location

Target location

​Overview

​Supported file formats

​Add a SharePoint Source or Target gem

​Grant service principal access to SharePoint sites

​Prerequisites

​Troubleshooting

​403 Forbidden when running the POST request

​Invalid hostname error

​Permissions don’t seem to take effect**

​Source location

​Target location

Overview

Supported file formats

Add a SharePoint Source or Target gem

Grant service principal access to SharePoint sites

Prerequisites

Troubleshooting

403 Forbidden when running the POST request

Invalid hostname error

Permissions don’t seem to take effect**

Source location

Target location