Getting a list of SharePoint list column names

[ryleybauer]

Building on this post in the PowerApps forum with a long method to get all list items, generate and parse the entire thing as a CSV table, I reviewed their method and enhanced it into a 3-step flow! I went down the rabbit hole of wondering if it was exposed via an endpoint, and sure enough, it is! Instead of using a CSV, I'll just use a SharePoint HTTP action, append array action to create the table of columns!  

1. HTTP request to SharePoint:

   a. Site Address: team site URL, not root site URL

   b. GET

   c. URI: /_api/web/lists/getbytitle('LISTNAME')/fields?$filter=Hidden eq false and ReadOnlyField eq false

           - Also, the filter above takes care of all of the reserved columns except for "Content Type" and "Attachments", which could be removed by changing the URI to: /_api/web/lists/getbytitle('LISTNAME')/fields?$filter=Hidden eq false and ReadOnlyField eq false and Title ne 'Content Type' and Title ne 'Attachments'

2. Run the flow/perform this request to get a sample payload

3. Add Parse JSON step, using the body from the previous step and paste in the sample payload in the "Generate from sample" option

4. Append "Title" to an array variable inside of an Apply to Each loop based on "results"

[Ach3322]

@ryleybauer Thank you for this helpful guide! Managed to get the array with all the column names correctly.
For my use case, I have 50 choice columns with different names, but all of them have identical choices.
I'd like to dynamically check each column's value and increment a variable accordingly.
My first idea was to loop through the "column names" array and have a SWITCH On [column name] action.
I'm stuck with getting the right syntax for PA to recognize the column name from the array as a SharePoint column and not a string. 
(I have a "when an item is created" trigger and the array loop is nested inside a for each loop with "

@{triggerOutputs()?['body/value']}" from the SharePoint trigger)
Grateful for any help/insights 🙂

[ryleybauer]

You are trying to increment a variable, or are you trying to increment a column's value and are having trouble mapping the update to the specific column?
The loop of column names and the switch sounds like the approach I would take. If you are updating a single column for each loop item but are updating a different column dependent on the loop item value, for each branch, add your update item step, but only set a value for the ID and the specific column you want to change, if the other columns on the step are left blank, they don't overwrite.

I don't think "SharePoint Column" is a type of data, it would be a string. 

If you're trying to use the name to update a column, you might have to make a raw HTTP SharePoint call so you can use plaintext to pass in the column name in the payload and the "Type" of data won't matter so you can make a single step that can set any column's value with both a dynamic column name and a dynamic value. 

If I'm not understanding your question, please clarify further. If this was helpful, please mark give Kudos to my reply.

[Ach3322]

Hey @ryleybauer ! Thank you for responding so fast 🙂
Hmmm let me clarify.
We have a list with 50 choice columns, all different questions basically. All of them have the same 4 choices (nothing, low, medium, high).
Users will submit a new item form, answering these 50 questions. Each item in the list is considered a "submission". The request is to calculate the "score" of each submission and add it to another list. The score is a simple addition, 0 points for nothing, 1 for low, 2 for medium and 3 for high.

Currently my solution looks like this:
1. When a new item is created (the submission) triggers the workflow.
2. I declare some variables, like the "column names" array and "total score".
3. Your steps from the original post go here:
       i. HTTP request to SharePoint
      ii. Parse JSON
     iii. Append column names to the "column names" array

4. This is where I'm stuck:
For each (loop) item in the "column names" array, I want to check the field's value.
I tested with "hardcoding" the switch actions, I can use dynamic content and check each field individually, e.g. case 1: "field x" equals to low, increment "total score" var by 1. This works, but it doesn't feel like a good solution in the long-run.
My idea was to use the contents of the array instead of dynamic content and loop through the same switch action, instead of having 50 different switch actions. Using the display names of columns in the array, returns an empty "total score", because it cannot find columns with those names, so it always goes to the default case (no addition to the "total score")
I tried adding the StaticName or InternalName (from the Parse JSON action) in the array, but when I try this, I get "The value cannot be null. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'., during the append to array variable action.

There is a 5th step, where I add the value of "total score" to another list, but nothing remarkable.

EDIT: to make it clearer, the HTTP request returns the column titles (display names) and not the internal names (with _x0020_ instead of spaces) which I seem to need to be able to execute the switch action correctly. The internal or static name produced from parse JSON action are always null and I have no clue why. 😅

[ryleybauer]

While I would agree that it feels crude, a loop is really nothing computationally, just an extra API call per iteration, which can be a problem if you have lots of flows as you only get 40K/day before getting throttled.
You wouldn't need 50 different switch actions, you would need a single condition to check if it was the right type of column (i.e. value equals choice A, or value equals choice B, or choice C or choice D, and then the switch would be based on the score to decide how much to increment the variable.

How are the items being created? Are you using PowerApps or a customized list form? Then you can calculate it in the app instead of power automate to sum up all the values which would work for blanks as they wouldn't be "null" per se and would probably feel easier.
If using MS forms as a front end, then keep the approach you've described as there isn't really a way to handle that calculation that I'm aware of.

One other option is to go with calculated column on the list, this is kind of a good use case for it, but it would be a messy formula with 50 columns and 4 states for each condition to translate them from text to a value.

[Ach3322]

Hi again @ryleybauer !
Thank you for taking the time and providing insights!
Figured out my mistake:
Added "and FieldTypeKind eq 6" in the Uri field of the "Send an HTTP request" action, so that only choice columns are returned.
And in the loop I added compose the current item of the array and then in the Switch I use "On":
item()?[Outputs(Compose)]?['Value'] 

This two seemed to do the trick 🙂