About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials Zapier University 101

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Popular Apps Q&A Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

Interest Areas User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier University Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

How do I duplicate a row in Google sheet based on cell

  • 23 November 2022
  • 6 replies
  • 223 views
E
Userlevel 1

How can I create a duplicate row based on the number of quantities? On the attached image you’ll see that a row is created with the quantities 2. What I want to happen is create single rows showing quantity 1 depending on the quantity. In the case of this image, I want to create 2 separate rows instead of only one row with quantity 2. If quantity is 3, then create 3 rows showing quantity 1. If quantity is 4, then create 4 rows showing quantity 1. Ect. Disregard first row, that was triggered to show how I would like it to show.  Is this possible?

 

icon

Best answer by GetUWired 23 November 2022, 19:14

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

6 replies

subhash
Userlevel 2
Badge +1

You can achieve this directly in gsheet

Sample data set

 

Data set

Formulae : =ArrayFormula(vlookup(transpose(split(query(rept(row(A2:A)&" ",F2:F),,9^9)," ")),{row(A2:A),A2:E},{2,3,4,5,6},0))

 

Explanation : https://infoinspired.com/google-docs/spreadsheet/insert-duplicate-rows-in-google-sheets/

GetUWired
Userlevel 7
Badge +12

Hi @Eduardo13 

A code block would be the most efficient way to do this in Zapier. You’ll need to map all of your line item data into the codes input and then use a split() function to duplicate lines n times based on their qtys. 
 

 

E
Userlevel 1

Hi thank you @GetUWired. I have tried doing what you said, this is how it came out. I only have 3 line item data points but, it still isn’t duplicating the lines. Is there something I’m missing? 

 

E
Userlevel 1

@subhash Thank you also so much for your response. The goal I have in mind is to do this in Zapier since I will be having a lot of data automatically pushing into the google sheet and working with formulas inside the gsheet will make it less autonomous but, I really appreciate your response and this other approach. 

E
Userlevel 1

@GetUWired I also tried it with the document you provided but, I’m getting a error. Is there something I’m missing? 

 

GetUWired
Userlevel 7
Badge +12

@Eduardo13
That document was intended just to get you started. 

Below is the code i would use to split it up and duplicate n number of times. You should be able to then map these outputs into the Create Rows action

let qtys = inputData.qty.split(",");
let tags = inputData.tag.split(",");
let variants = inputData.variant.split(",");

let outputLines = [];

qtys.forEach((qty,index) => {
  for (var i=0;i<qty;i++) {
    outputLines.push({
      'Quantity': 1,
      'Tag Name': tags[index],
      'Variant Name': variants[index]
    })
  }
})

return {outputLines}

 

 

Terms & Conditions