Best answer

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

  • 23 November 2022
  • 6 replies
  • 43 views

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

6 replies

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/

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. 
 

 

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? 

 

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. 

Userlevel 1

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

 

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}

 

 

Reply