Get ALL SharePoint Lists and Libraries Analytics
Вставка
- Опубліковано 22 лип 2024
- In this video, we use the list of sites we queried in our last video to pull data about ALL lists and libraries in our tenant. If you don’t have your sites query, please see this video first:
• Get ALL SharePoint Sit...
GitHub link for the list template ID references:
github.com/chpayton/BI-Files/...
This technique is courtesy of Jordan Murphy, who reached out suggesting it as a video topic. He’s done some awesome work creating queries for all sorts of things in relation to this:
/ jordanmurphysphr
0:00 Intro
1:25 Duplicate and modify the site query
3:50 Create a custom function
6:37 Invoke the function on all sites
10:42 Join in friendly list template names
12:26 Set column types
13:00 Get list URLs
15:36 Visualize the data - Наука та технологія
Christine, Thanks for the shout out. It was great talking to you about this topic.
Once your viewers start down the path of Expanding the structured columns, they will be amazed at what information they can find!
Hey Jordan, do you happen to know if the file modified user is in the feed anywher? There's a question in another comment about it and I dug around and do not see it, but I feel like it should be there. Have you come across it?
The content is dynamic and currently cannot be refreshed in the Power BI service. Are there any alternative methods or solutions available for refreshing the dynamic content? I need the contributor/ group on the list/document with the modified date.
The only way I know of to remove the dynamicness is to do each site in a separate query (you can copy/paste the same query from the advanced editor and manually change the desired site/subsite for each query) and then append the tables together into a final table. You can disable each individual table from loading and refreshing and just show/refresh the appended table.
I've used this method for accessing multiple other like data sources that I wanted to be combined. This can be tedious for many sites and requires import storage mode for the tables which can take up space.
If someone knows of a better way, I would love to know about it.
Christine, thanks for the great tutorial. Quick question - when I run the base query (renamed TenantRootUrl), I have all of my data and the 3,363 file entries on our SharePoint site. However, when I use the OData feed, the list returns anything in our Teams accounts. I am an admin on the site so it's not just teams groups I'm a part of, it's more than that. How can I get the Odata feed to pull the query data and not switch to the 47 items in Teams?
I'm not sure I understand - when you query SharePoint's OData, you're getting data back from Teams and not SharePoint? The Teams objects come with SharePoint sites, so it makes sense that you get some files from there, but it shouldn't preclude you from getting the rest of it unless one of the filters is wonky.
@@bi-ome Yes, I only get the 47 teams items in the odata feed. I've tried our main admin account (not my personal) and the same error occurs. I tried running the query on a subfolder and that just throws an error so I know I've got the correct base query path correct. And the 47 teams items are not even listed in the original 3,363 documents from the base query.
This is amazingly helpful, just wondering if there's a way to get the views by list item (specifically site pages)? The SharePoint analytics feature is great, but the fact there's no simple way to connect the same data to Power BI is causing me a bit of a headache.
Not easily - that is everyone's top request for sure. That data is in the audit logs, so you can get it with PowerShell, but there's not an easy live source to connect to that I'm aware of - you'd need to ETL it to a database on a schedule and then connect to the database.
I would like to point out the list of template references on your github page is incomplete. I found many within my own organizations SP structure not included in that list. I have not looked into an all inclusive list but wanted to let anyone know who stumbled upon it from here to be sure to do your due diligence if you are filtering by a template type.
I'm receiving a Token ',' expected error when creating the fnQueryAllLists in the Advanced editor. The error is on the second Source. What am I missing?
let
Source = (InputURL) =>
Let
Source = OData.Feed(InputURL & "/_api/web/lists/", null, [Implementation="2.0"] ) // Error is on this statement.
In Source
in
Source
Never mind. I'm a newbie to power query. It's case-sensitive. 'In' is not the same thing as 'in'.
Glad you figured it out!
Thanks for the detailed video.
One question: -
Is it possible to get the name of the user who last modified the list or maybe last visited the list?|
Does SharePoint even logs these type of data?
I suppose last visited user is too much but I hope last modified by user should be possible since it already tracks last modified date as well.
Yeah, the person who last modified a list would just be whoever's name is attached to the most recent modified item. All of the items will log the last modified user and date, so you can probably get it from there but you might have to do some hoop jumping. The list itself doesn't get modified, unless you're talking about list settings etc.
@@bi-ome Hi Thank you for the response.
Yes I am searching based on of someone modified some item in the list and therefore get the time and user who modified.
Can you point me to the column name which shows the user?
I am not able to find any column specifically showing the name of the user who modifies.
You know what, you're right... I thought that would be one of the easier things to get but it appears not. I see the "author" which is the person who created the file, but that's not what we want... It *is* available through the OData connector with listdata.svc, though? Here's a link to that one if that helps at all (there's a bookmark for the OData connection): ua-cam.com/video/yeIQ9jKln_I/v-deo.html It
@@bi-ome Hey !
Thank you for this video. The listdata.svc is a useful data to access to.
But there is one thing I was wondering.
I checked this listdata.svc can only be accessed using the Odata connector. it does not work with the Web connector.
So, for a case, if I have to get the details of multiple sites at the same time, I can create a custom function using Odata. But Odata in a custom function does not work in the service. Only in desktop.
Do you know if there is a way to make it work in service?
@@soumyadeepmondal6130 Oh, really? I didn't consider that, I've not actually tried it in a function - I feel like the modified-by HAS to be in the web connector somewhere, maybe I just missed it? I'll ping Jordan and see if he knows, he's used it a lot more than me with files-- I appear to not be able to @ two people in the same comment so it'll be separate lol.