Best answer

Cell with multiple data to new sheet rows


Userlevel 1

Hi,

 

For every new Google Spreadsheet row we have a cell with coma separated values, which could contain the first name, last name and email of 1 or more individuals:

row 1, cell 1: john,example,john.example@email.com,bob,example2,bob@mail.com,alice,example3,alice@test.com

 

We are trying to generate new Rows in a new Spreadsheet where we can populate 3 columns with this data, First Name, Last Name, Email Address.

 

We manage to use the Formatter with Text to Line Items and we get:

John

Example

john.example@email.com

Bob

Example2

bob@mail.com

Alice

Eample3

alice@test.com

 

But from here we cannot seem to map these to new individual rows in Google Sheets, we need help.

 

Thank you!

icon

Best answer by Andrei O. 1 April 2021, 09:58

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

10 replies

Userlevel 7
Badge +9

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/

Userlevel 7
Badge +14

@Andrei O. 

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

Userlevel 1

@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

 

Userlevel 7
Badge +14

@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?

Userlevel 1

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

Userlevel 7
Badge +14

@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.

Userlevel 1

@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.

Userlevel 7
Badge +14

@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.

Userlevel 7
Badge +14

@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}];

 

 

Userlevel 1

Hi @Troy Tessalone,

This solution is perfect! It is the first time I am using the Zapier Code Step and I can see how powerful this is.

I really appreaciate your help! How do we buy you a coffee?

Andrei

 

PS: If anyone else tries the solution, mind that Zapier Code seems is capital letter sensitive, just small edit at the end:

edit the f variable in FN.push(LI[f]); to FN.push(LI[F]);

edit the l variable LN.push(LI[l]); to LN.push(LI[L]);

edit the e variable in EM.push(LI[e]); to EM.push(LI[E]);