Assign Multiple Categories from a GoogleSheet

[bomanden]

Description

Since all cells becomes either text or numbers per default from the GoogleSheet output,
Is there a specific syntax I can use to import multiple categories ,

Tried the following in google cells
( Looks like form the docs that arrays need to be piped, but the json always wraps the output "Onshore|Offshore" as a string )
What I tried as in the cells,

1. "Onshore|Offshore" ( just piped )
2. "(Onshore|Offshore)" ( piped & brackets )
3. "[Onshore|Offshore]" ( piped & hard-brackets )
4. "Onshore,Offshore" ( comma )

Anyone has a solution/syntax to tell feedme its an array from googlesheets cell data ?

Cheers , Great plugin btw ;)

Steps to reproduce

``
[
"BP",
"Partnerships",
"Onshore|Offshore"
],
"Total",
"Partnerships",
"(Onshore|Offshore)"
],
[
"Shell",
"Partnerships",
"Onshore"|"Offshore"
],
[
"Other",
"Partnerships",
"Onshore,Offshore"
],

``

Additional info

• Plugin version:4.1.2
• Craft version:3.2.10

[bomanden]

[Link to a related post:]#516

Guess I could alter the string with this event ? split the piped string to an array.
But how to format it into multiple categories, and get it saved ?

From Other post :

use yii\base\Event;
use craft\feedme\services\Fields;
use craft\feedme\events\FieldEvent;
Event::on(Fields::class, Fields::EVENT_AFTER_PARSE_FIELD, function(FieldEvent $event) {
    if ($event->fieldHandle === 'myAssetsField' && $event->fieldValue !== null) {
    }
});

[bomanden]

Got it working with the EVENT_STEP_BEFORE_ELEMENT_SAVE
https://craftcms.stackexchange.com/questions/30734/modifying-slug-on-feedme-import

I split the piped string of categories from google sheet and match it against crafts categories to get the ids
$partnerCategories = Category::find()->group('partnerCategories')->all();

Does not feel like the most efficient solution ( needing to request the categories ) , but it works.

If anyone should have a more optimized solution please share
( best would be if there was a way , to get feedme to recognise the piped string/delimiter of categories from googleSheets )

Its kinda ugly I know but heres the 'solution' :

Event::on(Process::class,Process::EVENT_STEP_BEFORE_ELEMENT_SAVE,function(FeedProcessEvent $event){
switch($event->feed['name']){
    case 'MyFeedHandle':
    $element = $event->element;
    if (isset( $event->feedData['Categories'])){

        $strArray = strpos($event->feedData['Categories'], '|') ? array_map('trim', explode("|",$event->feedData['Categories'])) : $event->feedData['Categories'];

        if(is_array($strArray))
        {
            $catOut = array();
            $partnerCategories = Category::find()
            ->group('partnerCategories')
            ->all();

            for ($x = 0; $x < count($strArray); $x++) {
                $el = $strArray[$x];

                $keys = array_column($partnerCategories, 'title');
                $index = array_search($el, $keys);

                if ($index !== false) {
                    array_push($catOut , $partnerCategories[$index]['id']);
                }
            }

            if (!empty($catOut)) {
                $element->partnerCategories = $catOut;
            }
        }
    }
    break;
    }
});

[AMandujano-BI]

Got it working with the EVENT_STEP_BEFORE_ELEMENT_SAVE
https://craftcms.stackexchange.com/questions/30734/modifying-slug-on-feedme-import

I split the piped string of categories from google sheet and match it against crafts categories to get the ids
$partnerCategories = Category::find()->group('partnerCategories')->all();

Does not feel like the most efficient solution ( needing to request the categories ) , but it works.

If anyone should have a more optimized solution please share
( best would be if there was a way , to get feedme to recognise the piped string/delimiter of categories from googleSheets )

Its kinda ugly I know but heres the 'solution' :

Event::on(Process::class,Process::EVENT_STEP_BEFORE_ELEMENT_SAVE,function(FeedProcessEvent $event){
switch($event->feed['name']){
    case 'MyFeedHandle':
    $element = $event->element;
    if (isset( $event->feedData['Categories'])){

        $strArray = strpos($event->feedData['Categories'], '|') ? array_map('trim', explode("|",$event->feedData['Categories'])) : $event->feedData['Categories'];

        if(is_array($strArray))
        {
            $catOut = array();
            $partnerCategories = Category::find()
            ->group('partnerCategories')
            ->all();

            for ($x = 0; $x < count($strArray); $x++) {
                $el = $strArray[$x];

                $keys = array_column($partnerCategories, 'title');
                $index = array_search($el, $keys);

                if ($index !== false) {
                    array_push($catOut , $partnerCategories[$index]['id']);
                }
            }

            if (!empty($catOut)) {
                $element->partnerCategories = $catOut;
            }
        }
    }
    break;
    }
});

Thanks @bomanden, does this works also for uploading multiple assets to a field?

[BridgingUnit]

Here's another solution - am using Feed Me 4.3.6.

This updates the data in the feed at an earlier stage, using the beforeProcessFeed event and re-presents the feed data in the format Feed Me seems to expect so that you don't have to run any extra queries at a later stage to identify Ids etc. We just identify the columns with delimited data and then update the feed data to add columns for each bit of data that was previously delimited:

// Before the feed processing has started
Event::on(Process::class, Process::EVENT_BEFORE_PROCESS_FEED, function(FeedProcessEvent $event) {
    
    // Kick in only for specified feeds
    switch($event->feed['name']){

        // Which feeds need us to alter them?
        case 'Test':

            // Which columns have delimited data?
            $delimited_columns = array("COMPANY TYPE","SOMETHING ELSE");

            // What delimiter are we looking to split?
            $delimiter =',';
            
            // Go through each row in feedData
            foreach($event->feedData as $i => $row){
                
                // And update each column which has delimited data
                foreach($delimited_columns as $j => $col){

                    // Make an array of that delimited data from the column
                    $delimited_fields = explode($delimiter, $row[$col]);

                    // Add a new field to row for each datum within our data, using the naming convention Feed Me expects. 
                    foreach($delimited_fields as $ref => $val){

                        // Feed Me in other scenarios adds a separate column for each, where it was originally provided a single array of items. 
                        // This is probably something that ought to be updated within the GoogleSheet datatype within Feed Me, or better still at Google Sheets when they're generating their JSON
                        // The key for the new column matches our original column name
                        // but appended with / and an incrementing index that is zero based to indicate its position within the original array.
                        $row[$col.'/'.$ref] = $val;
                    }

                    // Remove original field now it has been replaced
                    unset($row[$col]);
                }

                // And add back our amended row again
                $event->feedData[$i] = $row;
            }

        break;
    }

});

If you var_dump the feed data before, you see something like this (COMPANY TYPE is our delimited column):

array(3) {
  [0]=>
  array(2) {
    ["COMPANY NAME"]=>
    string(3) "One"
    ["COMPANY TYPE"]=>
    string(12) "BROKER/AGENT"
  }
  [1]=>
  array(2) {
    ["COMPANY NAME"]=>
    string(3) "Two"
    ["COMPANY TYPE"]=>
    string(16) "FISHERY,RETAILER"
  }
  [2]=>
  array(2) {
    ["COMPANY NAME"]=>
    string(5) "Three"
    ["COMPANY TYPE"]=>
    string(23) "TRADING (IMPORT/EXPORT)"
  }
}

whereas afterwards you have made it into something like this, and it then imports as you intended:

array(3) {
  [0]=>
  array(2) {
    ["COMPANY NAME"]=>
    string(3) "One"
    ["COMPANY TYPE/0"]>
    string(12) "BROKER/AGENT"
  }
  [1]=>
  array(3) {
    ["COMPANY NAME"]=>
    string(3) "Two"
    ["COMPANY TYPE/0"]=>
    string(7) "FISHERY"
    ["COMPANY TYPE/1"]=>
    string(8) "RETAILER"
  }
  [2]=>
  array(2) {
    ["COMPANY NAME"]=>
    string(5) "Three"
    ["COMPANY TYPE/0"]=>
    string(23) "TRADING (IMPORT/EXPORT)"
  }
}

[hiasl]

Just a question: have you tried splitting the categories with the separator set for CSV files? For me it was '-|-' and this worked for image filenames, if I remember correctly, so I guess it would work with categories as well.

[al0xd]

Just a question: have you tried splitting the categories with the separator set for CSV files? For me it was '-|-' and this worked for image filenames, if I remember correctly, so I guess it would work with categories as well.

Thank, it's work perfect