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.
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!
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.
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.
In the Spreadsheet editor menu, select
Tools > Script editor.
Name your project whatever you want, and copy-paste the source code from here.
Go to this page, get your Trello API key and generate a token. Paste those into the code as values for
Go to your Trello board, but append
.jsonto 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_idvariable in the script.
In the script editor, select
Publish > Deploy as web app...from the menu. In the
Execute the app as:field, select
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
Anyonefirst 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.
Deployto 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
Advancedand 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
Newproject version when updating, or the app will keep using the old code!
To register the webhook, just run
registerfunction 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.
Now go back to
Publish > Deploy as web app...and change the last field to
Anyone, even anonymous. Press
Updateto 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.
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
On edit event.