Create Data Source in ZappySys Data Gateway based on API Driver
-
Download and install ZappySys ODBC PowerPack.
-
Search for gateway in start menu and Open ZappySys Data Gateway:
-
Go to Users Tab to add our first Gateway user. Click Add; we will give it a name tdsuser and enter password you like to give. Check Admin option and click OK to save. We will use these details later when we create linked server:
-
Now we are ready to add a data source. Click Add, give data source a name (Copy this name somewhere, we will need it later) and then select Native - ZappySys API Driver. Finally, click OK. And it will create the Data Set for it and open the ZS driver UI.
SharepointOnlineDSN
-
When the Configuration window appears give your data source a name if you haven't done that already, then select "SharePoint Online" from the list of Popular Connectors. If "SharePoint Online" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:
SharepointOnlineDSNSharePoint Online -
Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.
Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]
Steps how to get and use SharePoint Online credentials
Follow these simple steps below to create Microsoft Entra ID application with delegated access:
WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g.automation@my-company.com
). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to App registrations and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference. -
When configuration window opens, configure these fields:
-
Supported account type
- Use
Accounts in this organizational directory only
, if you need access to data in your organization only.
- Use
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Use
https://zappysys.com/oauth
as the URL.
- Set the type to
-
Supported account type
-
After registering the app, copy the Application (client) ID for later:
-
Copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
-
Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
- In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
-
In the Scope field use the default value or select individual scopes, e.g.:
-
email
-
offline_access
-
openid
-
profile
-
User.Read
-
Sites.Read.All
-
Sites.ReadWrite.All
-
Files.Read.All
-
Files.ReadWrite.All
-
- Press Generate Token button to generate Access and Refresh Tokens.
- Optional step. Choose Default Site Id from the drop down menu.
- Click Test Connection to confirm the connection is working.
- Done! Now you are ready to use the API Connector!
Fill in all required parameters and set optional parameters if needed:
SharepointOnlineDSNSharePoint OnlineUser Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Authorization URL Fill-in the parameter... Token URL Fill-in the parameter... Client ID Fill-in the parameter... Scope Fill-in the parameter... Return URL Fill-in the parameter... Default Site Id (select after pressing 'Generate Token') Fill-in the parameter... Optional Parameters Client Secret Default Drive Id (select after pressing 'Generate Token') Login Prompt Option RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503|423 RetryCountMax 5 RetryMultiplyWaitTime True Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed) Extra Headers (e.g. Header1:AAA||Header2:BBB) IsAppCred 0 Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]
Steps how to get and use SharePoint Online credentials
Follow these simple steps to create Microsoft Entra ID application with application access permissions:
-
Create an OAuth app
-
Grant application SharePoint Online permissions (optional, for granular permissions)
This step allows to grant OAuth application granular permissions, i.e. access configured specificSites
,Lists
, andList Items
.
Step-1: Create OAuth app
- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference.
-
When configuration window opens, configure these fields:
-
Supported account type
- e.g. select
Accounts in this organizational directory only
if you need access to data in your organization only.
- e.g. select
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Leave the URL field empty.
- Set the type to
-
Supported account type
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
-
Continue and create Client secret:
-
Then copy the Client secret for later steps:
-
Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
-
Select Microsoft Graph:
-
Then choose Application permissions option:
-
Continue by adding these Sites permissions (search for
site
):INFO: If you want to access specific lists or list items (table-level vs row-level security) rather than the full site, then addLists.SelectedOperations.Selected
orListItems.SelectedOperations.Selected
permissions , just like in the previous step (search forlist
).WARNING: If you add any of these permissions -Sites.Selected
,Lists.SelectedOperations.Selected
, orListItems.SelectedOperations.Selected
- you must grant the app the SharePoint permissions for the specific resource (e.g. aSite
, aList
, or aListItem
). Follow instructions in Grant SharePoint permissions to the OAuth app (optional) section on how to accomplish that. -
Finish by clicking Add permissions button:
-
Now it's time to Grant admin consent for your application:
-
Confirm all the permissions are granted:
-
Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
- In the Client Secret field paste the Client secret value you copied in the previous step.
- Optional step. Choose Default Site Id from the drop down menu.
- Click Test Connection to confirm the connection is working.
- Done!
Step-2 (optional): Grant SharePoint permissions to the OAuth app (optional)
If you used
Sites.Selected
,Lists.SelectedOperations.Selected
orListItems.SelectedOperations.Selected
permission in the previous section, you must grant the app the SharePoint permissions for the specific resource (e.g. aSite
, aList
, or aListItem
). You can do it using PowerShell or SharePoint admin center (obsolete method).Granting SharePoint permissions using PowerShell
Unfortunately, there is no user interface available to control these permissions yet. For now, granting permissions has to be accomplished via Microsoft Graph API [Microsoft reference]:
You must be the owner of the resource to grant permissions (i.e. belong to SharePoint owners group or be the owner of theSite
orList
).- Open PowerShell (run as admin).
-
Call the following PowerShell code to grant
read
andwrite
permission for the app we created earlier (assuming Application (client) ID is89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e
):##### CONFIGURATION ############################################################################################ # More info at: # - https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell # - https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.sites/?view=graph-powershell-1.0 # Find SharePoint Site Id by following these steps: # - Login into SharePoint Online # - Open this URL https://{your-company}.sharepoint.com/_api/site in the browser # NOTE: For a subsite use https://{your-company}.sharepoint.com/sites/{your-subsite}/_api/site # - Find 'Id' element in the response (e.g. <d:Id m:type="Edm.Guid">efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3</d:Id>) # - Copy the Site Id, i.e.: efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3 # Set $siteId variable to the retrieved Site Id: $siteId="efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3" # Find your Application Id (i.e. Client Id) in the Azure Portal, in App Registrations page: # https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade $applicationId="89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e" # Set one of app permissions: read, write, fullcontrol, owner ('write' includes 'read' permission) $appPermission="write" ##### SCRIPT ################################################################################################### # Step-1: Install 'Microsoft.Graph.Sites' module if it's not installed if (-not (Get-Module Microsoft.Graph.Sites -ListAvailable)) { Install-Module Microsoft.Graph.Sites } # Step-2: Load module Import-Module Microsoft.Graph.Sites # Step-3: Login (use Azure admin or SharePoint owner account) DisConnect-MgGraph Connect-MgGraph # Step-4: Set parameters for API call (set permissions, Site ID and Application ID) $params = @{ roles = @($appPermission) grantedTo = @{ application = @{id = $applicationId} } } # Step-5: Grant permissions New-MgSitePermission -SiteId $siteId -BodyParameter $params # Done! Write-Host "Granted SharePoint permissions to application '$applicationId' for site '$siteId'."
- That's it! Now you can use the API Connector!
Granting SharePoint permissions using SharePoint admin center (obsolete method)
If you used
Site.Selected
permission you can link it SharePoint site in SharePoint admin center [SharePoint reference]. Follow these simple steps to accomplish that:-
Log in to SharePoint admin center using this URL:
(replace
YOURCOMPANY
with your company name):https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appinv.aspx
INFO: To view all the registered apps in SharePoint, visit this page:https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web
. - In the App Id field enter Application (client) ID you copied in the previous step.
-
In the Permission Request XML field enter XML snippet which describes which SharePoint permissions you want to grant to the OAuth app, e.g.:
<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" /> </AppPermissionRequests>
INFO: This example gives appFullControl
, but you can also grant itRead
orWrite
permissions. - Click Create to grant the permission to your OAuth app.
- That's it! Now you can use the API Connector!
Fill in all required parameters and set optional parameters if needed:
SharepointOnlineDSNSharePoint OnlineApplication Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Token URL Fill-in the parameter... Client ID Fill-in the parameter... Client Secret Fill-in the parameter... Default Site Id Fill-in the parameter... Optional Parameters Scope https://graph.microsoft.com/.default Default Drive Id RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503|423 RetryCountMax 5 RetryMultiplyWaitTime True Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed) Extra Headers (e.g. Header1:AAA||Header2:BBB) IsAppCred 1 -
Once the data source has been configured, you can preview data. Select the Preview tab and use settings similar to the following to preview data:
-
Click OK to finish creating the data source.
Read data in SQL Server using ZappySys Data Gateway
To read the data in SQL Server, the first thing you have to do is create a Linked Server:
-
First, let's open SQL Server Management Studio, create a new Linked Server, and start configuring it:
-
Then click on Security option and configure username we created in ZappySys Data Gateway in one of the previous steps:
-
Optional step. Under the Server Options, Enable RPC and RPC Out and Disable Promotion of Distributed Transactions(MSDTC).
You need to enable RPC Out if you plan to use
EXEC(...) AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY]
rather than OPENQUERY.
If don't enabled it, you will encounter theServer 'LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY' is not configured for RPC
error.Query Example:
EXEC('SELECT * from Products') AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY]
If you plan to use
'INSERT INTO...EXEC(....) AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY]'
in that case you need to Disable Promotion of Distributed Transactions(MSDTC).
If don't disabled it, you will encounter theThe operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MY_LINKED_SERVER_NAME" was unable to begin a distributed transaction.
error.Query Example:
INSERT INTO dbo.Products EXEC('SELECT * FROM Products') AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY]
-
Finally, open a new query and execute a query we saved in one of the previous steps:
SELECT * FROM OPENQUERY([LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY], 'SELECT * FROM Products')
SELECT * FROM OPENQUERY([LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY], 'SELECT * FROM Products')
Create Linked Server using Code
In previous section you saw how to create a Linked Server from UI. You can do similar action by code too (see below). Run below script after changing necessary parameters. Assuming your Data Source name on ZappySys Data Gateway UI is 'SharepointOnlineDSN'
USE [master]
GO
--///////////////////////////////////////////////////////////////////////////////////////
--Run below code in SSMS to create Linked Server and use ZappySys Drivers in SQL Server
--///////////////////////////////////////////////////////////////////////////////////////
//Replace YOUR_GATEWAY_USER, YOUR_GATEWAY_PASSWORD
//Replace localhost with IP/Machine name if ZappySys Gateway Running on different machine other than SQL Server
//Replace Port 5000 if you configured gateway on a different port
--1. Configure your gateway service as per this article https://zappysys.com/links?id=10036
--2. Make sure you have SQL Server Installed. You can download FREE SQL Server Express Edition from here if you dont want to buy Paid version https://www.microsoft.com/en-us/sql-server/sql-server-editions-express
--Uncomment below if you like to drop linked server if it already exists
--EXEC master.dbo.sp_dropserver @server=N'LS_SharepointOnlineDSN', @droplogins='droplogins'
--3. Create new linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'LS_SharepointOnlineDSN' --Linked server name (this will be used in OPENQUERY sql
, @srvproduct=N''
---- For MSSQL 2012,2014,2016 and 2019 use below (SQL Server Native Client 11.0)---
, @provider=N'SQLNCLI11'
---- For MSSQL 2022 or higher use below (Microsoft OLE DB Driver for SQL Server)---
--, @provider=N'MSOLEDBSQL'
, @datasrc=N'localhost,5000' --//Machine / Port where Gateway service is running
, @provstr=N'Network Library=DBMSSOCN;'
, @catalog=N'SharepointOnlineDSN' --Data source name you gave on Gateway service settings
--4. Attach gateway login with linked server
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LS_SharepointOnlineDSN' --linked server name
, @useself=N'False'
, @locallogin=NULL
, @rmtuser=N'YOUR_GATEWAY_USER' --enter your Gateway user name
, @rmtpassword='YOUR_GATEWAY_PASSWORD' --enter your Gateway user's password
GO
--5. Enable RPC OUT (This is Optional - Only needed if you plan to use EXEC(...) AT YourLinkedServerName rather than OPENQUERY
EXEC sp_serveroption 'LS_SharepointOnlineDSN', 'rpc', true;
EXEC sp_serveroption 'LS_SharepointOnlineDSN', 'rpc out', true;
--Disable MSDTC - Below needed to support INSERT INTO from EXEC AT statement
EXEC sp_serveroption 'LS_SharepointOnlineDSN', 'remote proc transaction promotion', false;
--Increase query timeout if query is going to take longer than 10 mins (Default timeout is 600 seconds)
--EXEC sp_serveroption 'LS_SharepointOnlineDSN', 'query timeout', 1200;
GO
Create View in SQL Server
Finally, use this or similar query in a view or stored procedure, which you will be able to use in Tableau. We will create a view to return invoices:
-
CREATE VIEW vwApiInvoices AS SELECT * FROM OPENQUERY([LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY], 'SELECT * FROM Invoices')
Read data in Tableau from SQL Server
Actually, we will be getting data from SQL Server which in turn will be getting data from ZappySys Data Gateway data source. Let's begin and see how to accomplish that:
- Open Tableau Desktop and click File > New
-
To create new Connection click More > Microsoft SQL Server > Enter your credentials to connect to SQL Server (in our example before we used tdsuser):
-
Once connection is created for SQL Server we can read SharePoint Online data 3 different ways:
- Query View which contains OPENQUERY to Linked Server for SharePoint Online data
- Use direct SQL Query using OPENQUERY
- Use Stored Procedure (Mostly useful to parameterize calls
-
See below example to pull data from SharePoint Online in Tableau using SQL View approach:
-
Once your data sources are created you can click on Sheet1 and drag fields to create visualizations for Tableau Dashboard:
Passing Parameters to SharePoint Online calls in Tableau (Dynamic SQL)
Now let's look at scenario where you have to pass parameters to build Dynamic Dashboard. You can try to insert Parameters in your Direct SQL when you build Dynamic SQL but we found some issues with that so we are going to suggest Stored Procedure approach. For more information on Known issue on Dynamic Metadata Check this post.-
First lets create a stored procedure in SQL Server for Parameter Example. Notice how we added WITH RESULT SETS in the code to describe metadata.
--DROP PROC dbo.usp_GetInvoicesByCountry --GO /* Purpose: Parameterize SharePoint Online call via SQL. Call ZappySys Drivers inside SQL Server. */ CREATE PROC dbo.usp_GetInvoicesByCountry @country varchar(100) AS DECLARE @sql varchar(max) --//Escape single ticks carefully SET @sql = 'SELECT OrderID,CustomerID,Country,Quantity FROM $ WITH (Src=''https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json@filter=Country eq '+ @country +''' ,Filter=''$.value[*]'' ,DataFormat=''OData'' )' DECLARE @sqlFull varchar(max) SET @sqlFull='SELECT * FROM OPENQUERY([LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY], ''' + REPLACE( @sql, '''', '''''' ) + ''' )' PRINT @sqlFull --//For DEBUG purpose EXECUTE (@sqlFull) WITH RESULT SETS ( (OrderID int,CustomerID varchar(100),Country varchar(100),Quantity int) --//describe first result. If you don't do this then wont work in Tableau ) GO -- Example call EXEC dbo.usp_GetInvoicesByCountry @country='Germany'
- Once you create a stored procedure go to Tableau datasource and select Database which contains the stored procedure we just created.
-
Now find your stored proc and drag it on the datasource pane. You will see parameters UI as below. You can create new parameter - Select New Parameter under Value Column.
- Thats it now you can reuse your parameterized datasource anywhere in Dashboard.
-
If you have need to select Parameters from predefined values rather than free text then edit your parameter and select List option. Define values you like to select from as below.
-
When you create Tableau Dashboard you will see Parameter dropdown (If you selected List) elase you may see Textbox to enter custom value.
Firewall settings
So far we have assumed that Gateway is running on the same machine as SQL Server. However there will be a case when ZappySys ODBC PowerPack is installed on a different machine than SQL Server. In such case you may have to perform additional Firewall configurations. On most computers firewall settings wont allow outside traffic to ZappySys Data Gateway. In such case perform following steps to allow other machines to connect to Gateway.
Method-1 (Preferred)If you are using newer version of ZappySys Data Gateway then adding firewall rule is just a single click.
- Search for gateway in start menu and open ZappySys Data Gateway.
- Go to Firewall Tab and click Add Firewall Rule button like below. This will create Firewall rule to all Inbound Traffic on Port 5000 (Unless you changed it).
- Search for Windows Firewall Advanced Security in start menu.
- Under Inbound Rules > Right click and click [New Rule] >> Click Next
- Select Port on Rule Type >> Click Next
- Click on TCP and enter port number under specified local port as 5000 (use different one if you changed Default port) >> Click Next
- Select Profile (i.e. Private, Public) >> Click Next
- Enter Rule name [i.e. ZappySys Data Gateway – Allow Inbound ] >> Click Next
- Click OK to save the rule

Advanced topics
Create Custom Stored Procedure in ZappySys Driver
You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.
Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
-
Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
-
Enter the desired Procedure name and click on OK:
-
Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
CREATE PROCEDURE [usp_get_orders] @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>' AS SELECT * FROM Orders where OrderDate >= '<@fromdate>';
-
That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:
Exec usp_get_orders '1996-01-01';
-
Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
SELECT * FROM OPENQUERY([LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY], 'EXEC usp_get_orders @fromdate=''1996-07-30''')
-
Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
Create Custom Virtual Table in ZappySys Driver
ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.
If you're dealing with Microsoft Access and need to import data from an SQL query, it's important to note that Access doesn't allow direct import of SQL queries. Instead, you can create custom objects (Virtual Tables) to handle the import process.
Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.
-
Go to Custom Objects Tab and Click on Add button and Select Add Table:
-
Enter the desired Table name and click on OK:
-
And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.
-
Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here
SELECT "ShipCountry", "OrderID", "CustomerID", "EmployeeID", "OrderDate", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode" FROM "Orders" Where "ShipCountry"='USA'
-
That's it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:
SELECT * FROM "vt__usa_orders_only"
-
Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
SELECT * FROM OPENQUERY([LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY], 'EXEC [usp_get_orders] ''1996-01-01''')
-
Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
Actions supported by SharePoint Online Connector
SharePoint Online Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.Parameter | Description |
---|---|
Site Id |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Drive Id |
|
Parameter | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Record Filter (Client Side) |
|
||||||||||||||||||
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||||||
Drive Id |
|
||||||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||||||
Search Path (Default=Root Folder) - Max 200 items listed |
|
||||||||||||||||||
Order By Field(s) - Only for Recursive SearchType |
|
Parameter | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||||||
Drive Id |
|
||||||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||||||
Search Path (Default=Root Folder) - Max 200 items listed |
|
||||||||||||||||||
Order By Field(s) - Only for Recursive SearchType |
|
Parameter | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||||||
Drive Id |
|
||||||||||||||||||
Search Type (Default=Recursive) |
|
||||||||||||||||||
Search Path (Default=Root Folder) |
|
||||||||||||||||||
Order By Field(s) - Only for Recursive SearchType |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId |
|
Expand |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId |
|
Parameter | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SiteId (Re-Select ListId after you change this) |
|
||||||||||
ListId |
|
||||||||||
Filter |
|
Parameter | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SiteId (Re-Select ListId after you change this) |
|
||||||||||
ListId |
|
||||||||||
Filter |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId (Re-Enter ListItemId after you change this) |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId (Re-Enter ListItemId after you change this) |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId (Re-Enter ListItemId after you change this) |
|
ListItemId |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
FileId -OR- Path |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed |
|
||||||||||||||
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) |
|
||||||||||||||
AutoDetectByValue |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed |
|
||||||||||||||
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) |
|
||||||||||||||
Range |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||
Drive Id |
|
||||||||||
Disk File Path (e.g. c:\somefolder\file.xlsx) |
|
||||||||||
Target File Path OR Id (e.g. file.xlsx -OR- myfolder/file.xlsx -OR- 0Zxxxx1234) |
|
||||||||||
Conflict Behavior (What to do if file exists) |
|
||||||||||
Use Source File Created/Modified DateTime rather than Uploaded Time |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Drive Id |
|
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Operation |
|
||||||||||||||
Source Site Id (Optional - Select if different than default site) |
|
||||||||||||||
Source Drive Id |
|
||||||||||||||
Source File or Folder Id you like to move or rename |
|
||||||||||||||
Target Site Id (Optional - Select if different than source SiteId) |
|
||||||||||||||
Target DriveId (Optional - Select if different than source DriveId) |
|
||||||||||||||
Target Folder Id (e.g. 01R65Qxxxxxxx123 -OR- root) - Max 200 items listed |
|
||||||||||||||
Target Item Name (Optional for Move - e.g. Myfile.pdf or MyFolder) |
|
||||||||||||||
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Prefer Async Operation (Enable if you get error) |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Source Site Id (Optional - Select if different than default site) |
|
||||||||||||||
Source Drive Id |
|
||||||||||||||
Source File or Folder Id you like to copy |
|
||||||||||||||
Target Site Id (Optional - Select if different than source SiteId) |
|
||||||||||||||
Target DriveId (Optional - Select if different than source DriveId) |
|
||||||||||||||
Target Folder Id (e.g. 01R65QTTRxxxxx -OR- root) |
|
||||||||||||||
Target Item Name (Optional) |
|
||||||||||||||
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Prefer Async Operation (Enable if you get error) |
|
||||||||||||||
Conflict Behavior (What to do if file exists at target location) |
|
Parameter | Description | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Url |
|
||||||||||||||||||||||||||
Body |
|
||||||||||||||||||||||||||
IsMultiPart |
|
||||||||||||||||||||||||||
Filter |
|
||||||||||||||||||||||||||
Headers |
|
Parameter | Description |
---|---|
Url |
|
IsMultiPart |
|
Filter |
|
Headers |
|
SharePoint Online Connector Examples for Tableau Connection
This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.
Select lists from Lists table [ Read more... ]
SELECT * FROM Lists
Select data from a named list [ Read more... ]
SELECT * FROM MyList
--WHERE Id = 1234
Insert data into a named list [ Read more... ]
INSERT INTO MyList(Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)
Insert multiple Lookup or Person/Group IDs into a named list [ Read more... ]
--new version --
INSERT INTO MyList(Title, ManyPersonsLookupId, ManyPersonsLookupIdOdataType)
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--old version --
INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List
Insert multiple Choice Values into a named list [ Read more... ]
--new version --
INSERT INTO MyList(Title, MultiChoiceField, MultiChoiceFieldODataType)
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)
--old version --
INSERT INTO MyList(Title, MultiChoiceField, "fields.MultiChoiceField@odata\u002Etype")
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)
--Considering "MultiChoiceField" is the name of the field in SharePoint List
Update data in a named list [ Read more... ]
UPDATE MyList
SET Name = 'Elizabeth', Surname = 'Johnson'
WHERE Id = '123'
Update Multi Select Lookup or Person/Group IDs field [ Read more... ]
UPDATE MyList
SET Title = 'Elizabeth Johnson',
--Method#1 (Pass type info in the same value)
ManyPersonsLookupId = '[11,22,33]', "fields.ManyPersonsLookupId@odata\u002Etype" = 'Collection(Edm.Int32)'
--Method#2 (Pass type info via ODataField)
--OR--
--ManyPersonsLookupId = '[11,22,33]',
--ManyPersonsLookupIdODataType" = 'Collection(Edm.Int32)
WHERE Id = '123'
Update / Insert Sharepoint List Data (Mixed Data Types - e.g. Multi Choice, Lookup, Person, DateTime, Number) [ Read more... ]
UPDATE "Travel Requests" --list name
SET
Title='MyTitle @ <<FUN_NOW>>'
--Method#1 Pass Value and Type in 2 fields
, CustomText = 'This is text'
, CustomTextLarge = 'This is really long text ....'
, CustomNumber = 10000.12345
, CustomDateTime ='2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
, CustomChoice = 'BB'
--Choice Multi-Select
, CustomChoiceMulti = '["AA", "BB"]'
, CustomChoiceMultiODataType='Collection(Edm.String)' --for multi select must supply OData Type
--OR-- append like below in one field
--, CustomChoiceMulti = '["AA", "BB"], "CustomChoiceMulti@odata.type" : "Collection(Edm.String)"'
--Lookup
--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
, CustomLookupSingleLookupId = 1
--Lookup Multi-Select
, CustomLookupMultiLookupId = '[1,3]' --for multi select must supply OData Type
, CustomLookupMultiLookupIdODataType='Collection(Edm.Int32)'
--OR-- append like below in one field
--, CustomLookupMultiLookupId = '[1,3], "CustomLookupMultiLookupId@odata.type" : "Collection(Edm.String)"'
--Person
,CustomPersonLookupId = 11
--Person Multi-Select
, CustomPersonMultiLookupId = '[11, 22]'
, CustomPersonMultiLookupIdODataType='Collection(Edm.Int32)'
--OR-- append like below in one field
--, CustomPersonMultiLookupIdODataType = '[11,22], "CustomPersonMultiLookupIdODataType@odata.type" : "Collection(Edm.Int32)"'
Where Id=1 --List Item Id
--- Insert Example ----
INSERT INTO "Travel Requests" --list name
(
Title
, CustomText
, CustomTextLarge
, CustomNumber
, CustomDateTime
, CustomChoice
, CustomChoiceMulti
, CustomChoiceMultiODataType
, CustomLookupSingleLookupId
--Lookup Multi-Select
, CustomLookupMultiLookupId
, CustomLookupMultiLookupIdODataType
--Person
,CustomPersonLookupId
, CustomPersonMultiLookupId
, CustomPersonMultiLookupIdODataType
)
VALUES(
'MyTitle @ <<FUN_NOW>>'
, 'This is text'
, 'This is really long text .. '
, 10000.12345
, '2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
, 'BB'
--Choice Multi-Select
, '["AA", "BB"]'
, 'Collection(Edm.String)' --for multi select must supply OData Type
--Lookup
, 1
--Lookup Multi-Select
, '[1,3]' --for multi select must supply OData Type
, 'Collection(Edm.Int32)'
--Person
,11
--Person Multi-Select
, '[11, 22]'
, 'Collection(Edm.Int32)'
)
Delete item from a named list [ Read more... ]
DELETE FROM MyList WHERE Id=888
--OR--
DELETE FROM MyList WITH (ListItemId='123')
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyList'.
Main site [ Read more... ]
Gets main SharePoint site details
SELECT * FROM get_main_site
List sites [ Read more... ]
Lists all available SharePoint sites
SELECT * FROM Sites
List drives [ Read more... ]
Lists all drives
SELECT * FROM Drives
Get drive [ Read more... ]
SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726LE0eY5F2BBNi14-XXXXXXXXXXX-GuQ4DORpHy-XXXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List folders from SharePoint Document Library [ Read more... ]
SELECT * FROM list_folders
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List files from SharePoint Document Library [ Read more... ]
SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List Excel files from SharePoint Document Library [ Read more... ]
SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
Get list fields [ Read more... ]
SELECT * FROM get_list_fields
WITH (ListId='2e1d58e4-eced-4d1c-9279-XXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
Get list items (SharePoint Document Library) [ Read more... ]
SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
Get list items - dynamic metadata (SharePoint Document Library) [ Read more... ]
Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person)
SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
Create list item (SharePoint Document Library) [ Read more... ]
INSERT INTO create_list_item(Title, OrderId)
VALUES ('My super title', 12345)
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
Update list item (SharePoint Document Library) [ Read more... ]
UPDATE update_list_item
SET Title = 'My super title', OrderId = 12345
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXX'
,ListItemId='775'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.
Delete list item (SharePoint Document Library) [ Read more... ]
DELETE FROM delete_list_item
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,ListItemId='775')
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.
List Excel file worksheets (SharePoint Document Library) [ Read more... ]
SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
Read Excel Worksheet (SharePoint Document Library) - Auto Detect Range [ Read more... ]
Reads an Excel file without supplying Cell Address Range (It auto detects based on first and last cell)
SELECT * FROM get_excel_worksheet_autodetect
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,AutoDetectByValue='true'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
Read Excel Worksheet (SharePoint Document Library) for a specified Cell Address Range [ Read more... ]
Reads an Excel file from a specified Cell Range. To automatically read without cell range use other endpoint get_excel_worksheet_autodetect
SELECT * FROM get_excel_worksheet
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,Range='A1:K10000'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.
Upload a file to a SharePoint Document Library [ Read more... ]
SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
--,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
--,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
--,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
)
--DriveId can be retrieved by selecting from 'Drives' table.
Upload a file to SharePoint Document Library (Overwrite file if exists) [ Read more... ]
Uploads a file to a SharePoint Document Library and if file already exists then overwrite it rather than throwing error (Default is throw error)
SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
--,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
--,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
)
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
Download a file to a local disk from a SharePoint document library [ Read more... ]
SELECT * FROM download_file_to_disk
WITH(
DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMxxxxxxxxxxxxx'
, FileId='01SUOJPKDXTQL2XI2EIFDYZxxxxxxxxxxxxx'
, TargetFilePath='C:\temp\List.xlsx'
--, FileOverwriteMode='FailIfExists' //change default overwrite mode from AlwaysOverwrite to FailIfExists
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
Read a SharePoint file content as BASE64 (Binary to string) [ Read more... ]
Read file content as base64 string (Binary to Base64) and also save Base64 to disk to recreate file.
SELECT Data as BYTES_AS_BASE64_STRING, BASE64_TO_FILE('C:\My files\Employees\List.xls', Data) FILE_SAVED_TO_DISK_FROM_BASE64
FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'
,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
Get full image path from Image type field [ Read more... ]
By default Image field in your List will show JSON document which contains many pieces of your Image field. To get full path of your Image file you can use JSON_VALUE function and extract multiple fields and concat together to get full path like below.
SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
FROM "My SharePoint List Name"
List files in a folder path (Recursive) [ Read more... ]
Use list_files endpoint to list files recursively (list from child folder). However there are some limitations to list recursively, such as some new files may not appear right away or files not indexed wont show up.
SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
--,SearchPath='/root' --default
--,SearchPath='/root:/myfolder:' --folder path
,SearchPath='/root:/myfolder/subfolder:' --folder path nested
,OrderBy='lastModifiedDateTime desc'
)
--You can get DriveId by selecting from 'Drives' table.
List files in a folder Id (Non-Recursive) [ Read more... ]
This examplel shles under a specific FolderId (Available in Non-recursive mode only).
SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
,SearchType='children' --must use this type if you like to list items by FolderId
,SearchPath='/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX'
)
--You can get DriveId by selecting from 'Drives' table.
Create / Update a CSV File from External Data Source (e.g. MSSQL / ODBC) [ Read more... ]
This example shows how to create / update a CSV file directly inside SharePoint Document Library using streaming approach. This example reads records from Microsoft SQL Server database and writes data to CSV file on SharePoint Document Library. If file does not exists then it creates a new one.
INSERT INTO FileReaderWriterCsv
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/test_out.csv:'
--, FileId='root:/subfolder/test_out.csv:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ...
--, RowDelimiter='{NEWLINE}' --{LF}, {TAB}, | , \x0009 ...
--, HasColumnHeaderRow=0 --set for header less file
--, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
--See Query Builder for more options
)
Create / Update a JSON File in SharePoint Document Library from External Data Source (e.g. MSSQL / ODBC) [ Read more... ]
This example shows how to create / update a JSON file directly inside SharePoint Document Library using streaming approach. This example reads records from Microsoft SQL Server database and writes data to JSON file on SharePoint Document Library. If file does not exists then it creates a new one. It uses LayoutMap to generate JSON with custom layout.
INSERT INTO FileReaderWriterJson
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/test_out.json:'
--, FileId='root:/subfolder/test_out.json:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
, JsonOutputFormat='Multicontent' --Default , Array2D, ArrayLines ...
--, DoNotOutputNullProperty='True'
--, Encoding='UTF8' --UTF8WithoutBOM
--, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
--Example#1: Output all columns
, LayoutMap='<?xml version="1.0" ?>
<settings>
<dataset id="root" main="True" readfrominput="True" />
<map src="*" />
</settings>'
--Example#2: Nested JSON (Records under an array)
/*
, LayoutMap='<?xml version="1.0" ?>
<settings singledataset="True">
<dataset id="root" main="True" readfrominput="True" />
<map name="MyArray" dataset="root" maptype="DocArray">
<map src="OrderID" name="OrderID" />
<map src="OrderDate" name="OrderDate" />
</map>
</settings>'
*/
--Example#3: Nested JSON (Records under a sub document section)
/*
, LayoutMap='<?xml version="1.0" ?>
<settings>
<dataset id="dsRoot" main="True" readfrominput="True" />
<map name="NestedSection">
<map src="OrderID" name="OrderID_MyLabel" />
<map src="OrderDate" name="OrderDate_MyLabel" />
</map>
</settings>'
*/
)
Read CSV File from SharePoint Document Library (Parse Rows and Columns without downloading) [ Read more... ]
This example shows how to read a CSV file from SharePoint Document Library without downloading on the Disk (Streaming approach).
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.csv:'
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
--, HasColumnHeaderRow=0 --set for header less file
--, FileId='root:/MyFolder/test_out.csv.gz:'
--, FileCompressionType='GZip' --None, GZip, Zip
)
Read Zip/Gzip Compressed CSV File from SharePoint Document Library (Parse Rows and Columns without downloading) [ Read more... ]
This example shows how to read a compressed CSV file (Zip or Gzip) from SharePoint Document Library without downloading on the Disk (Streaming approach).
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.csv.zip:'
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
--, HasColumnHeaderRow=0 --set for header less file
--, FileId='root:/MyFolder/dump.csv.gz:'
, FileCompressionType='Zip' --None, GZip, Zip
)
Read JSON File from SharePoint Document Library (Parse Rows and Columns without downloading) [ Read more... ]
This example shows how to read a JSON file from SharePoint Document Library without downloading on Disk (Streaming approach).
SELECT * from get_json_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.json:'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
--to read compressed file use below way
--, FileId='root:/MyFolder/dump.json.gz:'
--, FileCompressionType='GZip' --None, GZip, Zip
)
Read Zip/Gzip Compressed JSON File from SharePoint Document Library (Parse Rows and Columns without downloading) [ Read more... ]
This example shows how to read a compressed JSON file (Zip or Gzip) from SharePoint Document Library without downloading on the Disk (Streaming approach).
SELECT * from get_json_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.json.zip:'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
, FileCompressionType='Zip' --None, GZip, Zip
)
Generic API Call - Update MultiChoice / Multi Select Lookup field in SharePoint List [ Read more... ]
This example shows how to make generic API call to update fields which allow multiple value selection in either Lookup or MultiChoice datatype. This example shows 2 types of fields. One is Multi Select e.g. MyMultiSelectLookupCol / MyMultiChoiceCol and another is Single select MySingleSelectCol (i.e. Dropdown). You must supply odata.type for Field. If its MultiChoice you can supply string ID/Value but set odata.type as Edm.String rather than Edm.Int32
SELECT * from generic_request
WITH(
Url='https://graph.microsoft.com/v1.0/sites/root/lists/1d3126af-14ca-46c7-a82a-4865873756c6/items/1'
, RequestMethod='PATCH'
, Filter='$.fields'
, Headers='Content-Type: application/json || x-header2: abcd'
, Body='{
"fields": {
"MyMultiSelectLookupColLookupId@odata.type": "Collection(Edm.Int32)"
,"MyMultiSelectLookupColLookupId": [ 1 , 2 ]
,"MyMultiChoiceColumn@odata.type": "Collection(Edm.String)"
,"MyMultiChoiceColumn": [ "AAA" , "BBB" ]
,"MySingleChoiceColumnLookupId":1
}
}'
)
Move a File or Folder in SharePoint Document Library [ Read more... ]
This example shows how to move a file or folder to a different location in SharePoint Document Library. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>
SELECT * FROM move_rename_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Item Id (File or Folder) you like to move or rename
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site Id (Optional): Only needed if moving file to a different Site
--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if moving file to a different Drive
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder Id
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR (for moving to root)
--, TargetFolderId='root'
--Target Item Name after Move (Optional): If you do not wish to change name after Move then do not supply below
--, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
Rename a File or Folder in SharePoint Document Library [ Read more... ]
This example shows how to rename a file or folder and you can also move to a different location in SharePoint Document Library. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>
SELECT * FROM move_rename_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Source Item you like to rename (File or Folder)
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site Id (Optional): Only needed if moving file to a different Site
--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if moving file to a different Drive
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder (Optional): If you are doing just rename and moving to different location then keep it keep blank
--, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--, TargetFolderId='root'
--Target Item Name (Required for rename)
, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
Copy a File or Folder in SharePoint Document Library [ Read more... ]
This example shows how to copy a file or folder to a different location in SharePoint Document Library (In same Drive or different SharePoint Document Library). It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>
SELECT * FROM copy_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='xxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Source Item you like to copy
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site (Optional)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if copying file to a different Drive (e.g. another User's drive)
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder where item will be copied
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR--
--, TargetFolderId='root'
--Target item name (Optional) - Only needed if new name needed after copy
, TargetItemName='new_file_or_folder_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--,ConflictBehavior='replace' --fail, rename (Ignored if TargetItemName is not supplied)
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
Conclusion
In this article we showed you how to connect to SharePoint Online in Tableau and integrate data without any coding, saving you time and effort. We encourage you to download SharePoint Online Connector for Tableau and see how easy it is to use it for yourself or your team.
If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.
Download SharePoint Online Connector for Tableau Documentation
More integrations
Other connectors for Tableau
Other application integration scenarios for SharePoint Online
How to connect SharePoint Online in Tableau?
How to get SharePoint Online data in Tableau?
How to read SharePoint Online data in Tableau?
How to load SharePoint Online data in Tableau?
How to import SharePoint Online data in Tableau?
How to pull SharePoint Online data in Tableau?
How to push data to SharePoint Online in Tableau?
How to write data to SharePoint Online in Tableau?
How to POST data to SharePoint Online in Tableau?
Call SharePoint Online API in Tableau
Consume SharePoint Online API in Tableau
SharePoint Online Tableau Automate
SharePoint Online Tableau Integration
Integration SharePoint Online in Tableau
Consume real-time SharePoint Online data in Tableau
Consume real-time SharePoint Online API data in Tableau
SharePoint Online ODBC Driver | ODBC Driver for SharePoint Online | ODBC SharePoint Online Driver | SSIS SharePoint Online Source | SSIS SharePoint Online Destination
Connect SharePoint Online in Tableau
Load SharePoint Online in Tableau
Load SharePoint Online data in Tableau
Read SharePoint Online data in Tableau
SharePoint Online API Call in Tableau