Cell with multiple data to new sheet rows

Hi @Andrei O.

Try Formatter’s split function and use ‘,’ (comma) as a separator. This will generate 3 values in the sequence, you can then easily map it.

https://zapier.com/blog/zapier-formatter-guide/

@Andrei O. 

From what app is the data coming from that is populating the GSheet row?

@robschmidt, thank you! We tried, but we only manage to get the line items and we did not figure out yet hot to map them to the new google sheet rows we are creating. each order has a different number of items in it.

@Troy Tessalone, we have a Shopify store where someone can place an order a product, one or more items of the same product, and they need to personalise each product with a First Name, Last Name, Email Address. 

We have a form in Shopify that collects the personalization fields, and then for every new order we push the date to Google Spreadsheets.

Basically we’re trying to get from here:

to here:

Many thanks!

Andrei

@Andrei O. 

Shopify custom data can be tricky to work with.

Can you share screenshots of how the data originally is retruned from Shopify in the trigger step?

Hi @Troy Tessalone, I will get a sample for the Shopify data, it is on my colleague’s account.

However, in the example above I a just using normal Google Spreadsheet formatting, I just typed the data in so we can do the tests, it does not have any Shopify formatting.

We figured how to get B2 in the first sheet to individual Line Items by using the Formatter, ex:

bob
doe
bob@example.com
alice
smith
alice@example.com
dean
martin
dean@example.com
frank
white
frank@example.com

After this step we got stuck, we only got to map the first 3 lines:

bob
doe
bob@example.com

 to

B2, C2, D2 in the second sheet.

Would Looping help here? So it could go in batches of 3 to populate the second sheet? I am starting to think that the solution could probably involve multiple Action steps not just one?

Many thanks!

Andrei

@Andrei O. 

All going to depend on how the data is returned from Shopify.

For example, will each set always have 3 data points: first name, last name, email?

May have to go with a more advanced approach and use a Code step to handle the data parsing.

@Troy Tessalone , correct, each set will always have 3 data points (first name, last name, email) although the number of persons might will vary from order to order. Sometime just 1 person, other times more (3-4, etc).

I think your advice is good, it might be more of a Code step than a Formatting/Looping/Etc one. I figure other people might try to do the same thing/same use case, maybe at some point it will be available as an action.

@Andrei O. 

The solution is dependent on the way the custom Shopify data is returned from the trigger step, not so much the available apps to use in actions.

@Andrei O.

Try this code in a JavaScript Code step.

This will return the First Names, Last Names, and Emails as separate arrays.

You can then use a GSheets Add Row(s) step.

NOTE: Replace the LI Input Data values with the comma delimited data returned from Shopify.

var LI = inputData.LI.split(','); // LI = Line Items
var Count = LI.length;
var Sets = Count / 3;
var F = 0; // F = First Name increment start
var L = 1; // L = Last Name increment start
var E = 2; // E = Email increment start
var FN = []; // FN = First Name
var LN = []; // LN = Last Name
var EM = []; // EM = Email

for (F, L, E; F < Count; F += 3, L += 3, E += 3) {
FN.push(LI[f]);
LN.push(LI[l]);
EM.push(LI[e]);
} 

output = [{LI, Count, Sets, FN, LN, EM, F, L, E}];