Update (2021-03-16): It looks like the workaround used here for registering the webhook doesn’t work if you use the new editor in Apps Script. Switch to legacy editor to make sure everything is set up correctly (hopefully the legacy editor is still available when you’re reading this).

Note: Using this method, you’re not limited to reading and writing just to Custom Fields, like my script does. You can use any data that Trello API provides, but you’ll have to modify the code to suit your needs.

Meet Horhand

I recently created a new character for a D&D party we play with friends. His name is Horhand and he has lots of neat tricks and abilities for me to use. The descriptions of all those abilities didn’t fit in the charater sheet, so I decided to create a Trello board to collect all the information I might need during the game.

I enabled the Custom Fields power-up to be able to attach values like Damage: 4d6 to cards and see it on the front of the card. But there’s a problem. The damage an ability can deal might depend on the character’s level. Every time Horhand gets a level-up, I would have to go over every card and check if I should update any values.

Sounds like something can be automated right here!

Trello board

Since I already reached my power-up limit with Custom Fields, I couldn’t add any automation power-ups like Butler without upgrading my account. Luckily, Trello provides webhooks API. Webhooks send HTTP requests to user-defined URL every time something on your board changes. Those requests can be routed to a Google Apps Script linked to a spreadsheet. That spreadsheet would consume updated values from Trello, do some spreadsheet magic and then update Trello cards with new values through the use of Trello API.

Horhand has an ability called Alchemical Fire that deals damage based on his level and requires a Dificulty Check of 8 + proficiency bonus (which also depends on the level) + Intelligence modifier. Here’s what the cards in Trello look like:

Please note that currently, the script doesn’t support the Dropdown/List Custom Fields and will ignore them.

Important: while Trello technically allows you to create multiple Custom Fields that share a name, this script assumes every field’s name is unique. Issues may arise if you break this assumption.

Google Spreadsheet

In the Google Spreadsheet, there is a list of all values I need to import from Trello and a list of values that will be exported back, along with the formulas for calculating them.
These lists use the format Card Name@Custom Field Name.

An important detail is that left columns of these lists are assigned to Named ranges called TrelloInput and TrelloOutput. The script will iterate over these ranges to match Trello cards with cells in the spreadsheet.

You may make a field both Input and Output, but be careful not to make a cyclical dependency that will cause an endless loop of updates.

I left a blank cell at the end of each range to be able to quickly add new values with Insert row without manually extending the Named range.

Also in the spreadsheet are tables for determining proficiency modifier and Alchemical Fire damage from character’s level and Intelligence modifier. These tables are referenced in Output cells.

Let’s move on to the script.

Google Apps Script

I’m not going to explain inner workings of the script here. If you are interested in that, check the code out – it has comments! Let’s go through the setup process.

1. In the Spreadsheet editor menu, select Tools > Script editor.

2. Name your project whatever you want, and copy-paste the source code from here.

3. Go to this page, get your Trello API key and generate a token. Paste those into the code as values for trello_key and trello_token variables.

4. Go to your Trello board, but append .json to the URL. This is the data model of your board. Grab the id of the board (it will be right in the beginning) and assign it to the trello_board_id variable in the script.

5. In the script editor, select Publish > Deploy as web app... from the menu. In the Execute the app as: field, select Me.

6. Since webhooks are sent from Trello server and don’t use any Google account, in Who has access to the app: we would want to select Anyone, even anonymous. But then we would face a problem with webhook registration.

   When you ask Trello to register a webhook, it sends a HTTP HEAD request to the provided URL and expects an HTTP 200 response. But for some reason, with that field set to Anyone, even anonymous, Google responds with HTTP 403 to such requests. The workaround, as suggested here, is to set the access option to Anyone first just to register a webhook, since that will return an HTTP 200 response. Do it now.

   Update (2021-03-16): This workaround doesn’t seem to work with the new Apps Script editor. See top of this article.

   

7. Press Deploy to start your app. Google will ask you to authorize and warn you that the app is not verified. It looks scary, but since it’s your own app, you can bravely click Advanced and click the link to go on with the authorization (the code doesn’t send any of your data anywhere but to your Trello board).

   Note: After you first published your app, if you make any changes to the code (like editing values on config variables) you will have to update the app through Deploy as web app... window. The important part is to always select New project version when updating, or the app will keep using the old code!

8. To register the webhook, just run register function in the script. You only have to do this once. Select it from the list of functions and press Run. If everything’s okay, it will run without any error messages.

   

9. Now go back to Publish > Deploy as web app... and change the last field to Anyone, even anonymous. Press Update to apply the change. Now the webhooks will trigger functions in the script!

You can run unregister function to clear all webhooks that callback to your app.

Result

Go to Trello and try changing any of the values you marked as Input in the spreadsheet. If you did set the spreadsheet up correctly, these changes will cause Output fields to update with new values!

If you also want updates to happen when you edit the spreadsheet, you’ll have to create a trigger. In the Script editor, select Edit > Current project's triggers. Click Add a new trigger and choose onUserEdit to run on From spreadsheet On edit event.