I`m working on a reporting project that mainly uses data sourced from 6 different lists replicated on over 90 SharePoint Online sub-sites handled by a global team in my organization. These sites are all centrally managed, so the lists I have to access are standardized across the sub-sites.

At first I was reading the data through Power BI/ Power Query`s native SharePoint List Connector, but it became fairly slow with just over 10 sites, and also very unproductive to replicate. I tried to make a switch to OData queries, also through Power Query, but this time on Excel, with a VBA script to help me build a query for each site, but that also bogged down very quickly.

So, what should be the best approach here? I should add that I don`t have much of a technical background but I can look into learning a specific approach. I just don't know where to start and I don't have much time. I need a data connection that is refreshable and have as good a performance I can get.

Should I look into Power Shell Scripts? REST API calls? Should I download a bunch of .iqy files? Use CAML queries? Use SQL Server Information Services? None of the above?

Any hint will be greatly appreciated.


I was able to solve the problem in a way that fits a business user skill set, probably not the best possible way, but one I could accomplish.

Started out with the list of sites i had to access, on a Excel worksheet. In there I had the following Power Query function, which accessed the lists and gave me the List and (All Items) View GUID:

let ListsID  = (site as text)=>
    Source = OData.Feed(site & "_api/lists"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"EntityTypeName", "Id", "Title", "ParentWebUrl", "Views"}),
    #"Expanded Views" = Table.ExpandTableColumn(#"Removed Other Columns", "Views", {"Id", "Title"}, {"Views.Id", "Views.Title"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Views", each ([Views.Title] = "All Items"))
    #"Filtered Rows"

In Excel VBA editor, I used the following loop to create one query for each site:

Sub LoopThroughSites()

Dim iCounter As Integer
Dim sURL As String

For iCounter = 1 To ActiveCell.SpecialCells(xlCellTypeLastCell).Row
sURL = Sheets("SiteList").Cells(iCounter, 1).Value
AddQuery (sURL)
Next iCounter

End Sub

Function AddQuery(ByVal sURL As String)

Dim sName As String, sFormula As String

    sName = Right(sURL, Len(sURL) -     Len("https://xxxxx.sharepoint.com/teams/xxx/"))

    sFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = #""Func:CallSharepointListData""(""" & sURL & """)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"

    ActiveWorkbook.Queries.Add Name:=sName, Formula:=sFormula

Workbooks("Lists Data Queries.xlsm").Connections.Add2 _
    "Query - " & sName, _
    "Connection to the 'Invoked FunctionFunc:CallSharepointListData' query in the workbook." _
    , Array( _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Propert" _
    , "ies="""""), "SELECT * FROM [" & sName & "]", 2

End Function

Now I had all of the sites queried on my Query Editor. It was easy to build a table with the Site URL, List and View IDs, and the final table name I wanted to have. I then uploaded this table to Access.

In Access VBA, I finally executed the following code (modified from here

Sub SharePoint_Loop()

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT Site, List, View, Title FROM QueriesToBe")

    While Not rs.EOF
        Import_SharePoint rs(0), rs(1), rs(2), rs(3)
        Debug.Print rs.PercentPosition

End Sub

Function Import_SharePoint(ByVal site As String, ByVal list As String, ByVal view As String, ByVal title As String)
On Error GoTo error

' Variables definition here
Dim tbl As DAO.TableDef
Dim fld As Field

'Delete table if exists
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name = title Then
            DoCmd.DeleteObject acTable, title
            Exit For
        End If

    'Import SharePoint list
    DoCmd.TransferSharePointList acImportSharePointList, site, "{" & list & "}", "{" & view & "}", title, True

    Exit Function

    MsgBox Err.Description
    'In case of error, delete imported table to empty the memory
    DoCmd.DeleteObject acTable, title
    Resume exitOnError

End Function

End result was 267 tables were added in about 20 minutes, which is not so bad. I think that the smoking gun on Power Query's SharePoint connection is that it doesn't load a predefined view, instead going for all the rows and columns on the list, visible or not, and that just slaughters the performance. The Access query, without any value on the View option, had similarly abysmal performance.

Thanks for the replies!

| improve this answer | |
  • Well done. This “pull on demand” approach could now be adapted as a means to periodically fetch new items into an offline data store, which you could then use as the source for your reporting queries. Which might be what you’re already thinking/doing. – sasfrog Apr 18 '18 at 21:56

Your best chances might be to go for the SharePoint Search, as it already does the aggregation continuously by crawling the contents of the (sub) sites. You might want to have a look at the SharePoint Search REST API as a way to query for the results.

Two things to note:

1) You will have to construct a precise search query for retrieving the correct results. "Managed Properties" from the search schema can be used for property restriction in search queries in the Keyword Query Language. If the lists were provisioned using content types, using the ContentTypeId property might be a good start (like "ContentTypeId:0x011234*").

2) For your custom lists, not all fields might come back as part of the results. Make sure that Managed Properties exist in the search schema for all relevant fields that you want to retrieve and that they are marked as "Retrievable". You can then include them in the SelectProperties of your search query. If you want to use the properties to filter the results, also mark them as "Queryable" (if used for property restriction) or "Searchable" (if used for free-text search). In case that a Managed Property does not exist for a field that you need, you will have to create it in the schema.

| improve this answer | |
  • Man, this Search API is way over my head. I was able to build a solution with Access and VBA I'll lay down on a post below – Gabriel Menezes Apr 18 '18 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.