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

1
votes

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:

  1. This script only works for that specific scenario where I needed every user to appear 9 times on the list.
  2. 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:

  1. 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.
  2. 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!

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

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:

output