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","Escudem@mac.com","","","0","FALSE","100"]
        numberOfRows = numberOfRows-1

        wr.writerow(row)
  • What have you tried so far? And Post your sheet or a dummy copy/sample for better clarity and specificity. – TheMaster Sep 26 '17 at 5:07
  • 1
    sample sheet provided – abbood Sep 26 '17 at 5:11
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

  • 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 Sep 26 '17 at 5:34
  • @abbood, If you want to repeat 10 times, Change ROW(A1:A4) to ROW (A1:A10) That's all. – TheMaster Sep 26 '17 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 Sep 26 '17 at 6:05
  • @abbood Just copy my formula and paste it in A3. You'll get it – TheMaster Sep 26 '17 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 Sep 26 '17 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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