Adding Member to the List Based on Google Sheet

[Jason Tan]

Current Situation

In this board, I have two lists - Day and Night. These two lists are already created within the boards. Both lists also have card created and there is only one card within the list.

I have 10 members who are A1, A2, A3, A4, A5, B1, B2, B3, B4, B5.

Think of A1 = Jason, A2 = Craig etc...

A1 - A5 are assigned to Day whilst B1 - B5 are assigned to Night.

Challenges

Over the time, I would like the different members assigned to each of the board. For example, A1 - A5 are currently assigned to the card in the Day list and often they stay the same until I change up the members for the card in the Day List. For example, I just want to simply mix it up by having A1, B1, A4, A5 and B5 assigned to the card in the Day List for next month. For the month after, it will be randomly mixed again with B2, B3, B4, A1 and A3 being assigned to the card in the Daylist on the month after.

For this step, I have to do it manually. 

Objective

I would like to automate the steps of assigning / rotating (randomly) the members to each of the the card in the list weekly.

What have I tried?

I have tried to use Zapier but I stumble into issue which requires organisation, when trying to use the function of "Find Members". I can't seem to change my board from Private to Organisation. See print screen

Has anyone ever tried this or any other solution to make it working?

[Iain Dooley · Accepted solution]

@Jason Tan can you explain what this means?

"I would like the different members assigned to each of the board"

Maybe if you provide some elaboration on what the real world scenario here is, rather than describing it in the abstract, the functionality you need will be clearer

[Jason Tan]

Hi @Iain Dooley 

Updated the details with better and more examples. Would this be making sense?

[Iain Dooley]

@Jason Tan i think there are some gaps in the terminology. Here are the parts I can't understand:

"A1 - A5 are assigned to Day List whilst B1 - B5 are assigned to Night List."

You can't assign Trello members to a list. What are "A1 - A5" are they Trello users? Are "Day List" and "Night List" lists in Trello? Columns in a Google Sheet?

"I would like the different members assigned to each of the board"

You can't assign members to a board. Do you mean add them to a board? Which board do you want to add them to? Do you mean assign members to a card? Which cards do you want to assign them to?

---

The rest of it I can't understand for similar reasons, once you clarify the above I might have a better idea of what's going on.

[Jason Tan]

A1 (Jason) - A5 are the Trello members whom I have invited to join the board Hello World.

In the Hello World board, I two Lists - Day is one list and Night is the other list.

Each of these Lists (Day & Night) in the Hello World board have got invited Trello members assigned to them.

Could this make better sense?

[Iain Dooley]

@Jason Tan you can't assign members to lists. You can only assign members to cards.

So I get that A1 - A5 are the members, but what do you mean when you say that these members are "assigned to lists"?

Lists contain cards, members can be added to cards.

[Jason Tan]

Thanks for the correction on the terminology. That's correct. Each of the list has only one card, and members are assigned to the cards. I would like to automate the process of assigning members to the cards of the respective list.

[Iain Dooley]

@Jason Tan okay cool, here is some Trellinator code that will remove all members from the cards called "Day List" and "Night List", then loop through all members of the board and randomly assign them to either of these cards, then schedule itself to execute again in 7 days:

https://gist.github.com/iaindooley/6a29a0ad523da19b3847796b47daacec

You can see how to set up Trellinator here:

https://community.atlassian.com/t5/Marketplace-Apps-articles/Introducing-Trellinator-Automate-Trello-with-Google-Apps-Script/ba-p/925271

Let me know if you have any problems with it :)

[Jason Tan]

Feeling a bit uneasy with this bit... :(

[Iain Dooley]

@Jason Tan The script you're providing access to is the code within the document. So no-one else can see, edit, create and delete all of your google drive files, but the code that is in that document can.

[Jason Tan]

@Iain Dooley 

Will give it a go...

Dumb questions: 

1. Where do I insert the provided script?

2. How do I call the function of the script to run?

3. Where do I insert the board name into the script?

[Iain Dooley]

@Jason Tan Fortunately all those questions kind of have the same answer. The setup below is a bit cumbersome at first, but once you get the hang of this you can easily create new functions and install them to automate Trello.

Under the heading "Something more interesting" in this article:

https://community.atlassian.com/t5/Marketplace-Apps-articles/Introducing-Trellinator-Automate-Trello-with-Google-Apps-Script/ba-p/925271

it has instructions for pasting in new code and publishing it, then installing it as a global command.

Trellinator commands are installed either as Global Commands or commands for an individual board.

Global commands can be restricted to run only on boards within a specific global command group, or can exclude groups.

When you initialise Trellinator, you'll see a sheet appear in the document for every board that the Trello user you connected to it is a member of. Whenever you are added to a board, a new sheet is added for that board.

What you'll want to do is install your command only to run on the board where you have the day/night lists and members.

Commands can also be installed either to respond to notifications, or on a time trigger.

In order to install a command to respond to notifications just copy and paste the function name into the "Function" column. This is the example given in the article above.

Before using Time Triggers you should set the timezone correctly.

It defaults to Sydney, Australia time, if you want to update the timezone to your locale, start by going to File > Spreadsheet Settings and setting the timezone in the configuration spreadsheet, but you'll also need to set the timezone in your script editor as well (pretty dumb!) by going to File > Project Properties.

In order to install a Time Trigger, choose "Time Trigger" from the dropdown in the Time Trigger column for the board sheet you want to install the command on, and you'll get a dialog where you can paste in the function name and choose when it will first execute. This will execute the function only once, but a function can push itself onto the ExecutionQueue and be run repeatedly (that's what the code I've provided you with does).

There is a little bug right now: when you add Trellinator to a new board, the sheet that gets created has the Time Trigger dropdown in the wrong column, you can fix it like this:

https://www.useloom.com/share/6f3038e8e9934dd6946d86ba8432ca7a

Once you have saved your Time Trigger, the function name will appear in the Function column, and you'll see an entry in the ExecutionQueue tab to run the command at the correct time.

Each time the above code executes, Trellinator will remove that instance from the ExecutionQueue and it will push itself again.

To your 3rd point, the params passed into the function will be the board id of the board on which you installed the command, so you could, for example, reuse the command on some other board or, as described above, as a global command.

You can even have a global time trigger command; that would loop through all boards and execute the function for each one.

[Jason Tan]

Thanks @Iain Dooley 

It makes so much sense and it's a brilliant setup. I had given it a try again, and multiple tests but it seems to constantly give me this problem...care to explain a bit on this? Also, I don't really have any function "Project Boards" in anywhere of the spreadsheet..

["reassignMembersRandomly executing from queue..."," calling: reassignMembersRandomly from the queue: : No data in IterableCollection: [object Object]\n\nSTACK: \tat apps/trellinator/Trellinator (Trellinator by The Procedure People):249\n\tat apps/trellinator-libs/Exceptions (Trellinator by The Procedure People):82\n\tat apps/trellinator-libs/IterableCollection (Trellinator by The Procedure People):269\n\tat apps/trellinator-libs/Board (Trellinator by The Procedure People):327\n\tat Commands (Trellinator by The Procedure People):28 (reassignMembersRandomly)\n\tat apps/trellinator/TrigAccessible (Trellinator by The Procedure People):55 (processQueue)\n","reassignMembersRandomly executing from queue..."," calling: reassignMembersRandomly from the queue: : No data in IterableCollection: [object Object]\n\nSTACK: \tat apps/trellinator/Trellinator (Trellinator by The Procedure People):249\n\tat apps/trellinator-libs/Exceptions (Trellinator by The Procedure People):82\n\tat apps/trellinator-libs/IterableCollection (Trellinator by The Procedure People):269\n\tat apps/trellinator-libs/Board (Trellinator by The Procedure People):327\n\tat Commands (Trellinator by The Procedure People):28 (reassignMembersRandomly)\n\tat apps/trellinator/TrigAccessible (Trellinator by The Procedure People):55 (processQueue)\n","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists","Project Boards executing from queue...","Error executing function: Error: Project Boards is not a function that exists"]

[Iain Dooley]

@Jason Tan if you share the sheet with iain@benkoboard.com I can check it out for you :)

[Jason Tan]

Thanks Iain.

[Iain Dooley]

@Jason Tan okay the issue is that if you put anything in the "function name" column of any of the boards, it will attempt to execute that as a function. When a function fails execution, it's pushed to the queue for a retry.

The script was also looking for a card called Day List and Night List. I've updated the card names for you. That's where the "no data in iterable collection" error came from.

I've cleared out the erroneous entries in the execution queue and added back in the command to execute reassignMembersRandomly. It just executed, all members were removed from each card and randomly assigned to one or the other. You can see in the execution queue now it's set to run again 1 day from the original time of execution that was scheduled (which was actually over the weekend, it will execute every 10 minutes until that time is in the future :)

[Jason Tan]

@Iain Dooley This is brilliant. Thank you so much