Shopify Flow: export orders to Google Sheets (one liner per order, even if multiple products ordered

[ScarletWave]

Hello,

I am using Shopify Flow in order to: Add line items to Google Sheets rows when a new order is placed

I managed to set it up, only one issue remains to solve. Everything is ok if the customer orders one products, because the system only generates one row in Google Sheets. But if the customer orders multiple products, each products gets its own row (so if 4 products are ordered, 4 rows). I only want one row per order.

The whole reason I created this gsheet, is for easier export to GLS, UPS etc. system, to print the shipping labels. And it’s really annoying if there are so many rows!

So the following columns need to have merged lines based on multiple products ordered (separated by comma):
1) Product SKUs

• so they are listed in the same field like: SKU1,SKU2,SKU3
  2) Products
• so they are listed in the same field like: product1,product2,product3
  3) Quantities
• so they are listed in the same field like: Q1,Q2,Q3

It looks like this right now (this is 1 order):

.

But I want it to look like this:

.

I tried this before, but the system gives ‘line_item error’:

1. {{ order.line_items | map: ‘sku’ | join: ', ’ }}
2. {{ order.line_items | map: ‘title’ | join: ', ’ }}
3. {{ order.line_items | map: ‘quantity’ | join: ', ’ }}

.

And right now I have it like this (but like this each item from the order has its own row):

1. {{lineItemsForeachitem.sku}}
2. {{lineItemsForeachitem.variant.displayName | replace: ‘,’, ’ ’ }}
3. {{lineItemsForeachitem.currentQuantity}}

My current code for the gsheet:

NEW_ORDER,{{ order.createdAt | date: ‘%Y-%m-%d %H:%M’ }},{{lineItemsForeachitem.sku}},{{lineItemsForeachitem.variant.displayName | replace: ‘,’, ’ ’ }},{{lineItemsForeachitem.currentQuantity}},{{ order.note }},{{ order.customer.firstName }} {{ order.customer.lastName }},{{ order.shippingAddress.address1 }},{{ order.shippingAddress.address2 }},{{ order.shippingAddress.zip }},{{ order.shippingAddress.city }},{{ order.shippingAddress.countryCodeV2 }},{{ order.customer.phone }},{{ order.customer.email }},{{ order.name }},{{order.fullyPaid}},{% for transactions_item in order.transactions %} {{transactions_item.gateway}} {% endfor %},Shopify,{{ order.createdAt | date: ‘%B’ }}

Thank you so much for your help!

[paul_n]

You are using a template that was designed to output each line item to a row. It does that by looping over the lineitems in the For each loop and repeatedly calling the “Add row” action. If you don’t want that to happen, remove the “For each” step. You will then need to adjust your liquid because you were getting data from a single line item and thus need to add a loop over the lineItems in your liquid. There are several examples of how to do that here: https://help.shopify.com/en/manual/shopify-flow/reference/variables#output-line-items

[ScarletWave]

Hello and thank you for your quick reply. I tried one of the options from the link you provided and I now closer to a solution than ever. I am now using the following code (bold part is new), but it opens up 3 additional questions that I hope you can help me with.

NEW_ORDER,{{ order.createdAt | date: ‘%Y-%m-%d %H:%M’ }},{% for a in order.lineItems %}
Product: {{a.title | replace: ‘,’, ’ ’ }}
SKU: {{a.sku}}
Quantity: {{a.quantity}}
{% endfor %},{{ order.note }},{{ order.customer.firstName }} {{ order.customer.lastName }},{{ order.shippingAddress.address1 }},{{ order.shippingAddress.address2 }},{{ order.shippingAddress.zip }},{{ order.shippingAddress.city }},{{ order.shippingAddress.countryCodeV2 }},{{ order.customer.phone }},{{ order.customer.email }},{{ order.name }},{{order.fullyPaid}},{% for transactions_item in order.transactions %} {{transactions_item.gateway}} {% endfor %},Shopify,{{ order.createdAt | date: ‘%B’ }}

PROBLEM 1:

I liked how the order info is now grouped together in one field and it’s much clearer what the customer ordered. But even though both ordered products are now in the same field, it still makes 2 rows for the same (1) order. Can you please help me adjust the code so it’s only one line per order?

PROBLEM 2:

In column C (under Order) I also want to add the variant name. So the info would follow as follows:

Product:

Variant:

SKU:

Quantity:

Can you please help me with the code I need to insert in between, so variant name is also shown?

PROBLEM 3

As you can see, it says ‘(blank)’ in the column with Notes. I suppose that’s because the customer didn’t leave any notes in her order. Is it possible that this field would be empty…and be filled unless the customer wrote something?

Another column like this is ‘Phone Number’ - it says ‘(blank)’ because the customer didn’t leave the phone number. It would be really helpful if this field was left empty (unless the customer wrote a phone number).

I can’t thank you again and can’t wait to hear from you soon.

[paul_n]

Problem 3 - you can insert =CHAR(20) to force an empty cell.

Problem 2 - variant name at the time of the order is. That field should be easy to find in Flow’s variable picker if you need any more fields…just know that you may need to delete the loop it inserts.

{{ a.variantTitle }}

Problem 1 - flow only adds one row per action. So you have something happened where it does it twice. I can’t troubleshoot that from what you provided. My guess is that you ran the worklow twice in testing.

[ScarletWave]

Hello,

thank you for all of the help you keep providing. The ‘variant issue’ is now solved, it is included in this field with order info.

PROBLEM 3 - you can insert =CHAR(20) to force an empty cell

When I wrote only =CHAR(20) (between 2 commas), all it did was create a new field with text =CHAR(20) inside. You maybe meant that this =CHAR(20) should be a part of another variable?

If you remember, what I wanted to do was set up a special function of the column Note, where the field only has text if the customer wrote something, otherwise it should be empty (at the moment it has ‘(blank)’ inside). Before your suggestion, I only used this code for the Note field: {{ order.note }}

PROBLEM 1 - flow only adds one row per action

I just placed a new test order, to start fresh and check if the same error happens again. In this new test order I ordered 4 items…and when I checked the gsheet, it had 4 identical rows! So previously when I tested order, I ordered 2 items - and in gsheet it gave me 2 rows.

It appears that the system generates the number of (identical) rows based on how many products the customer ordered. Let me paste the code I am using again below + the screenshot. Look at the yellow marked text. Is the issue this ‘line items’ part of the code?

NEW_ORDER,{{ order.createdAt | date: ‘%Y-%m-%d %H:%M’ }},{% for a in order.lineItems %}
Product: {{a.title | replace: ‘,’, ’ ’ }}
Variant: {{ a.variantTitle }}
SKU: {{a.sku}}
Quantity: {{a.quantity}}
{% endfor %},{{ order.note }},{{ order.customer.firstName }} {{ order.customer.lastName }},{{ order.shippingAddress.address1 }},{{ order.shippingAddress.address2 }},{{ order.shippingAddress.zip }},{{ order.shippingAddress.city }},{{ order.shippingAddress.countryCodeV2 }},{{ order.customer.phone }},{{ order.customer.email }},{{ order.name }},{{order.fullyPaid}},{% for transactions_item in order.transactions %} {{transactions_item.gateway}} {% endfor %},Shopify,{{ order.createdAt | date: ‘%B’ }}

Thank you very much again!

[paul_n]

This works for me:

{{order.name}},=CHAR(20),{{order.customer.displayName}}

[paul_n]

Regarding having 4 identical rows, your liquid isn’t referencing the lineItem from the loop. It’s getting order / lineitems, which will be the same list of line items. In the template it showed what that variable looks like, but you deleted it:

To add again, choose the this:

[ScarletWave]

Hello,

replying to your last answer, regarding having 4 identical rows. I am sorry, but I don’t know what you mean with these *'*liquid isn’t referencing the lineItem from the loop’ and ‘It’s getting order / lineitems, which will be the same list of line items’. I have never done this before, I started studying this just a few days ago and somehow I managed to get this far (with only one big issue - these multiple rows).

You said this: in the template it showed what that variable looks like, but you deleted it:

{{order.name}}, {{lineItemsForeachitem.title}}, {{lineItemsForeachitem.quantity}}, ${{lineItemsForeachitem.originalTotalSet.shopMoney.amount}}

Ok, so I went from the beginning and pasted this code in again and did ‘retry run’ - these are different variables than what I wanted, but ok. Even with this, the issue remains - there are 4 rows for one order (where the customer ordered 4 items).

This is the code I am using right now - can you be more specific? Which part do I need to adjust and how (exact code) in order for this ‘multiple row’ issue to be corrected?

NEW_ORDER,{{ order.createdAt | date: ‘%Y-%m-%d %H:%M’ }},{% for a in order.lineItems %}
Product: {{a.title | replace: ‘,’, ’ ’ }}
Variant: {{ a.variantTitle }}
SKU: {{a.sku}}
Quantity: {{a.quantity}}
{% endfor %},{{ order.note }},{{ order.customer.firstName }} {{ order.customer.lastName }},{{ order.shippingAddress.address1 }},{{ order.shippingAddress.address2 }},{{ order.shippingAddress.zip }},{{ order.shippingAddress.city }},{{ order.shippingAddress.countryCodeV2 }},{{ order.customer.phone }},{{ order.customer.email }},{{ order.name }},{{order.fullyPaid}},{% for transactions_item in order.transactions %} {{transactions_item.gateway}} {% endfor %},Shopify,{{ order.createdAt | date: ‘%B’ }}

I helped myself with this link you sent me, especially this marked part:

Thank you so much for your help!

[paul_n]

Sorry it’s hard to follow this thread and exactly what you are trying to do. Based on your last few replies, I thought the issue was that you had 4 rows of the exact same item. But looking out your initial post, I think you want a single row. For that, you should not use “For each” at all, because the purpose of that is to add one row per line item.

Instead, you would just direction connect in the trigger and the “Add row” action

As of the liquid, if you want those numbers in separate columns, it would look something like this, where you loop over lineItems each time.

NEW_ORDER,{{ order.createdAt | date: '%Y-%m-%d %H:%M' }},
{%- for a in order.lineItems -%}
Product: {{a.title | replace: ',', ' ' }}
Variant: {{ a.variantTitle }}
{%- endfor -%},
{%- for b in order.lineItems -%}
SKU: {{b.sku}}
{%- endfor -%},
{%- for c in order.lineItems -%}
Quantity: {{c.quantity}}
{%- endfor -%}

You could also loop once and assign each thing to a variable:

{%- for a in order.lineItems -%}
{% capture product_text %} 
{{ product_text }}
Product: {{a.title | replace: ',', ' ' }}
Variant: {{ a.variantTitle }}
{% endcapture %}
{% capture sku_text %} 
{{ sku_text }}
SKU: {{a.sku}}
{% endcapture %}
{% capture quantity_text %} 
{{ quantity_text }}
Quantity: {{a.quantity}}
{% endcapture %}
{%- endfor -%}
NEW_ORDER,{{ order.createdAt | date: '%Y-%m-%d %H:%M' }},{{product_text}}, {{sku_text}}, {{quantity_text}}

[ScarletWave]

Hello,

I know, I have written so much, that it’s easy to get lost. The examples of code you sent above are also good (in terms of how the data is presented. I love that the whole order is in the same row (and that everything that the customer ordered is in the same row).

Before you helped me out with this, the issue was that each products (of the order, if the customer ordered multiple ones) was in a separate row. Now at least all is in one row…but for some reason because she ordered 4 different products, the system gives me 4 identical rows in gsheet.

I pasted the (second) code you wrote, exactly as you wrote it:

{%- for a in order.lineItems -%}
{% capture product_text %}
{{ product_text }}
Product: {{a.title | replace: ‘,’, ’ ’ }}
Variant: {{ a.variantTitle }}
{% endcapture %}
{% capture sku_text %}
{{ sku_text }}
SKU: {{a.sku}}
{% endcapture %}
{% capture quantity_text %}
{{ quantity_text }}
Quantity: {{a.quantity}}
{% endcapture %}
{%- endfor -%}
NEW_ORDER,{{ order.createdAt | date: ‘%Y-%m-%d %H:%M’ }},{{product_text}}, {{sku_text}}, {{quantity_text}}

…and this is what the system put out - 4 identical rows for 1 order. So what I need it to do is to write it exactly like this, but only 1 row!!! And for example if the order had 2 items, the system put out 4 identical rows for this order:

So what am I doing wrong here? What part of the code is doing this?

The same thing (4 rows if she ordered 4 items) also happens with my previous code:

NEW_ORDER,{{ order.createdAt | date: ‘%Y-%m-%d %H:%M’ }},{% for a in order.lineItems %}
Product: {{a.title | replace: ‘,’, ’ ’ }}
Variant: {{ a.variantTitle }}
SKU: {{a.sku}}
Quantity: {{a.quantity}}
{% endfor %},{{ order.note }},{{ order.customer.firstName }} {{ order.customer.lastName }},{{ order.shippingAddress.address1 }},{{ order.shippingAddress.address2 }},{{ order.shippingAddress.zip }},{{ order.shippingAddress.city }},{{ order.shippingAddress.countryCodeV2 }},{{ order.customer.phone }},{{ order.customer.email }},{{ order.name }},{{order.fullyPaid}},{% for transactions_item in order.transactions %} {{transactions_item.gateway}} {% endfor %},Shopify,{{ order.createdAt | date: ‘%B’ }}

Thank you in advance!

[paul_n]

You missed a step in my instructions above:

[ScarletWave]

Hello,

that’s great, it worked! So the solution is to delete this middle part of the code and then there’s no repeat rows (but all in one row); no matter if it’s the formulas you shared in your previous message or the formula I previously used. THANK YOU!

Another QUESTION: what does this =CHAR(20) do?

I tried it now and if I insert this =CHAR(20) it leaves that column empty. Which is very useful for sure.

But I wanted this functionality: I added a variable that customers don’t always fill out (e.g. Notes, second Address row, Telephone number). So if the customer didn’t write anything in that field, it says ‘(blank’) in the table. This is very distracting when exporting the data to GLS or DHL system, because I then have to delete this ‘(blank)’ manually.

Is there an option like this: if this field has text, then text is written in the ghseet. If the customer didn’t write anything, then it’s just an empty place in the gsheet (and no (blank)!). I need this for the following variables:

• Notes: {{ order.note }}

• Address Notes: {{ order.shippingAddress.address2 }}

• Phone Number: {{ order.customer.phone }}

Thank you so much again!

[paul_n]

CHAR in sheets outputs a character. In this case a blank. So it’s a workaround way to force a blank.

If you want it to show only when something is blank:
{% if order.note == blank %}=CHAR(20){% else %}{{order.note}}{%endif%}

[ScarletWave]

Thank you! This works!