'Get Items' does not return all columns in Sharepoint List

(2) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Has anyone faced this before? What am I missing?

Scenario1:

Trigger: Button push
Action1: Get Items from Sharepoint List
Action2: Create .csv (automatic headers)
Action 3: Send email (with .csv as attachment)
Works fine EXCEPT there are columns missing from the Sharepoint list (6 columns!)

Scenario2:

Trigger: Button push
Action1: Send HTTP Request to Sharepoint list
- GET with _api/web/lists/GetByTitle('LISTNAMEHERE')/items as the "Uri"
Action2: Parse JSON (parse the response from above)
Action3: Compose (grab the nested array from JSON using outputs(['ParseJson']?['body']?['d']?['results']
Action4: Create CSV (from Body of Compose action)
Action5: Send email (with .csv as attachment)
Works fine and returns ALL COLUMNS in the Sharepoint list

Why partial results with Get Items?

Thank you

Categories:

General topics

I have the same question (0)

All responses (26)

Answers (1)

Verified answer

Pstork1 68,898 Most Valuable Professional on at

Like (9)

Report
Copy link

Link copied!

The Get Items action normally brings back a limited set of columns to improve performance. Make sure you open up the advanced properties and set the Limit Columns by View to Use all columns (do not limit) to get all the columns.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Does not work on lists larger than 5k records. Bummer.

Moving to trying 'Send HTTP Request to Sharepoint' action.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Pstork1 , do you know what HTTP query Sharepoint uses when you click "Export to Excel" from a Sharepoint list?

This is the query I need. Can't figure out how to view request using F12 in Chrome.
Thank yoU!

Was this reply helpful? Yes No
Pstork1 68,898 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I'm not sure that's done through an HTTP query. But if it is the best way to get it would probably be to use something like Wireshark or Fiddler.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Pstork1 , thanks for the quick reply. Yeah, I can't use those tools within my org (proxies, firewalls, etc.)

If anyone can pull this, might be useful workaround to the cumbersome workflow for pulling SP List items >5k.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Ended up closely following this blog post: https://blogs.msdn.microsoft.com/svarukala/2017/08/08/iterate-and-get-all-items-from-a-sharepoint-large-list-or-library-using-microsoft-flow-no-code-solution/.

Have all ~35k records pulled from this list, but having troubles appending each 5k pull to the same .json file for export.

I'll get it!

Was this reply helpful? Yes No
Rodolfo1840 155 on at

Like (6)

Report
Copy link

Link copied!

In our case, it didn't work even choosing: "Use all columns (Do not limit)".

Our list has 19 columns and it's getting only 11 columns.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (3)

Report
Copy link

Link copied!

I only have 240 list items and Get Items with return all colums set does not work. Seem like every component of this softwars suite has serious gotchas. I guess I have to find a work around because Microsoft has let us down, yet again. Sorry MS your stuff is not ready for prime time.

If only this stuff worked as well as a LAMP stack.
Sigh

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Anonymous, try moving to the 'Send HTTP Request to Sharepoint' action.

Docs here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?tabs=csom

Was this reply helpful? Yes No
Pstork1 68,898 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Unless you are running into memory limitations due to really big columns there shouldn't be any reason that you can't get all the columns when you tell it not to limit based on Columns. Have you tried creating a view that has the columns you need and then setting the Get Items to use that View? What columns are missing when you set it to not limit columns based on a view?

Was this reply helpful? Yes No