SHEET_UPDATERECORD
Class SHEET_UPDATERECORD
Source Source
File: src/integrations/google-sheet/actions/sheet-updaterecord.php
class SHEET_UPDATERECORD {
/**
* Integration code
*
* @var string
*/
public static $integration = 'GOOGLESHEET';
/**
* The action code.
*
* @var string
*/
private $action_code;
/**
* The action meta.
*
* @var string
*/
private $action_meta;
/**
* The Automator endpoint url.
*
* @var string
*/
public $endpoint_url;
/**
* Set up Automator action constructor.
*/
public function __construct() {
$this->action_code = 'GOOGLESHEETUPDATERECORD';
$this->action_meta = 'GOOGLESHEETROW';
$this->define_action();
$this->endpoint_url = AUTOMATOR_API_URL;
}
/**
* Define and register the action by pushing it into the Automator object
*/
public function define_action() {
$action = array(
'author' => Automator()->get_author_name( $this->action_code ),
'support_link' => Automator()->get_author_support_link( $this->action_code, 'knowledge-base/google-sheets/' ),
'is_pro' => false,
'integration' => self::$integration,
'code' => $this->action_code,
'sentence' => sprintf(
/* translators: Action sentence */
esc_html__( 'Update a row in a {{Google Sheet:%1$s}}', 'uncanny-automator' ),
$this->action_meta
),
'select_option_name' => esc_html__( 'Update a row in a {{Google Sheet}}', 'uncanny-automator' ),
'priority' => 10,
'accepted_args' => 1,
'requires_user' => false,
'execution_function' => array( $this, 'update_row_google_sheet' ),
'options_callback' => array( $this, 'load_options' ),
'custom_html' => $this->custom_html(),
'buttons' => array(
array(
'show_in' => $this->action_meta,
'text' => __( 'Get columns', 'uncanny-automator' ),
'css_classes' => 'uap-btn uap-btn--red',
'on_click' => $this->get_samples_js(),
'modules' => array( 'modal', 'markdown' ),
),
),
);
Automator()->register->action( $action );
}
/**
* load_options
*
* @return void
*/
public function load_options() {
$options = array(
'options_group' => array(
$this->action_meta => array(
Automator()->helpers->recipe->google_sheet->options->get_google_drives(
__( 'Drive', 'uncanny-automator' ),
'GSDRIVE',
array(
'is_ajax' => true,
'target_field' => 'GSSPREADSHEET',
'endpoint' => 'select_gsspreadsheet_from_gsdrive',
)
),
Automator()->helpers->recipe->google_sheet->get_google_spreadsheets(
__( 'Spreadsheet', 'uncanny-automator' ),
'GSSPREADSHEET',
array(
'is_ajax' => true,
'target_field' => 'GSWORKSHEET',
'endpoint' => 'select_gsworksheet_from_gsspreadsheet',
)
),
Automator()->helpers->recipe->google_sheet->get_google_worksheets(
__( 'Worksheet', 'uncanny-automator' ),
'GSWORKSHEET',
array(
'is_ajax' => true,
'target_field' => 'GSWORKSHEETCOLUMN',
'endpoint' => 'select_gsworksheet_from_gsspreadsheet_columns',
)
),
Automator()->helpers->recipe->google_sheet->get_google_sheet_columns(
__( 'Column search', 'uncanny-automator' ),
'GSWORKSHEETCOLUMN',
array(
'is_ajax' => true,
)
),
Automator()->helpers->recipe->field->text(
array(
'option_code' => 'GSWORKSHEET_SOURCE_VALUE',
'input_type' => 'text',
'label' => esc_attr__( 'Match value', 'uncanny-automator' ),
)
),
array(
'option_code' => 'WORKSHEET_FIELDS',
'input_type' => 'repeater',
'label' => __( 'Row', 'uncanny-automator' ),
/* translators: 1. Button */
'description' => '',
'required' => true,
'fields' => array(
array(
'option_code' => 'GS_COLUMN_NAME',
'label' => __( 'Column', 'uncanny-automator' ),
'input_type' => 'text',
'required' => true,
'read_only' => true,
'options' => array(),
),
array(
'option_code' => 'COLUMN_UPDATE',
'label' => __( 'Update?', 'uncanny-automator' ),
'input_type' => 'checkbox',
'is_toggle' => true,
),
Automator()->helpers->recipe->field->text_field( 'GS_COLUMN_VALUE', __( 'Value', 'uncanny-automator' ), true, 'text', '', false ),
),
'add_row_button' => __( 'Add pair', 'uncanny-automator' ),
'remove_row_button' => __( 'Remove pair', 'uncanny-automator' ),
'hide_actions' => true,
'can_sort_rows' => false,
),
),
),
);
return $options;
}
/**
* Injects JS to the page when the action is added
*
* @return String The string with the JS
*/
private function custom_html() {
// Start output
ob_start();
// Add the <script> tag
?>
<style>
/**
* Set width of columns
*/
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table {
table-layout: auto;
}
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-heading-cell[data-id="GS_COLUMN_NAME"],
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows-cell[data-id="GS_COLUMN_NAME"],
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-heading-cell[data-id="GS_COLUMN_VALUE"],
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows-cell[data-id="GS_COLUMN_VALUE"] {
width: 50%;
}
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-heading-cell[data-id="COLUMN_UPDATE"],
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows-cell[data-id="COLUMN_UPDATE"] {
width: 1px;
white-space: nowrap;
}
/**
* Hide the "Actions" column
*/
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-heading-cell--actions,
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows-cell--actions {
display: none;
}
/**
* Hide the actions row of the repeater field
*/
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__actions {
display: none;
}
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows tr:last-child td {
border-bottom: 0 !important;
}
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows tr:last-child td:first-child {
border-bottom-left-radius: var(--uap-border-radius);
}
.uap-item[data-id="{{item_id}}"] .form-repeater-fields[data-id="WORKSHEET_FIELDS"] .form-repeater-fields__table-rows tr:last-child td[data-id="GS_COLUMN_VALUE"] {
border-bottom-right-radius: var(--uap-border-radius);
}
</style>
<script>
/**
* Hide the "Value" field when the "Update?" checkbox is unchecked
*
* @return {undefined}
*/
window.hideValueFieldDynamically = () => {
// Get all checkboxes
// {{item_id}} will be replaced with a real integer
const $checkboxes = document.querySelectorAll( '.uap-item[data-id="{{item_id}}"] input[name="COLUMN_UPDATE"]' );
/**
* Check the checkbox value
*
* @return {undefined}
*/
const checkCheckboxValue = ( $checkbox ) => {
// Get the status
const shouldUpdate = $checkbox.checked;
// Get the row
const $row = $checkbox.closest( 'tr' );
// Get the value field in the row
const $field = $row.querySelector( 'td[data-id="GS_COLUMN_VALUE"] .form-element' );
// Set the visibility dynamically
if ( shouldUpdate ) {
$field.style.display = 'block';
} else {
$field.style.display = 'none';
}
}
// Iterate checkboxes
$checkboxes.forEach( ( $checkbox ) => {
// Check checkbox value
checkCheckboxValue( $checkbox );
// Add event listener to check the value on change
$checkbox.addEventListener( 'change', () => {
// Check checkbox value
checkCheckboxValue( $checkbox );
} );
} );
}
// Listen when the options are rendered
document.addEventListener( 'automator/item/options/open', ( event ) => {
// Check if it's the options of our item
// {{item_id}} will be replaced with a real integer
if ( event.detail.itemId == '{{item_id}}' ) {
// Check if the libraries are ready
if ( event.detail.librariesReady ) {
// Load our custom function
hideValueFieldDynamically();
}
}
} );
// Listen when the call of "Get columns" is completed
// {{item_id}} will be replaced with a real integer
document.addEventListener( 'automator/{{item_id}}/get-columns', ( event ) => {
// Load our custom function
hideValueFieldDynamically();
} );
</script>
<?php
// Get output
$output = ob_get_clean();
// Return output
return $output;
}
/**
* Anonymous JS function invoked as callback when clicking
* the custom button "Send test". The JS function requires
* the JS module "modal". Make sure it's included in
* the "modules" array
*
* @return string The JS code, with or without the <script> tags
*/
private function get_samples_js() {
// Start output
ob_start();
// It's optional to add the <script> tags
// This must have only one anonymous function
?>
<script>
// Do when the user clicks on send test
function ($button, data, modules) {
// Create a configuration object
let config = {
// In milliseconds, the time between each call
timeBetweenCalls: 1 * 1000,
// In milliseconds, the time we're going to check for samples
checkingTime: 60 * 1000,
// Links
links: {
noResultsSupport: 'https://automatorplugin.com/knowledge-base/google-sheets/'
},
// i18n
i18n: {
<?php /* translators: The time in seconds left */ ?>
checkingHooks: "<?php printf( esc_html__( "We're checking for columns. We'll keep trying for %s seconds.", 'uncanny-automator' ), '{{time}}' ); ?>",
noResultsTrouble: "<?php esc_html_e( 'We had trouble finding columns.', 'uncanny-automator' ); ?>",
noResultsSupport: "<?php esc_html_e( 'See more details or get help', 'uncanny-automator' ); ?>",
samplesModalTitle: "<?php esc_html_e( "Here is the data we've collected", 'uncanny-automator' ); ?>",
samplesModalWarning: "<?php /* translators: 1. Button */ printf( esc_html__( 'Clicking on \"%1$s\" will remove your current fields and will use the ones on the table above instead.', 'uncanny-automator' ), '{{confirmButton}}' ); ?>",
samplesTableValueType: "<?php esc_html_e( 'Value type', 'uncanny-automator' ); ?>",
samplesTableReceivedData: "<?php esc_html_e( 'Received data', 'uncanny-automator' ); ?>",
samplesModalButtonConfirm: "<?php /* translators: Non-personal infinitive verb */ esc_html_e( 'Use these fields', 'uncanny-automator' ); ?>",
samplesModalButtonCancel: "<?php /* translators: Non-personal infinitive verb */ esc_html_e( 'Do nothing', 'uncanny-automator' ); ?>",
}
}
// Create the variable we're going to use to know if we have to keep doing calls
let foundResults = false;
// Get the date when this function started
let startDate = new Date();
// Create array with the data we're going to send
let dataToBeSent = {
action: 'get_worksheet_ROWS_GOOGLESHEETS',
nonce: UncannyAutomator.nonce,
recipe_id: UncannyAutomator.recipe.id,
item_id: data.item.id,
drive: data.values.GSDRIVE,
sheet: data.values.GSSPREADSHEET,
worksheet: data.values.GSWORKSHEET
};
// Add notice to the item
// Create notice
let $notice = jQuery('<div/>', {
'class': 'item-options__notice item-options__notice--warning'
});
// Add notice message
$notice.html(config.i18n.checkingHooks.replace('{{time}}', parseInt(config.checkingTime / 1000)));
// Get the notices container
let $noticesContainer = jQuery('.item[data-id="' + data.item.id + '"] .item-options__notices');
// Add notice
$noticesContainer.html($notice);
// Create the function we're going to use recursively to
// do check for the samples
var getSamples = function () {
// Do AJAX call
jQuery.ajax({
method: 'POST',
dataType: 'json',
url: ajaxurl,
data: dataToBeSent,
// Set the checking time as the timeout
timeout: config.checkingTime,
success: function (response) {
// Get new date
let currentDate = new Date();
// Define the default value of foundResults
let foundResults = false;
// Check if the response was successful
if (response.success) {
// Check if we got the rows from a sample
if (response.samples.length > 0) {
// Update foundResults
foundResults = true;
}
}
// Check if we have to do another call
let shouldDoAnotherCall = false;
// First, check if we don't have results
if (!foundResults) {
// Check if we still have time left
if ((currentDate.getTime() - startDate.getTime()) <= config.checkingTime) {
// Update result
shouldDoAnotherCall = true;
}
}
if (shouldDoAnotherCall) {
// Wait and do another call
setTimeout(function () {
// Invoke this function again
getSamples();
}, config.timeBetweenCalls);
} else {
// Add loading animation to the button
$button.removeClass('uap-btn--loading uap-btn--disabled');
// Iterate samples and create an array with the rows
let rows = [];
let keys = {}
jQuery.each(response.samples, function (index, sample) {
// Iterate keys
jQuery.each(sample, function (index, row) {
// Check if the we already added this key
if (typeof keys[row.key] !== 'undefined') {
// Then just append the value
// rows[ keys[ row.key ] ].data = rows[ keys[ row.key ] ].data + ', ' + row.data;
} else {
// Add row and save the index
keys[row.key] = rows.push(row);
}
});
});
// Get the field with the fields (WEBHOOK_DATA)
let worksheetFields = data.item.options.GOOGLESHEETROW.fields[5];
// Remove all the current fields
worksheetFields.fieldRows = [];
// Add new rows. Iterate rows from the sample
jQuery.each(rows, function (index, row) {
// Add row
worksheetFields.addRow({
GS_COLUMN_NAME: row.key,
GS_COLUMN_VALUE: ''
}, false );
});
// Render again
worksheetFields.reRender();
// Check if it has results
if (foundResults) {
// Remove notice
$notice.remove();
} else {
// Change the notice type
$notice.removeClass('item-options__notice--warning').addClass('item-options__notice--error');
// Create a new notice message
let noticeMessage = config.i18n.noResultsTrouble;
// Change the notice message
$notice.html(noticeMessage + ' ');
// Add help link
let $noticeHelpLink = jQuery('<a/>', {
target: '_blank',
href: config.links.noResultsSupport
}).text(config.i18n.noResultsSupport);
$notice.append($noticeHelpLink);
}
// Dispatch custom event when everything is rendered
document.dispatchEvent(
new CustomEvent(
`automator/${ data.item.id }/get-columns`,
)
);
}
},
statusCode: {
403: function () {
location.reload();
}
},
fail: function (response) {
}
});
}
// Add loading animation to the button
$button.addClass('uap-btn--loading uap-btn--disabled');
// Try to get samples
getSamples();
}
</script>
<?php
// Get output
$output = ob_get_clean();
// Return output
return $output;
}
/**
* Validation function when the action is hit
*
* @param $user_id
* @param $action_data
* @param $recipe_id
* @param $args
*/
public function update_row_google_sheet( $user_id, $action_data, $recipe_id, $args ) {
$gs_spreadsheet = $action_data['meta']['GSSPREADSHEET'];
$gs_worksheet = $action_data['meta']['GSWORKSHEET'];
$lookup_field = $action_data['meta']['GSWORKSHEETCOLUMN'];
$lookup_field_value = Automator()->parse->text( $action_data['meta']['GSWORKSHEET_SOURCE_VALUE'], $recipe_id, $user_id, $args );
$worksheet_field = $action_data['meta']['WORKSHEET_FIELDS'];
$fields = json_decode( $worksheet_field, true );
$key_values = array();
$check_all_empty = true;
$hashed = sha1( Google_Sheet_Helpers::$hash_string );
$sheet_id = substr( $hashed, 0, 9 );
if ( (string) $gs_worksheet === (string) $sheet_id || intval( '-1' ) === intval( $gs_worksheet ) ) {
$gs_worksheet = 0;
}
$fields_count = count( $fields );
for ( $i = 0; $i < $fields_count; $i ++ ) {
$key = $fields[ $i ]['GS_COLUMN_NAME'];
$value = Automator()->parse->text( $fields[ $i ]['GS_COLUMN_VALUE'], $recipe_id, $user_id, $args );
$key_values[ $key ] = $value;
if ( ! empty( $value ) ) {
$check_all_empty = false;
}
}
// Process the update.
$sheet = $action_data['meta']['GSWORKSHEET_readable'];
$lookup_field_parts = explode( '-', $lookup_field );
$selected_column_range = $sheet . '!' . $lookup_field_parts[1];
$helper = Automator()->helpers->recipe->google_sheet->options;
try {
$range_values = $helper->api_get_range_values( $gs_spreadsheet, $selected_column_range );
$existing_rows = array();
if ( isset( $range_values['data']['values'] ) && isset( $range_values['data']['range'] ) ) {
$existing_rows[ $range_values['data']['range'] ] = $range_values['data']['values'];
}
$matched_range = $this->match_range( $existing_rows, $sheet, $selected_column_range, $lookup_field_value );
$row_values = array();
foreach ( $fields as $field ) {
$cell_value = null; // Pass null to avoid overwriting the cell value.
if ( true === $field['COLUMN_UPDATE'] ) {
$cell_value = Automator()->parse->text( $field['GS_COLUMN_VALUE'], $recipe_id, $user_id, $args );
}
// Add the value to our request body.
$row_values[] = $cell_value;
}
$response = $helper->api_update_row( $gs_spreadsheet, $matched_range, $row_values, $action_data );
// Complete the action if there are no issues.
Automator()->complete_action( $user_id, $action_data, $recipe_id );
return;
} catch ( \Exception $e ) {
return $this->complete_with_errors( $user_id, $action_data, $recipe_id, $e->getMessage() );
}
}
/**
* match_range
*
* Look for the range that matches the value.
*
* @param mixed $existing_rows
* @param mixed $sheet
* @param mixed $selected_column_range
* @param mixed $lookup_field_value
* @return void
*/
public function match_range( $existing_rows, $sheet, $selected_column_range, $lookup_field_value ) {
$done_lookup = false;
$matched_range = false;
$i = 0;
foreach ( $existing_rows as $range => $sheet_rows ) {
$j = 1;
foreach ( $sheet_rows as $existing_row_value ) {
$value = array_shift( $existing_row_value );
// The field value that we are matching against.
if ( $lookup_field_value === $value ) {
$done_lookup = true;
break;
}
$j ++;
}
$i ++;
if ( $done_lookup ) {
// Range starts at "A".
$matched_range = $sheet . '!A' . ( $j + 1 );
break;
}
}
if ( empty( $matched_range ) ) {
// Complete with error if no cell value matches the lookup value.
$error_message = sprintf(
esc_html__( "Notice: No cell values matches with '%1\$s' under '%2\$s' column in Sheet: '%3\$s'.", 'uncanny-automator' ),
$lookup_field_value,
$selected_column_range,
$sheet
);
throw new \Exception( $error_message );
}
return $matched_range;
}
/**
* Wrapper method function Automator complete with errors method.
*
* @return boolean True.
*/
protected function complete_with_errors( $user_id, $action_data, $recipe_id, $error_message ) {
$action_data['do-nothing'] = true;
$action_data['complete_with_errors'] = true;
Automator()->complete_action( $user_id, $action_data, $recipe_id, $error_message );
return true;
}
/**
* Get the response status from Google API response.
*
* @return array The status.
*/
public function get_response_status( $response ) {
$response_status = json_decode( $response );
// Google returned a valid response but there was a valid error.
if ( isset( $response_status->error ) && ! empty( $response_status->error ) ) {
$error_message = sprintf(
/* translators: The error code, type, and description */
esc_html__( 'Error: %1$s | %2$s | %3$s', 'uncanny-automator' ),
$response_status->statusCode, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
$response_status->error->type,
$response_status->error->description
);
return array(
'error' => true,
'message' => $error_message,
);
}
return array(
'error' => false,
'message' => null,
);
}
}
Expand full source code Collapse full source code View on Github
Methods Methods
- __construct — Set up Automator action constructor.
- complete_with_errors — Wrapper method function Automator complete with errors method.
- custom_html — Injects JS to the page when the action is added
- define_action — Define and register the action by pushing it into the Automator object
- get_response_status — Get the response status from Google API response.
- get_samples_js — Anonymous JS function invoked as callback when clicking the custom button "Send test". The JS function requires the JS module "modal". Make sure it's included in the "modules" array
- load_options — load_options
- match_range — match_range
- update_row_google_sheet — Validation function when the action is hit