Introducing Trellinator: Automate Trello with Google Apps Script

[#1]

UPDATE 2023: Hi all, unfortunately changes in Google Apps Script have made it very difficult to register the webhooks we need to in order to run Trellinator. We were kind of keeping up with some workarounds for a while but recent changes in the editor have made even those workarounds untenable. We have developed a standalone alternative to run Trellinator code called BenkoBot, it's a paid service because we have to host all the code but you can set up an account and try your first command for free https://app.benkobot.com/ most of you will find that Trellinator in Google Apps Script still works for a while, but eventually it will stop working and we won't be able to fix it for you. All your Trellinator code can be ported to BenkoBot with minimal changes, and if you email team@benkoworks.com we can put you in touch with a developer experienced with those migrations. You can see some additional documentation on our community board https://trello.com/b/IoHmhz5c/benkobot-community-board

Sorry :(

What is Trellinator?

Trellinator is an open source framework for automating Trello (and other things) using Google Apps Script (NB: since setting this up in Google Apps Script isn't always possible or ideal we can do the setup and hosting for you, you can check it out at BenkoBot).

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.

NB: There is a bug currently in Google Apps Script V8 runtime that returns the incorrect published web app URL, so even though you can switch to the V8 runtime later on, you need to disable the V8 runtime before you initialise Trellinator. You can track the issue here (and don't forget to "star" the issue so that Google is encouraged to fix it!) https://issuetracker.google.com/issues/184467246

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:

http://docs.trellinator.com/trellinator_hello_world.pdf

And browse the complete documentation here:

http://docs.trellinator.com/

If you'd rather have someone else write the code and/or host your code, check out BenkoBot:

http://www.benkobot.com/

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.

[Meg Holbrook]

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. 

[Iain Dooley]

@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]

Hey Iain, 

I've looped in @Bridget 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. 

[Iain Dooley]

@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.

[Rob Schaerer]

I just happened across this post -- I've implemented a bot to function on Team boards that I use for my business and it's been a game changer, fun that I had the same Idea as you although I wish I was as gifted with the coding (I'm a terrible programmer but I own lots of caffeine so it makes up for it).  I'm going to need that caffeine to follow what you're doing here -- I've not worked with the class structure that you have going on there before.  I'm going to look forward to going through some of this -- will make me a better programmer as a result. 

I didn't realize that Google apps only allowed 5 connections though.  I tried to find references about that and I couldn't (I'm not doubting you), but I was just about to roll out a responsive upgrade for my team that used the webhooks to 'respond' to actions on the board and now I'm thinking that your comment might have been one of those -- MANY -- learning experiences about finding a solution only to be blocked by a limitation of GAS.  I was surprised at how many times it sends webhooks just with my activity, much less the team -- What does GAS do with the bounced connections, do you know?  My understanding is that Trello will retry up to 3 times, correct?  

Edit: Do you have a process in your code that might be able to help me sort cards by their due dates in the JSON response from the API?  Of all the things to have a mental block on I just can't seem to get a simple solution to work in GAS without using a clunky for loop.   

[Iain Dooley]

@Rob Schaerer hey thanks for checking it out!

I settled on this version of classes because Google Apps Script doesn't have all the features of later versions of ECMA Script that are available in Node etc. I tried a few different options but this one was the easiest to implement, understand, modify and use. I'm sure there would be competing points of view :)

I also hadn't really used JavaScript much before I wrote Trellinator, I was thrown into a bit because I'd spent a year building Butler scripts for my clients and then I wasn't able to use it as a consulting platform any more so in about 8 weeks I had to write a replacement and then port over the 1500-1600 odd Butler commands over to it!! Caffeine certainly played a vital role there ;)

There's certainly no published quota on the number of concurrent connections allowed in Google Apps Script, but what I found was that Trello notifications were so voluminous, that the sheet where the script was bound would become unresponsive (ie. you couldn't even open the sheet) and vast quantities of the Trello notifications were just unceremoniously dropped into oblivion.

After building the buffer I experimented with various thresholds. 5 seems about optimal. The buffer keeps separate beanstalkd queues for each 10 second window, for each script and drip feeds out notifications with no more than 5 connections per script id over time, randomly selecting from the various queues for that script. This means that if you, say, archive 1000 cards at once even though it might take 4 hours for those notifications to feed through, subsequent events still get a chance to execute. This does mean, however, that the order in which notifications arrive is not guaranteed so you need to take that into account when writing your code.

I can't remember what code GAS sends when it bounces a request -- I think it might even accept them then just not execute them. It was a total train wreck, like the whole sheet would just lock up, it was not graceful in the slightest.

I'm actually going to modify the buffer to write notifications to a sheet in the target script via the API rather than using the web app deployment feature, so that Trellinator scripts can be deployed as add-ons at some point.

Funny you should mention sorting functions, I added some just the other day:

https://github.com/iaindooley/trellinator-libs/blob/master/List.js#L57

The key is just to sort in an array then use the "move" API endpoint to move into the same list at a specific position -- however there is some nagging little glitch that means sometimes one of the cards isn't in the correct order that I have to test and figure out!

[Rob Schaerer]

@Iain Dooley You actually hit on something that I have noticed as I have been writing some scripts that use google sheets as storage.  Any call to a sheet takes forever though if you're using that as a way to store the information -- I've migrated over to a private SQL server for data storage and it's been phenomenal.  For your purposes, either firebase or BigQuery (googles version of SQL) might be an opportunity too.  I've been playing around with the caching service too for short term information holds in scripts too and that might be something to consider for the short-term overloads of information for a sheet?  Just a thought.  

I did a test to see what writing to a sheet does to a script and when I post to the web app endpoint for a google sheet and then have that payload written to a sheet it takes roughly .75 - 1.25 seconds per post to write to the spreadsheet, which is ridiculous.  my mySQL server will run about .007-.03 seconds per read/write which is a breath of fresh air, and for your purposes I know that won't work if you want all of this to be run on the end user's side, but Big Query and firebase are still going to be within the google API umbrella and each user actually has an allotment of data that would more than accommodate the usage that you would have from trello api calls -- and they also have the ability to delete old info that you don't need any longer as well.  Just a thought.  

I do have a question for you since you have successfully deployed this in your business - what is a good resource for to help understand the permissions schedule in Google Apps Script?  I find it very tedious to have something up and running for me and then deliver it to a team member to solve an issue and have it error out or not work because of a permissions issue -- not because of functionality.  Trying to deploy my first sheet add-on to try to get around this but I'm having a very hard time navigating those waters and the resources online kinda die off when it comes to situations like mine.  

I don't want to write completely container bound scripts because I can already see that would lead to all sorts of maintenance issues with code and multiple versions of functions and the versioning nightmare that it creates b/c of multiple copies of sheets, but having everything be container bound seems to be the only way around all of the permissions issues to make them actually usable for my business.  

[Lucas Fogolin]

@Iain Dooley, is it possible to deploy the buffer to heroku?

[Iain Dooley]

@Lucas Fogolin I don't see why not, the components of the buffer are:

1) The Node.js "putter" script: this accepts notifications and puts them onto a beanstalkd queue. Very very lightweight. Splits up jobs per script ID and in 20 second "windows" to avoid a large number of operations slowing down all subsequent operations

2) The PHP "buffer" script: this takes jobs off the beanstalkd queue and posts them to the relevant Google Apps Script URL, with a maximum connections per script ID and randomly selecting from all 20 second "windows" for that script (I originally was just going to use Node for both but found myself in callback hell and figured I would just switch back to PHP where I belong :)

3) pm2: the npm package that monitors and runs both putter.js and buffer.php so they run again if they crash or the machine reboots

4) beanstalkd: the open source queue software from Facebook

5) Nginx: the thing that responds to Trello's HEAD request when a new webhook is registered and passes through everything else to the putter script

As long as you can make all those things work, you can deploy it there :)

[Iain Dooley]

@Rob Schaerer Things could definitely go faster if it weren't in a spreadsheet, but then it would all become so much more expensive ;)

So long as the buffer can sit there and drip feed the notifications through to the scripts it's actually no that big of a deal that they run slowly. It's kind of an advantage because it means that the scripts will never exceed Trello's on API rate limits!

And yes everything I do is a container bound script. The actual reason for this is not so much because of deployment but because that's the only way I can get access to a spreadsheet (for example to read configuration values) without having a document ID hard coded anywhere.

Which I guess is a deployment issue :)

I am in the process of developing Benko Board as an add-on and it's a bit of a headache, not least because you can't deploy scripts as both web apps and add-ons so I'll have to rewrite the buffer thing to write to a document using the API, and then use onEdit triggers in the script to execute the notification parser. Watch this space ... for a long time ... and you might see it happen ;)

In the meantime I just use Zoom to remote control people's screens when I need to set up a script to run as them.

The issue of script maintenance and versions is, in part, resolved for my use case by using libraries. That's what I do for Benko Board. Each client script links back to a library which is what gets updated, but then when it's updated, each client script needs the version bumped and the web app needs to be redeployed -- as such, Benko Board updates are a hassle! An add-on will fix that problem ...

However Trellinator is deployed as a complete block of code inside a container bound script. Also, for each client, they have their own set of Benko Board library, obviously there needs to be a good way to push code of various versions out to various containers, so what I use is clasp as git submodules.

So for example when you set up Trellinator using the init script, it adds a few submodules that are the "core" Trellinator code. You can then easily pull updates from these later using the trellinator upd command line command.

You can see in more detail how I handle version control etc. by going through the more complicated "end to end" Hello World which includes how to use clasp and node for command line automated testing:

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

[coccoinomane]

Hi @Iain Dooley

Thank you for sharing Trellinator!

I like the way you concatenate methods in order to handle notifications from webhooks.
The whole notification class is very clever 👍🏼

Cheers,
Guido

[Rob Schaerer]

@Iain Dooley I've been playing around with this a bit and I have a suggestion that might help with the buffer issue quite a bit.  I've been utilizing the script properties more now that I've gotten used to using them -- they are hundreds or thousands of times faster than doing a Spreadsheet read.  

In going through your code it looks like every call to the endpoint goes back through that bottleneck and sometimes a couple times.  I've modified the code on the version that I have, but with two very simple changes I think that you might be able to avoid the buffer altogether by adding an onEdit to capture the range in the sheet for the api key and then to store it in the script properties service (few issues with permissions with that) and then referring to the properties service instead of the spreadsheet:  Based on the quick overview of what you've done it all filters back to that spot so it's a pretty clean change in the code to increase the speed of this from seconds per run to milliseconds per run.  

1) adding onEdit():

function onEdit(e) {
       var cell = e.range.getA1Notation();
       if (cell == 'C2') {
          PropertiesService.getScriptProperties().setProperty('trellokey',e.value);
       } else if (cell =='C3') {
          PropertiesService.getScriptProperties().setProperty('trellotoken',e.value);
       }
}

2) changing: TrelloApi.checkControlValues = function() to:

TrelloApi.checkControlValues = function()
{
if(!TrelloApi.trello_api_key_information)
{
var appKey = PropertiesService.getScriptProperties().getProperty('trellokey');

if(appKey == "")
TrelloApi.trello_api_key_information = {key: "", token: "", err: "Trello Key not found in " + CONFIG_NAME_ + " tab." };

var token = PropertiesService.getScriptProperties().getProperty('trellotoken');

if(token == "")
TrelloApi.trello_api_key_information = {key: "", token: "", err: "Trello Token not found in " + CONFIG_NAME_ + " tab." };

//both f ound
TrelloApi.trello_api_key_information = {key: appKey, token: token, err:""};
}

return TrelloApi.trello_api_key_information;
}

-----
Edit: there is another place that you will need to make the change above in the function getTrelloKeys_ that I forgot to post up above:  

function getTrelloKeys_()
{
var trelloData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG_NAME_).getRange("B2:B3").getValues();

var appKey = PropertiesService.getScriptProperties().getProperty('trellokey');

if(appKey == "")
{
throw "Trello Key not found in " + CONFIG_NAME_ + " tab.";
}

var token = PropertiesService.getScriptProperties().getProperty('trellotoken');
if(token == "")
{
throw "Trello Token not found in " + CONFIG_NAME_ + " tab.";
}
//both found
return {key: appKey,
token: token,
err:""};
}

I'll be testing this out soon as well to make sure but on first glance, I think this might help.

[Iain Dooley]

@Rob Schaerer to avoid me screwing this up when I copy/paste from here could you send a pull request on github?

Also if memory serves me correctly, we used to use PropertiesService to get the credentials and store them, however it would exhaust the GAS quota for that pretty quickly so switched to fetching from the spreadsheet once per execution and caching the values in memory for the duration of that execution.

[Iain Dooley]

@coccoinomane thanks! Yeah I wanted to create something that felt as "fluent" as Butler commands, so you'll notice that the notification class is very "butler like" in its syntax.

new Notification(posted).addedCard().moveTo("List Name");

etc. I wrote Trellinator to replace Butler and had to create it then port over about 1600 commands I'd written for clients during the previous year so I needed something that allowed me to quickly and easily map all those Butler commands and then continue to deliver work for my clients at a similar price point going forward -- all in about 2 months! Didn't get much sleep I have to say ;)

Let me know how you go with it, you can comment on the Roadmap Trello board with ideas or anything, I'd love to hear how you're using it.

[Rob Schaerer]

@Iain Dooley I'll do that.  

In terms of the properties service I'd be surprised if you had issues bumping into limits with that since even the basic service allows 50,000 calls to the properties service per day and for the business accounts is 500,000 per day.  I'll take a look back at the code again but from what I was seeing I didn't see where it stored the values in memory but then that could also be my lack of direct familiarity with how Google handles the class structures but my assumption is that because it's a class that for every object has it will run the function separately for each class and subclass as it's defined or used.  I'll throw a logger in that area and try it out though -- I've already learned quite a bit from going through this code.  Thanks again for both writing this and putting it out there.   

[Iain Dooley]

@Rob Schaerer you'd be surprised how many notifications Trello sends and if you're using PropertiesService for fetching credentials + a few other variables you run out even at 500,000.

However, it does strike as possible that the best bet is to combine the 2 options and cache the values in PropertiesService but then cache them in memory also, so that we get the speed up from having it in PropertiesService but only hit that once per execution.

You can see the function sets the global variable trello_api_information or something similarly named so that it only fetches it from the spreadsheet once per execution, but even this could slow things down quite a bit -- I don't think it would remove the need for the buffer entirely, but even if we could increase the allowed SCRIPT_MAX from 5 to, say, 50 that would be a 10x increase in throughput!

[Rob Schaerer]

@Iain Dooley I've seen how verbose Trello is -- you get a couple people using it at once and I bet it would get pretty busy!  

If there is any concern for the limit of the properties service then you could actually cut down on the calls to the properties service in half by only using one to store both properties by using JSON.stringify("keys":{"token":token,"apikey":apikey}}), then just call the 'keys' property to get the object.  would just be a simple change of the onEdit to accomplish this as well.  I'll play around with it.  

In some of my apps that I'm using I'm opting to hard code in global variables for some of the keys to avoid this as well, but from an end user perspective it's nice to not have to make them pop the hood.

[coccoinomane]

@Iain Dooley @Rob Schaerer 

Hi guys,

You might be interested in how this developer managed to store in Google Scripts' cache arbitrary amounts of data by dividing them in 10KB chunks.

He uses the CacheService though, which has a maximum persistence of 6 hours... not sure whether it solves the problem at hand, but might be useful for other parts of Trellinator.

Cheers,
Guido

[lukasz.popowski]

HI ! 

If it's possibility that i will create the card and than this card will automatically attachments to another card as "subtask" (on another board) . And this merge will by {triggercardname} ??

Please helps guys !

[Iain Dooley]

@lukasz.popowski yeah sure you can set that up, I wrote about sub-tasking with Butler and Trellinator here:

https://community.atlassian.com/t5/Trello-articles/Trello-Subtasks-with-Butler/ba-p/1060392

No reason the cards can't be on different boards

[Thibault Molleman]

@Iain Dooley from what I understand, Trellinator runs as a bot account (similar to how the, soon to be legacy, butler bot did)? If so, do I then need to pay an extra user license? (or is it excluded from that rule like the butler bot is/was)?

[Iain Dooley]

@Thibault Molleman yep it's a bot user, and with the changes to guest user licenses you would need to pay if you invite the user to more than one board in a business class/enterprise team.

[Thibault Molleman]

I feel like it's something that should atleast be shortly mentioned on this page you made, so people are aware of that.
(what's the reason btw that going with a bot instead of the api is more common it seems? (because butler also started as a bot))

[Iain Dooley]

@Thibault Molleman I don't feel compelled to mention it because it's pretty clear already how users and billing work from Trello's own documentation.

Trellinator does use the API, it's a Google Apps Script wrapper around Trello's own REST API but with a "butler like" syntax and some ease of use around webhook registration, deployment and command scheduling.

The reason I don't use power ups is because they're architecturally inadequate to build the sorts of systems my clients require.

[Camiel_Wieme_SA]

Hello, 

I'm trying to get Trellinator work for me, but when Initializing the sheet, I get an errormessage:

["Getting Trello member ID invalid token","Webhook registration failed - HTTP:400:invalid value for idModel","Trellinator Initialization SyntaxError: Unexpected token: i"]

Do you have any idea what I've done wrong?

regards, 

Camiel