I want to build a report:

  • with Excel 2013 + PowerPivot Add-In

  • using data from SharePoint Online

  • that should work in Excel Services (so I'm not able to use VB, Power Query, etc.)

Report should be build against list items stored on multiple sub sites of a site collection.

For example, here is a root site:


that have the following sub sites:


each of these sites has a list CustomItems. I need to retrieve all the items from CustomItems lists in Excel.

It looks that items can be retrieved with the OData query like:


but if I add this query as Data Source, Excel extracts only Webs items, but doesn't extract Lists or Items expanded in the request.

Is there a way to retrieve these items in Excel?

  • I have a similar requirement... could you please share your approach... Sep 18, 2014 at 2:42
  • Unfortunately I haven't find a solution yet. It seems that it is not possible to resolve that for the moment...
    – Vader
    Sep 18, 2014 at 9:11

3 Answers 3


If you want to do this as a single request, then I suggest you take a look at the search API.

The way you are doing it, won't be manageable, if possible at all unless you are able to do it with Macros and an iterative approach.


Using this you can filter by URL, Current Site, Site Collection and Web application amongst many other things, make sure the field you want is indexed and retrievable:


And you will be able to query very easily the data you require.

The downside is waiting for the item to be crawled, but the delay in this is often not more than 15 minutes with SharePoint 2013, due to incremental crawl.

  • I cannot use macro as report should work in Excel Services and I don't have any idea how to implement iterative approach without it. Search approach doesn't work as well as Excel doesn't accept search feed as data source.
    – Vader
    May 18, 2014 at 14:43

I have seen this so many times and also always wanted to know if there was a way. Craig Tarr wrote up a way on how to do it using front-end code. Basically the script structures unstructured data using REST API. Once you have the data you can export to excel or another DB. Completely configurable.



Can you replace the Webs in http://<root>/_api/Web/**Webs**/GetByTitle('list-name')/ItemsItems with the lists word. I think that should solve your problem.

Also have a look on Basic operation for retrieving data using REST interface in SharePoint.

For operation with subsites http://<root>/subsite/_api/Web/lists/GetByTitle('list-name')/Items. sample: using get operation.

url: http://site-url/_api/web/lists/GetByTitle(‘CustomList')/items method: GET headers: Authorization: "Bearer " + accessToken accept: "application/json;odata=verbose" or "application/atom+xml"

site-url = root/sub-site

For more details have a look at this diagram. SharePoint Rest Api Diagram

  • This wouldn't work, because Web/Lists returns lists on current site, but not from the sub sites.
    – Vader
    May 18, 2014 at 14:41
  • Ok, in that case you can use http://<root>/subsite/_api/web/lists?$expand=Lists/Items. May 18, 2014 at 15:47
  • As I said, this doesn't work, since it doesn't return neither sub site lists nor list items.
    – Vader
    May 18, 2014 at 16:24
  • Let me know if this serves your problem: http://site-url/_api/web/lists/GetByTitle(‘CustomItems')/items. May 18, 2014 at 16:37
  • Again, I need to retrieve items from lists located on sub sites. The way you propose returns list items only from root site.
    – Vader
    May 18, 2014 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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