Open SharePoint List in Excel + Update excel from SharePoint list automatically

hello and welcome to the channel my name is bijay and i'm a microsoft mvp specialized in sharepoint in today's video we are going to discuss how we can open sharepoint list in excel as well as we will see how we can update excel from sharepoint list automatically so basically these are the things that we are going to cover on this video we will see how we can open the sharepoint list in excel and then we will see how we can update the excel from sharepoint list automatically that's quite important and then also i'll show you two error messages that i usually i got it while working with this this scenario or this example so now let me tell you the scenario that where you can what where the business requirement is suppose you have a list and you want to get this list data in an excel file now if i will actually show you uh on the list here you can see i have this uh sharepoint employee list it has couple of items over there and then there is one option which is saying that export to excel so this one you can export the items to an excel file uh on one button click and uh this is this is where you have uh where you know this site url and then you can get it from there having little bit of developer knowledge and this is quite uh easy now it's about every browser uh but it will not update now let's say if i want to uh let's say tomorrow you added couple of more items then those items will not come again so that's the difference that or you you you cannot use export to excel in at every scenario but if you just want to export the data at one or just for once then you can use this export to excel option now by the way i have also written a article for the same you can see here and you will get the entire things on this particular url so i'll put the url in the video description as well and also if you are interested for sharepoint trainings i have a couple of training courses you can see here i have sharepoint development training framework trainings i don't know training nintex and the power automatic training as well and the a small client side development training also is there and if you want to go for the entire training then you can uh you can get it you can click over this url and you will see how we can enroll it and you can get access the entire course for the lifetime now i want according to the business requirement here we want to get this data in an excel file and the update should also happen so what i'll do is i will go here and i'll create a list and in this uh sorry i'll create an excel file in this case you can see here i have just an employee employees excel and nothing is there on this uh excel file you can see here is just a blank excel file now how we can do this is you can go to the data tab and then you can see here gate data and then you can see here from online services and then you can see here from sharepoint online list so click on that and once you click on that it will open a dialog box where it will ask you to provide the site url remember to it is saying sharepoint list you just put the site url so i'll go here i will copy the site url you can see here i put the url click on ok so once you click on ok then if you have not signed in then it will ask you to sign in and you can use other microsoft account office 365 account and then you you will be able to get it so so once you sign in then you will come up with this screen where it will saying it will open the all the list and libraries from that particular sharepoint site you can see here now uh if you will if i will show you actually if you have not signed in then you will get an error something or a screen something like this you can see here now it is saying sign in button you are not signed in and you can click on the sign in button then you sign in with your office 365 account and then finally you select the sharepoint site and click on connect so if you have not signed in then it will show you that option and once you have this then you can select employees and in this scenario if you'll see you are seeing two buttons here one button is saying that load and the other one is transform data so if you look at this the load if i'll click on load then it will load all the columns here you can see there are a lot of hidden columns i actually don't need all these columns uh probably i want to just the columns like i want the title full name designation like this i need these three four columns maybe i'll just add the id column like that and so if you if you want that way then you you have to click on you can see here transform data so once you click on transform data then it will open the query editor power query editor where you can actually select the columns that you required so in this case i will click on this choose columns and click on choose columns here again and then you can see here it is showing me all the columns i'll just simply select the first ones uncheck everything and then i can select the columns which are required in this case probably i require these four columns and then i'll click on ok now you can see here this is the data and then close and load so you can click on that and now if we'll see uh now this is your data that uh that has been there in the sharepoint list and you can see it is it is in the excel file and so this is how you can get the data here now this is not that's what we wanted to see we wanted to see now how we can actually uh automatically update it for example if i will update or if i'll insert some some of the data here then uh it should actually update there in this list now for that uh what we will do is i will go to meanwhile let me just insert one record so i'll just click on new item and then i will add here mr and then i'll put here testing so just to know that this is the in record that we want to insert and i'll say senior control run like this i'll click on save so we saved it uh now and now i'll go to the list sorry through the excel and what i will do here is i will go to the data tab again and then you can see here queries and connections if you click on that it will automatically open this this box and here what i'll do is click on this refresh icon click on that and then it will you can see here it is refreshing so this is how we can do it manually so manually you have to click on the refresh icon and then it actually opened it so you can see here now it added to the testing and then senior consultant that's one thing that it did the other thing that we can do is uh if you just click on this refresh icon and then click on edit and then you can see here properties so once you click on this properties you have couple of options here for example it is saying enable background refresh yes we want this and then if you'll see here refresh in every let's i'll put one minute here we can see here and then i will say refresh data when opening the file so let's say i close this file i'll open it it will do a refresh that time so you can click on ok and then you can see here refresh this connection on refresh all so uh this is the refresh all button so if you'll enable this option which is by default on uh then this uh this connection will get refreshed now i'll click on okay so we gave that in one minute it will do a refresh and what i will do is i will just add one record here i will say mistress and then i'll put testing one two three or 1 3 4 and then i'll put as consultant i'll save this now we save it now if i'll open the excel ideally it should automatically update after one minute because that's what the settings that we have done and so let's wait for a couple of second and then we will see whether it is updating or not now you can see here it is uh uploading uh updating and you can see here now it was loaded and you can see the last one also testing one two three consultant is the item is there so this is how we can do update automatically and these are the options that you can get the other thing that we can do is uh i wanted to show you a couple of error message now if you'll see sometimes you might get this error unable to connect and then access to the resource is forbidden and then you can also get this error remote server returned an error for not uh for not found like this it's a simple thing that you can do that to fix this issues let me just show you in that excel uh one thing is though of the first error the error which is saying unable to connect access to the resource is forwarded so in this scenario what i was doing is that i was going to data tab and then from web and then here i was trying to give the site url and trying to connect and that time it was going it was giving the error ideally you should not do that you can go to data and then get data and then from here you can go to from online service and then you can see you can select from sharepoint online list that's the one thing the other error that i was talking is unable to connect uh this was what i was doing is i was doing here from online service sharepoint online list but instead of providing the site url i was providing the complete list url for example if my list is employees list i was trying like this so it will give error to solve this just remove this you can just put the site url and then you literally you can select the list from the list of all the lists and libraries which will be presented and it will be displaying that from there you can select this so it will actually do those steps then you will be able to fix these two shows easily so if you like the videos kindly subscribe to our channel you will get a lot of free videos on sharepoint office 365 powerapps power bi all these technologies you will get spfx so all these technologies you get and also if you are interested for the training go for it check it out and thank you and have a nice day

Channel: EnjoySharePoint

Views: 30,012

Keywords: SharePoint 2013, SharePoint 2016, SharePoint Online, Office 365, SharePoint 2019, SharePointTutorial, Open SharePoint list in Excel, Update excel from SharePoint list automatically, Access to the resource is forbidden excel SharePoint, The remote server returned an error 404 not found, access to the resource is forbidden excel sharepoint online, open sharepoint list in excel online, how to open sharepoint online list in excel, update excel from sharepoint list automatically

Id: IReHxv-1xwI

Length: 10min 55sec (655 seconds)

Published: Wed Aug 19 2020