Shopify Flow inserts all data in one row in Google Sheets

[karilynae]

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!

[paul_n · 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

[karilynae]

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

[paul_n]

look at the result tab

[karilynae]

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/22

08/26/22

Elsie

Kari

Activated CAL-MAG 180 tabs

1

true

St Paul

(blank)

## #1276

08/24/22

08/26/22

Elsie

But this is what I need...

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

Thanks for your assistance on this!!!

[paul_n]

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

[karilynae]

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]

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}}

[Aina-amrn11]

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]

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:

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

Hope that helps!

[Aina-amrn11]

This helps a lot! thank you so much Dave

[maddienchapman]

Hello! 

I am trying to pass over line items to a spreadsheet and each line item needs to fall into the next row. Using 'for each' passes over all the all order info for the applicable number of variants into that number of rows. Is there a way to pull all info into the next row but not all line items, just the next line item? Does that make sense? Copy of my row contents below and screenshot of flow + outcome.

{{order.name}},{{order.processedAt}},{{order.customer.firstName}},{{order.customer.lastName}}, {{ order.shippingAddress.address1 }}, {{ order.shippingAddress.address2 }}, {{ order.shippingAddress.city }}, {{ order.shippingAddress.province }}, {{ order.shippingAddress.countryCodeV2 }}, {{ order.shippingAddress.zip}}, USPS Ground Advantage,

{% for lineItems_item in order.lineItems %}
{% for tags_item in lineItems_item.product.tags %}
{% if tags_item == 'nuwallpapersample' %}
{{lineItems_item.quantity}},
{{lineItems_item.sku}},
{% endif %}
{% endfor %}
{% endfor %}

[paul_n]

Yes, you are looping over lineItems. When you click "add a variable" you'll see an "item" from the For each loop that you can select.