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 .

GSuite domain preparation and configuration

In order to use this integration, you will need to create a Service Account in the Google Apps / G Suite domain that will be used. You can find directions on how to do that here.

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:

The purpose of the form is to capture the name to be given to the new sheet as well as some grid data that will be populated on the grid as well.

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 endpoint was tested using the Try it feature of the corresponding sheet's API page.

The Populate sheet trigger is shown below:

$ovw = array('sub'=>@@email);

// Add headers
$res = executeRestConnector("updateSheet",
							$ovw,
							array( 'spreadsheetId' => @@ssId, 'range' => 'A1:D1' ),
							array( 'valueInputOption' => 'USER_ENTERED' ),
							array( 'majorDimension' => "ROWS",
								   'values' => array( array("Col1","Col2","Col3","Col4") )
								)
						   );

// Add grid content
$vals = array();
for($row=1;$row<=count(@@grid); $row++) {
	$rowData = array(@@grid[$row]['text0000000001_label'],
					@@grid[$row]['text0000000002_label'],
					@@grid[$row]['datetime0000000001_label'],
					@@grid[$row]['dropdown0000000001_label']);
	$vals[] = $rowData;
}
$rangeLowerLimit = count(@@grid)+1;
$res = executeRestConnector("updateSheet",
							$ovw,
							array( 'spreadsheetId' => @@ssId, 'range' => 'A2:D'.$rangeLowerLimit ),
							array( 'valueInputOption' => 'USER_ENTERED' ),
							array( 'majorDimension' => "ROWS",
								   'values' => $vals
								)
						   );
//@@ssId = $res->message->spreadsheetId;
/*var_dump($res);
die();*/

From this trigger, it is worth mentioning the following:

• The $ovw array is also set up as to perform the changes as the GSuite account that created the document.
• The executeRestConnector() function is called twice: the first time, to set up the headers of the grid; the second time, to set the values.
• The executeRestConnector() function is called with parameters that are to be overridden in the url (spreadsheetId and range). The spreadsheetId is the one obtained from the response on the previous trigger, once the sheet is created.
• The endpoint was tested using the Try it feature of the corresponding sheet's API page.

Task Read Sheet data

The task is configured as shown below:

Forms

Form Read data from cell is shown below:

The purpose of the form is to capture the cell from which data is to be read.

Form Data from sheet is shown below:

The purpose of the form is to show the cell from which data was read (the same value that was entered by the user), as well as the actual value read from the sheet.

Triggers

Trigger Get sheet data is shown below:

$ovw = array('sub'=>@@email);
//$ovw = array();
// Add headers
$res = executeRestConnector("getSheets",
							$ovw,
							array( 'spreadsheetId' => @@ssId, 'range' => @@cell ),
							array(),
							array()
						   );
@@read_value = $res->message->values[0][0];

From this trigger, it is worth mentioning the following:

• The $ovw array is also set up as to access the sheet as the GSuite account that created the document.
• The executeRestConnector() function is called with parameters that are to be overridden in the url (spreadsheetId and range). The spreadsheetId is the one obtained from the response on a previous trigger, once the sheet is created.
• The endpoint was tested using the Try it feature of the corresponding sheet's API page.