Back to Blog

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 a Trello – Google Sheets integration. This allows you 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. A Google Sheets script like this one will do the job easily. But there is also an alternate way that many users may prefer. Why? It allows you to automate data import from Trello without any code line. Plus, you’ll be able to refresh your knowledge of Google Sheets functions. Check out this how-to guide below.

For a better understanding of the following steps, we recommend you read our blog post “Trello to Google Sheets Integration“. It’s an intro to connecting Trello to spreadsheets (with the example of getting Trello board data)

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!

Step #1: Install Coupler.io

Coupler.io is a Google Sheets add-on. It connects spreadsheets to third-party apps and data sources, such as Trello, Airtable, online published CSV, and many more. For more on this, visit the Coupler.io home page

Install the add-on from the G Suite Marketplace with this direct link

Step #2: Set up a Trello #1 importer to import 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. 

Run Coupler.io: You’ll see it in the Add-ons menu of your spreadsheet. Open the dashboard, click +Add Importer and select JSON Client.

After that, insert the following parameters into the respective fields:

Title – Add the name of the first Trello importer (it’s up to you). 

Sheet Name – Type in the name of the sheet the data will be placed in (it’s up to you).  

JSON URL – Insert 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:

  • {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 click Add Importer. A separate sheet for the imported data will be created. Run the importer to pull data.

If needed, choose the frequency of Automatic data refresh or toggle off to disable this feature.  

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

Step #3: Set up a Trello #2 importer 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 Client importer. Click +Add in the Coupler.io dashboard and select JSON Client.

The rest of the flow is the same, but the JSON URL will differ:

JSON URL – Insert the following JSON URL to import all custom fields for a Trello board by cards:

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 Add Importer and run it afterwards to pull data to the spreadsheet.

Step #4: 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 will be needed. The Coupler.io team hopes this example will be useful for your workflow. Good luck with your data!

Back to Blog

Access your data
in a simple format for free!

Start Free