Create table like CSV export using REST API

Our team manually downloads the CSV export from a private board. We are trying to recreate the same table from the CSV export using data pulled from the REST API. It becomes very difficult when you have different types of custom fields (boolean, text, datetime, etc.).

Data is successfully importing via web API in Power BI, but I can’t seem to organize custom fields into a usable table like the CSV export because of the way the JSON is structured.

How can someone create a table that is identical to the CSV export using data pulled from the REST API?

Can you share a sample row (or even just the name row at the top) so that we can see what you’re expecting?

I’d recommend checking out this guide as a starting point: https://developer.atlassian.com/cloud/trello/guides/rest-api/getting-started-with-custom-fields/

At the top-level, you’re going to want to get all of the custom field definitions. You do that at the board level (/1/boards/{idBoard}/customFields). This will give you the custom fields that are on that board:

[
  {
    "id": "5c1a585305866b72d88730f8",
    "idModel": "5b6893f01cb3228998cf629e",
    "modelType": "board",
    "fieldGroup": "61911ee6e245c643db79c8f8c69283588eb3d1a26b993f6764a8f9a5bda7691c",
    "display": {
      "cardFront": true
    },
    "name": "Rating",
    "pos": 16384,
    "options": [
      {
        "id": "5c1a585305866b72d88730fa",
        "idCustomField": "5c1a585305866b72d88730f8",
        "value": {
          "text": "Flow"
        },
        "color": "none",
        "pos": 18432
      },
      {
        "id": "5c1a585305866b72d88730f9",
        "idCustomField": "5c1a585305866b72d88730f8",
        "value": {
          "text": "80's NHL"
        },
        "color": "none",
        "pos": 34816
      }
    ],
    "type": "list"
  }
]

For each custom field definition returned, you need to add a new column to your CSV.

Then, when you’re looping through and getting each card, you need to ask whether the card has any values set for each custom field and make sure you place the value, if there is one, in the correct column for the corresponding definition.