Skip to main content
Best answer

Zap is only Looping on a single row in Google Sheets instead of all line items

This post has been edited by a moderator to remove personal information. Please remember that this is a public forum and to remove any sensitive information prior to posting.

 Hi Guys,

 

So I stumbled upon an article about Digest 

and I figured this is a solution I can use to what I wanted to achieve and have been tinkering on it all day. But, it has come to a point that I am stuck and need your expert opinions.

 

Here’s what I have in my Zap:

 

So what I wanted to do here is to Get New Rows from Shopify Paid Orders.

It has these items:

Name:(Order No)

Updated at: (Date of the Order)

SKU: (SKU from the order)

Corrected SKU:(Using vlookup formula I call a value from a separate sheet and output it to the said cell)

Vendor:(Vendor from Shopify Order)

Description: (Using vlookup formula I call a value from a separate sheet and output it to the said cell)

Price:(Line Item Price from the order)

Total:(Total Price from the Order)

 

It then goes to Looping and I used Loop From Line Items, correct me if I am wrong but the rows created in the Google Sheets are line items separated in Rows right?

So this is what I did for the Loop:

When I did the test it returned:

Shouldn’t there be 4 total iterations if there are four rows created?

Next I did an Append Entry Digest:

 A filter to terminate the loop: with the Condition as Loop Iteration Last: True Boolean True

Then Made a Setup for the Release of the Digest as so:

The final output is to make a Document from a Template wherein the I used Both Final Digest and List as output:

The issue now is it is only showing one record not the total number of iterations, please can anyone check this out?

This is the Data in and out for the Loop:

 

So in there I can see only one record is being processed instead of 4. Any help will be much appreciated.

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

16 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @KWF 

Good question.

Try mapping the line items from the Shopify trigger step instead of the Google Sheets step here:

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
K
Userlevel 2
Badge

@Troy Tessalone the problem with that step is that it will not include the data I have in the spreadsheet I need to replace the value of the SKU and Title from Shopify with the corrected ones in the Google Sheet spreadsheet.

If your suggestion is the best solution, can you then add another Formatter step before or after the Digest so it will replace those fields first?

 

Any other suggestions?

Troy Tessalone
Userlevel 7
Badge +14

@KWF

If you are trying to use the GSheet step it will only return 1 row from the Row(s) created.

 

One option would be to move the GSheet row inside the Loop.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
jesse
Userlevel 7
Badge +9

@KWF checking in here to see if moving the Google Sheets step inside of the Loop did the trick for you. Let us know - we want to make sure you’re good to go here!

K
Userlevel 2
Badge

@jesse 

Hope you are doing well. 

What do you mean like do the action loop first?

then the google sheets create Rows?

The Logic behind this zap is this:

 

From a New Order, It would pull out the Order SKU QTY VENDOR DESCRIPTION into a Sheet. This is because the SKU needs to be replaced by a Corrected SKU Value coming from another Worksheet. Same goes to the Description

I then want to get those rows with the same Order number and make it a single BULK DATA That should be formatted like this:

 

{{SHIPMENT}} == QTY:1

DESC: {{Value of Vendor}}, {{ Value of Description}}

SKU {{Value of SKU}}

 

If there are 5 items in the said order then it should look like this:

QTY:1

DESC: {{Value of Vendor}}, {{ Value of Description}}

SKU {{Value of SKU}}

QTY:1

DESC: {{Value of Vendor}}, {{ Value of Description}}

SKU {{Value of SKU}}

QTY:1

DESC: {{Value of Vendor}}, {{ Value of Description}}

SKU {{Value of SKU}}

QTY:1

DESC: {{Value of Vendor}}, {{ Value of Description}}

SKU {{Value of SKU}}

QTY:1

DESC: {{Value of Vendor}}, {{ Value of Description}}

SKU {{Value of SKU}}

 

 

Now this will be placed in a Document template like this

 

Order Number - Firstname Last Name

Address1

City State

ZIP

PHONE NUMBER

 

Order is for:

{{SHIPMENT}}

 

Notes

 

Shipment is via Freight 

 

Thanks,

 

{{OWNER}}

 


If you can check my other inquiry it is almost the same thing but I have been trying different approaches like Google sheet to Get Rows (This one has limits though of 20 rows and I think I cannot group them based on the Order number it will get all rows from the 2nd to wherever so it would mean that I need to create multiple spreadsheet one for each order.

 

The last attempt I did is to get Airtable to be used. The problem is the same if I am using Sheets. Just one row is being imported to Airtable. If I use Shopify to Airtable it makes a line item like this

 

qty: 1,1,1,1,1 SKU: SKU1, SKU2, SKU3, SKU4 DESC: Description1, Description2, Description3, etc

 

and I cannot get that to do a VLOOKUP that way.

 

I was thinking of doing a Code step to get an array based on the order number and then make it a JSON to be outputted to a Digest but I am not good at coding Javascript/Python.

 

 

I am not sure if this is even possible but we need it to make an automated Order Request.

 

If there is a Detailed Tutorial of how it can be done that is in your articles section or if you can give me a much more detailed answer not a one-liner explanation then it would be a bit easier. 

 

Thank you.

Troy Tessalone
Userlevel 7
Badge +14

@KWF

The GSheets Create Row(s) will only return the 1st of X rows created, so you can NOT use that Zap configuration to feed the line items into the Looping app step, because it will always only return 1 row’s values.

(I’ve tested this myself.)

 

If you add the GSheet Create Row (singular) within the Looping app, then each new row will return the values from the VLOOKUP for the corrected SKU and Description, assuming you’ve configured that correctly in the GSheet.

 

Most Zap configurations are unique per each use case, so it’s unlikely there will be a detailed tutorial available.

 

At this point, perhaps it may be worth hiring a Zapier Expert: https://zapier.com/experts

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
K
Userlevel 2
Badge

@jesse Hi, I hope you can help me out with this situation so above are the screenshots and the issue. I am stuck at the part that after I created an Action - Google Sheets Create Rows (with Line Item Support) it creates multiple rows and I cannot use that in the action Looping from Line Items because it only takes the top row of my previous action so what exactly can I do as an alternative? I need the values from the Rows that were created so I can make it loop in a Digest and make that Digest release it to a Google Docs Template because I need to insert those data in a specific area. One more thing if I have the Action Create Document from a template following a Looping action it will create multiple documents. What I need is to have just one document with all the data gathered from the Digest but I think it’s not possible? or is there a way? 

Danvers
Userlevel 7
Badge +12

Hi @KWF 

It sounds like you have two things that you need solving:

  1. The loop needs to run for every line item
  2. The gmail document should only be created once the last line item has been added to the Digest

Is that right?

 

For the first one, @Troy Tessalone has the answer: use the information from the Shopify trigger in the loop, not the information from the Google Sheets ‘Create Spreadsheet Row(s)’ step. 

For the second problem, a filter step should work here. 

Inside the loop, you’ll need the following steps:

  1. Append Entry and schedule digest
  2. Filter
  3. Release Existing Digest
  4. Create Document from text

Each time the Zap runs it will loop through adding each item to the digest but stop there unless the filter condition is met. The filter condition you want is the field ‘Loop iteration is last’ and set the condition to ‘(Boolean) Is true):

With this filter, the final two steps of the loop will only happen if the loop is on its last iteration, its last piece of information (in this case, line item)

So, on the last iteration of the loop, the release Digest step will give you all of the information that you need for the document and clear the digest, then create a Google Doc from the text. 

 

Does that do what you need it to? Or is there something that I’ve missed? Let me know! 

K
Userlevel 2
Badge

I understand what you are saying but the Google Sheets Create Rows is a very critical step, I need that to pull out the correct SKU and the Simplified product description from a spreadsheet that I have. Is there an alternative that I can use for that?

Troy Tessalone
Userlevel 7
Badge +14

@KWF 

Why can’t you use Create Row within the Looping app VS Create Row(s) before the Looping app?

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Danvers
Userlevel 7
Badge +12

Hi @KWF!

Thanks for your question! I think I’m a little confused. So that I can help you brainstorm, can you help me to understand why you can’t use one of the following ideas:

  1. Have a Create Spreadsheet Row(s) step before the loop and then run the loop using the Shopify data. If you need to know information from the Google Sheet you could add a Find Row step in the loop?
  2. As per Troy’s suggestion: Use Create Spreadsheet Row in the loop

Thanks!

K
Userlevel 2
Badge

Hi @KWF!

Thanks for your question! I think I’m a little confused. So that I can help you brainstorm, can you help me to understand why you can’t use one of the following ideas:

  1. Have a Create Spreadsheet Row(s) step before the loop and then run the loop using the Shopify data. If you need to know information from the Google Sheet you could add a Find Row step in the loop?
  2. As per Troy’s suggestion: Use Create Spreadsheet Row in the loop

Thanks!

Hi @Danvers To answer your question

 

For number 1 = Create Spreadsheet Rows before the loop sequence was my original configuration, in this scenario I can only get the first Row of the items with the same Order Number.

 

For Number 2 = Create Spreadsheet Row After Looping will not do me any good,

Let’s say there are 4 items in the order and it will look like this in the Spread Sheet

 

QTY                            |  VENDOR                                                       | DESCRIPTION                       | SKU

1,1,1,1                        | Vendor1, Vendor 2, Vendor 3, Vendor 4        | Desc1, Desc2, Desc3, Desc4 | Sku1….

 

In this scenario, I cannot get the corrected SKU from the spreadsheet as well as the Simplified Description from the same Spreadsheet.

The loop will work fine but the values will not be complete. I need the VLOOKUP step in my spreadsheet to REPLACE all SKUs and DESCRIPTION from Shopify to Google Sheet.

 

The other thing I am trying to prevent in this step is that if I do the Create Rows in the Looping Action and then proceed with Create Document From Text it will Create multiple Documents instead of one bulk Invoice/Document. The Suggested Action Append Text to Document is not a good idea either since I will not have control where to put the lines of text in the document it will only append it in the end.

 

I think @jesse already has an idea what to do I am just waiting for them to hop in the conversation.

Danvers
Userlevel 7
Badge +12

I think that we might be misunderstanding each other, let me see if I can phrase this differently. 

 

Option 1 - Add Create Spreadsheet Row(s) BEFORE the loop

I know that you started with your Zap having create Spreadsheet Row(s) outside before the loop and it wasn’t working the way that you want to. What I’m suggesting is different from your original Zap.

You said that the Zap wasn’t working because you can only get the first Row of the items with the same Order Number. That sounds like you’re using the information from the Google Sheets step in the loop, is that right? In this suggestion, you need to use the use the SHOPIFY line items inside the loop, which should give you each line item. 

 

Have you tired setting up the Zap like that? With the Create Spreadsheet Row(s) before the Zap and using Shopify information in the Zap?

 

Option 2 - Create Spreadsheet Row in the loop

In your reply you said that if you set up the Zap this way, you would get each of the line items in the same row in Google Sheets. Is this something that you’ve tested in your Zap? Using a loop, the loop should run for each line item in the Shopify data, so it should create a new row in Google Sheets for each item in the order. If you have tested this and you’re seeing all of the information in each row, could you share a screenshot of the way that the Google Sheet step is set up (in the loop, not before the loop). If you share a screenshot, don’t forget to obscure any personal/private information. 

 

Let’s keep going and get you there!

K
Userlevel 2
Badge

Okay, sorry if I am confusing you guys with the question I have.

 

I tested several zap configurations and each has its own issues.

 

If I do a Create Rows Google Sheet after the Shopify Trigger (New Paid Order) and then do a loop it will only take the first row of the said group of rows and put it in the document. 

 

If I do Google Sheet Rows after the loop like you are suggesting it will create multiple Google Docs when I use the Create Document from Template Action.

 

Now, here’s the workflow I was trying to design and make with your app.

 

  1. Get New Paid Orders from Shopify

 

  1. Get a list of item/s from that order

 

  1. The SKU from Shopify needs to be replaced by the SKU I have in the spreadsheet so VLOOKUP is handling that in the spreadsheet itself.

          The Line Items Name / Title needs to be replaced by a description in the spreadsheet as well and                    again being handled by a VLOOKUP function in the spreadsheet.

 

Number 3 is critical for me since this is a transition from a customer’s view to a manufacturer’s view of the order so I cannot omit this step.

 

Once The list will then need to be entered in a Template filling out {{SHIPOUT}}

 

So the Template will look something like this:

 

Hello,

 

{{OrderNo}} - {{FIrstName}} {{Lastname}}

 

{{FIrstName}} {{Lastname}}

{{Address1}}

{{City}} {{State}} {{ZIP}}

{{PhoneNo}}

{{email}}

 

Please ship out the following items”

 

{{SHIPOUT}} « this is from the loop / or any step I can use that gathers the list of items from the Create Rows Action that I created in step 2. 

 

Please send via {{courier}}  and use the card on file.

 

Thank you,

 

XX XX XX

There are still other things I need to put in the template but that’s basically how I wanted it to look like so the Append Text to Document will not be a viable option since it only can put the texts at the last part of  the Document.

 

I guess the only tricky part is how to obtain the data from the Google Sheets to the Google Docs Template.

And I hope you guys can help me simplify it as I maybe just doing it wrong. I understand the suggestions that you are giving but it is not exactly how I am planning the zap to work.

 

Danvers
Userlevel 7
Badge +12

Hi @KWF 

If you use a filter in your Zap, you can stop it from creating a document every time by using a Filter step. You can set up the filter step so that it only continues if the Zap is on the last loop, which means that it will only create the document once. 

So, to make sure I’m clear, you need the SKU that will is added to the Google Sheet via the Lookup code to be added to the Google Document, is that right?

If so then these Zap steps should work:

  1. Shopify
  2. Create Spreadsheet Rows
    1. Loop
    2. Find Spreadsheet Row (use the data from shopify to find the relevant row in Google Sheets that was just created)
    3. Digest by Zapier - Append Entry and schedule digest
    4. Filter - only continue if this is the last loop
    5. Release Existing Digest
    6. Create Document from text - use the information from the Digest

 

I think that covers everything you’re looking for:

  • Having the create Row(s) sheet ahead of the loop means that all of the data will be added and the SKU will be added before the loop starts
  • Find Spreadsheet Row will run for each line item in the order, so you will get all of lines in the order
  • Adding the filter step means that the Google Document is only created once, and the Digest step means that the information from ALL of line items will be added to the Google Document.
K
Userlevel 2
Badge

@Danvers 

You said that I should do this

  1. Shopify
  2. Create Spreadsheet Rows
    1. Loop
    2. Find Spreadsheet Row (use the data from shopify to find the relevant row in Google Sheets that was just created)
    3. Digest by Zapier - Append Entry and schedule digest
    4. Filter - only continue if this is the last loop
    5. Release Existing Digest
    6. Create Document from text - use the information from the Digest

In Loop step which Value should I use? is it the Shopify or the Google Sheets? 

Also I tried doing something similar but it would include the Test Data from the Trigger, example I have Order 5267 as the Test Data and a New data comes in it will process only one Row still and add the Test data so it will have 

 

Order 5267

Qty 1

Desc: Vendor1 Description1

SKU: SKU1

Order 5270

Qty 1 

Desc: Vendor1 Description1

SKU: SKU1

 

something like this.  

Terms & Conditions