Introduction
This page describes how to set up and use Google Sheets integration using ProcessMaker's Enterprise Connectors. It has been set up and tested under ProcessMaker 3.1.1 in the Enterprise Trial server, since it had to be demonstrated and made available to a lead in this environment. Therefore, it can be accessed at https://trial.processmaker.com/sysejortegau02/en/neoclassic/login/login. The username is admin
and the password is 80012195
.
Connectors configuration
In order to configure the connectors, log in as an admin user and head to Admin → PM Connectors → Configuration Auth. A list of existing authentication configurations is shown. By default, it just shows Service Account Google, as illustrated below:
Click on Edit. The configuration options ofr Google's service account are shown. The file_credential parameter is empty, and must be added by uploading the corresponding JSON-encoded service account key file. For this workspace, it was set up with a key provided by Dante, which had previously been created and configured with access to the required Drive and Sheets scopes. Once uploaded, the configuration screen should look as shown below:
Click on the Test button. If the key is correct, a message "The configuration test was successful" should be shown. Click on Save and close the dialog.
Since ProcessMaker already includes the connectors used for this demonstration, there is no need to perform additional configuration under the Category and Connectors options in the PM Connectors menu. Therefore, we can move to creating a Process that makes use of them.
Demonstration process
Process Sheets integration test has been created in the workspace. It consists of a task in which a sheet name and grid are captured. The Enterprise Connectors are used to create a sheet with the given name and fill it with the data entered on the grid. Then, on the second task, the user is asked to enter a cell, and the data from that cell is retrieved from the sheet and shown in ProcessMaker. Thus, three Google Sheets interactions are performed:
- Create a sheet: this is achieved by using the createSheet connector.
- Populate the sheet with data: this is achieved by using the updateSheet connector.
- Read data from a sheet: this is achieved by using the getSheets connector.
The process is shown below:
Task Create Sheet
This task is configured as shown below:
Forms
The New sheet form
Dynaform is illustrated below:
Triggers
The Create sheet
trigger executes the connector required for creating the sheet. The trigger is shown below:
$email = userInfo(@@USER_LOGGED); $email = $email['mail']; @@email = $email; $ovw = array('sub'=>$email); $sheetName = @@sheet_name; $res = executeRestConnector("createSheet", $ovw, array(), array(), array( 'properties' => array( 'title' => $sheetName ) ) ); @@ssId = $res->message->spreadsheetId;
From the trigger, it is worth mentioning the following:
- The
$ovw
array is created to add so-called overwrite parameters to the request performed by the connector. For this particular use scenario, it includes parameter sub set to the email address of the logged-in user, which must correspond to a Google account of the domain whose Service account key was uploaded in the Admin → PM Connectors → Configuration auth section as described above. Otherwise, the new sheet will be created for the admin user, not the currently logged-in user. - the
executeRestConnector()
function received as first parameter the name of the connector, in this case,createSheet
. - The name of the sheet is passed as an array of properties for to be created, as part of the body of the request to be performed.
- The newly created sheet ID is returned in the message of the response, and is stored on the process'
@@ssId
variable.
The Populate sheet
trigger is shown below: