Loop script and specify script values to cells (Google Sheets Scripts)

Question

I'm trying to run a script on Google Sheets that will allow me to copy a number of users a set amount of times and add them as a list.

A coworker made this code for me:

function BulkUpload() {
 var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(0, 0, 8, 1).activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 8);
  spreadsheet.getCurrentCell().offset(8, 0).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().offset(9, 0).activate();

Since I needed this for 36 users, what I did is that I copied the script and pasted it 36 times. This worked great and I ended up with a list of my 36 users copied 9 times each, but there's two problems:

This script only works for that specific scenario where I needed every user to appear 9 times on the list.
Having to manually copy/paste the script to the number of users I have is not functional at all and I'm aware that was a terrible way for me to solve the problem.

In order to fix these 2 issues, I would need:

A way to get the script to recognize a number on a cell, so if I input "6" in the "repetitions needed" cell, it would tell the script to only add 5 rows, and copy so I get the 6 repetitions I needed; this way, I would be able to specify and change an amount without having to touch the script.
In a similar fashion, a way to be able to loop the script N number of times, that I could also specify on a cell.

As an example, let's say that we have 7 users and I wanna copy each 6 times. I'd like to be able to go to my Google Sheets, have this little table and add the numbers needed, (ie, 5 in cell B1 and 10 in cell B2 according to this table:

Column A	Column B
Repetitions needed	6
Users	7

Then, after this is specified, I would paste the list and run the script, and this should take me from:

Alex
John
Sam
Bob
Tony
Laura
Victor

to this: GoogleSheets Screenshot

For transparency, the numbers I have to do are quite large (hundreds of users and dozens of repetitions) so that's why I'd need the script, I just made it simple here so I could add an example without adding too much space.

Do note that both my coworker and I are complete beginners when it comes to coding so there might be much easier ways to accomplish this; I'm open to any other solution that achieves the same desired result.

Thank you!

NightEye · Accepted Answer · 2021-05-21 16:30:26Z

1

Your script seems manual and I think this approach would be better. But in this one, you would need to list the names on the sheet itself, whether in a separate cell or as a parameter.

Code:

function BulkUpload(names, repetition) {
  var output = Array(names.length * repetition);

  names.split(",").forEach(function (name, index) {
    output.fill([name.trim()], repetition * index, repetition * (index + 1));
  });
  
  return output;
}

Behavior:

function accepts 2 parameters, the names and how much to repeat. It can be a cell containing a list of names, or passing the whole string containing the names (must be separated by commas).
it creates an array that have ({number of names} * {repetitions}) elements
every name we get (separated by comma), we add them with respect with their repetitions
return the array that contains the repeated names

A4 vs B4:

=BulkUpload(B2, B1)
=BulkUpload("Alex, John, Sam, Bob, Tony, Laura, Victor", B1)

Sample Output:

answered May 21, 2021 at 16:30

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alvaro Over a year ago

I appreciate your response; this definitely did it! This is a much nicer approach! I was able to fix the need of names by adding the formula =TEXTJOIN(",",TRUE,A4:A), so I'll just have to specify the number of repetitions needed in B2, then dump my name list on A4, which reformats it as a comma separated list in B2, and then the custom "=BulkUpload()" formula kicks in and makes everything work. Thanks so much for your help NaziA, you gave me a fresh, more simplistic approach to solve my issue!

NightEye Over a year ago

No problem @Alvaro, it seems I didn't have to explain anything after providing you a function and you were able to adjust your data accordingly. Good luck.

Alvaro Over a year ago

I did, and it worked wonderfully. Thanks again, NaziA!

Collectives™ on Stack Overflow

Loop script and specify script values to cells (Google Sheets Scripts)

1 Answer 1

Code:

Behavior:

A4 vs B4:

Sample Output:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Code:

Behavior:

A4 vs B4:

Sample Output:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related