Table of Contents

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

...

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:

...

• 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 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:

Image Added

Forms

Form Read data from cell is shown below:

Image Added

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

Form Data from sheet is shown below:

Image Added

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.