-2

I have a list of roughly 3,000 First Names, Last Names, and other items on a sheet in Google Sheets. The name of that sheet is "Database." (link below).

I want to create a script that takes 100 random entries from each column in "Database" and populates cells on another sheet called, "Results."

So, basically, Col 1 on "Results" would take 100 random results from Col 1 in "Database." Col 2 in "Results" would take 100 random results from Col 2 in "Database." So on and so forth. Ideally, this can be regenerated by assigning a script to a button on the sheet as opposed to using a formula that will create new results every time an action is performed.

Ultimately, I'll end up having about 10 columns or so, but I'm using test data for now just to get the structure built. Here's a link to the test document I'm using...

https://docs.google.com/spreadsheets/d/1qOgZxLtl8ruQHUDMkIjCNdXV3Jy5eOv0dC5DkLEpB8w/edit?usp=sharing

Thanks so much for the help!

bboston
  • 1
  • 1
  • can you provide code that you have written in an attempt to solve your problem? – beniutek Dec 30 '20 at 21:02
  • Hi there, I'm not a developer, so I've been trying to look around for people with similar problems. This post solved a similar issue, but it was just filling in one cell from a dataset. I want to fill in 100 rows. I tried tinkering with the script, but it's just not something I can figure out on my own: https://stackoverflow.com/questions/57945260/script-for-selecting-random-data-from-a-list-on-button-press – bboston Dec 30 '20 at 22:02

1 Answers1

1

You can try this:

Pre-requisite: LodashGS Library should be added in your project

//Load LodashGS:
var _ = LodashGS.load();

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var resultSheet = ss.getSheetByName("Results");
  var dBSheet = ss.getSheetByName("Database");

  //Loop all columns in Database Sheet
  for (var col = 1; col <= dBSheet.getLastColumn() ; col++){

    //Generate an array of unique numbers from 2 to the last row number of the Database Sheet
    const uniqRandomNumbers  = _.sampleSize(_.range(2,dBSheet.getLastRow() + 1), 100);
    Logger.log(uniqRandomNumbers);

    var rowValues = [];
    //Get the cell value based on the uniqRandomNumbers as row index, create a 2-d array of row values
    uniqRandomNumbers.forEach(row=>{rowValues.push([dBSheet.getRange(row,col).getValue()]);});

    //Write the row values into the Results Sheet with 100 row entries
    resultSheet.getRange(2,col,100).setValues(rowValues);
    Logger.log(rowValues);
  }

}

What it does? (This is done for each column)

  1. Generate an array of unique numbers from the range provided in _.range(). We will use this unique array numbers as row indexes.

  2. Get the cell value based on the unique array numbers generated in Step1, and create a 2-d array of row values.

  3. Write the 2-d array of row values into the Results Sheet using .setValues()

Sample Output:

enter image description here

Additional References:

Creating array of length n with random numbers in JavaScript

Ron M
  • 5,791
  • 1
  • 4
  • 16
  • Thank you so much! That worked perfectly. Also, I really appreciate you leaving comments in the code and explaining how everything works. That's incredibly helpful. Thanks again for your help. All the best! – bboston Dec 31 '20 at 01:04
  • No problem, glad that i could help. Please have time reading this reference as well, thank you. https://stackoverflow.com/help/someone-answers – Ron M Dec 31 '20 at 14:58