Hi all, I am working on a dataset on Google Sheet and I need to get the random data (specified number) by the col 3 names to audit I am using google app script for the same but couldn't able to get the data. Here's the code I tried but I don't want the % of data I want the equally distributed random data for each employee in col 3.

 function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var trn = ss.getSheetByName('Data');
  var originalData = trn.getRange(2, 1, trn.getLastRow() - 1, 3).getValues();

  var ReviewerEmail = data;

  var data = originalData.filter(function(item) {
    return item[1] === 'rob' || item[1] === 'john' || item[1] === 'toger';
  });

  //Logger.log(data);

  var targetsheet = ss.insertSheet(Reviewer);
  targetsheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
function getsampledata() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A:C');
  var values = range.getValues();
  var headers = values.shift();
  var nameColumn = 1;
  var uniqueNames = values
    .map((row) => row[nameColumn])
    .filter((item, i, ar) => ar.indexOf(item) === i)
    .filter(String);
  var data = [headers];
  uniqueNames.forEach(function(name) {
    var nameEntries = values.filter((row) => row[nameColumn] == name);
    var entries = nameEntries.length;
    var tenth = Math.round((entries / 100) * 27.35); //Sampling Percentage
    for (i = 0; i < tenth; i  ) {
      var random = Math.floor(Math.random() * nameEntries.length);
      data.push(nameEntries[random]);
      nameEntries.splice(random, 1);
    }
  });
  return data;
}

CodePudding user response：

If you want a specific number of sample, then use a parameter to indicate how many you want for each.

Whole working script:

function getsampledata(sample) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A:C');
  var values = range.getValues();
  var headers = values.shift();
  var nameColumn = 2;
  var uniqueNames = values
    .map((row) => row[nameColumn])
    .filter((item, i, ar) => ar.indexOf(item) === i)
    .filter(String);
  var data = [headers];
  uniqueNames.forEach(function(name) {
    var nameEntries = values.filter((row) => row[nameColumn] == name);
    for (i = 0; i < sample; i  ) {
      var random = Math.floor(Math.random() * nameEntries.length);
      data.push(nameEntries[random]);
      nameEntries.splice(random, 1);
    }
  });
  return data;
}