SharePoint Online

Plugin information

How to set up

Note

This plugin is intended to be used with SharePoint Online only. For the other SharePoint Server editions (2013, 2016, 2019), please refer to the unsupported plugin which can be found here.

Option 1: using your SharePoint login

Limitations

This authentication method cannot be used if your account is set up with MFA or if it belongs to a federated space name.

You can find out whether or not this is the case by going to this URL (after editing it with your SharePoint account email address):  https://login.microsoftonline.com/[email protected]. The key NameSpaceType should read Managed. If it is Federated, you will need to use “Azure Single Sign On” or “Site App Token” instead.

Set up

1. Find the tenant and site name of the SharePoint you want to sync with DSS.

   

2. In DSS, go to Plugins > Installed > SharePoint > Settings > SharePoint login. There, fill in the details of the SharePoint instance you are trying to sync with DSS.

   

    

Say a typical URL for the files you want to give access to is https://dataiku.sharepoint.com/sites/rnd/plugins/Shared%20Documents/safe/list.xlsx

• Tenant is the sub domain prior to sharepoint.com, here dataiku
• Site path is the path to the SharePoint site or sub-site you want to give access to. In this example it would be sites/rnd/plugins
• Root directory is the path to the highest level directory you want your DSS users to have access to. In the current example, Shared Documents/safe will let the user browse any files and folders in the sub-directory safe. Default value for Root directory is Shared Documents, but it can also be left blank, in which case the user can access all the document libraries of the rnd/plugins site.

Option 2 : using Azure Single Sign On

1. From the Azure Portal, go to Azure Active Directory > App registrations create a new App (New registration). Set a name and a redirect URI pointing back to your DSS instance. It should follow this structure: https://<<your DSS instance domain>>:<<your DSS instance port>>/dip/api/oauth2-callback. Unless your DSS instance is on a localhost, the URI has to point to a https secured server.

   

2. Click on the newly created app. Copy the Application ID. Then go to the Certificates & secrets > New client secret tab. Set a description, choose an expire date, and copy the value of the created secret.

   

3. Then navigate to the API permissions tab and add the following permissions: User.Read, AllSites.manage, AllSites.Read, AllSites.Write, MyFiles.Read and MyFiles.Write.

   

4. In DSS, go to Plugins > Installed > SharePoint > Settings > Azure Single Sign On. There, set paste the App ID and App secret copied from step 1. The authorization endpoint should be https://login.microsoftonline.com/common/oauth2/authorize?resource=https://<<Your Tenant>>.sharepoint.com (make sure to set the appropriate tenant)

   

5. Finally, each DSS user who needs access to SharePoint Online will have to go to Profile & settings > Credentials from their account. Click the edit button for the SharePoint preset you are configuring. This will redirect you to a Microsoft Single Sign On page. Log in to the account if necessary click Yes

   

How to use

• In your DSS project flow, select Dataset > SharePoint
• Click Shared Documents or Lists, according to the data source you are trying to sync DSS to.
• Pick the authentication type and the preset, and browse to the document or folder you want to use as dataset.

  

• For Azure Single Sign On presets, the DSS users can access other SharePoint sites they have access rights to. For this, select Show advanced parameters and set the site path and/or root path in Site path preset overwrite / Root directory preset overwrite
• If the source is a list,
  • you will have know its name beforehand.
  • although all the column names will be visible, only these selected in the SharePoint’s list standard view will be populated with data.

If necessary, another list view can be selected:

1. First, find the name of the view for which the required columns are visible

   

2. In your DSS list dataset, tick Show advanced parameters, and write the name of the view in the View name section

   

Export data back to SharePoint Online

As a document

1. On your SharePoint Online, create a destination directory
2. On your DSS project flow, create a SharePoint folder by pressing Dataset > Folder and then select the SharePoint Server Shared Documents plugin as the Store into parameter
3. You will get a red error box at this stage. To resolve the error, go to the Settings tab and set the correct type of authentication and connection. Once this is done, type “/” in the path window and press Browse. Navigate to the destination directory created on step 1 and press OK. It is important that the destination directory is created for the sole use of your DSS project. Being a managed folder, its content could be deleted. For instance, with the parameters as shown below, removing the folder from the flow with the drop data option selected will result in the actual “dss” SharePoint directory being deleted as well.

   

4. In your DSS flow, pick the dataset you want to export back to SharePoint, and select the Export to folder recipe. In the Export Recipe window, click on Use existing and pick the folder you created in steps 2-3. You can change the file format to you liking.
5. Save and Run, and your dataset will be exported to the SharePoint directory.

As a list (full list)

Limitations: DSS datasets can also be exported to SharePoint Online as lists. However, this operation will overwrite any existing list bearing the same name. Also, some types (such as lookup or calculated fields) do not exist in DSS and would therefore be replaced by strings. For these reasons, it is strongly recommended to export lists from DSS to SharePoint only on specific lists created with the intent of being updated by DSS. Keep in mind that any user modification on the SharePoint side will be overwritten at the next sync.

1. Create your destination list by creating a SharePoint Online list dataset ( +Dataset > SharePoint Online > Lists ). Select the appropriate authentication and list title. Check that this title is not already in use on SharePoint to avoid any deletion. Project variables in the title, such as DSS_${projectKey}_ can help reduce conflicts in the name space.
2. Name the DSS dataset and click Create. An error should show because the dataset does not exist on the SharePoint side.
3. In you flow, create a sync recipe from the dataset you want to export. In the Output dataset section, use Existing dataset and select the SharePoint dataset named at step 2.
4. Click Run to start the export.

As a list (incremental)

A Dataiku dataset can be appended to an existing list, using the Append to list recipe.

1. In your project’s flow, click on the dataset to append to the SharePoint Online list
2. Once the dataset highlighted, click on Plugin recipes > SharePoint Online > Append to list at the bottom of the right panel
3. Set an output dataset. This dataset will contain a copy of the line that are appended to the SharePoint Online list. This is not the actual SharePoint list, so it can be stored into anything convenient.
4. Select an active authentication preset and type the name of the existing SharePoint Online list to append to.
5. Press run, and the content of the dataset will up appended to the list

Note that :

• Upload speed can be increased by using multiple workers. However, this will result in the lines being appended in random order.
• To trigger the upload from a scenario, use a step to build the recipe’s output dataset.