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.

[questions6]

Hi There,

Im trying to do something simular, but to separate each item ordered into a separate row.
How can I go about doing that?

Below is my current row contents
{{order.name}}, {{order.shippingAddress.name}}, {{order.shippingAddress.address1}}, {{order.shippingAddress.address2}}, {{order.shippingAddress.company}}, {{order.shippingAddress.city}}, {{order.shippingAddress.zip}}, {{order.shippingAddress.province}}, {{order.shippingAddress.country}}, {% for lineItems_item in order.lineItems %}
{{lineItems_item.variant.sku}}
{% endfor %}, {% for lineItems_item in order.lineItems %}
{{lineItems_item.quantity}}
{% endfor %}

[paul_n]

Use a For each action to loop over the lineItems and call the Sheets action for each of those line items. This has been answered several times in this thread, with screenshots, if you scroll up. 

[questions6]

Hi @paul_n 

Thanks for taking a look.  I believe I already used the for each loop.  see screenshots below.  Would you be so kind as to confirm what Im missing?

[questions6]

Following up on this again @paul_n 

thank you in advance

[paul_n]

Your logic is skipping the for each because you don't have the action on the "do this for each item" port. 

[questions6]

Thanks @paul_n 

When I do that there are multiple lines created, but all items are in the same cell repeated. 

Would you be so kind as to share how I can change it so theres only 1 item per line?

[paul_n]

Your liquid is looping over the order.lineitems instead of the lineItems from the loop. 

Click "Add a variable", choose the lineItem from the for each loop, and then the values you want. Once you see the syntax, it should be easy to adjust the rest. You shouldn't need to loop over the lineItems any more, so it should be a bit simpler

[questions6]

Hi @paul_n ,

Thanks for your guidance earlier!

I tried to follow your directions, but I’m still encountering an issue where all the information is populating into one cell in the spreadsheet. I’ve worked with Shopify Support, but they weren’t able to provide a solution.

Could you please take a look at what I might need to fix to ensure the data is separated correctly?

Thank you in advance for your help!

Best,
June

Link to Flow

[paul_n]

I was not saying to remove the "For each" action. I was saying to remove the loop from your liquid in the Sheets action. 

At this point, just use this template to get started: https://shopify.com/admin/apps/flow/editor/templates/9002b26c-b2fa-4d9b-adb5-5e9db9c6beb9. Then add your data to fill out the row.