How to Import Custom Fields from Trello to Google Sheets

In one of our blog posts, the Coupler.io team explained how to set up the Trello to Google Sheets integration to import versatile data entities from Trello. However, one of the users faced a challenge: how to get card data with Trello custom fields data attached.

You can do the job without any code line, but you’ll need to set up another integration. Check out this how-to guide below.

Trello Custom Fields in a spreadsheet

Task:

Import custom fields attached to a specific Trello card.

Solution:

We’ll set up an importer that pulls all custom fields for a Trello board. But it does not import field names. So, we’ll need to set up another one that imports all custom fields by cards for a Trello board. 

Once the raw data is in the spreadsheet, we’ll make a separate sheet and map data from both importers by cards. Here we go!

Export data from Trello using Coupler.io

Coupler.io is a tool that allows you to connect third-party apps and data sources, such as Trello, to Google Sheets, Excel, or BigQuery. Check out the available integrations in addition to Trello. 

If your destination is Google Sheets, you can install the Coupler.io add-on from the Google Workspace Marketplace. 

Set up a Trello importer #1 to pull all custom fields for a Trello board

An importer is a ‘bridge’ between a spreadsheet and a data source. For our use case, we need to erect two ‘bridges’ to pull data from Trello to Google Sheets. 

Sign up to Coupler.io with your Google account and click +Add new importer. Give it a name and complete the setup:

Source

• Choose JSON as your source application.
• Use the following boilerplate as the JSON URL to import all custom fields for a Trello board:

https://api.trello.com/1/boards/{board-id}/customFields?key={API-key}&token={server-token}

• {board-id} – you’ll find it in the Menu => More => Link to this board 

or simply in the URL bar:

• {API-key} – Get your Trello API Key at https://trello.com/app-key 

• {server-token} – to generate a server token, click Token on the Developer API Keys page and allow access for the server token. 

Replace {board-id}, {API-key} and {server-token} with your values. Here is the JSON URL you should get:

https://api.trello.com/1/boards/REAi4UiD/customFields?key=cfd453b9d2dfe5321137gdb3465c7og&token=23dch4dte21a8daf3et1fch546de712css341g24cb6dcd6ca

Insert it into the JSON URL field and Jump to the destination settings.

Destination

• Select Google Sheets as your destination application and connect your Google account
• Pick a file and a sheet where to load your Trello data.

Now you can click Save and Run to make the first import. Optionally, you can also enable the Automatic data refresh and configure the frequency.

We did not touch upon the Additional fields and Settings. If you want to learn more about these sections, check out the Coupler.io Help Center. 

Set up a Trello importer #2 to import all custom fields sorted by cards 

The second “bridge” will let us differentiate custom fields by card. For this, we need to set up another JSON importer. Click +Add new in the Coupler.io dashboard.

The setup flow is the same, except for the JSON URL which should be the following:

https://api.trello.com/1/boards/{board-id}/cards?fields=name&customFieldItems=true&key={API-key}&token={server-token}

The variables ({board-id}, {API-key} and {server-token}) are the same. So, the resulting JSON URL should look as follows:

https://api.trello.com/1/boards/REAi4UiD/cards?fields=name&customFieldItems=true&key=cfd453b9d2dfe5321137gdb3465c7og&token=23dch4dte21a8daf3et1fch546de712css341g24cb6dcd6ca

Click Save and Run to pull data to the spreadsheet.

Map imported data to deliver summary on Trello custom fields 

We’ve applied the power of Google Sheets to put the custom fields data in order:

Check out how it looks in the spreadsheet. And here are the formulas we used: 

Column A

At first, we applied the QUERY function to pull the column with the Card Names:

={"Card name";
   query('All custom fields by cards'!A:Z, "select B offset 1", 0)
}

Read our blog post to learn more about Google Sheets QUERY.

Columns B and C

In columns B, we applied a combination of ARRAYFORMULA, IF, LEN, and VLOOKUP. As a result, we mapped Custom Field types by cards.

={"Custom Field type","Custom Field name";
   ARRAYFORMULA(
     IF(LEN('All custom fields by cards'!E2:E)=0,,
       VLOOKUP(
          'All custom fields by cards'!E2:E,
          {'All custom fields'!A2:A, 'All custom fields'!H2:H, 'All custom fields'!F2:F},
          {2,3},false
       )
     )
   )
}

IF, LEN in the formula is a hack that allows you to switch off ARRAYFORMULA+VLOOKUP for empty cells. Without this hack, the formula would have worked with every row and returned #N/A for empty cells.

Columns D, E, F, G 

We queried columns with Text value, Number value, Date value, and Checklist value of Custom Fields and assigned the respective labels. 

=query('All custom fields by cards'!D:Z,
             "select D,H,J,K 
             label D 'Text value', 
                       H 'Number value', 
                       J 'Date value', 
                       K 'Checklist value'",
1)

Column H

Another VLOOKUP-based formula to map Custom Field dropdown list values by cards.

={"Dropdown list value";
   ARRAYFORMULA(
    IF(LEN('All custom fields by cards'!I2:I)=0,,
     VLOOKUP(
        'All custom fields by cards'!I2:I,
        {'All custom fields'!I2:I,'All custom fields'!K2:K},
        2,false
     )
    )
   )
}

To wrap up

That’s it! Two Coupler.io importers and a few manipulations in Google Sheets did the job. Moreover, if you automate the data import, the Custom Fields Summary will also update automatically. So, no manual work or Google Apps scripts like this one are needed. In addition, you had a chance to refresh your knowledge of Google Sheets functions :). So, we hope that this example is useful for your workflow. Good luck with your data!