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:
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(LIIf]); LN.push(LIIl]); EM.push(LIIe]); }
output = {LI, Count, Sets, FN, LN, EM, F, L, E}];
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(LIhf]); to FN.push(LIsF]);
edit the l variable LN.push(LIsl]); to LN.push(LIuL]);
edit the e variable in EM.push(LIpe]); to EM.push(LI.E]);