Get Trello Custom Fields to Google Sheets

Hello,

I’m new to the n8n world and I am trying to help automate some things in my company.
Currently, we’ve been using Trello as a workflow for laptop selling.

I am currently blocked from getting the Custom Fields information from a card.
Is it possible to get a custom field information from a card that just have been added to a list so I can insert it in my google sheet?

Sorry if my question is in the wrong place…

Hi @suardim, welcome to the community!

You’re absolutely in the right place here with your question :slight_smile:

Does your Trello node already return your custom fields? Or in other words, do you struggle with adding data you already have to your Google Sheet (so the second step of your workflow) or is fetching the data already causing the problem here?

Hi @MutedJam, thank you!

I am struggling to get the custom fields out of Trello yet…

From giving this a go on my end it seems the Get Cards operation wouldn’t get custom fields, even when requesting “all” fields. So, you would need to pass on the IDs for your card to another Trello node fetching the full details for each card. For example, like so (I’ve replaced the IDs with my own Trello IDs, so you would need to update them accordingly):

The second Trello node would then return a field customFieldItems with the content of your custom fields:

image

You can then add a Set node to format the data as needed in your Google Sheet. Make sure to enable the “Keep Only Set” option on the Set node to only keep the columns you actually want to append to your Sheet (Trello will most likely give you way more data than you want).

Let me know if you run into any trouble with this!

Great @MutedJam ! Thanks a lot… is already a good sign it’s working now on my side.

Is there a way to separate each custom field into a singular columm?

I suppose the best approach depends a bit on how exactly your data looks like. Do all the cards you are fetching have the same custom fields? Or do you need to add logic to deal with different data structures for each card?

Perhaps you can share the JSON data returned by your second Trello node and confirm which of the values you’d like to use? Feel free to replace any confidential values with dummy values, I am just interested in the data structure you’re getting from Trello.

All the cards have the same custom fields, some of them can be empty, but I don’t think that’s a problem right? The structure is the same.

Some of the fields are not text, so I think I must create a logical expression somewhere to insert the correct data into sheets.

[
  {
    "id": "635904193bd194021cfbbffe",
    "badges": {
      "attachmentsByType": {
        "trello": {
          "board": 0,
          "card": 0
        }
      },
      "location": false,
      "votes": 0,
      "viewingMemberVoted": false,
      "subscribed": false,
      "fogbugz": "",
      "checkItems": 0,
      "checkItemsChecked": 0,
      "checkItemsEarliestDue": null,
      "comments": 2,
      "attachments": 0,
      "description": true,
      "due": null,
      "dueComplete": false,
      "start": null
    },
    "checkItemStates": [],
    "closed": false,
    "dueComplete": false,
    "dateLastActivity": "2022-10-26T09:58:08.829Z",
    "desc": "Lenovo X250",
    "descData": {
      "emoji": {}
    },
    "due": null,
    "dueReminder": null,
    "email": null,
    "idBoard": "6346e8745d10ef037709bbcf",
    "idChecklists": [],
    "idList": "634d6e1c9db7c200797f30c7",
    "idMembers": [],
    "idMembersVoted": [],
    "idShort": 183,
    "idAttachmentCover": null,
    "labels": [],
    "idLabels": [],
    "manualCoverAttachment": false,
    "name": "matheus - teste",
    "pos": 4096,
    "shortLink": "dg5b4H7l",
    "shortUrl": "https://trello.com/c/dg5b4H7l",
    "start": null,
    "subscribed": false,
    "url": "https://trello.com/c/dg5b4H7l/183-matheus-teste",
    "cover": {
      "idAttachment": null,
      "color": null,
      "idUploadedBackground": null,
      "size": "normal",
      "brightness": "dark",
      "idPlugin": null
    },
    "isTemplate": false,
    "cardRole": null,
    "customFieldItems": [
      {
        "id": "635904236d6eff00b4ac6efd",
        "idValue": "6346e8dfd9040d001803108e",
        "idCustomField": "6346e8dfd9040d001803108c",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "6359042bce529704b03c8897",
        "value": {
          "text": "Matheus"
        },
        "idCustomField": "6346e8dfd9040d0018031106",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "63590430fb86d304795e1111",
        "value": {
          "number": "939999999"
        },
        "idCustomField": "6346e8dfd9040d0018031109",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "6359043968d8f5005ac12b51",
        "value": {
          "text": "[email protected]"
        },
        "idCustomField": "6346e8dfd9040d0018031163",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "6359043b715efe040d574cd6",
        "idValue": "6346e8e0d9040d0018031221",
        "idCustomField": "6346e8e0d9040d001803121e",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "6359043f37d8c9022983f75e",
        "value": {
          "text": "Morada"
        },
        "idCustomField": "6346e8e0d9040d0018031298",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "635904432e16b70207b1f4d4",
        "value": {
          "text": "Traking Code"
        },
        "idCustomField": "6346e8e5d9040d001803193b",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "63590444754add03e1d17abb",
        "idValue": "6346e8e5d9040d00180319da",
        "idCustomField": "6346e8e5d9040d00180319d8",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "63590447df4195010551e5d0",
        "idValue": "6346e8e5d9040d00180319e4",
        "idCustomField": "6346e8e5d9040d00180319e3",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "635904480a049701f640634f",
        "idValue": "6346e8e5d9040d00180319ec",
        "idCustomField": "6346e8e5d9040d00180319eb",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "6359044e65469b11a8a21fc6",
        "value": {
          "text": "10001221"
        },
        "idCustomField": "6357f39e0d5ead0085b00f9d",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "63590450225f7a00a19d7767",
        "value": {
          "text": "ASD14SA31ASD3"
        },
        "idCustomField": "6357f3a9d3334202cba5ed1c",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      },
      {
        "id": "63590458abc94906218a716e",
        "value": {
          "number": "50"
        },
        "idCustomField": "6357fadaacdcb204963566c8",
        "idModel": "635904193bd194021cfbbffe",
        "modelType": "card"
      }
    ]
  }
]

Thanks for confirming @suardim! The reason I asked about this is that empty fields were simply omitted by Trello. So, the number and order of items inside the customFieldItems for each card would differ for me, meaning I couldn’t simply drag and drop them in n8n. It would appear the same is true for you.

So, you would need to identify which idCustomField you want to use. For example, 6346e8dfd9040d0018031163 in your dataset belongs to an email:

image

Assuming you want to reliable write the values in this field in a column named “email” you could achieve this like so in your workflow:

In this workflow, I have of course replaced the Trello node with a Code node mocking the data you have. The interesting part is the Set node:

It transforms the rather complex and ugly Trello dataset into a more straightforward item with a single “Email” property. This works through an expression like

{{ $jmespath($json["customFieldItems"], "[?idCustomField=='6346e8dfd9040d0018031163']")[0].value.text }}

This expression uses the filter functionality of JMESPath to find the custom field item where the idCustomField equals 6346e8dfd9040d0018031106, regardless of how many custom field items there are or in which order they are. JMESPath can be used in n8n through the $jmespath() method. You can copy this expression and use it for other fields as needed, e.g. {{ $jmespath($json["customFieldItems"], "[?idCustomField=='6346e8dfd9040d0018031106']")[0].value.text }} should work for the first name field.

Once your items have columns headers such as Email (or top level JSON keys if you are looking at the JSON view), you should be able to append these to a Google Sheet with the same column headers.

Hope this helps! Let me know if you have any questions on this.

Thanks a lot for you helping out @MutedJam. Really appreciate it!
I am getting over it now… but I’m struggling to find information about this type of code structure.

If you now where can I lear more about it, please let me know.

How can set the value of a List Custom field using the $jmespath?

That’s my case:

image

Hi @suardim, I suspect these dropdown values would be defined in like this in your data:

{
  "id": "6359043b715efe040d574cd6",
  "idValue": "6346e8e0d9040d0018031221",
  "idCustomField": "6346e8e0d9040d001803121e",
  "idModel": "635904193bd194021cfbbffe",
  "modelType": "card"
}

So there doesn’t seem to be enough information for n8n to read the clear text values for such fields. This is the API call you would need for this, though I am not sure that’s worth the implementation effort.

Assuming your fields don’t change very often the easiest way would to be simply reference idValue in your Set node (similar to the text fields in the example above) and then replacing the possible options as needed. So something like this:

This uses the JS .replace() method to replace a known numeric ID with a human-readable text. You can chain multiple replace methods to account for all options.

Great @MutedJam you’re very helpfull! That worked well with an exception… how can I insert the other values? Is there an if option to insert between the code?

Because if I just creat another field with the same name, the first field stopped showing…

In your example, it would stop showing Option A and it would show the idValue, if the Option B is selected in trello.

As you can see bellow.

Just found out how to do it.

{{ $json["Tipo de Cliente"].replace('6346e8dfd9040d001803108e', 'Particular') .replace('6346e8dfd9040d001803108f', 'Empresa') }}
1 Like

Awesome, glad to hear you figured it out & thanks for confirming :slight_smile:

Now another problem.

The trello trigger is giving me an error

I inserted the in the Model ID the idLabel from a list, that I want to activate the trigger when a new card is moved.

image

It might be worth opening a new thread for this problem @suardim as it seems very different from the original problem. With regards to the trigger, it would be very important to understand how you have deployed n8n, which webhook URLs are shown in the trigger node and if you have any reverse proxy running in front of n8n.

I will be away for a couple of days and new topics on old threads are easily overlooked.

Hello everyone. Hello @MutedJam

Is there a way to configure the get cards list node?

I am running into some trouble when a card is added to a list, sometimes (it looks like it depends on the card creation date), the get cards list does not get the last card added to it.
It just gets the newest card by creation date in the list and gives that information

Hey @suardim,

Are you using the normal Trello node or a trigger? Can you also create a new thread as mentioned by @MutedJam replying to old threads can lead to the posts getting lost or older information that may not apply moving up the search list.

ok. will do that, sorry