Google-sheets – Randomly mark the column based on the percentage of the total data from each operator

google sheetsgoogle-apps-script

I have data as in this link.
The data will be checked by several operators. Each operator must check data as much as 50% of the total data of each operator randomly.
If the number of data from the operator is 5 then the data must be checked all.
And the data checked is marked with the remark "yes".

I have written a script like this:

function userActionFillData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() === 'Sheet1') {
    SpreadsheetApp.getActive().toast('Start ...');
    fillData_(sheet);
  }
}
function fillData_(sheet) {
  const percent = sheet.getRange('I2').getValue() || 0.1;
  const range = sheet.getRange('B3:C');
  const values = range.getValues();
  const operator = sheet.getRange('G2').getValue();

  const totalData = values
    .map((_, i) => i)
    .filter((_, i) => values[i][0] !== '' && values[i][0] === operator);

  const sortIndexes = totalData
    .sort(() => 0.5 - Math.random())
    .slice(0, parseInt(totalData.length * percent));

  const newValues = values.map((_, i) => [
    sortIndexes.indexOf(i) !== -1 ? 'yes' : '',
  ]);

  sheet.getRange('C3:C').setValues(newValues);
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My tools')
    .addItem('Fill data', 'userActionFillData')
    .addToUi();
}

But whenever I want a random remark for another operator, the existing remark belonging to another operator is deleted, I want the remark belonging to the previous operator not to be deleted.
enter image description here

Best Answer

I've changed this code and it works

function fillData_(sheet) {
  
  sheet.getRange('C3:C').clearContent()
  
  const percent = sheet.getRange('I2').getValue() || 0.1;
  const range = sheet.getRange('B3:C');
  const operator = sheet.getRange('F6:F8').getValues();

  for(var j =0 ; j<operator.length;j++){
    
    const values = range.getValues();
    
    const totalData = values.map((_, i) => i)
      .filter((_, i) => values[i][0] !== '' && values[i][0] === operator[j][0]);
    
    const sortIndexes = totalData.length <= 5 ? totalData : totalData
      .sort(() => 0.5 - Math.random())
      .slice(0, parseInt(totalData.length * percent));
    
    let newValues = values.map((_, i) => [
      sortIndexes.indexOf(i) !== -1 ? 'yes' : values[i][1],
    ]);
    
    sheet.getRange('C3:C').setValues(newValues);
  
  }
}