0

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

0

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);
  
  }
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.