Re: Shopify Flow inserts all data in one row in Google Sheets

Solved

Shopify Flow inserts all data in one row in Google Sheets

karilynae
Excursionist
14 0 39

I'm using Flow to insert return data into Google Sheets.  When there is more than one item returned it is putting all of the data in a single row.  How can I break up the data into new rows?  I have tried many different characters and can't find anything that'll work.

This is what I have in Flow.

{% for refundLineItems_item in refund.refundLineItems %}
{{order.name}}, {{order.createdAt | date: '%x' }}, {{refund.createdAt | date: '%x' }}, {{order.customer.displayName}}, {{refund.staffMember.name}},
{{refundLineItems_item.lineItem.name | remove: "," }}, {{refundLineItems_item.quantity}},
{{refundLineItems_item.restocked}}, {{refundLineItems_item.location.name}}, {{refund.note}},
{% endfor %}

 

Thanks for your assistance!

Accepted Solution (1)

paul_n
Shopify Staff
1361 152 319

This is an accepted solution.

This is how the action works. It inserts one row. We are working to make multiple rows possible, but in the meantime you could insert a delimiter like `##` between rows and then split in Sheets with some code. I have an example here:
https://docs.google.com/spreadsheets/d/1w9qSHe_hccBQ90dSPSVESWTFNWhdLhgMmtq6VE5ZPRA/edit?usp=sharing

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.

View solution in original post

Replies 10 (10)

paul_n
Shopify Staff
1361 152 319

This is an accepted solution.

This is how the action works. It inserts one row. We are working to make multiple rows possible, but in the meantime you could insert a delimiter like `##` between rows and then split in Sheets with some code. I have an example here:
https://docs.google.com/spreadsheets/d/1w9qSHe_hccBQ90dSPSVESWTFNWhdLhgMmtq6VE5ZPRA/edit?usp=sharing

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
karilynae
Excursionist
14 0 39

Thank you for your response.  I really am not familiar with Google Sheets, so will need to figure out what the ## is supposed to do and how.  Below is what I see when I click on your link.  I don't see new rows splitting at the ##.

karilynae_0-1661527006594.png

 

paul_n
Shopify Staff
1361 152 319

look at the result tab

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
karilynae
Excursionist
14 0 39

Oops, see it now.  I inserted ## at the end of my flow statement, and added your code into google sheets. but it is only splitting within the one cell.  I need it to start a whole new row with all the data after the ##.

This is what I get...


#1276
08/24/2208/26/22ElsieKari
Activated CAL-MAG 180 tabs
1
true
St Paul(blank)##


#1276
08/24/2208/26/22Elsie

 

But this is what I need...


#1276
08/24/2208/26/22ElsieKari
Activated CAL-MAG 180 tabs
1
true
St Paul(blank)
#127608/24/2208/26/22ElsieKari
Aller-B 90 caps
1
true
St Paul(blank)

 

Thanks for your assistance on this!!!

paul_n
Shopify Staff
1361 152 319

I might need to see the code...but note that the column with the lineItems should be a single column and use :: to separate fields (because the comma will be split by Sheets and mess things up). Also note the heading uses the same approach to generate column titles

 

For example, the code for row2 would look like (ignoring liquid for now):

1234, Product A::1::sku_0##Product B::3::sku_2, John Doe

 

 

 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
karilynae
Excursionist
14 0 39

So I replaced the comma's with :: and this is what I get now.  Everything in one cell.


#1277::08/26/22::08/26/22::Elsie Mae McShane::Kari Myers::
Cenitol 7.8oz::1::
true::Eagan::
##

#1277::08/26/22::08/26/22::Elsie Mae McShane::Kari Myers::
Candicid Forte 90 caps::1::
true::Eagan::
##


 

I just copied and pasted your example code, didn't change a thing.

and this is what I have in Flow...

{% for refundLineItems_item in refund.refundLineItems %}
{{order.name}}::{{order.createdAt | date: '%x' }}::{{refund.createdAt | date: '%x' }}::{{order.customer.displayName}}::{{refund.staffMember.name}}::
{{refundLineItems_item.lineItem.name | remove: "," }}::{{refundLineItems_item.quantity}}::
{{refundLineItems_item.restocked}}::{{refundLineItems_item.location.name}}::{{refund.note}}, ##
{% endfor %}

paul_n
Shopify Staff
1361 152 319

Look again at my code...not all commas are replaced. Just those for the line items. Also, you putting all of the order details inside the loop, causing them to repeat. Try something like this:

 

{{order.name}},{{order.createdAt | date: '%x' }},{{refund.createdAt | date: '%x' }},{{order.customer.displayName}},{{refund.staffMember.name}},
{% for refundLineItems_item in refund.refundLineItems %}
{{refundLineItems_item.lineItem.name | remove: "," }}::{{refundLineItems_item.quantity}}::
{{refundLineItems_item.restocked}}::{{refundLineItems_item.location.name}}{% if forloop.last %},{% else %}##{% endif %}
{% endfor %}
{{refund.note}}

 

Paul_N | Flow Product Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
Aina-amrn11
Shopify Partner
2 0 0

Hi Paul, I am trying to recreate the sample you have given by duplicating the same example data + code, but it returns to this error. 

TypeError: Cannot read properties of undefined (reading 'length')
result
@ Untitled.gs:14

 

also, what do you mean by this approach should no longer be necessary if I use the "For Each" loop? is there an alternative way to achieve this without using appscript? 

This is my row content on shopify flow:

{{draftOrder.id}},
{{draftOrder.totalPrice}},
{{draftOrder.currencyCode}},
{% for lineItems_item in draftOrder.lineItems %}
{{lineItems_item.name}}::
{{lineItems_item.id}}::
{{lineItems_item.quantity}}##
{% endfor %}
DaveMcV
Shopify Staff
104 31 29

Hi Aina-Amrn11,

 

You can use the For Each action now to iterate through the line items on the refund and add rows to the spreadsheet one at a time. Here's a quick example of how that could work:

DaveMcV_0-1682169776143.png

 

This is a feature we released earlier this year, but was not yet available when the original post was made.

 

Hope that helps!

DaveMcV | Flow Development Manager @ Shopify
- Finding Flow useful? Leave us a review
- Need Flow help? Check out our help docs.
- Building for Flow? Check out Flow's dev docs.
Aina-amrn11
Shopify Partner
2 0 0

This helps a lot! thank you so much Dave