Enhancement: Extend `csv` output mode to include more properties than `text` output mode

[martinlingstuyl]

The csv output mode is currently restricted to return the properties that are also returned in text mode.

For example, the following will force csv output mode to return just three columns.

public defaultProperties(): string[] | undefined {
    return ['id', 'displayName', 'isInUse'];
}

The problem with this is that the defaultProperties have been limited on purpose because text mode is ugly when too many properties are side by side. CSV however is not limited by the breadth of the screen as these files are usually opened with a tool like excel. Also, you'd typically want more information than when compiling a csv.

💡Idea

What if we enhanced the csv output mode to return EVERY property from objects.
This would be problematic with nested objects, so those could be excluded automatically.

Another route would be to add an extra option: --csv-columns that allows people to write down a comma-separated list of columns they want to export to the csv. But maybe that's a bit too much, people can always use the JSON output mode and shell commands to create their own CSV file.

❗ Conclusion

This Issue is resolved when the CSV output mode includes ALL properties of the returned object.
Nested objects should be excluded by default, UNLESS when the --query JMESPath option is used to force return a selection of properties.
So we'll NOT add extra global options to influence the csv output.

For the MD output mode, I made a separate issue (#4150) , it might be that the change fixes it for both output modes. So here's a note to check that on review and if necessary close the other issue.

[MathijsVerbeeck]

I love the idea @martinlingstuyl, but I've also noticed when we execute --output csv for a simple get command, for example spo listitem get --withPermissions we get the following response in json:

{
  "FileSystemObjectType": 0,
  "Id": 147,
  "ServerRedirectedEmbedUri": null,
  "ServerRedirectedEmbedUrl": "",
  "ContentTypeId": "0x010078BC2C6F12F0DB41BA554210A2BFA81600A320A11ABE6E90468525ECC747660126",
  "Title": "Demo Item",
  "Modified": "2022-10-30T10:55:37Z",
  "Created": "2022-10-30T10:55:22Z",
  "AuthorId": 10,
  "EditorId": 10,
  "OData__UIVersionString": "3.0",
  "Attachments": false,
  "GUID": "87f3138d-fac3-4126-97c0-543e55672261",
  "ComplianceAssetId": null,
  "RoleAssignments": [
    {
      "Member": {
        "Id": 3,
        "IsHiddenInUI": false,
        "LoginName": "Communication site Owners",
        "Title": "Communication site Owners",
        "PrincipalType": 8,
        "AllowMembersEditMembership": false,
        "AllowRequestToJoinLeave": false,
        "AutoAcceptRequestToJoinLeave": false,
        "Description": null,
        "OnlyAllowMembersViewMembership": false,
        "OwnerTitle": "Communication site Owners",
        "RequestToJoinLeaveEmailSetting": ""
      }
 ]
}

If we execute the following using --output csv, we get something like this:

FileSystemObjectType,Id,ServerRedirectedEmbedUri,ServerRedirectedEmbedUrl,ContentTypeId,Title,Modified,Created,AuthorId,EditorId,OData__UIVersionString,Attachments,GUID,ComplianceAssetId,RoleAssignments
0,147,,,0x010078BC2C6F12F0DB41BA554210A2BFA81600A320A11ABE6E90468525ECC747660126,Demo Item,2022-10-30T10:55:37Z,2022-10-30T10:55:22Z,10,10,3.0,,87f3138d-fac3-4126-97c0-543e55672261,"[{""Member"":{""Id"":3,""IsHiddenInUI"":false,""LoginName"":""Communication site Owners"",""Title"":""Communication site Owners"",""PrincipalType"":8,""AllowMembersEditMembership"":false,""AllowRequestToJoinLeave"":false,""AutoAcceptRequestToJoinLeave"":false,""Description"":null,""OnlyAllowMembersViewMembership"":false,""OwnerTitle"":""Communication site Owners"",""RequestToJoinLeaveEmailSetting"":""""}]"

Notice that, when we have an object in the csv-format, it will just return this object under roleAssignment as the json-string, which doesn't seem useful to return. How would we handle this kind of response?

[martinlingstuyl]

Yeah, that's what I meant with nested objects... I'd say strip them out...

[arjunumenon]

Nice idea Martin. Definitely make sense to have the more fields in the csv output than what is there in the text.

strip nested objects
OR

Another route would be to add an extra option: --csv-columns that allows people to write down a comma-separated list of columns they want to export to the csv.

My personal suggestion is not to strip the nested objects automatically when you export, but rather include a new column like you suggested.
Personally I would love to have --excludeColumns rather than --csv-columns which will enable the users to avoid the columns which they think might be problematic (Nested Objects).

[waldekmastykarz]

I like the idea. I'm not sure if we should automatically filter out objects, or let users choose which properties they want to get using the --query option. While JMESPath queries are not trivial, they offer flexibility for things we wouldn't have known. An alternative would be to exclude objects from output unless user specified --query. That way we've got a sane default with an advanced mode if need be. The question is if it's obvious though. I'd steer away from introducing new global properties for the sake of CSV output.

[waldekmastykarz]

@pnp/cli-for-microsoft-365-maintainers any other ideas? If not, I suggest we proceed with my proposal at #4150 (comment)

[arjunumenon]

An alternative would be to exclude objects from output unless user specified --query. That way we've got a sane default with an advanced mode if need be. The question is if it's obvious though. I'd steer away from introducing new global properties for the sake of CSV output.

So If I understand correctly, what you are recommending is to remove nested objects when you have specified the output as csv.
If that is the case, I guess we may have to mention that in the docs that any nested objects will be excluded when output is given as csv

[waldekmastykarz]

If that is the case, I guess we may have to mention that in the docs that any nested objects will be excluded when output is given as csv

Correct, and also that nested objects will only be excluded if they haven't been selected with --query

[Jwaegebaert]

Sounds like a solid plan. I'm fan of excluding the nested objects by default but allowing them to be included when users work with --query. For the documentation, I think it would be best to explain this functionality under User Guide > CLI output mode

[waldekmastykarz]

I think that concludes it. @martinlingstuyl could you please update the spec so that it reflects our decision to remove complex objects from the CSV and MD output and then update docs to explain this?