Google Spreadsheet Connector

1
0
-1

Hi,

I'm trying to implement a Google Spreadsheet Connector for adding a row to a spreadsheet, but I'm having lots of problems. These are the steps I'm following: 1.- Create a new connector definition. The configuration I've used for this purpose is the following: - ID, title etc... - Inputs: login, password, spreadsheet_id, worksheet_id, entry. All of them required and typed like java.lang.String. Each one has a default value, based on a spreadsheet I have for testing - Empty list of pages. I don't need any pages, I want this conector in an activity without asking info. - Empty list of outputs. The result I need is a new row in a spreadsheet. No output variables are needed. - No languages selected at the final window.

2.- Create a new implementation for the previous definition: - For doing this part, I'm using the Bonita documentation and the Google API for spreadsheet documentation, but I don't know how to integrate them together. My code doesn't use, at this moment, any input data. It's like this:

/** * */ package org.mycompany.connector;

import org.bonitasoft.engine.connector.ConnectorException;

import com.google.gdata.client.authn.oauth.*; import com.google.gdata.client.spreadsheet.*; import com.google.gdata.data.*; import com.google.gdata.data.batch.*; import com.google.gdata.data.spreadsheet.*; import com.google.gdata.util.*;

import java.io.IOException; import java.net.*; import java.util.*;

/** *The connector execution will follow the steps * 1 - setInputParameters() --> the connector receives input parameters values * 2 - validateInputParameters() --> the connector can validate input parameters values * 3 - connect() --> the connector can establish a connection to a remote server (if necessary) * 4 - executeBusinessLogic() --> execute the connector * 5 - getOutputParameters() --> output are retrieved from connector * 6 - disconnect() --> the connector can close connection to remote server (if any) */ public class SpreadsheetImpl extends AbstractSpreadsheetImpl {

@Override
protected void executeBusinessLogic() throws ConnectorException{
    //Get access to the connector input parameters
    //getLogin();
    //getPassword();
    //getSpreadsheet_id();
    //getWorksheet_id();
    //getEntry();

    //TODO execute your business logic here 
    SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration");
    //service.setProtocolVersion(SpreadsheetService.Versions.V3);

    try {
        service.setUserCredentials("javiermmm.fujitsu@gmail.com", "<my_password>");
    } catch (AuthenticationException e) {
        // TODO Bloque catch generado automáticamente
        e.printStackTrace();
    }

    // Define the URL to request.  This should never change.
    URL SPREADSHEET_FEED_URL;
    try {
        SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
        // Make a request to the API and get all spreadsheets.
        SpreadsheetFeed feed;
        try {
            feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class);
            List<SpreadsheetEntry> spreadsheets = feed.getEntries();
            if (spreadsheets.size() == 0) {
                // TODO: There were no spreadsheets, act accordingly.
                System.out.println("No hay spreadsheets");
            }

            // TODO: Choose a spreadsheet more intelligently based on your
            // app's needs.
            SpreadsheetEntry spreadsheet = spreadsheets.get(0);
            System.out.println(spreadsheet.getTitle().getPlainText());

            // Get the first worksheet of the first spreadsheet.
            // TODO: Choose a worksheet more intelligently based on your
            // app's needs.
            WorksheetFeed worksheetFeed = service.getFeed(spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
            List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
            WorksheetEntry worksheet = worksheets.get(0);

            // Fetch the list feed of the worksheet.
            URL listFeedUrl = worksheet.getListFeedUrl();
            ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

            // Create a local representation of the new row.
            ListEntry row = new ListEntry();
            row.getCustomElements().setValueLocal("Company Name", "Company_Bonita");
            row.getCustomElements().setValueLocal("Customer Name", "Customer_Bonita");
            row.getCustomElements().setValueLocal("Date", "hoy, lunes");

            // Send the new row to the API for insertion.
            row = service.insert(listFeedUrl, row);
        } catch (IOException e) {
            // TODO Bloque catch generado automáticamente
            e.printStackTrace();
        } catch (ServiceException e) {
            // TODO Bloque catch generado automáticamente
            e.printStackTrace();
        }
    } catch (MalformedURLException e) {
        // TODO Bloque catch generado automáticamente
        e.printStackTrace();
    }





    //WARNING : Set the output of the connector execution. If outputs are not set, connector fails

 }

@Override
public void connect() throws ConnectorException{
    //[Optional] Open a connection to remote server

}

@Override
public void disconnect() throws ConnectorException{
    //[Optional] Close connection to remote server

}

}

When I saved my code, without errors (what have been hard), I try to attach the connector to an activity, and after fill the data required (name, event, etc.), the Bonita window doesn't enable the buttons next or finish, so I cannot continue to test it. Besides, I've thought that maybe this behavior, could be origined for the lack of dependencies, so I tried to add dependencies editing the definition and the implementation of the connector and adding them with the wizard, but I'm having the same problem: I clcik on add in the dependencies section, and a window is opened. I click the import button, and I select the .jar files I have, but the Accept button is not enabled at any moment.

For this reason, I've thought to try the code in an eclipse project (I've followed the google intruction of adding the dependencies set of google services + java mail + JavaBeans Activation Framework with the 'add external Jars' option in eclipse) , independent of Bonita BPM, but I'm having a different problem. When I launch the main method I got the following error: java.lang.NoClassDefFoundError: com/google/common/collect/Maps on executing the line SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration"); Diving in Google, I've found that error may be solved by including another .jar (guava-r07.jar), and it seems it works, but now the problem is that in the next line service.setUserCredentials("javiermmm.fujitsu@gmail.com", "my_password"); The application doesn't throw any error but, it waits forever, I don't know why.

I'm getting crazy with that, so I would thank any help about that. Is my code, correct? Is my configuration correct? Why Bonita doesn'enable the buttons? I've searched in the community too, but the only post I found relevant, does not have any solution. Has anybody, a tutorial, or similar for making connectors for Google services (better if spreadsheets)?

Tnaks in advance, Regards.

1 answer

1
0
-1

My answer has disappeared, not because anyone did anything but because of a problem my side. I didn't know this had happened.

Oh yeah, now i know what I said, but first...for my methodology of creating the necessary code see http://community.bonitasoft.com/answers/google-maps#comment-39299

Hope that helps.

Right back to this one...

Your biggest problem is not understanding how to connect your process to your connector, you MUST provide a Input Page! This is not seen by the user at all and only the page in which you define the variable to connector link. For an example just add a database connector to a step and fill it in - the details you give for JDBC, database User and password are on an Input page.

Also I disagree that you don't need an Output page. What about information from connector? Did it fail for Connection reasons? Did it succeed? Always take a positive or negative result to say if it worked or not. How else would your process now how to proceed.

Processes always have Exception conditions, you have to build them into your process.

Imagine the following process and the problems it would cause if there was no exception processing...

Payroll run->record is Javier->Database call failed so will not pay him->no error processing->continue.... :)

regards Seán

Comments

Submitted by javiermmm.fujitsu on Fri, 05/29/2015 - 11:46

Hi Sean,

I cannot see your answer, please review it, I'm very interested on it.

thanks and regards

Submitted by javiermmm.fujitsu on Mon, 06/15/2015 - 11:59

Hi again,

Your biggest problem is not understanding how to connect your process to your connector, you MUST provide a Input Page!

Yes, you're right. I had understood the page would have been added like one more form.

I have added also an output variable for saving some connector information, and removed some code, leaving only the login:

  1. package org.mycompany.connector;
  2.  
  3. import org.bonitasoft.engine.connector.ConnectorException;
  4.  
  5. import com.google.gdata.client.spreadsheet.SpreadsheetService;
  6. import com.google.gdata.util.AuthenticationException;
  7.  
  8. /**
  9.  *The connector execution will follow the steps
  10.  * 1 - setInputParameters() --> the connector receives input parameters values
  11.  * 2 - validateInputParameters() --> the connector can validate input parameters values
  12.  * 3 - connect() --> the connector can establish a connection to a remote server (if necessary)
  13.  * 4 - executeBusinessLogic() --> execute the connector
  14.  * 5 - getOutputParameters() --> output are retrieved from connector
  15.  * 6 - disconnect() --> the connector can close connection to remote server (if any)
  16.  */
  17. public class SpreadsheetImpl extends AbstractSpreadsheetImpl {
  18.  
  19. @Override
  20. protected void executeBusinessLogic() throws ConnectorException{
  21. //Get access to the connector input parameters
  22. //getLogin();
  23. //getPassword();
  24. //getSpreadsheet_id();
  25. //getWorksheet_id();
  26. //getEntry();
  27.  
  28. //TODO execute your business logic here
  29. //--------------------------------------
  30.  
  31. SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration");
  32. //service.setProtocolVersion(SpreadsheetService.Versions.V3);
  33.  
  34. String out = "-1-fail-1-";
  35.  
  36. try {
  37. service.setUserCredentials("javiermmm.fujitsu@gmail.com", "nexusZero0");
  38. // TODO Bloque catch generado automáticamente
  39. e.printStackTrace();
  40. setOutput(out);
  41. }
  42.  
  43. //WARNING : Set the output of the connector execution. If outputs are not set, connector fails
  44. //setOutput(output);
  45. setOutput(OUTPUT_OUTPUT_PARAMETER + " ---2-succes-2--- ");
  46. }
  47.  
  48. @Override
  49. public void connect() throws ConnectorException{
  50. //[Optional] Open a connection to remote server
  51.  
  52. }
  53.  
  54. @Override
  55. public void disconnect() throws ConnectorException{
  56. //[Optional] Close connection to remote server
  57.  
  58. }
  59.  
  60. }

And I have experimented the following: 1.- First, trying the connector I got a java.lang.NoClassDefFoundError, of an exception Google class, so I thought maybe there was a missing jar that I needed. Well, when I try to add a jar (Development>Manage jars..>import) the jar is not added, I don't know why. No error is shown but the jar is not added. And this happens even restarting Bonita. So what I've done is copying all the jar files into the \workspace\default\lib folder. Doing that, the error is solved, but I would like to notify you about the import jar problem. 2.- With this situation, I have tried the connector again, and the result is a couple of minutes waiting for the execution, for getting finally the following error: java.lang.reflect.InvocationTargetException org.bonitasoft.engine.bpm.process.ProcessDeployException: USERNAME=install | org.bonitasoft.engine.dependency.SDependencyCreationException: Can't create dependency SDependencyImpl [tenantId=1, id=0, name=9118175329926840956_gdata-spreadsheet-meta-3.0.jar, fileName=9118175329926840956_gdata-spreadsheet-meta-3.0.jar.jar, description=null]

I am not able to understand this error. Please, can you give me some information about it? I would appreciate some help on making this connector work.

thanks in advance and regards.

Submitted by javiermmm.fujitsu on Tue, 06/23/2015 - 10:21

Hi again,

Well, it seems the code I posted (a Google's example) is for the previous way of login, which is now deprecated. After looking for some more information and examples, I've found a new way to make it work, however, I'm not getting it. Now, the connector is executing, but it fails always in the same place. The code is:

  1. package org.mycompany.connector;
  2.  
  3. import java.io.File;
  4. import java.io.IOException;
  5. import java.net.MalformedURLException;
  6. import java.net.URL;
  7. import java.security.GeneralSecurityException;
  8. import java.util.Arrays;
  9. import java.util.List;
  10.  
  11. import org.bonitasoft.engine.connector.ConnectorException;
  12.  
  13. import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
  14. import com.google.api.client.http.HttpTransport;
  15. import com.google.api.client.http.javanet.NetHttpTransport;
  16. import com.google.api.client.json.jackson2.JacksonFactory;
  17. import com.google.gdata.client.spreadsheet.SpreadsheetService;
  18. import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
  19. import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
  20. import com.google.gdata.util.ServiceException;
  21.  
  22. /**
  23.  *The connector execution will follow the steps
  24.  * 1 - setInputParameters() --> the connector receives input parameters values
  25.  * 2 - validateInputParameters() --> the connector can validate input parameters values
  26.  * 3 - connect() --> the connector can establish a connection to a remote server (if necessary)
  27.  * 4 - executeBusinessLogic() --> execute the connector
  28.  * 5 - getOutputParameters() --> output are retrieved from connector
  29.  * 6 - disconnect() --> the connector can close connection to remote server (if any)
  30.  */
  31. public class SpreadsheetImpl extends AbstractSpreadsheetImpl {
  32.  
  33. @Override
  34. protected void executeBusinessLogic() throws ConnectorException{
  35. //Get access to the connector input parameters
  36. //getLogin();
  37. //getPassword();
  38. //getSpreadsheet_id();
  39. //getWorksheet_id();
  40. //getEntry();
  41.  
  42. //TODO execute your business logic here
  43. //--------------------------------------
  44.  
  45. URL SPREADSHEET_FEED_URL;
  46.  
  47. try {
  48.  
  49. SPREADSHEET_FEED_URL = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
  50.  
  51. File p12 = new File("C:\\Users\\essanchezpa1\\Desktop\\Javier\\Bonita\\Connectores\\calendar\\incidencias-Bonita-b5a3e235f5de.p12");
  52.  
  53. HttpTransport httpTransport = new NetHttpTransport();
  54. JacksonFactory jsonFactory = new JacksonFactory();
  55. String[] SCOPESArray = {"https://spreadsheets.google.com/feeds", "https://spreadsheets.google.com/feeds/spreadsheets/private/full", "https://docs.google.com/feeds"};
  56. final List SCOPES = Arrays.asList(SCOPESArray);
  57. GoogleCredential credential;
  58.  
  59. credential = new GoogleCredential.Builder()
  60. .setTransport(httpTransport)
  61. .setJsonFactory(jsonFactory)
  62. .setServiceAccountId("854094303956-c5lpvs0nkkufvbudc7mr2an16k383ih8@developer.gserviceaccount.com")
  63. .setServiceAccountScopes(SCOPES)
  64. .setServiceAccountPrivateKeyFromP12File(p12)
  65. .build();
  66.  
  67. SpreadsheetService service = new SpreadsheetService("Test");
  68.  
  69. service.setOAuth2Credentials(credential);
  70. SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class);
  71. List<SpreadsheetEntry> spreadsheets = feed.getEntries();
  72.  
  73. if (spreadsheets.size() == 0) {
  74. setOutput("No se encontraron spreadsheets");
  75. }
  76. else {
  77. setOutput("spreadsheets encontrados: " + spreadsheets.size());
  78. }
  79.  
  80.  
  81.  
  82. } catch (MalformedURLException e) {
  83. // TODO Bloque catch generado automáticamente
  84. e.printStackTrace();
  85. setOutput("-1- fail -1-");
  86. // TODO Bloque catch generado automáticamente
  87. e.printStackTrace();
  88. setOutput("-2- fail -2-");
  89. } catch (IOException e) {
  90. // TODO Bloque catch generado automáticamente
  91. e.printStackTrace();
  92. setOutput("-3- fail -3-");
  93. } catch (ServiceException e) {
  94. // TODO Bloque catch generado automáticamente
  95. e.printStackTrace();
  96. setOutput("-4- fail -4-");
  97. }
  98.  
  99. //WARNING : Set the output of the connector execution. If outputs are not set, connector fails
  100. //setOutput(output);
  101.  
  102. }
  103.  
  104. @Override
  105. public void connect() throws ConnectorException{
  106. //[Optional] Open a connection to remote server
  107.  
  108. }
  109.  
  110. @Override
  111. public void disconnect() throws ConnectorException{
  112. //[Optional] Close connection to remote server
  113.  
  114. }
  115.  
  116. }

Well, the issue I have is that, when trying to execute the line 70 (service.getFeed(..)), the IOException is always catched. Can anyone help me with that? I don't know why this happens, and on the Google doc, I don't find any clue. I've assured that I've put the correct Service Account mail addres, and judging some others examples of code, it is apparently correct. Ive copied it on an eclipse IDE, an debugged it, but I cannot fix it cause I don't aunderstand what's happening and why is the exception thrown. The stacktrace I'm getting is:

  1. java.net.ConnectException: Connection timed out: connect
  2. at java.net.PlainSocketImpl.socketConnect(Native Method)
  3. at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
  4. at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
  5. at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
  6. at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
  7. at java.net.Socket.connect(Socket.java:529)
  8. at com.sun.net.ssl.internal.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:559)
  9. at com.sun.net.ssl.internal.ssl.BaseSSLSocketImpl.connect(BaseSSLSocketImpl.java:141)
  10. at sun.net.NetworkClient.doConnect(NetworkClient.java:163)
  11. at sun.net.www.http.HttpClient.openServer(HttpClient.java:394)
  12. at sun.net.www.http.HttpClient.openServer(HttpClient.java:529)
  13. at sun.net.www.protocol.https.HttpsClient.<init>(HttpsClient.java:272)
  14. at sun.net.www.protocol.https.HttpsClient.New(HttpsClient.java:329)
  15. at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.getNewHttpClient(AbstractDelegateHttpsURLConnection.java:172)
  16. at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:916)
  17. at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(AbstractDelegateHttpsURLConnection.java:158)
  18. at sun.net.www.protocol.https.HttpsURLConnectionImpl.connect(HttpsURLConnectionImpl.java:133)
  19. at com.google.gdata.client.http.HttpGDataRequest.execute(HttpGDataRequest.java:511)
  20. at com.google.gdata.client.http.GoogleGDataRequest.execute(GoogleGDataRequest.java:536)
  21. at com.google.gdata.client.Service.getFeed(Service.java:1135)
  22. at com.google.gdata.client.Service.getFeed(Service.java:998)
  23. at com.google.gdata.client.GoogleService.getFeed(GoogleService.java:645)
  24. at com.google.gdata.client.Service.getFeed(Service.java:1017)
  25. at google.SpreadsheetImpl.main(SpreadsheetImpl.java:54)

... so I think, something's wrong with the connection to Google, but what?

thanks in advance and regards.

EDIT: May my company's proxy or firewall be the cause? I think the code is right...

Notifications