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:
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:
Let’s add another card to Trello and see how it displays in 1C:
Add to the member’s card and write a comment:
Then we update the data in 1C and voila:
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:
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:
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.
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)
Also on the form are three commands:
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!
