API is not respecting Raw Data column order

Grist Feedback
1

Hi there,

None of these APIs are returning the column order (or header order) correctly:

I assume these are returning columns in the order of “colRef” from low to high, as the order of they are created:
The schema of a table
Content of table, as a CSV file
List columns in a table

this one’s JSON response seems respecting Code View order, which is for me now totally different from order of 3 above;
Fetch records from a table

I guess I understand what you do for in this way, you don’t want that sorting columns in Raw Data view should affect internal aligning of some column references, or user-generated-content column references.

When you view Raw Data view of a table and click 3 dots menu top-right corner and “Download as CSV” or “Download as XLSX” you get exactly what you see on the screen as columns order, etc. It’s total confusion right now as UX and API expectations built from there.

It is so much meaningful when Page or Widget Views on the left is independent of Raw Data order, as we can make changes in column order and that are not reflected to Raw Data. But when you go in the Raw Data view I expect it to also define API structure of JSON, CSV, XLSX, as we define Raw structure there. Isn’t that is the whole point of Raw Data?

And for the last, the most horrifying situation is, If you work on a table and sort out a structure that needs a column change later on, you need to probably do everything from start in correct order. Luckily in my case it was a single column order swap away from the result I want, so I created another column for temp buffer and swapped columns.

You can say that a well crafted system shouldn’t depend on this exact order, and I won’t, even I sort it out, I will probably be providing order over column names and filtering/sorting results for now, but it seems odd to me nonetheless when I think of classical database or excel/sheets structures.

I searched community all the way down before creating this topic to not waste your time, and tried every option of endpoints in an up-to-date Insomnia client.

I love Grist, and I care about the great product you are building, I hope I could be able to communicate the problem well. Keep up the good work!

2

You are absolutely right, this is an unpolished corner of the API and Raw Data views. Thank you for searching the community and writing up the issues in detail.

For API users, the order usually doesn’t matter, or at least not much. So I imagine people have simply been treating columns returned in the API as unordered.

I can also explain the difficulty. Raw Data views were added relatively recently. Prior to that, column objects in Grist had a parentPos property, which determined their relative position within their table, and is still used various parts of code. Raw Data views, however, were easier to implement as an actual “widget” on top of the metadata table, and so as any other widget, it has its own concept of order. That’s why rearranging columns in Raw Data view doesn’t actually update parentPos. Unfortunately, this makes it impossible to order columns by sorting the API result (the order information isn’t part of the column record).

If you are pressed to do something before this is fixed, you could get all the info needed from metadata tables _grist_Views_section (the ones with parentId of 0 are the “Raw Data” ones) and _grist_Views_section_field (matching on colRef to the /column records). The “field” records also have a parentPos property, sorting which should produce the order matching that in Raw Data view.

But I agree that it would be better for Grist to treat column order consistently.

1 Like
3

Thank you for receiving my feedback and through explanation over the topic.

I solved it by giving order numbers within column names with a delimiter to filter and sort them in a hard-coded fashion. But, I will definitely try your work-around to get more grasp on Grist.

I hope and believe Grist will be better day after day with this approach to feedbacks from community.

Keep up the good work!