...
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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
$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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
$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.