How to transpose and split in Google Apps Script and set trigger?

Below is Form response in spreadsheet (documentTransmittal)

time stamp Document No. Recipient Name Document transmittal ref no
7/9/2021 21:36:11 170709, 170738, 171769, 172003, 172025 Susan 20210001
7/18/2021 18:19:59 131034, 131192, 132438, 132465, 133016 Jhon 20210002

Above is Current data, I would like to process as below

Time stamp Document no. Recipient Name Document transmittal Ref no.
7/9/2021 21:36:11 170709 Susan 20210001
7/9/2021 21:36:11 170738 Susan 20210001
7/9/2021 21:36:11 171769 Susan 20210001
7/9/2021 21:36:11 172003 Susan 20210001
7/9/2021 21:36:11 172025 Susan 20210001
7/18/2021 18:19:59 131034 Jhon 20210002
7/18/2021 18:19:59 131192 Jhon 20210002
7/18/2021 18:19:59 132438 Jhon 20210002
7/18/2021 18:19:59 132465 Jhon 20210002
7/18/2021 18:19:59 133016 Jhon 20210002

Previous Script

    function formatData() {
 // File
 var ss = SpreadsheetApp.getActiveSpreadsheet();

 // Get responseData sheet
 var rawData = ss.getSheetByName('documentTransmittal');

 // Input data
 var data = rawData.getRange("A2:D").getValues(); // Gets titles and options in a single call to the sheet

 // Initialise an array that will hold the output
 var outputArray = ();

 // Name a variable to hold the data from each set of options
 var options;

 // Start looping through the data
 for (var row = 0; row < data.length; row++) {

 // Split the document with individual no.
 options = data(row)(1).split(", ");

 // Loop through the array of split options and place each of them in a new row
 for (var element = 0; element < options.length; element++) {

 outputArray.push((data(row)(0), // Place the title in a new row
                  options(element))); // Place one option in the 2nd column of the row

  } // Options loop ends here

 } // Data loop ends here

 // Get processedData sheet
 var processedData = ss.getSheetByName('responseData');

 // Get last row in processedData sheet
 var lastRow = processedData.getLastRow();

 // Post the outputArray to the sheet in a single call
 processedData.getRange(lastRow + 1, 1, outputArray.length,
 outputArray(2).length).setValues(outputArray);

}

revise script as per advised

function formatData() {

  // File
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get responseData sheet
  var rawData = ss.getSheetByName('documentTransmittal');

  // Input data
  var data = rawData.getDataRange().getValues(); // Gets titles and options in a single call to the sheet

  // Initialise an array that will hold the output
  var outputArray = ();

  // Name a variable to hold the data from each set of options
  var options;

  // Start looping through the data
  for (var row = 0; row < data.length; row++) {

    // Split the document with individual no.
    options = data(row)(1).split(", ");

    // Loop through the array of split options and place each of them in a new row
    for (var element = 0; element < options.length; element++) {

      outputArray.push((data(row)(0), // Place the title in a new row
                        options(element), // Place one option in the 2nd column of the row,
                        data(row)(2), // Place the recipient name,
                        data (row)(3))); // place the document transmittal ref no.

    } // Options loop ends here

  } // Data loop ends here

  // Get processedData sheet
  var processedData = ss.getSheetByName('responseData');

  // Get last row in processedData sheet
  var lastRow = processedData.getLastRow();

  // Post the outputArray to the sheet in a single call
  processedData.getRange(lastRow + 1, 1, outputArray.length, outputArray(2).length).setValues(outputArray);
}

i was notice that after run the script the same data will repeating copy-paste to sheet (proceseData), I was advised to

You need to modify your script to reflect a couple of things. First, trigger the script with an installable onFormSubmit trigger. Enable event objects to get the new response. Last, append only the new response”

The last sentence of the advise, i really dont get the point, could somebody elaborate it

thanks !

plugins – WP Function does not trigger on Webhook API Call

I have created a function that triggers and connect parent post with Child post based on a Custom Meta Field value. If Meta field value of an existing Parent post matches with the newly created child post then function automatically connect the parent post with child post. and this function works perfectly If I am creating the child post using WP Admin area or using a Frontend Post Submission form. But If I am creating the Child Post usign a Webhook Incoming data then function doesn’t trigger. and it doesn’t connect the parent post based on the Custom Meta field “wpcf-patient-id“.

I am using WP Webhooks Pro to receive data from a external source that includes the post title, content, wpcf-patient-id, and few other details. Each time Webhook receives a payload data, it creates a new post using the received payload data. But the function doesn’t trigger at all.

So if anyone can help me make it work, Please suggest the possible solution.

add_action( 'save_post', 'assign_parent_patient_func', 100,3 );
function assign_parent_patient_func( $post_id, $post, $update ) {
    
    // Only set for post_type = post!
    if ( !in_array($post->post_type, array('qev-attachment', 'note')) ) {
        return;
    }
      
    $patient_id = get_post_meta($post_id, 'wpcf-patient-id', true); 
    if($patient_id){
        $client_ids = get_posts( array(
            'post_type' => 'client',
            'meta_key' => 'wpcf-patient-id', 
            'meta_value'=> $patient_id,
            'fields' => 'ids'
        ));
        if(isset($client_ids(0))){
            $client_id = $client_ids(0);
            if($post->post_type == 'qev-attachment'){
                toolset_connect_posts('client-attachment', $client_id, $post_id);
            }
            if($post->post_type == 'note'){
                toolset_connect_posts('client-note', $client_id, $post_id);
            }
        }
    }
}

mariadb – How To fix this Trigger in mysql?

it’s from an old php setup script that i got.

I can a little sql but not so much

<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Daniel
 * Date: 21-08-13
 * Time: 18:07
 * To change this template use File | Settings | File Templates.
 */

require_once('config.php');

try {
    $dbh = new PDO('mysql:host='. $dbHost .';charset=UTF8', $dbUser, $dbPass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
    echo 'Failed to connect to database';
    echo $e->getMessage();

    die();
}

$sql = "
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for ". $dbName ."
CREATE DATABASE IF NOT EXISTS `". $dbName ."` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `". $dbName ."`;


CREATE TABLE IF NOT EXIST `notes` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `User` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `Timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `Note` MEDIUMTEXT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `cases` (
  `Id` int(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `Title` varchar(64) NOT NULL,
  `Status` int(11) NOT NULL,
  `Ticket` text,
  `Priority` INT(11) NOT NULL,
  `Created` datetime NOT NULL,
  `Closed` datetime DEFAULT NULL,
  `LastUpdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `LastUser` CHAR(16) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `Status` (`Status`,`Created`,`Closed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


/*!40000 ALTER TABLE `cases` DISABLE KEYS */;
/*!40000 ALTER TABLE `cases` ENABLE KEYS */;


CREATE TABLE IF NOT EXISTS `statuses` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `Status` varchar(32) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


/*!40000 ALTER TABLE `statuses` DISABLE KEYS */;
INSERT IGNORE INTO `statuses` (`Id`, `Status`) VALUES
    (1, 'New'),
    (2, 'Work In Progress'),
    (3, 'Pending'),
    (4, 'Closed');
/*!40000 ALTER TABLE `statuses` ENABLE KEYS */;


CREATE TABLE IF NOT EXISTS `worklogs` (
  `Id` int(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `CaseId` int(10) DEFAULT NULL,
  `Text` text,
  `User` CHAR(16) NOT NULL,
  `Timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `CaseId` (`CaseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


/*!40000 ALTER TABLE `worklogs` DISABLE KEYS */;
/*!40000 ALTER TABLE `worklogs` ENABLE KEYS */;


CREATE TABLE IF NOT EXISTS `priorities` (
    `Id` INT(10) NOT NULL AUTO_INCREMENT,
    `Priority` VARCHAR(32) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*!40000 ALTER TABLE `priorities` DISABLE KEYS */;
INSERT IGNORE INTO `priorities` (`Id`, `Priority`) VALUES
    (1, 'Medium'),
    (2, 'High');
    (3, 'Critical');
/*!40000 ALTER TABLE `priorities` ENABLE KEYS */;


CREATE TRIGGER `StatusUpdate` AFTER UPDATE ON `cases` FOR EACH ROW BEGIN
   BEGIN
    DECLARE OldStatusText VARCHAR(32);
    DECLARE NewStatusText VARCHAR(32);
    DECLARE OldPriorityText VARCHAR(32);
    DECLARE NewPriorityText VARCHAR(32);
    DECLARE WorkLogText VARCHAR(256);

    SET WorkLogText = NULL;

    IF NEW.`Status` != OLD.`Status` THEN
        SET OldStatusText = (SELECT `Status` FROM statuses WHERE Id = OLD.`Status`);
        SET NewStatusText = (SELECT `Status` FROM statuses WHERE Id = NEW.`Status`);

        SET WorkLogText = CONCAT("Status changed from '", OldStatusText, "' to '", NewStatusText, "'");
    END IF;

   IF NEW.`Priority` != OLD.`Priority` THEN

        SET OldPriorityText = (SELECT `Priority` FROM priorities WHERE Id = OLD.`Priority`);
        SET NewPriorityText = (SELECT `Priority` FROM priorities WHERE Id = NEW.`Priority`);

      IF WorkLogText IS NOT NULL THEN
          SET WorkLogText = CONCAT(WorkLogText, "rn", "Priority changed from '", OldPriorityText, "' to '", NewPriorityText, "'");
       ELSE
            SET WorkLogText = CONCAT("Priority changed from '", OldPriorityText, "' to '", NewPriorityText, "'");
      END IF;
    END IF;

    IF WorkLogText IS NOT NULL THEN
      INSERT INTO worklogs (`CaseId`, `User`, `Text`)
       VALUES
        (
            NEW.Id,
            NEW.LastUser,
            WorkLogText
          );
    END IF;
END;

CREATE TRIGGER `NewCases` AFTER INSERT ON `cases` FOR EACH ROW
BEGIN
   INSERT INTO worklogs (`CaseId`, `User`, `Text`)
   VALUES
     (
        NEW.Id,
        NEW.LastUser,
        "Case created"
      );
END;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
";

try {
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
}
catch(PDOException $e)
{
    echo $e->getMessage();
    die();
}
echo '<center>Database created.<br /><a href="index.php">Click here</a> to continue.</center>';```

macbook pro – Can I trigger a HomeKit scene when my laptop’s camera is turned on or off?

Like a lot of people nowadays, I’m set up in a home office and I’m often on video calls on my MacBook Pro. My partner sometimes wants to come into my office, but doesn’t want to interrupt a video call. We’ve got a Hue bulb in the landing outside my office, so I was wondering whether I could use that to show whether I’m on a call. Can I trigger a HomeKit scene based on whether my laptop’s camera is turned on or off?

mysql – Trigger maestro detalle

tengo unos triggers funcionando bien, pero tengo problemas para el rollback. Tengo una estructura de tabla:
Producto: id, Nombre, Precio, Stock
Venta: id, IDCliente, Fecha, Estado
VentaDetalle: id, IDProducto, Precio, Cantidad, SubTotal

En la tabla VentaDetalle, evento insert:
begin
update producto set Stock=producto.Stock-new.Cantidad
where new.IDProducto=producto.IDProducto;
end

En la tabla VentaDetalle, evento deleted:
begin
update producto set Stock=producto.Stock+old.Cantidad
where old.IDProducto=producto.IDProducto;
end

Funcionan bien, pero… cuando necesito hacer el rollback eliminando una venta y que se devuelva el stock a productos, solo lo realiza cuando elimino ventadetalle, pero si elimino desde venta no se dispara el evento.
Tengo el fk en la tabla ventadetalle referida a venta con eliminacion en cascada.
Como puedo solucionar el de que al eliminar venta y se eliminan tambien en ventadetalle se dispare el trigger de ventadetalle tambien.
Saludos!

How can I use the onEdit() trigger to timestamp a cell if another cell in the same row is checked “TRUE” in apps script?

I am able to add a timestamp in column 5 using a conditional when I’m comparing the column to a number, but what I am actually trying to do is add a stamp to the corresponding cell in column 5 if a cell in column 1 is checked as TRUE. When the box is checked, the corresponding cell in column 5 never displays the timestamp:

function onEdit(e) {
  var attendanceSheet = e.source.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if(col === true){
    attendanceSheet.getRange(row,5).setValue(new Date());    
  }
  else{
    attendanceSheet.getRange(row,5).setValue("");

  }
}

enter image description here

algorithm – How do i create an event trigger system (discord bot)

So i am making a card game, i having issues implementing “effects” and effects that are continuous. How can i get effects to activate at the right times, before and after an attack does damage as well as apply a continuous effect through out each phase? Is there a way i can implement some sort of checklist system before and after attack? I’ve tried 5 times with this, and this is just my latest attempt, How can i code this system without making if-code mess if there any tips or help, i would greatly appreciate it! (as this is my first time making a trading card game.)

for context i am making this into a discord bot!

What i need help with:

  • A System where i can check effects before, during and after attack
  • A system where i continuous effects are applied before during or after an attack
  • Applying the effect to the right target or to both targets
#while both players have health
# apply CONTINUOUS EFFECTS THROUGH OUT LOOP
while player1.hp > 0 and player2.hp > 0:
            print("test battle in progress")
            #class Card(object):
              #def __init__(self, name, hp, cardType, effect, attacks, description, ability):

            # Instantiate test subjects
            player1 = Card("Pinata Trent", 9, "Strong", None,
                       (Attack("Trample", 24, 2, None, None, None),
                        Attack("Body Bash", 62, 4, "selfDamage", 2, "self")),
                       "Trent card", None)

            player2 = Card("Barbarian", 10, "Strong", None,
                       (Attack("Head Bang", 68, 1, "selfDamage", 1, "self"),
                        Attack("Barbaric Swing ", 82, 2, None, None, None)),
                       None, None)


            #randomly choose a move for both NPC
            player1_move = random.choice(player1.attacks)
            player2_move = random.choice(player2.attacks)

            # Who ever player has the fastest attack value attacks first
            first_attack = firstAttack(player1_move, player2_move)

            if first_attack == 1:
                print("player 1 attacks first")
                await apply_damage(player2, player1_move)

                # Apply effect of attack after or before attack
                if player1_move.effect is not None:
                    print("player 1 attack effect activates")
                    player1 = player1.attack_effect(player1_move.effect, player1, player1_move.effectDamage)
                print(str(player1.hp) + " // " + str(player2.hp))

                # Player 2 attacks!
                if player2.hp > 0:

                    
                    player1.hp -= player2_move.damage
                    if player2_move.effect is not None:
                        print("player 2 attack effect activates")
                    player2 = player2.attack_effect(player2_move.effect, player2, player2_move.effectDamage)
                print(str(player1.hp) + " // " + str(player2.hp))
                await asyncio.sleep(2)

            elif first_attack == 2 and player2.hp > 0:
                print("player 2 attacks first")
                player1.hp -= player2_move.damage
                if player2_move.effect is not None:
                    print("player 2 attack effect activates")
                    player2 = player2.attack_effect(player2_move.effect, player2, player2_move.effectDamage)
                print(str(player1.hp) + " // " + str(player2.hp))

                # Player 1 attacks!
                if player1.hp > 0:
                    player2.hp -= player1_move.damage
                    if player1_move.effect is not None:
                        print("player 1 attack effect activates")
                        player1 = player1.attack_effect(player1_move.effect, player1, player1_move.effectDamage)
                print(str(player1.hp) + " // " + str(player2.hp))
                await asyncio.sleep(2)

attack move class:

class Attack:
    def __init__(self, name, speed, damage, effect, effectDamage, target):
        self.name = name
        self.speed = speed
        self.damage = damage
        self.effect = effect
        self.effectDamage = effectDamage
        self.target = target

    def attack_effect(self, tag, target, x):
        """
        todo: card attack effects
        """

        if tag == "selfDamage":
            target.hp -= x
            return target

        if tag == "forEachDamageMoreDamage":
            totalDamageTaken = target.starting_health - target.hp
            for i in target.attacks():
                if i.effect == "forEachDamageMoreDamage":
                    i.damage += totalDamageTaken
            return target

card class itself

class Card(object):
    def __init__(self, name, hp, cardType, effect, attacks, description, ability):
        self.name = name
        self.hp = hp
        self.starting_health = hp
        self.type = cardType
        self.attacks = attacks
        self.original_attacks = attacks
        self.effect = effect
        self.description = description

attack speed calculation

        def first_attack(player_atk_speed, opponent_atk_speed):
            if player_atk_speed.speed > opponent_atk_speed.speed:
                return 1
            elif player_atk_speed.speed < opponent_atk_speed.speed:
                return 2
            else:
                # decides who attacks based on random generation if attack speeds are EQUAL
                return random.randint(1, 2)

http – MongoDB Realm Scheduled Trigger – One Hour Timeout

I have a few links to trigger via HTTP.
Some of those links I have to trigger every 72 hours.
Others I have to trigger every 24 hours.

I was thinking I could use the MongoDB Schedule Triggers, setting a CRON expression to trigger every 24 hours and make some verifications and do the triggering.

Problem is, I have to wait almost 1 hour between each link in each array.

I am struggling with the setTimeout.

Anyone could give me a hint on the best way to make intervals between the items of the loop?
Or even point me to a better approach?

Note: I thought about creating a different function for each link. But since there are 12 of them, I wonder if there is a better way to make a server work for me..

      if(shouldExecute) {
        next.requestsList.forEach((link, index) => {
          var loopExecutedTime =  new Date();

          var response = context.http.post({url: "https://dummyapi.io/data/api/user?limit=1"});
          var query = { _id: ObjectId, "requestsList.title": link.title };
          var updateObj = { $set: { "requestsList.$.executed" : loopExecutedTime } };
          collection.findOneAndUpdate(query, updateObj);



        });
      }

mysql – Creating a trigger to update one table’s row based on another update from a different table’s row

So, I have the following two tables:

CREATE TABLE IF NOT EXISTS `services` (
  `services_id` INT NOT NULL AUTO_INCREMENT,
  `staff_num` INT NOT NULL,
  `health_and_wellness` INT NOT NULL,
  `tutoring` INT NOT NULL,
  PRIMARY KEY (`services_id`),
  UNIQUE INDEX `services_id_UNIQUE` (`services_id` ASC) VISIBLE,
  INDEX `fk_services_healthandwell_idx` (`health_and_wellness` ASC) VISIBLE,
  INDEX `fk_services_tutoring_idx` (`tutoring` ASC) VISIBLE,
  CONSTRAINT `fk_services_tutoring`
    FOREIGN KEY (`tutoring`)
    REFERENCES `tutoring` (`tutoring_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_services_healthandwell`
    FOREIGN KEY (`health_and_wellness`)
    REFERENCES `health and wellness` (`health_and_wellness_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

and

CREATE TABLE IF NOT EXISTS `tutoring` (
  `tutoring_id` INT NOT NULL AUTO_INCREMENT,
  `subject` INT NOT NULL,
  `staff_members` INT NOT NULL,
  PRIMARY KEY (`tutoring_id`),
  UNIQUE INDEX `tutoring_id_UNIQUE` (`tutoring_id` ASC) VISIBLE,
  INDEX `fk_tutoring_subject_idx` (`subject` ASC) VISIBLE,
  CONSTRAINT `fk_tutoring_subject`
    FOREIGN KEY (`subject`)
    REFERENCES `subjects` (`subject_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

I want to create a trigger that updates services.staff_num whenever tutoring.staff_members is updated.. I’ve tried a couple different triggers, but each time nothing happens or I get an error.. My latest attempt was this:

DELIMITER $$
CREATE TRIGGER UpdateStaffNum AFTER UPDATE ON collegedb.tutoring FOR EACH ROW
    BEGIN
        UPDATE services
        SET services.staff_num = services.staff_num + tutoring.staff_members; 
END;

Which of course would be too simple too work, heh. Anyone have any suggestions on this? Thanks.

magento2 – How to trigger discount amount calculation in checkout page

To offer different discounts based on the selected payment method, I’ve created two Cart Price Rules (one for each payment method) to apply the “Percent of product price discount” action. After selecting one of these two payment methods on the checkout page, the corresponding Cart Price Rule discount is applied over the subtotal. If I select another payment method (either with or without a Cart Price Rule associated), the discount amount does not reset or update. This happens in both local and test environments. Is there a way to trigger the recalculation of the discount amount in my custom module? Or at least reset the discount amount?

I’m using Magento version 2.4.0.