How to integrate 1C and Trello

Alexander Biryukov

03.04.2020 25 min


Hi friends!

Humanity is experiencing a pandemic unprecedented in modern history, offices are closing down, employees are starting to work online, everything is changing. In these conditions, it’s essential to have cloud tools for planning and managing projects. One such tool is Trello.

Trello allows you to quickly and easily organize tasks, to-do lists, initiatives, discussions and ideas on one board. However, there are also disadvantages - Trello does not have time tracking, and the reporting required in any project or service activity is quite limited. In conditions when you need to get reports on the tasks of employees in the context of Customers / Projects / Employees etc. 1C may come to the rescue.
As we know, 1C is ideal for solving accounting problems. So we can quickly implement a scenario when Trello is working with tasks, and in 1C application, information accumulated, processed, and based on it, the necessary reporting generated or other processes using this data are performed, for example, calculating KPI or payroll, billing customers.

Can we connect 1C and Trello? If it were possible, this would allow an employee in 1C to see changes in Trello cards quickly.

I’ll answer right away - yes, it’s possible! Below you will see how to do it.

First, let’s look at a ready-made solution and then analyze the source code.

So, we have a board in Trello:

1.png

This is our working Trello, in parallel with the main work, we are building a rocket for flying to Mars - a little competition with Elon Musk.

And here is the Trello board already in 1C:

2.png


Let’s add another card to Trello and see how it displays in 1C:

3.png


Add to the member’s card and write a comment:

4.png

Then we update the data in 1C and voila:

5.png


The card we just created is already in 1C.

Well, now let’s look at how it all works.

First, you need to get the API key and token. How to do this is described on the page: https://developers.trello.com/reference#api-key-tokens

Once you have received the API key and token, you can open 1C and start writing code.

Let’s create a new 1C, add a subsystem “Trello” and a server module “Trello” there:

6.png

Also, let’s add two constants (the API key and token will be stored in them) and the Common form on which these constants will be located:

7.png

Finally, we add three Catalogs and one Data Processor. I’ll not describe the attributes of Catalogs, and you can look at them independently in the source code, which is attached to this article.

8.png


All main work will take place in Data Processor “Trello”. Let’s look at how it works.


Two dynamic lists are placed on the form: a list of boards and a list of cards. (Attention! To simplify the example, we do not consider working with Trello lists, only with boards and cards)

9.png


Also on the form are three commands:

10.png

Well - now let’s move on to the 1C source code. 

Here is the source code for the “RefreshAll” command:

&AtClient

Procedure Refresh(Command)

        ShowQueryBox(New NotifyDescription("RefreshCompletion", ThisObject),"The refresh may take a long time. Continue?", QuestionDialogMode.YesNo);

EndProcedure

 

&AtClient

Procedure RefreshCompletion(QuestionResult, AdditionalOptions) Export

      If QuestionResult = DialogReturnCode.Yes Then

            Trello.GettingDataTrello();

      EndIf;

EndProcedure


If the user answers “Yes” to the question, then the GettingDataTrello() procedure from the server module is called. Here is the source code for this procedure:

Procedure GettingDataTrello() Export

      Connection = Undefined;

      GetAndRefreshBoards(Connection);

      arrayBoards = GetListBoards();

      For Each board In arrayBoards Do

                    GetAndRefreshCards(board, Connection);

      EndDo;

EndProcedure


The operation algorithm is as follows:

1. First we receive (or update) data about our boards (GetAndRefreshBoards(Connection));
2. Then get an array of updated boards and in a cycle we process this array (arrayBoards = Trello.GetListBoards());
3. Inside the cycle, we get all the cards on the current board (GetAndRefreshCards(board, Connection));

Consider each of the procedures in more detail. Let’s start with GetAndRefreshBoards:

Function GetAndRefreshBoards(Connection = Undefined) Export

      ReturnParameters = GettingResultQueryJSON("GetBoards",,,Connection);

      If Not ReturnParameters.Error Then

            For Each board In ReturnParameters.Result Do

                  RefreshBoard(board, Connection);

            EndDo;

      EndIf;

      Return Not ReturnParameters.Error;

EndFunction

 

Procedure RefreshBoard(board, Connection) Export

      Query = New Query;

      Query.Text =

            "SELECT

            |     Trello_Boards.Ref AS Ref

            |FROM

            |     Catalog.Trello_Boards AS Trello_Boards

            |WHERE

            |     Trello_Boards.id = &id";

      Query.SetParameter("id", board.id);

      Selection = Query.Execute().Select();

      If Selection.Next() Then

            BoardObject = Selection.Ref.GetObject();

      Else

            BoardObject = Catalogs.Trello_Boards.CreateItem();

      EndIf;

      FillPropertyValues(BoardObject, board);

      BoardObject.Description            = board.name;

      BoardObject.desc_                        = board.desc;

      BoardObject.t_memberships.Clear();

     For Each member In board.memberships Do

            new_member = BoardObject.t_memberships.Add();

            FillPropertyValues(new_member, member);

            new_member.Member = RefreshMember(member.idMember, Connection)

      EndDo;

      BoardObject.Write();

EndProcedure


First, the GettingResultQueryJSON() procedure is called with the parameter GetBoards. This procedure creates a connection to Trello, transfers all the necessary parameters to Trello, and gets back a list of all the boards.

The source code of the GettingResultQueryJSON() procedure will be considered later.

So, from Trello, we got a list of boards, and in a loop, we process this list (RefreshBoard(board, Connection)).

Here, too, is simple: we are looking for the current board in the Catalog “Trello_Boards” by ID. If nothing is found, a new entry is created in the Catalog “Trello_Boards” with these IDs. After that, the attributes (properties) of the current board are filled.

As we know, each board has several members. In the Catalog “Trello_Boards” for storing members, the tabular part “t_memberships” is provided. In a cycle, this tabular part is filled with the data of members:

      BoardObject.t_memberships.Clear();

      For Each member In board.memberships Do

            new_member = BoardObject.t_memberships.Add();

            FillPropertyValues(new_member, member);

            new_member.Member = RefreshMember(member.idMember, Connection)

      EndDo;


After filling in the member’s data, the procedure RefreshMember() is called, which updates the member’s data in the Catalog “Trello_members”. Here is the source code for this procedure:

Function RefreshMember(idMember, Connection)

      Query = New Query;

      Query.Text =

            "SELECT

            |     Trello_members.Ref AS Ref

            |FROM

            |     Catalog.Trello_members AS Trello_members

            |WHERE

            |     Trello_members.id = &idMember";

      Query.SetParameter("idMember",idMember);

      Selection = Query.Execute().Select();

      If Selection.Next() Then

            MemberObject = Selection.Ref.GetObject();

      Else

            MemberObject = Catalogs.Trello_members.CreateItem();

      EndIf;

      MemberObject.id = idMember;

      QueryParameters = New Structure("MEMBER_ID,fields", idMember, "fullName,email,confirmed,initials,memberType,username,url,avatarUrl");

      ReturnParameters = GettingResultQueryJSON("GetDataMember",QueryParameters,,Connection);

      If Not ReturnParameters.Error Then

            FillPropertyValues(MemberObject, ReturnParameters.Result);

      EndIf;

      If Not ValueIsFilled(MemberObject.Description) Then

            MemberObject.Description = MemberObject.fullName;

      EndIf;

      MemberObject.Write();

      Return MemberObject.ref;

EndFunction


The algorithm of this procedure is similar to the algorithm of the RefreshBoard procedure. The member is also first searched in the Catalog “Trello_members”. If it’s not, then a new one is created.

Also in this procedure, GettingResultQueryJSON, already familiar to us, is called, into which “GetDataMember” parameter is passed, and which returns the data of a specific member.


Well - we took apart the first step, received data on available boards. Let’s go further.

The source code for the procedure GetListBoards is as follows:

Function GetListBoards() Export

      Query = New Query;

      Query.Text =

            "SELECT

            |     Trello_boards.Ref AS board

            |FROM

            |     Catalog.Trello_Boards AS Trello_boards

            |WHERE

            |     NOT Trello_boards.DeletionMark";

      Return Query.Execute().Unload().UnloadColumn("board");

EndFunction


The code is straightforward - a request to the Catalog “Trello_Boards” is created, and a list of available boards is returned.

After receiving the list of available boards, this list is processed in a cycle. And already in the cycle, the information on the cards is updated. Source code of the GetAndRefreshCards() procedure:

Function GetAndRefreshCards(Board, Connection = Undefined) Export

      RequestParameters = New Structure("BOARD_ID",Board.id);

      ReturnParameters = GettingResultQueryJSON("GetCards", RequestParameters,,Connection);

      If Not ReturnParameters.Error Then

            For Each card In ReturnParameters.Result Do

                  RefreshCard(card, Connection);

                  GetAndRefreshComments(GetCardByID(card.id), Connection);

            EndDo;

      EndIf;

      Return Not ReturnParameters.Error;

EndFunction

 

Procedure RefreshCard(card, Connection) Export

      Query = New Query;

      Query.Text =

            "SELECT

            |     Trello_cards.Ref AS Ref

            |FROM

            |     Catalog.Trello_cards AS Trello_cards

            |WHERE

            |     Trello_cards.id = &id";

      Query.SetParameter("id", card.id);

      Selection = Query.Execute().Select();

      If Selection.Next() Then

            CardObject = Selection.Ref.GetObject();

      Else

            CardObject = Catalogs.Trello_cards.CreateItem();

      EndIf;

      FillPropertyValues(CardObject, card);

      CardObject.Description             = card.name;

      CardObject.desc_                   = card.desc;

      CardObject.dateLastActivity   = ConvertDate(card.dateLastActivity);

      CardObject.due                           = ConvertDate(card.due);

      CardObject.t_Members.Clear();

      For Each idMember In card.idMembers Do

            newMember = CardObject.t_Members.Add();

             newMember.id            = idMember;

            newMember.member = RefreshMember(idMember, Connection);

      EndDo;

      CardObject.Write();

EndProcedure

First, the procedure GettingResultQueryJSON is called with the “GetCards” parameter, and it returns the current list of cards from Trello. Then the resulting list in a cycle is processed and updated information on each card (procedure RefreshCard()).

Information is also updated immediately on the comments for the current card (procedure GetAndRefreshComments()).

The algorithm of the RefreshCard procedure is similar to the algorithm of the RefreshBoard procedure. First, look for the current card by ID in the Catalog “Trello_cards”, if you don’t find it, then create a new card.

Let's look at the source code for the GetAndRefreshComments procedure:

Function GetAndRefreshComments(Card, Connection = Undefined) Export

      arrayComments = New Array;

      RequestParameters = New Structure("id,field",Card.id,"all");

      ReturnParameters = GettingResultQueryJSON("GetCardInfo", RequestParameters,,Connection);

      If Not ReturnParameters.Error Then

            For Each data IN ReturnParameters.Result Do

                  If data.data.Property("text") And ValueIsFilled(data.data.text) Then

                        arrayComments.Add(data);

                  EndIf;

            EndDo;

      EndIf;

      If Not arrayComments.Count() = 0 Then

            cardObject = Card.GetObject();

            cardObject.t_Comments.Clear();

            For Each comment In arrayComments Do

                  newComment              = cardObject.t_Comments.Add();

                  newComment.id           = comment.id;

                  newComment.text         = comment.data.text;

                  newComment.date         = ConvertDate(comment.date);

                  newComment.idMember     = comment.idMemberCreator;

                  newComment.member       = RefreshMember(comment.idMemberCreator, Connection);

            EndDo;

              cardObject.Write();

      EndIf;

      Return Not ReturnParameters.Error;

EndFunction


First, we get data on a specific card from Trello (the procedure GettingResultQueryJSON already known to us with the parameter “GetCardInfo”). Then, if there are comments in the card, it is added to the array arrayComments and then the data from this array is added to the tabular section t_Comments of the Catalog “Trello_cards”.


Thus, we obtain from Trello data on boards, cards, and comments, and write this data in 1C.

In general, using REST API, you can not only receive data from Trello but also add your data to Trello. For example, you can add a new card to 1C, and it will appear in Trello. 

A detailed description of REST API is on this page: https://developer.atlassian.com/cloud/trello/rest/

Oops, we did not consider the source code of the last procedure GettingResultQueryJSON. Well, let's do it:


Function GettingResultQueryJSON(Kind, RequestParameters = Undefined, RequestType = "GET", HTTPConnection = Undefined)

      ParametersAuthorization = GetParametersAuthorization();

      ReturnParameters = InitializeReturnParameters();

      If Kind = “GetBoards” Then

            ResourceAddress = "/1/members/me/boards?";

      ElsIf Kind = "GetDataMember" Then

            ResourceAddress = "/1/members/" + RequestParameters.MEMBER_ID + "?fields=" + RequestParameters.fields + "&";

      ElsIf Kind = "RefreshBoard" Then

            ResourceAddress = "/1/boards/" + RequestParameters.id + "/" + RequestParameters.name + "?value=" + RequestParameters.value + "&";

      ElsIf Kind = "GetCards" Then

            ResourceAddress = "/1/boards/" + RequestParameters.BOARD_ID + "/cards?";

      ElsIf Kind = "GetCardInfo" Then

            ResourceAddress = "/1/cards/" + RequestParameters.id + "/actions?" + RequestParameters.field + "&";

      EndIf;

      ResourceAddress = StrReplace(ResourceAddress," ","%20");

      ResourceAddress = ResourceAddress + "key=" + ParametersAuthorization.Key + "&token=" + ParametersAuthorization.Token;

      If HTTPConnection = Undefined Then

            HTTPConnection = New HTTPConnection("api.trello.com",443,,,,, New OpenSSLSecureConnection);

      EndIf;

    HTTPRequest = New HTTPRequest();

    HTTPRequest.ResourceAddress = ResourceAddress;

      If RequestType = "GET" Then

          HTTPResponse = HTTPConnection.Get(HTTPRequest);

      ElsIf RequestType = "POST" Then

          HTTPResponse = HTTPConnection.Post(HTTPRequest);

      ElsIf RequestType = "PUT" Then

          HTTPResponse = HTTPConnection.HTTPConnection("PUT",HTTPRequest);

      ElsIf RequestType = "DELETE" Then

          HTTPResponse = HTTPConnection.HTTPConnection("DELETE",HTTPRequest);

      EndIf;

      If Not HTTPResponse.StatusCode <> 200 Then

          JSONReader = New JSONReader;

          JSONReader.SetString(HTTPResponse.GetBodyAsString());

          ReturnParameters.Result = ReadJSON(JSONReader, False);

      Else

            ReturnParameters.Result = HTTPResponse.GetBodyAsString();

            ReturnParameters.Error = True;

      EndIf;

      Return ReturnParameters;

EndFunction

 

Function GetParametersAuthorization() Export

      ParametersAuthorization = New Structure("Key,Token");

      ParametersAuthorization.Key   = Constants.Trello_Key.Get();

      ParametersAuthorization.Token      = Constants.Trello_Token.Get();

      Return ParametersAuthorization;

EndFunction

 

Function InitializeReturnParameters() Export

      ReturnParameters = New Structure("Error, Result");

      ReturnParameters.Error = False;

      ReturnParameters.Result = "";

      Return ReturnParameters;

EndFunction



The function GetParametersAuthorization returns authorization parameters for the Trello server. The function InitializeReturnParameters initializes the structure into which the response from the Trello server will be written.

Then an HTTP Request is created with specific parameters, depending on what information we want to get from Trello. This request is sent to the server (in this example, we use only the GET request), and the server response is processed.

If there is no error (Not HTTPResponse.StatusCode <> 200), then the response from the server is converted to the JSON format and returned.

Also, if you add processing of additional commands (for example, work with lists or attachments) to this function, you can make full integration of your 1C program and Trello.

So, we made a simple example of 1C and Trello integration. As you can see, the 1C platform is very flexible, and it’s effortless to connect with any of the other information systems.

You can download this Example for your own application.

If you have any questions about this article, you can always get answers on our forum: https://1c-dn.com/forum/

Stay with us!
Be the first to know tips & trick on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.