SharePoint Input Tool - SharePoint Online

Hi,

We have had workflows with connections to a SharePoint List running for year's on our Prod Server because we have used SharePoint 2010 and we can utilize our Batch ID and password to gain access to the list.

We are moving to SharePoint Online and the SharePoint Input Tool's configuration asks for an email address... My BatchID doesnt have an email address, so I am at a loss for what to do.

Anyone know how to gain access to a list like this with a BatchID in use?

Thanks for any assistance that can be provided.

Dan

Input

Sharepoint

Accepted answers

JakeS

Hi @DanielG

There are only two options for connection to SharePoint online - using an email address, or using app creds:

Can you make app credentials for your process?

sean_bolte_dup_544

@DanielG

Here is what you do to obtain SharePoint credentials.

1) You need to be elevated to "Owner" status for the site in question.

2) Utilizing a browser, go to your tenant (your company) SharePoint site with the following appended to the end: "_layouts/15/appregnew.aspx"

Example - https://(your company name).sharepoint.com/sites/sitename(your SharePoint groups name)/_layouts/15/appregnew.aspx

3. Click on "Generate" for both Client ID and Client secret as shown below in step 5

4. Give title as shown in the screenshot.

5. Give "www.localhost.com" for App domain and " https://www.localhost.com" for Redirect URI as shown in the screenshot..

6. Click on create as shown in the screenshot.

7. Add _layouts/15/appinv.aspx at the end of URL in yuor browser

Example - https://(your company name).sharepoint.com/sites/sitename(your SharePoint groups name)/_layouts/15/appinv.aspx

8. Give App id which is already generated and click on look up as shown in the screenshot.

9. Repeat 4 and 5 steps for the remaining fields.

10. In Permission box, enter the below code

11. Click on create button.

12. In next dialog, Click on trust it as shown in the screenshot.

13. Now with generated Client Id and Client secret , one can connect to SharePoint Online through app only authentication.

These are what you have to use to be able to pull from a Sharepoint365 list. Make sure that you are pulling the correct View - there are often several ones that have been created by users of the site !!

All comments

JakeS

Hi @DanielG

There are only two options for connection to SharePoint online - using an email address, or using app creds:

Can you make app credentials for your process?

DanielG

I am not sure if I have the capability to create app credentials. I have never had to do that before, so I will need to work with some of our more technical folks to figure that out.

Thanks for the information!!!

oracleoftemple

I did the process listed above to obtain my SharePoint credentials, but when I tried to use them with a SharePoint List Input tool, I got the following error: "Error encountered: Token request failed." The error popped up after I pasted the URL, Client ID, and Secret Key. I wasn't able to enter List or View after the error appeared. Both drop downs were empty. Can anyone help me with this?

salbol1

You have been granted SCA (Site Collection Admin) rights by the tenant IT admin team? That, and you are also utilizing "App Only Authentication (Online/Office 365, 2016)" for the tool? You know that the Oauth creds you are attempting to submit are not valid if no List/Views populate after entering those datapoints.

oracleoftemple

No, I'm afraid I didn't know that. All of that is so far over my head it's clear that I'm trying to do more than I'm qualified to do.

salbol1

I'd suggest searching your Outlook contacts for "O365" or other such PDL's if you're in a corp environment and you're not aware of the IT teams that are responsible for your SharePoint online tenant ("yourcorpsname.sharepoint.com/sites/your_tenant_site_where_your_lists_are/one_of_your_list_names"). Most corps are setup so that SCA rights would be granted to what your particular sub-tenant address is (yourcorpsname.sharepoint.com/sites/your_tenant_site_where_your_lists_are/) this would be what they'd grant for since it would be very unusual/unwise to grant a user access to the whole tenant -- you'd be able to obtain data from any other part of the company if that was the case. There is also quite a bit that can be gleaned from using powershell office 365 commandlets, and there's quite a bit on MS and other sites that can serve as demo's for how to install the needed cmdlets and drivers to obtain info in that regard. Something like this can be a primer, but there is a ton of info out there to do this --https://www.virtualgyanis.com/post/office365-how-to-connect-office365-tenant-via-powershell You have to remember that the backend Azure where SP365 data is stored is just a database, these functions are the equivalent to obtaining dbase access:

1) Owner on a sub-tenant (NOT Admin, you have to be elevated to an Owner status) = Being placed into an identity mananger "group"

2) SCA rights = being granted the permissions to obtain OAuth credentials from the Azure db

3) your sub-tenant = the particular schema of the db holding data pertinent to your Lists/View you're trying to access

4) OAuth creds = they act just like your active directory creds if you were given access to a SQL db that used AD for authentication

Once you find the IT teams that are your admins, they'll likely know, or know how to find out from the MS team supporting your O365 instance.

smccaghren

This works perfectly for Lists - Thank you

What about for Files? Ie Excel files that are stored on Sharepoint that I need to pull in. I Tried Sharepoint Input - Files - Provide Client ID and Secrete and Tenant ID - so the piece that I'm missing is tenant ID.

Any help appreciated

DanielG

@smccaghren -- That is provided internally by your IT/SharePoint admins. (not 100% sure which). It is not something the community will be able to provide to you.

salbol1

@smccaghren

This connector is designed for Lists, but you can always utilize python for accessing the O365 libraries:

Package.installPackages(['Office365-REST-Python-Client','office365'])

This snippet below pulls in a URL and a set of Document Loads to a SP site that contain .zip files -

This is the python code for accessing the SP site documents in the identified sub folder (shown as 1 down in the bottom snippet)

from ayx import Alteryx
import numpy as np
import pandas as pd
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.client_credential import ClientCredential
from office365.sharepoint.files.file import File

InputDf = Alteryx.read("#1")

SharePointURL = np.array2string(InputDf['SharePointURL'].values[:1])
SharePointURL = SharePointURL.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")
SPFolder = np.array2string(InputDf['SPFolder'].values[:1])
SPFolder = SPFolder.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

#SharePointURL = "https://YOUR_COMPANY.sharepoint.com/sites/Some .zip Reports/"

site_url = "https://YOUR_COMPANY.sharepoint.com/sites/A COMPANY SUBTENANT SITE/A SUB SCHEMA OF THE TENANT"
sp_list = "SP_SharePoint_Credentials" ##(This is a SharePoint list where we house URL's, Client ID's and Secret Keys)
ClientId = "xxxxxxxxxxxxxxxxxxxxxxxxx" ##(Use your real ones)
ClientSecret = "yyyyyyyyyyyyyyyyyyyyyyyyyyy" ##(Use your real ones)
credentials = ClientCredential(ClientId, ClientSecret)
SPClientID = ""
SPSecKey = ""

###Connecting to Sharepoint Mapping list
ctx = ClientContext(site_url).with_credentials(credentials)
sp_lists = ctx.web.lists
s_list = sp_lists.get_by_title(sp_list)
l_items = s_list.get_items()
ctx.load(l_items)
ctx.execute_query()

###Looking for credentials
for item in l_items:
if item.properties['SharePointURL'] == SharePointURL:
SPClientID = item.properties['Client_ID']
SPSecKey = item.properties['SecretKey']
print("Credentials Found\n\n")

del ctx

data = {'SharePointURL':[SharePointURL],
'SPFolder':[SPFolder],
'Client_ID':[SPClientID],
'SecretKey':[SPSecKey]}
OutDf = pd.DataFrame(data)

#Writing Output
Alteryx.write(OutDf,1)

We then append to this output DF, the name of file we are looking for in the documents folder. The variable is 'FileName' and indicates we are looking for files with say, PM_Reporting in it's name (shown as 2 in the snippet at the bottom):

from ayx import Alteryx
import numpy as np
import pandas as pd
import datetime, os
from datetime import date, datetime
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.client_credential import ClientCredential
from office365.sharepoint.files.file import File
InputDf = Alteryx.read("#1")

FilesPath = np.array2string(InputDf['LocalFolder'].values[:1])
FilesPath = FilesPath.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

SharePointURL = np.array2string(InputDf['SharePointURL'].values[:1])
SharePointURL = SharePointURL.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

SPFolder = np.array2string(InputDf['SPFolder'].values[:1])
SPFolder = SPFolder.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

FileName = np.array2string(InputDf['FileName'].values[:1])
FileName = FileName.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

ClientId = np.array2string(InputDf['Client_ID'].values[:1])
ClientId = ClientId.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

ClientSecret = np.array2string(InputDf['SecretKey'].values[:1])
ClientSecret = ClientSecret.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

credentials = ClientCredential(ClientId, ClientSecret)

TodayD = date.today().strftime("%Y-%m-%d")

print(SharePointURL)
print(SPFolder)
print(SharePointURL.replace("https://YOUR_COMPANY.sharepoint.com","") + SPFolder + "/" )
################Connecting to SP
ctx = ClientContext(SharePointURL[:-1]).with_credentials(credentials)
WebElem = dir(ctx.web)
SpWeb = ctx.web
files = ctx.web.get_folder_by_server_relative_url(SharePointURL.replace("https://YOUR_COMPANY.sharepoint.com","") + SPFolder + "/" ).files
ctx.load(files).execute_query()
file_list = []
for file in files:
if file.name.endswith(".zip") and file.time_last_modified[:10] == TodayD and FileName.lower() in file.name.lower():
file_list.append(file.name)

################Downloading Files
for i in file_list:
if i.endswith(".zip"):
print("Downloading " + i + "\n\n")
if os.path.exists(os.path.join(FilesPath, i)):
os.remove(os.path.join(FilesPath, i))
response = File.open_binary(ctx, SharePointURL.replace("https://YOUR_COMPANY.sharepoint.com","") + SPFolder + "/" + i)
with open(os.path.join(FilesPath, i), "wb") as local_file:
local_file.write(response.content)

del ctx

#Writing Output
Alteryx.write(InputDf,1)

This portion then takes the csv files that are contained within the zip files, and loads them to a local directory path, also identified in the Text input tool shown here (A), and appended with the first output dataframe (B):

(from the python tool 3)

from ayx import Alteryx
import os, datetime
import pandas as pd
from zipfile import ZipFile
from datetime import datetime, date
import numpy as np

InputDf = Alteryx.read("#1")

FilesPath = np.array2string(InputDf['LocalFolder'].values[:1])
FilesPath = FilesPath.replace("\\\\","\\").replace("'","").replace("[","").replace("]","")

# Validate file date function

def ModifiedToday(FileName):
lastmodified = datetime.fromtimestamp(os.stat(FileName).st_mtime).strftime("%m-%d-%y")
TodayD = date.today().strftime("%m-%d-%y")
if lastmodified == TodayD:
return True
else:
return False

##############################Script Start
#list files in folder:
print(FilesPath)
DirFiles = os.listdir(FilesPath)
#Looping through dir validating files and retrieving grid files to write grid load sql
print("searching updated Zips and unzipping\n\n")
for i in DirFiles:
if ModifiedToday(os.path.join(FilesPath,i)) and i.endswith(".zip"):
with ZipFile(os.path.join(FilesPath,i), 'r') as zipObj:
for info in zipObj.infolist():
if info.filename.endswith(".csv"):
zipObj.extract(info.filename, FilesPath)
if os.path.exists(os.path.join(FilesPath,i.replace(".zip", ".csv"))):
os.remove(os.path.join(FilesPath,i.replace(".zip", ".csv")))
os.rename(os.path.join(FilesPath,info.filename),os.path.join(FilesPath,i.replace(".zip", ".csv")))

#Writing Output
Alteryx.write(InputDf,1)
print("All CSV Files in Path have been unzipped\n\n")

We have a team member that has been dubbed the 'king of SP python" that allows us to make great use of the REST and O365 client libraries.

DanielG

There is also a SharePoint File Input Tool which should be able to handle it in one shot. Would still need the Client ID, Secret and Tenant though.

This post has evolved a whole lot since the original question I asked 3 years ago... 😂

DanielG

@Magneto_ I have since changed jobs, but I think we were able to find the corresponding email address for that BatchID and then granted it permissions to the SharePoint list. However, I dont recall the specifics at this point. Sorry. It will probably require some work with IT to find batchID's email or the creation of a batchID that has an email address to start. Make sure you work with IT to avoid violating any rules on data access (depending on what type of data work you are doing ie PPI data.)

DanielG

@NatSnook -- sorry. I am a bit removed from Alteryx these days but I do know the permissioning is the hardest part to figure out initially. Ideally you are using SharePoint Online these days, as I think that is easier to deal with. Set up your DCMs for SharePoint input and output then use those in setting up your workflows. I believe allot has changed with SP and the SP Alteryx tools in the 5 years since this was originally posted, so I am not sure what is relevant or not any more. I'd consider asking a new question in community to get the most up to date information from people who are more actively using it. I dont know the other person you linked on your message, but it looks like it has been 4 years since they logged in.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703