Sync spreadsheets data from SharePoint, as an application and with no user sign-in

The tap reads files with the Microsoft Graph API, and authenticates with the OAuth 2.0 client credentials flow - use this specialisation for a scheduled sync

Setup

In the Microsoft Entra admin center or Microsoft Entra ID section of the Azure portal:

1. Create an app registration under App registrations > New registration, with the Accounts in this organizational directory only account type and no redirect URI
2. Add a client secret under Certificates & secrets - copy the Value column, because the portal masks the value after you leave the page, and record the expiry date, because the tap fails when the secret expires
3. Add the Sites.Read.All Application permission for Microsoft Graph under API permissions, then select Grant admin consent - an application permission has no user consent, so a tenant administrator must grant the consent, and the permission must show the Granted state, or Microsoft Graph responds with 401 Unauthorized and no explicit notice that the consent is absent

In SharePoint:

1. Identify the site you want to sync files from
2. Identify the directory structure where the files you want to sync exist

In Meltano Cloud:

1. Add the app registration configuration for Client ID, Client Secret and Tenant ID
2. Add Tables configuration, with respect to the identified site and directory structure

Restrict the access to named sites

The Sites.Read.All application permission lets the application read every site of the tenant. To give the application access to named sites only, use the Sites.Selected application permission in place of it. This permission grants no access on consent - a SharePoint administrator grants the application access to each site, with the Microsoft Graph API:

1. In API permissions, remove Sites.Read.All, add the Sites.Selected Application permission for Microsoft Graph, then select Grant admin consent

   Go to Enterprise applications also, select the application, and revoke Sites.Read.All under Security > Permissions. The application keeps a granted permission until you revoke it there. A change of permissions can take some minutes to apply, and an access token keeps the permissions that it had at issue time, for the full lifetime of the token.

2. Open Graph Explorer, sign in as a tenant administrator, and consent to Sites.FullControl.All on the Modify permissions tab - Graph Explorer makes the requests below as itself, so it needs this permission

   Do not give Sites.FullControl.All to the app registration of the tap, even for a short time. This permission gives write access to every site of the tenant.

3. Get the ID of the site, where <host> is the SharePoint host of the tenant (such as <tenant>.sharepoint.com) and <site_name> is the last part of the URL of the site

   GET https://graph.microsoft.com/v1.0/sites/<host>:/sites/<site_name>
   

4. Grant the read role on that site to the application, where id is the application (client) ID of the app registration and displayName is a label only

   POST https://graph.microsoft.com/v1.0/sites/<site_id>/permissions
   
   {
       "roles": ["read"],
       "grantedToIdentities": [
           {
               "application": {
                   "id": "<client_id>",
                   "displayName": "<application_name>"
               }
           }
       ]
   }
   

5. Repeat the last two steps for each site

To remove the access to a site, get the ID of the site, then list the permissions of that site with GET https://graph.microsoft.com/v1.0/sites/<site_id>/permissions. Find the item where grantedToIdentitiesV2 names the application, and delete that permission with DELETE https://graph.microsoft.com/v1.0/sites/<site_id>/permissions/<permission_id>.

Settings

Tables

A list of table definition objects

Table definition

• name: string (required)

  The name to assign to the stream

• path: string (required)

  A path to a directory containing files in the format sharepoint://<site_name>/path/to/dir

• format: string (required)

  The format of files to sync - one of csv, json, jsonl, excel or detect

• pattern: string (required)

  A regex pattern to filter resolved files on name by - set to "" if path specifies some kind of filtering mechanism (e.g. glob pattern matching) and no more granular filtering is required

• start_date: string (required)

  An ISO-8601 date-time to filter resolved files on last modified timestamp by

• key_properties: array of strings (required)

  The stream primary keys - for files where a primary key cannot be clearly identified, you can reference meta-properties

  [
    "_smart_source_bucket",
    "_smart_source_file",
    "_smart_source_lineno"
  ]
  

  or [] for append-only behaviour

  If using the meta-property approach outlined above, be aware that changes to file locations or contents may result in unexpected duplicates or overwrites - therefore, it is safest to use this approach if the targeted files are, for all intents and purposes, immutable

• encoding: string (default: utf-8)

  The encoding to use when reading files

• skip_initial: integer (default: 0)

  The number of lines to skip over when reading a file - mostly useful for excel format files

• max_sampled_files: integer (default: 50)

  The number of files to sample during dynamic catalog discovery

• max_sampling_read: integer (default: 1000)

  The number of lines to sample for each file during dynamic catalog discovery

• sample_rate: integer (default: 5)

  Controls how frequently lines are sampled (i.e. every nth line) during dynamic catalog discovery

• prefer_schema_as_string: boolean (default: false)

  Whether or not to skip inferring property types during sampling - if using with CSV files, you can set max_sampling_read to 1 alongside this to improve discovery performance (only the header row needs to be sampled to resolve the property set, given that all values will be treated as strings)

csv format only

• delimiter: string (default: ,)

  The value delimiter sequence used in the targeted files

• quotechar: string (default: ")

  The quotechar delimiter character used in the targeted files - set to detect to auto-discover

excel format only

• worksheet_name: string

  The specific worksheet name to pull out - defaults to the sheet with the most available data

See the tap README for further information and other miscellaneous settings

Client ID

The application (client) ID of the app registration, shown in the Overview page

Client Secret

The value of a client secret on the app registration - copy the Value column immediately after creating the secret

Tenant ID

The directory (tenant) ID of the app registration, shown in the Overview page - a verified domain, such as <tenant>.onmicrosoft.com, also works