Introducing Trellinator: Automate Trello with Google Apps Script

What is Trellinator?

Trellinator is an open source framework for automating Trello (and other things) using Google Apps Script.

Why is this a good idea?

  1. Google Apps Script comes free with every Google account
  2. Google Apps Script is serverless and maintenance free
  3. Google Apps Script allows you to easily interact with all your other Google Services, and other products too via their APIs
  4. You can easily use Google Sheets to store information in a transparent way that you can interact with without having to learn any special database skills
  5. Google Apps Script is just JavaScript so there are millions of people who know how to write it, and it’s not hard to learn

Trellinator runs as a “bot”, a separate Trello account created just to automate commands in Trello. Trellinator code lives inside your Google Drive account and is owned entirely by you.

Trellinator is deployed as a script attached to a Google Sheet document, which is then deployed as a web application. This means that it can register a “webhook” with Trello boards, and react to events within Trello, for example when you set a due date it can schedule to remind you when the card is due.

A Note About Privacy

Even though Trellinator runs using a Trello account you control inside your own Google Drive account, there are 2 problems with using Google Apps Script to automate Trello that need to be solved with a “buffer” that sits in between Trello and Google Apps Script:

  1. When you register a webhook with Trello, Trello sends a special web request called a “HEAD” request. Google Apps Script doesn’t know how to deal with this type of request and;
  2. Trello sends LOTS of notifications, and Google Apps Scripts deployed as web applications can only handle about 5 connections at a time

Therefore, a little “buffer” needs to sit in between Trello and Google Apps Script. By default, this sits on an AWS server owned by The Procedure People, but the code for it is open source and if you wanted to you could set up your own:

https://github.com/iaindooley/trellinator-buffer

The notifications are never stored permanently and in fact this is probably less of a privacy concern than almost all the other power ups and Google Chrome extensions you already use regularly but I thought I should mention it.

Hello World!

First, make sure you’re logged into your Google Drive account in a web browser and, in the same browser, login to a Trello account that you have set up just to be your Trellinator.

You don’t need a separate Google Account, you can set up a new Trello account using an alias of your existing Google account by just adding a “+” sign.

For example if I have a Google account team@benkoboard.com I can create a new Trello account using the email team+trellinator@benkoboard.com to run my Trellinator bot.

In this new Trello account, change the default “Untitled Board” to be named “Test Board” and then add your personal Trello account to the board.

Now open this template:

https://docs.google.com/spreadsheets/d/1-GfswT3E9szGl0vkCbFRvvirvicQ8ScUyncRoUCX3SM/edit#gid=0

Choose “File > Make a copy”. You can name the file whatever you like.

Now open the link in cell C2 of the Configuration sheet in a new browser tab and copy and paste your Trello API key back to cell B2 and hit enter to save this value.

Likewise, open the URL in cell C3 of the Configuration sheet in a new browser tab and authorise Trellinator, then copy and paste the token back to the cell B3 and hit enter.

Now choose “Tools > Script Editor”. Once the Script Editor opens choose “Publish > Deploy as web app”. Change the value of the “Who has access to the app:” dropdown to “Anyone, even anonymous” and then click the “Deploy” button. Click “Review Permissions” then allow access.

Switch back to the spreadsheet (but leave the Script Editor open in a new tab) and choose “Trellinator Utilities > Initialize”.

A bunch of sheets will be created, including one called “Test Board” (assuming you renamed the default “Untitled Board” to “Test Board” above).

Go to the “Test Board” sheet and put the value helloWorld into cell A2.

Now, logged into your personal Trello account, go to the Test Board, create a card and then post a comment on the card.

If everything is set up correctly, after a 4 - 5 seconds you will see a comment posted back by your Trellinator user that says “Hi! I'm Trellinator”.

Something more interesting

In order to really take this thing for a spin and give you a glimpse of the power of the platform, start by copying this code and pasting it into the end of the Commands.gs file in the Script Editor you opened earlier:

https://gist.github.com/iaindooley/5b4f7cbd3e3c4bfe7ad94a5e7abdb7cc

Now choose “Publish > Deploy as web app” again and change “Project version” to “New”, double check that the “Who has access to the app” dropdown is still set to “Anyone, even anonymous” and then click the “Update” button.

Now choose “Run > Run function > setup”. This will create two new boards in your Trellinator Trello account, one called Project Template and one called Projects Overview.

Using your Trellinator Trello account, add your personal Trello account to both of these boards.

You will also see 2 new sheets in your Trellinator spreadsheet. Open the sheet for “Projects Overview”, and enter the value createNewProjectBoard into cell A3.

Now go to the sheet called “Global Commands” and enter the following values into the cells indicated:

  1. Enter the value “Project Boards” (without double quotes) into cells A2 and A3
  2. Enter the value cardStarted into cell C2
  3. Enter the value cardFinished into cell C3

Now, from your personal Trello account try creating a card in the “To Do” list of the “Projects Overview” board.

You will see a new board copied from the template, your personal Trello account will be added to it and a link to this board will be attached to the card you created in Projects Overview.

If you look in the Global Command Groups tab of the spreadsheet, you’ll see that this newly created board was added to the Project Boards group. This means it inherits the behaviour defined for Project Boards in the Global Commands sheet.

Now create a bunch of cards in the “To Do” list on your new project board. As you move each card to the “Doing” list, a comment will be added to the overview card indicating that work has started, and as you move each card to “Done” a comment is added indicating that work has finished on that card.

Removing Trellinator

If you want to safely remove Trellinator, all you need to do is revoke access to Trellinator from the Applications section of your Trellinator Trello account, then delete the Google sheet you created for it.

Learn more

This has just been a little taste of how Trellinator works. There’s lots more to learn, in particular how to run time triggered and recurring tasks, and how to create well tested code with proper version control.

You can find a much more comprehensive tutorial here:

https://www.theprocedurepeople.com/trellinator-automate-trello/docs/trellinator_hello_world.pdf

And browse the complete documentation here:

https://www.theprocedurepeople.com/trellinator-automate-trello/docs/

We also have a Trello board where you can leave ideas and comments, and see what’s planned:

https://trello.com/b/4SEU3lqj

And of course you can email team@benkoboard.com we’d love to hear from you.

4 comments

Meg Holbrook Community Champion Nov 07, 2018

Hi Iain, 

I can appreciate the product you are offering here, but would recommend that you review the Rules of Engagement post for the community best practices.

Posts like this should only be created in the Marketplace section of the forums (and only if your products are featured on the marketplace). Since your products are not a part of the marketplace and have not been reviewed by Atlassian, this could be seen as predatory. 

I welcome you to engage with the community in meaningful ways that are not direct opportunities to sell your product. 

Like 4 people like this

@Meg Holbrook Trellinator is free and open source so this article isn't selling a product, however I have made posts selling either Benko Board or Trello automation services where appropriate. Whenever there is a solution that doesn't involve the API or a 3rd party product, I present that. Wherever the solution is a product that I haven't created (like Butler or Ultimello or other power ups or extensions) I present that solution.

Is there even a solutions partner programme for Trello? I have asked and looked and can't find one. I also can't find a marketplace segment for Trello other than the Power Ups section.

From what I've seen the typical response people get when they say "I would like Trello to do XYZ" is "It can't do that, submit a feature request" which is a total downer of a response and makes Trello look super weak.

In fact, just the other day one of your responses was "Trello can't do that, you should try Jira"!

Trello's secret weapon is its API but most people don't know where to start with that and the only "marketplace" for Trello API solutions right now are Chrome extensions and Power Ups both of which have architectural flaws preventing them from truly solving the bulk of these problems people have satisfactorily.

I initially did this post promoting a free, open source API framework I wrote that allows people to combine the power of Google Apps Script with the Trello API to create some amazing solutions, and then I proceeded to provide free code samples using the Trellinator API that would allow people to copy and paste the code after following the instructions to set up Trellinator and get the outcome they were looking for.

Using the setup instructions above anyone would be able to pay a small fee to a freelancer on Upwork and get this set up on their account, and build a thriving market for Trello automation but even then it's too much.

So I created a page where people could submit their feature request to get a one off, low cost estimate to have me write and install the Trellinator API code that would create the behaviour they were looking for.

As far as I can tell, people just want their problems solved, and right now they're not getting solved. I have the solutions, so I present them.

How can I present them differently so that they're conforming to the community guidelines while still actually presenting useful solutions?

Meg Holbrook Community Champion Nov 07, 2018

Hey Iain, 

I've looped in @Bridget Sauer from the Atlassian team to discuss. 

Solution design is great and should be encouraged, but when the posts are sales-like in nature, it's a pitch. 

So I created a page where people could submit their feature request to get a one off, low cost estimate to have me write and install the Trellinator API code that would create the behaviour they were looking for.

I agree that you have posts that are beneficial to the community and are organic so I would encourage more posts like that without the express intent that people are going to click through to your external site and implement your tools. 

Best of luck to you with finding the right balance to adhere to community guidelines and contribute your valuable feedback. 

Like 1 person likes this

@Meg Holbrook thanks for tagging in @Bridget Sauer. I've also sent her an email with some of the same questions.

The post you're referring to is this one:

https://community.atlassian.com/t5/Trello-articles/Trello-Automation-on-Demand/ba-p/933617

Which has been marked as Spam (probably why you couldn't locate it and found the above post instead which is not a pitch but an introduction to an open source framework I've created for automating Trello with Google Apps Script).

I understand entirely the guidelines guarding against people coming in here to simply peddle wares, but let me illustrate the challenge with providing an API solution.

Here is an example of some code I wrote, tested and shared for free in response to a community post the other day requesting a sort of "table of contents" list for each board:

https://gist.github.com/iaindooley/3b5098e08fa2d92cbee6a1e1541de61a

Those functions, installed using Trellinator, would achieve precisely what the OP wanted to in the answer where I posted it.

The reason I can do that for free in response to a community post is because Trellinator is so powerful.

In order to provide the same functionality in "raw" API calls would take 100 times (maybe 1000 times!) the amount of code and LOTS of trial and error and the recipient of the advice would have a real task ahead of them in setting up the hosting capability to register the webhooks required to make it work.

It would be unfeasible to solve that problem in response to an adhoc community question.

Even creating the same system with Butler Bot would be pushing its capabilities, let alone with the Butler Power Up which isn't as powerful as Butler Bot.

With Trellinator, I can write that solution in 10 minutes!

I can then provide people with instructions to install Trellinator in their Google account and set up the code to execute.

So I started off doing that: I created a few posts linking to the above instructions and providing code samples that would enable people to have the solution they were looking for, and I did that for free because I want to promote more Trello automation generally and also maybe get a few enquiries about my professional services (as you say, organically).

But people don't know how to do that, they just want their problem solved, they don't know how to hire on Upwork or get a developer to do what they want, or how to design the solution etc.

So all that was happening was that I was spending my time writing code that no-one was actually using, even though it solved their problem, because they didn't know how to install it.

Currently no-one else in the world knows how to use Trellinator, so I have to build up support for it from somewhere, where better than within the Trello community?

I hope there's a way to do this formally because this framework (which again is free and open source) is capable of delivering the sorts of solutions via the Trello API that the community is crying out for, at a tiny fraction of the cost that it would have been traditionally to get these sorts of things built on a bespoke basis (and in a way that neither Power Ups nor browser extensions will ever be able to match because of limitations of their architecture).

Perhaps the best way for me to approach it is to say:

1) Here is the framework Trellinator, it's free, it's open source, and here are the instructions to set it up

2) Here is some code that will do what you want in Trellinator, for free (such as the "toc lists" solution example I gave above)

3) If you don't know how to set that up and install it, or don't know how or don't want to bother hiring someone on Upwork to do that for you, here's my website, I can do it for $50 - $200

I look forward to more feedback from Bridget and yourself on this approach. I want to solve those problems and make Trello stronger in the market, but I also don't just want to keep writing the code samples for people who don't know where to start in terms of installing and running them.

I can't spend the time to install, test and debug these solutions for free, but I can do that for a very low one off cost. As far as I can see that will be of a huge benefit to almost everyone posting questions in the Trello community.

Comment

Log in or Sign up to comment
Community showcase
Published Nov 29, 2018 in Marketplace Apps

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,330 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you