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,email@example.com,bob,example2,firstname.lastname@example.org,alice,example3,email@example.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:
But from here we cannot seem to map these to new individual rows in Google Sheets, we need help.
Best answer by Andrei O.View original
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.
From what app is the data coming from that is populating the GSheet row?
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:
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?
@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:
After this step we got stuck, we only got to map the first 3 lines:
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?
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.
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.
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.
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.
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?
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]);