How To Access SharePoint List Using Python

hey what up everybody i am lou and today we're going to talk about how to access a sharepoint list so i've been getting a lot of requests on hey how do we use python to access a sharepoint list and then in the example that we're going to do we're going to access the list something basic to begin with there are other stuff you could do and i'll talk about it in a few but ultimately what we're doing is we're going to access the list then we're going to save that list to an excel file so let's go ahead and get started alright so i have my text editor open so before we get started there's two packages that we're going to be dealing with um so if you don't have these go ahead and get them um the first one is going to be share pump so go ahead and get that installed um ultimately this is the package that's needed to be able to access your sharepoint site to do many things right you could upload documents download documents in this case we're accessing a sharepoint list and so on um the next one is going to is going to be open py excel so this is the one that we're going to use to create a excel file so again go ahead and get that installed um as well and we're going to be creating a workbook a worksheet writing our data um to this worksheet and then saving it out so again i'll walk you through the process um before we get started let me show you the sharepoint site that we're going to be dealing with uh let's see where is it okay so this is the this is the sharepoint site um i call it the level mac because it's literally for testing i don't have anything production or anything of true value in here it's all for testing so i created a a list called clients again you can have a list on whatever you know you call your list to be in my case again pretty straightforward we have the name then we have address information um street city state postal code etc right so this is just our lit our basic sample list so this is what we're going to be ultimately accessing right so let's kind of let's get started let's go to the process okay so once you install your two packages share plump open uh py excel then we could go ahead and get started um so one thing too uh i guess we probably do this first um you could do it differently if you want i normally again what i'm doing here strictly for test purposes this is not how you would do it in a production environment you will create um environment variables in your production server to bring in any kind of credentialing in my case i'm going to show you this sharepoint um json object that that i have again just call sharepoint and you know i would have my user which is my email password a url so the url would be your sub domain whatever that that may be right um even if you don't have a true like your own sub domain if it's more of a microsoft generated version that's fine you know whatever that is um and then of course your site so in this case would be your main url then of course under sites you have some kind of site name now if i go back to mine my site name again it's called development and we can see it right here right sharepoint my subdomain sharepoint.com sites deliverment so that's kind of what you know um you go ahead and get this created and um and we'll be calling it throughout the process now that being said i'm gonna actually put my information in here now i'm not gonna show this going forward so you need to reference it just kind of go back to the video but i'm going to put my information so let me go ahead and do that right quick all right so got that done all right cool so now let's before we do our project so i created a folder called project py and sharepoint py so let's start off with the sharepoint py file um what we're gonna do here we're gonna put in our sharepoint um authentication our sharepoint on you know we're gonna create a function uh to be able to connect to a sharepoint list we're kind of gonna we're gonna build that out it'll make a lot easier once we start coding it up so um let's let's do that all right so that's fine all right so i'm going to end up bringing in um share plump endpoint import the hell i'm talking about uh site and office 365. so also keep in mind in my in this example i'm dealing with a office 365 account now if you're dealing with a on-prem sharepoint site it would be slightly different um very similar but there will be something that will be different like your authentication also it kind of depends on if you have access to the network if it's part of the same network where you're in uh most of the time it is you know if you have access to the sharepoint most likely you have access to to the network to some degree you can actually call it to the directory path again it's different this video was not designed for that so that's a whole different video but just want to put that out there this is strictly for um office 365. so we have sharepoint um uh we also want to bring in share plump um sites import versions there goes version oops running the wrong one lowercase all right there it goes all right so now that we got that in order where we're gonna end up doing is uh we're gonna have to read that json config file again this is how i'm doing it just for the purpose of this example this is not what you would want to do in a production environment a production environment you would want to probably create some environment variables and then you'll be able to just you know execute the the call of these variables and just bring them into your project so all right so i'm bringing in those packages um the next thing is let's create a root directory that i'm going to need os pass oops uh directory name os pass absolute pass um and the next piece is our config our config pass is which would be our root directory plus oops hold uh up forgot to put two forward slashes all right um plus the file which in this case is config.json again i probably make it more difficult than what it should be but you can just ultimately recording this file that's that's what we're trying to do there uh next thing we're going to open up this file the pass config file so now i'm going to create a config object and we're going to load this file so kind of going back it's a json object that had a key value of sharepoint so [Music] what i'm doing here i'm bringing in the whole object in now you're going to kind of narrow that down to only bring in the the attributes the the the values and the keys that relate to the sharepoint key so let's do that right quick all right so now that i have that in order so now let's create our um create some variables for username config not user uh password sharepoint url [Music] uh url sharepoint site all right so now that we have those in orders so so now that we have that in order again ultimately what we're doing um we're just creating the string that ultimately show the full directory pass for this file we're reading this file um read json file uh json config file which is what we're doing we're pretty much creating this config object down below we're pretty much setting um our values from the config file you know to just variable that we're going to use throughout the project so next i'm going to create a class okay in this class i'm going to call sharepoint um i don't need that i'm going to end up creating um two methods in this class one is for authentication and the other one is for connecting to a sharepoint list so let's do that uh yep no parameters we're going to do yourself uh cookie and we're calling office 365. oops um we're gonna pass in sharepoint url so you could still up one of one of the first argument is sharepoint site um so i'm just passing in the foil sharepoint url the next thing is a username there's gonna be no username and then it is password there you go password um yep all right so now now that we have that in order now we're going to create uh in a site instance which in this case would be site sharepoint site and then versions see how we have site url my name is a little bit off that's fine but at least you kind of understand what we're trying to do and then we got version right so so version would be version uh v365 because again that's what i'm dealing with here you know it's an office 365 account and then my last argument would be us cookies right now set to none i want to set that to my ass cookies object that i created up here which ultimately have my credentials again the sharepoint url um username and password so i'm passing that into my site and then i'm going to return back my site object all right so this is strictly for authentication uh the next one is going to be connect to list all right so ultimate this is again we're connected to a sharepoint list which is what we're doing here um i do want to pass in a i'll call it list name because again we could be trying to connect to many other lists uh sharepoint list okay so now that we got that i'm gonna have to i'm going to end up creating um an off site object which ultimately is going to it's calling this method up here on top um i'm gonna call it let's call it list data um all site list so see where we have right here list name that's ultimately where we're gonna pass end up passing in so let's name equals let's name and this is the argument up here on top so you know what to make a little bit easier just make sure there's no confusion um ls name so at least you know that's what this is up here right these two go together um at the end of it there is another um function that we're going to end up calling which is called get list items so we're we're pretty much determining what list we want but then within this class we have this other method of what we're trying to do in this case we're trying to get the list items which is ultimately what we want then we're going to do a return list data all right so just kind of just kind of go back and to reiterate we are creating the full path of our config file again you wanted to hard code it in that's fine you could do that as well we're reading the file and then we're setting the sharepoint keys and values under the config object all right so below that we're then setting our username password sharepoint so on again this is the way i did it you don't need you don't you could just use call these directly if you want and just call them over here and you can avoid the whole section that's fine that's the way i did it anyways and so next we created a sharepoint class the first method that we have is a awes message we ultimately to authenticate you know we need to connect to the sharepoint and that's what we're doing here um one thing i want to notate though if you have a sharepoint site again office 365 account and you have um a multifactory factor authentication setup um you're gonna have issues this is not gonna work so there's a few workarounds to to work around that but it's more of of a um like ideally you have to probably create a new user or that user that they have um multi-factor authentication and then on top of that they're very limited on what access they have very specific to what you know what you wanted to do and then you use that account to be able to connect again it's i can make a video about it you know on why that's the case but this wouldn't work if if you're the setup that way um let's see all right so now that we have our sharepoint setup now let's go to our project file okay so our project file [Music] um okay so now let's go to our project file so in this project file the first thing we're going to end up doing is we're going to end up um importing that let me do it do it this way we're going to end up importing this sharepoint class all right so let's go ahead and call the sharepoint file import sharepoint got it the next thing we're going to end up doing is we're going to end up importing open py excel workbook because we're going to be creating a workbook right so that's the whole purpose behind this all right so now i'm gonna just put some notes get client client uh share sharepoint list that's the first thing we're going to do again let me kind of go back to [Music] this is the sharepoint list that we're going to test out and we're going to get back right so ultimately that's the first thing that we need to do so now let's do that um and i'm going to call this clients sharepoint uh get client list in my case my my list is called clients if you're not for sure a lot of i mean it should be the name that you have on the side nevertheless once you're in it um [Music] let's see the url should tell you as well right so in this case you have your site name list and then of course in mind it says clients so that's how you could determine what it is so let's take a quick look at it right now that we kind of have everything in order before we continue on let me go ahead and and let's let's take a look right so [Music] let me go ahead and run it uh project py what happened here oh okay let's hold up i got an error so let's see what's happening [Music] hmm oh i know what i forgot so in my office 365 class that i call right um there's another method that i should have caught at the end which is called get cookies and i forgot to include that in here all right so let's go ahead and try it again okay there you go so as we can see this is our data that we have here we have you'll see the first section we have like a one pound sign ultimately this is pretty much your row number so we got one where's number two at there goes number two over here if we look at some of the data we'll see we have address info postcode right um where's the name at title there goes so we have mark and then down below we have jan which again if i go to my there it goes mark and jan right so that's pretty much what we're pulling back all right so just want to make sure to show you how the data actually looks um so the next thing that we're going to do we're going to all right so before i go any further what we just did right now was the very basics of authenticating the sharepoint and pulling a list of sharepoint now there's other features you could do in sharepoint um for example you could filter so let's say you have a a sharepoint list of 5000 entries right just picking a number well you could filter to those entries there's a capability to apply a filter i'll do a video on that if you're interested kind of show you some examples on how to filter this the main purpose here is kind of show you how to connect and how to pull um a sharepoint list access it right once you access it then you can do whatever you want to it you can do something programmatically you know parse the values call an api save it i mean it's up to you what we're going to do here next so we're going to next create it to an excel file and save it um so and this kind of like a good idea for this would be if you have a client where you're you know different people are entering data into a sharepoint list and then maybe you know the client wants the customer whoever you know the business uh wants to see a report of that data at the end of the month they just you could pull it kind of what i'm doing so but we're going to create an excel file next let's go ahead and do that we're going to create excel workbook okay so i'm going i'm going to call it wb for workbook yep there you go wb workbook so i'm calling the workbook class oops um the next day i'm gonna create my destination file pass ultimate this is where are you going to save this file in my case i'm going to just call it um client list and i'm going to save it on the same folder where i'm at you could save this anywhere else again you could um just specify the paths and where you want it to go i'm just going to create in the same folder all right so the next thing is going to be create um worksheet um so let's do ws for worksheet equals workbook dot active all right so what i'm doing here by doing using the w be active i'm just pretty much saying hey yes you know give me the just kind of create a default worksheet um there's you could do other stuff you create specific um like um like many worksheets and access them differently i'm just kind of i'm just activating one that's all i'm doing i'm gonna assign a title to it so in my case it would be worksheet um title and let's call it client list right pretty straightforward uh so the next thing is going to be um setting um share point list values to excel cells all right so this is the whole we're going to start writing it to an excel worksheet object and then once we do that then we'll we'll save it so let's go ahead and let's do that so we're going to do four index um client and uh enumerate uh clients and one we wanted to start with the one value if you're not familiar with the enumerate is it's uh think of it as a um let's say you have a list of 10 um records in the list right 10. so ultimately this is doing this is just kind of doing a count of each record of you as you iterate through it now you can specify your value where you want to start by default you know and again in programming your index your starting index is zero right zero in excel that's not the case it is one so that's why i'm specifying we want to start on one not zero all right so [Music] um so next now i'm going to specify my um my worksheet sales sell my best cell column equals 1 for my first value because i know what column i'm placing it in row equals um the index on which row i'm in right because again i'm iterating over each row ultimately what i'm doing um and then my value and my value is going to be client so my first one is going to be title again just kind of go back i have this name the client name it's being um the key value of it it's called title and then we have these other information address info and and so on but ultimately that's what i'm you know what i'm gonna have to call which is title then let's go ahead and repeat the process for all the other ones and this one is going to be address um info let's do street because again we have a few we have street city state et cetera in this case we're gonna we're gonna do street now let's do one more i'm not gonna do all of them you know you kind of get the idea here um value client uh city all right so now that we have that the final thing is save workbook to do this actually fairly easy workbook safe that's it so now what we're doing we're calling sharepoint by whatever list we want to call um so one thing to just kind of keep in mind um i did kind of hard code in uh the site that we're dealing with of course in your dinner with the variable sites where you have multiple sites multiple lists then of course you know you could add another function in here um to kind of specify which sharepoint um site you want to access but either way we're just dealing with one sharepoint site here um so let's go ahead and run it let's try it out and let's see if it works it does not work oh you know what i forgot to specify my name so this is going to be file named equals oops um destination file pass all right so now let's try it again all right there you go i saw a file pop up over here where is it at there it goes client list excel let's see let me go ahead and open this open this up um uh it's fine let me just go to let me find it right quick all right here it goes so boom we have our our name we have the street and we have the city again because that's what we specified over here right so damn it there it goes again name street city if i provided the other ones we get the other data as well as you could tell i didn't add a header and that's fine we could always go back and add a header but just want to kind of give you the fundamentals right the it's really not that hard you could tell we have our client list name on the bottom too our file name right so that's pretty much based on what we specified over here in the title and then of course the uh the file name but hopefully that gives you hopefully that gives you a better idea right on uh connecting to sharepoint um connecting to your sharepoint list to access data and then of course in our case if you weren't familiar with well now you know how to create a a you know basic workbook and save your data to the workbook um it's really not not that hard um hopefully y'all find this useful um again let me know man give me just give me some requests again i made this video because it was requested by a viewer a few viewers to be honest with you um so man if there's any anything else related to sharepoint or anything else you know you know send me a message and i may make a video of it um again man thanks for watching um hit that thumbs up hit the like button follow me uh again i'm trying to grow my channel anything i can do to help you out by all means um let me know now and i'll try to see how i could help you out with some knowledge sharing some of my knowledge that i have and uh thanks for watching peace

Channel: I am Lu

Views: 50,176

Keywords:

Id: Qf1Qemn2O64

Length: 35min 27sec (2127 seconds)

Published: Wed Jun 16 2021