1

I'm stress testing an application that relies on importing spreadsheets. I can populate those spreadsheets with dummy data, but sometimes I need thousands upon thousands of rows (the only thing that needs to change from one row to another is the email, where it can auto increment like so: user1, user2 etc).

I don't want to copy/paste 10,000 rows, I'd rather I have an arrayformula or something where I simply state: given the contents of row1, repeat X times and concatenate the user field by a number (I know how to do the last part, it's the publication of the rows number that I find difficult).

Sample sheet Sample sheet

Script workaround

I wrote this quick Python script to do what I'm trying to do here using a spreadsheet formula:

#!/usr/bin/env python
import csv

with open("orders.csv", 'w') as myfile:
    header =["Site","Email","Password","Size","NotificationEmail","NotificationText","PostiveKW","NegativeKW","StyleNumber","EarlyLinkMonitorKeywords","NewPageMonitorKeywords","FirstNameBilling","LastNameBilling","address1Billing","address2Billing","cityBilling","stateBilling","zipCodeBilling","countryBilling","phoneBilling","houseNbBilling","FirstNameShipping","LastNameShipping","address1Shipping","address2Shipping","cityShipping","stateShipping","zipCodeShipping","countryShipping","phoneShipping","houseNbShipping","friendlyName","NameOnCard","DOB","cardType","CardNumber","CardExpirationMonth","CardExpirationYear","CardSecurityCode","billingEmail","paypalEmail","paypalPassword","CheckoutDelaySeconds","CheckoutOncePerWebsite","OrderThreads"]

    wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
    wr.writerow(header)

    numberOfRows = 10
    while numberOfRows > 0:
        row=["StressTest","guest.805642","guest","3","","","xxx","","","http://www.footaction.com/product/model:95404/sku:23419012/jordan-6-rings-boys-grade-school/black/white/","","Mark","Escude","105 Putney bridge lane","","Simpsonville","SC","29681","US","9018345575","","Mark","Escude","105 Putney bridge lane","","Simpsonville","SC","29681","US","9018345575","","6676","Mark Escude","0/0/0","AMEX","3.71E+14","1","2020","562","[email protected]","","","0","FALSE","100"]
        numberOfRows = numberOfRows-1

        wr.writerow(row)
Improve this question
1
  • 1
    sample sheet provided
    – abbood
    Commented Sep 26, 2017 at 5:11

2 Answers 2

Reset to default
1

Try:

A3:

=ARRAYFORMULA(SPLIT(QUERY(ROW(A1:A4),"Select '"& JOIN("👮",IF(ISBLANK(A2:AS2),"-",A2:AS2))&"' label '"& JOIN("👮",IF(ISBLANK(A2:AS2),"-",A2:AS2))&"' '' ",0),"👮",1))

We're inputting a blank array ROW (A1:A4) of size 4 rows. And we're using QUERY literals to repeat B2:AS2 4 times.

EDIT:

Formula Simplification #3

Improve this answer
5
  • this is not clear.. so what is it that i'm querying from? suppose the only row of Data i have is in Sheet1!A1:Y1
    – abbood
    Commented Sep 26, 2017 at 5:34
  • @abbood, If you want to repeat 10 times, Change ROW(A1:A4) to ROW (A1:A10) That's all.
    – TheMaster
    Commented Sep 26, 2017 at 6:00
  • again i'm not sure what i'm copying from.. perhaps if you make a copy of the above sample sheet and use your script above to demonstrate, that would eliminate all the guesswork
    – abbood
    Commented Sep 26, 2017 at 6:05
  • @abbood Just copy my formula and paste it in A3. You'll get it
    – TheMaster
    Commented Sep 26, 2017 at 6:22
  • 1
    @pnuts No. That's the modification op has to make/ had already made. " the only thing that needs to change from one row to another is the email, where it can auto increment like so: user1, user2 etc)..... concatenate the user field by a number (I know how to do the last part, it's the publication of the rows number that i find difficult). " So, He would need to skip the email column and enter his formula( that he already made) in there, while filling the rest with my formula.
    – TheMaster
    Commented Sep 26, 2017 at 7:38
1

A much much simpler answer:

A3:

=ARRAYFORMULA(IF(ROW(A1:A4)=2:2,"",2:2))

2:2 will be the array to copy.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.