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... – Kannan Karmegam Sep 18 '14 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 '14 at 9:11

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 '14 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 '14 at 14:41
  • Ok, in that case you can use http://<root>/subsite/_api/web/lists?$expand=Lists/Items. – Ravi Ranjan May 18 '14 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 '14 at 16:24
  • Let me know if this serves your problem: http://site-url/_api/web/lists/GetByTitle(‘CustomItems')/items. – Ravi Ranjan May 18 '14 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 '14 at 17:28

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.