def __init__(self, url, username=None, password=None):
self.root_url = url[:url.index(".com") + 4]
self.base_url = url
if (username is not None) and (password is not None):
self.sharepoint_client = sharepy.connect(self.root_url,
username=username,
password=password)
else:
self.sharepoint_client = sharepy.connect(self.root_url)
def file_upload(website, site, relpath, filename, inputfile, username, password):
"""
Function to upload single file from local machine to Sharepoint
param website (str) : Sharepoint link. Eg, https://abc.sharepoint.com
param site (str) : Sharepoint site/teams names, Eg site/myfolder
param relpath (str) : Relative path to upload folder in Sharepoint
param filename (str) : Filename of the upload
param inputfile (str) : File path and extension of the file to uploaded
param username (str) : Username to login to Sharepoint
param password (str) : Password to login to Sharepoint
"""
sess = sharepy.connect(site=website, username=username, password=password)
headers = {
"accept": "application/json;odata=verbose",
"content-type": "application/x-www-urlencoded; charset=UTF-8",
}
with open(inputfile, "rb") as read_file:
content = read_file.read()
# Upload link
uploadpath = (
f"{website}/{site}/_api/web/GetFolderByServerRelativeUrl"
f"('/{relpath}')/Files/add(url='{filename}',overwrite=true)"
)
sess.post(uploadpath, data=content, headers=headers)
sess.close()
def syncSPList():
s = sharepy.connect('https://kwaustin.sharepoint.com', "Someuser",
"Somepass")
r = s.get(
"https://kwaustin.sharepoint.com/am/_api/web/lists/GetByTitle('Agents')/Items?$select=Real_x0020_Estate_x0020_License_,FullName,CellPhone,Top20Percent&$filter=New_x0020_Agent_x0020_Status eq 'Active Agent' or New_x0020_Agent_x0020_Status eq 'New Agent'&$skiptoken=Paged=TRNE&$top=1000",
headers={"accept": "application/json; odata=nometadata"})
splist = r.json()
spdict = splist['value']
Ids = [i['Real_x0020_Estate_x0020_License_'] for i in spdict]
FullName = [i['FullName'] for i in spdict]
MobileNumber = [i['CellPhone'] for i in spdict]
spdata = list(zip(
Ids,
FullName,
MobileNumber,
))
for s in spdata:
agentcol.find_one_and_update({'_id': s[0]}, {
'$set': {
'agentname': s[1],
'mobilenumber': s[2],
'production': 'lower80',
}
},
upsert=True)
return
def downloadlatestwb():
s = sharepy.connect("https://ltsystems.sharepoint.com",
username=config1.username,
password=config1.password)
file = getfile(
s, 'Fct_Check_Market_Responses_LTEC.xlsx',
'https://ltsystems.sharepoint.com/sites/PowerBIAdmin45/Shared%20Documents/Source_Data_Files/Fct_Check_Market_Responses_LTEC.xlsx'
)
return s, file
def load_or_create_sp_session(sp_url="genomicsenglandltd.sharepoint.com",
username=None,
password=None):
"""
check if a session file already exists
if it does, load it, if not create one
the resulting sp-session.pkl files are portable, to create one you'll
need to create an app password to get through 2FA. See
https://support.office.com/en-gb/article/create-an-app-password-for-office-365-3e7c860f-bda4-4441-a618-b53953ee1183
username for sign-in is your full GEL email, e.g.
[email protected]
the sessions will auto-refresh
"""
if os.path.isfile("sp-session.pkl"):
s = sharepy.load()
else:
if username and password:
s = sharepy.connect(sp_url, username=username, password=password)
else:
s = sharepy.connect(sp_url)
s.save()
return s
def __init__(self, config):
self.sharepoint_root = None
self.sharepoint_tenant = None
self.sharepoint_url = None
self.sharepoint_origin = None
if config.get('auth_type') == DSSConstants.AUTH_OAUTH:
login_details = config.get('sharepoint_oauth')
self.assert_login_details(DSSConstants.OAUTH_DETAILS,
login_details)
self.setup_login_details(login_details)
self.setup_sharepoint_online_url(login_details)
self.sharepoint_access_token = login_details['sharepoint_oauth']
self.session = SharePointSession(
None,
None,
self.sharepoint_tenant,
self.sharepoint_site,
sharepoint_access_token=self.sharepoint_access_token)
elif config.get('auth_type') == DSSConstants.AUTH_LOGIN:
login_details = config.get('sharepoint_sharepy')
self.assert_login_details(DSSConstants.LOGIN_DETAILS,
login_details)
self.setup_login_details(login_details)
username = login_details['sharepoint_username']
password = login_details['sharepoint_password']
self.assert_email_address(username)
self.setup_sharepoint_online_url(login_details)
self.session = sharepy.connect(self.sharepoint_url,
username=username,
password=password)
elif config.get('auth_type') == DSSConstants.AUTH_SITE_APP:
login_details = config.get('site_app_permissions')
self.assert_login_details(DSSConstants.SITE_APP_DETAILS,
login_details)
self.setup_sharepoint_online_url(login_details)
self.setup_login_details(login_details)
self.tenant_id = login_details.get("tenant_id")
self.client_secret = login_details.get("client_secret")
self.client_id = login_details.get("client_id")
self.sharepoint_tenant = login_details.get('sharepoint_tenant')
self.sharepoint_access_token = self.get_site_app_access_token()
self.session = SharePointSession(
None,
None,
self.sharepoint_tenant,
self.sharepoint_site,
sharepoint_access_token=self.sharepoint_access_token)
else:
raise Exception("The type of authentication is not selected")
self.sharepoint_list_title = config.get("sharepoint_list_title")
def write_bands():
s = sharepy.connect("downingcollege.sharepoint.com", sharepoint_user,
sharepoint_password)
r = s.get(
"https://downingcollege.sharepoint.com/sites/RoomsBrowserJCR/_api/web/lists/GetByTitle('RoomsData')/Items?$top=4000&select=Room_x0020_Identifier,New_x0020_Room_x0020_Band"
)
context_request = s.post(
"https://downingcollege.sharepoint.com/sites/RoomsBrowserJCR/_api/contextinfo"
)
update_headers = {
"Accept":
"application/json; odata=verbose",
"Content-Type":
"application/json; odata=verbose",
"odata":
"verbose",
"X-RequestForceAuthentication":
"true",
"X-RequestDigest":
context_request.json()['d']['GetContextWebInformation']
['FormDigestValue'],
"IF-MATCH":
"*",
"X-HTTP-Method":
"MERGE"
}
print(context_request.json()['d']['GetContextWebInformation']
['FormDigestValue'])
for room in (r.json()['d'])['results']:
name = room['Room_x0020_Identifier']
identifier = name.split('(', 1)[-1].replace(')', '')
try:
r = Room.objects.get(identifier=identifier)
if r.type != 4:
uri = (room['__metadata'])['uri']
s.post(uri,
json={
"__metadata": {
"type": "SP.Data.RoomsListItem"
},
'New_x0020_Room_x0020_Band': r.new_band.band_name
},
headers=update_headers)
except Room.DoesNotExist:
print("Couldn't find room", identifier)
def get_folder_list(website, site, library, relpath, username, password):
"""
Function to get list of all the files present in a folder in Sharepoint.
The API call has a limit of only 5000 files. Therefore, this activity has
to be done recursively. After first call, the next call is made after
'fileid'.
param website (str) : Sharepoint link. Eg, https://abc.sharepoint.com
param site (str) : Sharepoint site/teams names, Eg site/myfolder
param library (str) : Sharepoint Library. Refer SP sidebar on left.
param relpath (str) : Relative path to upload folder in Sharepoint
param username (str) : Username to login to Sharepoint
param password (str) : Password to login to Sharepoint
"""
sess = sharepy.connect(site=website, username=username, password=password)
# Count of JSON returns
list1 = []
condt = True
fileid = ""
while condt:
link = (
f"{site}/_api/web/lists/getbytitle('{library}')/items"
f"?$select=FileLeafRef,FileRef,Id&top={5000}&%24"
f"skiptoken=Paged%3DTRUE%26p_ID%{fileid}"
)
files = sess.get(link).json()["d"]["results"]
list1 = list1 + files
# Get the ID of the last element in the list
# The next loop will continue from this Id onwards
fileid = files[-1]["Id"]
if len(files) != 5000:
condt = False
output_list = []
for file in list1:
fullpath = file["FileRef"]
if (fullpath.startswith(relpath)) & (fullpath != relpath):
output_list.append(file["FileRef"])
# DEPRECATED:
# relpath = (f"{site}/Shared Documents/RPA_Templates/I&A Projects/NAMETRUB/RUB"
# f"/Volume Report/{previous_qtrnm}")
# r = ses.get(f"{website}/{site}/{api_call}('/{relpath}')/Files")
# data = r.json()
# file_date = [(elm['Name'], elm["TimeCreated"])for elm in data["d"]["results"]]
# df_files = pd.DataFrame(file_date, columns=["File_name", "Created_date"])
return output_list
def file_download(website, splink, username, password, filepath):
"""
Function to download a single file from Sharepoint location
param website (str) : Website link. Eg, https://abc.sharepoint.com
param splink (str) : Sharepoint file link to be downloaded
param username (str) : Username to login to Sharepoint
param password (str) : Password to login to Sharepoint
param filepath (str) : Local path to download the file
"""
sess = sharepy.connect(site=website, username=username, password=password)
file = sess.getfile(splink, filename=filepath)
if file.status_code == 200:
print("File downloaded successfully.")
else:
print("Unable to download file from Sharepoint.")
print(f"Error Code: {file.status_code}")
sess.close()
def UploadToSharepoint(event=None, context=None):
SPUrl = os.environ['SPUrl']
siteName = os.environ['siteName']
libraryName = os.environ['libraryName']
username = os.environ['username']
password = os.environ['password']
os.chdir("/tmp/")
s = sharepy.connect(SPUrl,username,password)
s.save()
headers = {"accept": "application/json;odata=verbose",
"content-type": "application/x-www-urlencoded; charset=UTF-8"}
fileToUpload = "/var/task/uploadfile.txt"
file_name=os.path.basename(fileToUpload)
with open(fileToUpload, 'rb') as read_file:
content = read_file.read()
p = s.post(f"{SPUrl}/sites/{siteName}/_api/web/GetFolderByServerRelativeUrl('{libraryName}')/Files/add(url='{file_name}',overwrite=true)", data=content, headers=headers)
print("File Successfully Uploaded!")
return(file_name)
def df_from_sharepoint_masterdata(list_name, site_name='VyMasterdata'):
### Connect til Vy Sharepoint
s = sharepy.connect("nsbas.sharepoint.com", env('SHAREPOINT_ONLINE_USERNAME'), env('SHAREPOINT_ONLINE_PASSWORD'))
### Henter først lesbare navn for kolonnene i Sharepoint fra et annet API (Sharepoints egne navn er noe tricky)
response_metadata = s.get("https://nsbas.sharepoint.com/sites/{}/_api/web/lists/getbytitle('{}')/fields".format(site_name, list_name))
if not "d" in response_metadata.json().keys():
raise Exception("Could not load list from Sharepoint! Details: " + str(response_metadata.json()))
friendly_names = {x['InternalName']: x['Title'].lower().replace(" ","_")
for x in response_metadata.json()['d']['results']
if x['CanBeDeleted'] or x['InternalName'] in ['Title']}
### Henter så dataen
resp = s.get("https://nsbas.sharepoint.com/sites/{}/_api/web/lists/getbytitle('{}')/items".format(site_name, list_name)).json()
res = resp
### Itererer over alle sidene for å få alle elementene
while resp.get('d').get('__next'):
resp = s.get(resp.get('d').get('__next')).json()
res['d']['results'] = res['d']['results'] + resp.get('d').get('results')
### PySpark liker bedre å lese json fra fil enn fra string. Lagrer til fil og leser derifra
filename = "/tmp/{}_{}.json".format(list_name, time.time())
dbutils.fs.put(filename, json.dumps(res), True)
df = spark.read.json(filename)
### Plasserer JSON-objektene til riktige kolonner
df = df.select('d.*').select(explode('results').alias('results')).select('results.*')
### Gir nytt navn til kolonnene vi vil beholde, og dropper metadata
for column in df.schema:
if column.name in friendly_names:
df = df.withColumnRenamed(column.name,friendly_names[column.name])
else:
df = df.drop(column.name)
return df
def Sharepoint_Login(username, password):
return sp.connect("english1com.sharepoint.com",
username=username,
password=password)
def updateUserData(s, getHeaderData):
# get all records & roll stuff
strListContentURI = strListDataURI
while strListContentURI:
r1 = s.get(strListContentURI)
jsonReply = json.loads(r1.text)
jsonListContent = jsonReply['d']
strListContentURI = jsonListContent.get("__next")
print("Next URL is now %s" % strListContentURI)
i = 0
for listRecord in jsonListContent['results']:
i = i + 1
if (i % 100) is 0:
print("On cycle %s, refreshing access token" % i)
postData = {
"grant_type": "client_credentials",
"client_id": strClientID,
"client_secret": strClientSecret,
"scope": "https://graph.microsoft.com/.default"
}
r = requests.post(strGraphAuthURL, data=postData)
strJSONResponse = r.text
if len(strJSONResponse) > 5:
jsonResponse = json.loads(strJSONResponse)
strAccessToken = jsonResponse['access_token']
getHeaderData = {
"Authorization": "Bearer " + strAccessToken
}
print("getHeaderData is %s" % getHeaderData)
connectionSP = sharepy.connect(strConnectURL, strUID, strPass)
iFoundItemID = listRecord['ID']
strItemURI = ("%s(%s)" % (strListDataURI, iFoundItemID))
strUserUID = listRecord.get("Title")
if strUserUID is not None:
r2 = requests.get(
"https://graph.microsoft.com/v1.0/users/%[email protected]/?$select=displayName,Department"
% strUserUID,
headers=getHeaderData)
strUserData = r2.text
jsonUserData = json.loads(strUserData)
if len(jsonUserData) > 1:
strDepartment = jsonUserData.get("department")
r3 = requests.get(
"https://graph.microsoft.com/v1.0/users/%[email protected]/manager"
% strUserUID,
headers=getHeaderData)
strManagerData = r3.text
jsonManagerData = json.loads(strManagerData)
print("%s in %s reports to %s" %
(strUserUID, strDepartment,
jsonManagerData.get("userPrincipalName")))
dictRecordPatch = {
"__metadata": {
"type": strItemTypeName
},
'department': strDepartment,
'manager': jsonManagerData.get("userPrincipalName"),
'Active': 1
}
updateRecord(s, strItemURI, dictRecordPatch)
else:
print("%s is inactive in ADFS" % strUserUID)
dictRecordPatch = {
"__metadata": {
"type": strItemTypeName
},
'Active': 0
}
updateRecord(s, strItemURI, dictRecordPatch)
print(
"Completed data maintenance -- user department and managers are updated"
)
"Completed data maintenance -- user department and managers are updated"
)
################################################################################
# End of functions
################################################################################
f = Fernet(strKey)
strUID = f.decrypt(strUsername)
strUID = strUID.decode("utf-8")
strPass = f.decrypt(strPassword)
strPass = strPass.decode("utf-8")
connectionSP = sharepy.connect(strConnectURL, strUID, strPass)
## Get ListItemEntityTypeFullName from list
r = connectionSP.get(strListInfoURI)
jsonReply = json.loads(r.text)
strItemTypeName = jsonReply['d']['ListItemEntityTypeFullName']
postData = {
"grant_type": "client_credentials",
"client_id": strClientID,
"client_secret": strClientSecret,
"scope": "https://graph.microsoft.com/.default"
}
r = requests.post(strGraphAuthURL, data=postData)
import sharepy
import requests
import sys
import shutil
import os
from requests_toolbelt import MultipartEncoder
# edit these to change the upload destination
siteURL = "aerisllc.sharepoint.com"
spfoldername = "Shared%20Documents/APITest/"
filename = sys.argv[1]
localFileSize = os.stat(filename).st_size
s = sharepy.connect(siteURL)
headers = {
"accept": "application/json;odata=verbose",
"content-type": "multipart/form-data",
"content-length": str(localFileSize),
"connection": "keep-alive",
"X-RequestDigest": s._redigest(),
"X-FORMS_BASED_AUTH_ACCEPTED": "f",
"If-Match": "*",
"Authorization": "Bearer " + s._redigest(),
}
with f as open(filename, "rb"):
m = MultipartEncoder(fields={(filename, f)})
p = s.post(
def getSharepointSession():
"This creates session with sharepoint using SAML authentication"
sess = sharepy.connect(config.spo_link, config.spo_user, config.spo_pwd)
return sess
def __init__(self, username, password, domain):
self.domain = domain
self.sharepoint = sharepy.connect(self.domain,
username=username,
password=password)
def get_data():
s = sharepy.connect("downingcollege.sharepoint.com", sharepoint_user,
sharepoint_password)
r = s.get(
"https://downingcollege.sharepoint.com/sites/RoomsBrowserJCR/_api/web/lists/GetByTitle('%s')/Items?$top=4000"
% sharepoint_listname)
values_as_html_links = []
errors = []
count = 0
successes = 0
for room in (r.json()['d'])['results']:
values_as_html_links.append(
room['FieldValuesAsHtml']['__deferred']['uri'])
count += 1
print("Found " + str(count) + " rooms.\n")
current_room = ""
for link in values_as_html_links:
try:
request = s.get(link)
soup = (request.json()['d'])
# Parsing room name.
name = soup['Room_x005f_x0020_x005f_Identifier']
current_room = name
identifier = name.split('(', 1)[-1].replace(')', '')
staircase_identifier = ''
if identifier[len(identifier) - 1].isdigit():
staircase_identifier = identifier[0:len(identifier) - 2]
number = identifier[len(identifier) - 2:len(identifier)]
if number[0] == '0':
number = number[1:len(number)]
else:
staircase_identifier = identifier[0:len(identifier) - 3]
number = identifier[len(identifier) - 3:len(identifier)]
if number[0] == '0':
number = number[1:len(number)]
disabled_facilities = soup['DisabledFacilities'] == "Yes"
floor_str = soup['Floor']
floor = int(floor_str) + 1
size = float(soup['RoomSize'])
is_double_bed = soup['DoubleBed'] == "Yes"
is_ensuite = soup['Ensuite'] == "Yes"
year_last_renovated_bathroom = int(
soup['Yearlastrenovatedbathroom'].replace(',', ''))
year_last_renovated_kitchen = int(
soup['YearlastrenovatedKitchen'].replace(',', ''))
year_last_renovated_room = int(
soup['Yearlastrenovatedroom'].replace(',', ''))
bathroom_sharing = int(soup[
'Number_x005f_x0020_x005f_of_x005f_x0020_x005f_people_x005f_x00']
)
contract_length = int(soup['ContractLength'])
facing_court = soup[
'Facing_x005f_x0020_x005f_Court'] == "Yes" and not identifier.__contains__(
'LR')
facing_lensfield = soup[
'Facing_x005f_x0020_x005f_Lensfield_x005f_x0020_x005f_Roa'] == "Yes"
in_ballot = soup['IntheBallot'] == "Yes"
occupancy = soup['Occupancy']
is_battcock = identifier.__contains__("BL")
# If room already exists.
try:
room = Room.objects.get(identifier=identifier)
print(identifier)
if floor != room.floor:
print("Old floor: %s, New floor: %s" % (room.floor, floor))
if size != room.size:
print("Old size: %s, New size: %s" % (room.size, size))
if is_double_bed != room.is_double_bed:
print("Old double bed: %s, New double bed: %s" %
(room.is_double_bed, is_double_bed))
if is_ensuite != room.is_ensuite:
print("Old ensuite: %s, New ensuite: %s" %
(room.is_ensuite, is_ensuite))
if year_last_renovated_kitchen != room.kitchen_last_renovated:
print("Old kitchen: %s, New kitchen: %s" %
(room.kitchen_last_renovated,
year_last_renovated_kitchen))
if year_last_renovated_bathroom != room.bathroom_last_renovated:
print("Old bathroom: %s, New bathroom: %s" %
(room.bathroom_last_renovated,
year_last_renovated_bathroom))
if year_last_renovated_room != room.room_last_renovated:
print("Old room: %s, New room: %s" %
(room.room_last_renovated, year_last_renovated_room))
if bathroom_sharing != room.bathroom_sharing:
print(
"Old bathroom sharing: %s, New bathroom sharing: %s" %
(room.bathroom_sharing, bathroom_sharing))
if facing_lensfield != room.faces_lensfield:
print("Old Lensfield: %s, New Lensfield: %s" %
(room.faces_lensfield, facing_lensfield))
if facing_court != room.faces_court:
print("Old court: %s, New court: %s" %
(room.faces_court, facing_court))
if occupancy == 'UG':
if in_ballot and not room.type == 2:
print("Should be in the ballot")
elif not in_ballot and not room.type == 3:
print("Shouldn't be in the ballot")
print()
except Room.DoesNotExist:
room = Room()
print("Room does not exist", identifier)
room.identifier = identifier
room.room_number = number
room.floor = floor
room.has_disabled_facilities = disabled_facilities
room.size = size
room.is_double_bed = is_double_bed
room.is_ensuite = is_ensuite
room.bathroom_last_renovated = year_last_renovated_bathroom
room.room_last_renovated = year_last_renovated_room
room.kitchen_last_renovated = year_last_renovated_kitchen
if not is_battcock:
room.faces_court = facing_court
room.faces_lensfield = facing_lensfield
if contract_length > 0:
room.contract_length = contract_length
if room.sort_number is None:
room.sort_number = number
room.bathroom_sharing = min(bathroom_sharing, 5)
if occupancy == 'UG':
room.type = 2 if in_ballot else 3
elif occupancy == 'Fresher':
room.type = 1
else:
room.type = 4
try:
staircase = Staircase.objects.get(
identifier=staircase_identifier)
except Staircase.DoesNotExist:
staircase = Staircase()
staircase.identifier = staircase_identifier
staircase.name = "ToBeNamed"
staircase.contract_length = 0
staircase.save()
room.staircase = staircase
room.save()
successes += 1
except Exception as e:
errors.append(current_room + " - " + str(e))
print("=== IMPORT REPORT ===")
print("Total Rooms:", str(count))
print("Succeeded:", str(successes))
print("Failed:", str(len(errors)))
print()
for error in errors:
print(error)
print()
import datetime
from requests_toolbelt.utils import dump
import json
from config import strListDataURI, strContextURL, strConnectURL, strUsername, strPassword, strFilePath, strListInfoURI
import sharepy
#https://github.com/JonathanHolvey/sharepy
strContentType = "application/json;odata=verbose"
dateNow = datetime.datetime.now().replace(microsecond=0).isoformat()
s = sharepy.connect(strConnectURL, strUsername, strPassword)
## Get ListItemEntityTypeFullName from list
r = s.get(strListInfoURI)
jsonReply = json.loads(r.text)
strListItemEntityTypeFullName = jsonReply['d']['ListItemEntityTypeFullName']
r = s.post(strContextURL)
# Get digest value for use in POST
jsonDigestRaw = json.loads(r.text)
jsonDigestValue = jsonDigestRaw['d']['GetContextWebInformation'][
'FormDigestValue']
iCounter = 1
with open(strFilePath) as f:
for strRecord in f:
strRecordList = strRecord.split("\t")
from pprint import pprint
import sharepy
import json
import os
import urllib
HOST = os.environ.get('GSPR_HOST', '')
s = sharepy.connect(f"https://{HOST}", os.environ.get('GSPR_USER', ''),
os.environ.get('GSPR_PASS', ''))
def caml(site, listname):
API = f"https://{HOST}/sites/{site}/_api/web"
LISTAPI = f"{API}/lists/getbytitle('{listname}')"
typefilter = """<Eq><FieldRef Name="FSObjType" /><Value Type="Integer">1</Value></Eq>"""
vf = """<ViewFields>
<FieldRef Name="ID" />
<FieldRef Name="FileLeafRef" />
<FieldRef Name="FileRef" />
</ViewFields>"""
# vf = ""
rl = """<RowLimit>100</RowLimit>"""
rl = ""
responses = []
idshift = 500
lastid = 0
import requests
from requests.auth import HTTPBasicAuth
import os
import pandas as pd
from os import listdir
from os.path import isfile, join
import argparse
import urllib.parse
import datetime
import math
# (1) Authenticate
# #The below code Authenticate the user by passing the user email id and password
#replace example.sharepoint.com with your sharepoint website followed by user name and password
s = sharepy.connect("https://example.sharepoint.com",\
username='******', password='******')
def create_dir(dirPath):
if not os.path.exists(dirPath):
os.makedirs(dirPath)
d = datetime.date.today()
#d=d+timedelta(days=23)
c_year = str(d.year)
#print('Year :'+c_year)
Path = 'D:\new\docs\\' + c_year #the documents will be downloaded to the path described here, for instance D:\new\docs
create_dir(Path)
Q = math.ceil(d.month / 3.)
c_qtr = 'Q ' + str(Q)
def downloadlatestwb():
t = sharepy.connect("https://ltsystems.sharepoint.com", username = config1.username, password = config1.password)
t.getfile('https://ltsystems.sharepoint.com/sites/PowerBIAdmin45/Shared%20Documents/Source_Data_Files/Fund Control Document Type Codes.xlsx'
,filename="Fund Control Document Type Codes.xlsx")
def __init__(self, site_url, username, password):
self.session = sharepy.connect(site_url, username, password)
self.site_url = site_url
import sharepy
from sharepy import connect
from sharepy import SharePointSession
import os
import pandas as pd
server='https://group.sharepoint.com'
user='******'
password='******'
data_file = "Reporting_ETL.xlsx"
# Copy/Paste file link from sharepoint
site = "https://group.sharepoint.com/sites/WORKGROUP/Shared%20Documents/5.%20MASTERDATA%20powerbi/Reporting_ETL.xlsx"
s = sharepy.connect(server,user,password)
# s.save()
def download_file():
data_file = "Reporting_ETL.xlsx"
# Download file to same folder as python script.
r = s.getfile(site,\
filename = data_file)
print("file downloaded")
# Dataframe to send to script.py (easier with Pandas!)
df_config = pd.read_excel(data_file, sheet_name="Config")
df_config = df_config.astype(str)
return df_config
def _authenticate(self, url, username=None, password=None):
self.session = sharepy.connect(site=url,
username=username,
password=password)
def connectsp():
s = sharepy.connect("https://ltsystems.sharepoint.com", username = config1.username, password = config1.password)
return s
import sharepy
# (1) Authenticate
s = sharepy.connect(
site=
'https://alterramtnco.sharepoint.com/sites/AlterraDataTeam/MasterData/Forms/AllItems.aspx?id=%2Fsites%2FAlterraDataTeam%2FMasterData%2Fdev',
username='******',
password='******')
r = s.get(
'https://alterramtnco.sharepoint.com/sites/AlterraDataTeam/MasterData/Forms/AllItems.aspx?id=%2Fsites%2FAlterraDataTeam%2FMasterData%2Fdev'
)
from slackclient import SlackClient
from fuzzywuzzy import process
#import webbrowser
#chrome_path = 'C:/Program Files (x86)/Google/Chrome/Application/chrome.exe'
#
#webbrowser.register('chrome', None, webbrowser.BackgroundBrowser(chrome_path))
try:
conn = sharepy.connect("https://jda365.sharepoint.com", username = "******", password = "******")
site = "https://domain.sharepoint.com/sites/Pages/"
library = "Documents"
files = conn.get("{}/_api/web/lists/getbyTitle('{}')/Items?$select=FileLeafRef,FileRef,Id&$top=5000"
.format(site, library)).json()["d"]["results"]
array = []
Mapper = {}
farewell = ["Bye", "Good Bye", "Until next time...", "Au revoir", "Adios", "Ciao", "See you soon", "Auf Wiedersehen..!"]
def upload_to_sharepoint(filenames, sharepoint_host, sharepoint_site,
sharepoint_library, user):
logging.info("Using SharePoint host %s, site %s, library %s",
sharepoint_host, sharepoint_site, sharepoint_library)
password = keyring.get_password(sharepoint_host, user)
if password is None:
raise ValueError(
f"Could not get password from keyring for {user}@{sharepoint_host}"
)
s = sharepy.connect(f"https://{sharepoint_host}",
username=user,
password=password)
library_root = f"https://{sharepoint_host}/{sharepoint_site}/{sharepoint_library}"
if s.get(library_root).status_code == 403:
raise ValueError(
f"Forbidden for {library_root} - authentication failed")
else:
logging.info("Access to library root confirmed for user %s: %s ", user,
library_root)
headers = {
"accept": "application/json;odata=verbose",
"content-type": "application/x-www-urlencoded; charset=UTF-8"
}
successful_upload_count = 0
failed_upload_count = 0
created_folders = set()
uploaded_filenames = []
for filename in filenames:
filename_object = pathlib.Path(filename)
with open(filename_object, 'rb') as read_file:
content = read_file.read()
folder_name = timestamp_to_string(modification_date(filename),
YEAR_MONTH_FORMAT)
relative_target = sharepoint_library + '/' + folder_name
if relative_target not in created_folders:
p = s.post(
f"https://{sharepoint_host}/{sharepoint_site}/_api/web/folders",
json={
"__metadata": {
"type": "SP.Folder"
},
"ServerRelativeUrl": relative_target
})
if p.status_code < 200 or p.status_code >= 300:
logging.error(
"ERROR: Post to create folder %s resulted in status %s",
relative_target, p.status_code)
failed_upload_count += 1
continue
else:
created_folders.add(relative_target)
logging.info(
"OK: Post to create folder %s resulted in status %s",
relative_target, p.status_code)
p = s.post(
f"https://{sharepoint_host}/{sharepoint_site}/_api/web/getFolderByServerRelativeUrl('{sharepoint_library}/{folder_name}')/Files//add(url='{filename_object.name}', overwrite=true)",
data=content,
headers=headers)
if p.status_code < 200 or p.status_code >= 300:
logging.error("ERROR: Post for file %s resulted in status %s",
filename, p.status_code)
failed_upload_count += 1
else:
logging.info("OK: Post for file %s resulted in status %s",
filename, p.status_code)
successful_upload_count += 1
uploaded_filenames.append(filename)
logging.info("Successful uploads: %s, failed uploads: %s",
successful_upload_count, failed_upload_count)
return uploaded_filenames
