Google Apps Script审计样本Team A/B有序分配逻辑调整需求

问题分析 #

当前代码在抽取随机样本后，通过以下两行对样本进行随机打乱，导致Team A和B的分配结果是随机的：

professionalAuditSamples.sort(() => Math.random() - 0.5);
personalAuditSamples.sort(() => Math.random() - 0.5);

要实现抽取后的样本按原始抽取顺序拆分，前半部分分配给Team A，后半部分给Team B，需要移除随机打乱逻辑，并根据需求调整分配逻辑。

修改方案1：按类型分别保留抽取顺序，各分前半/后半给团队 #

这种方案保持原代码的分类型拆分逻辑，仅移除随机打乱步骤，让每个类型的抽取样本直接按顺序拆分：

修改后的完整代码 #

function auditCases() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Master Sheet");
 var auditSheet = ss.getSheetByName("Audit sheet");

 // Clear previous data in the audit sheet
 auditSheet.clear();

 var data = sheet.getDataRange().getValues();
 var headers = data[0];

 // Get the columns indices based on headers
 var typeIndex = headers.indexOf("Type");
 var locationIndex = headers.indexOf("Location");
 var statusIndex = headers.indexOf("Status");
 var monthIndex = headers.indexOf("Month Number");
 var yearIndex = headers.indexOf("Year");

 // Prompt user for input
 var ui = SpreadsheetApp.getUi();
 var monthResponse = ui.prompt("Enter Month Number (1-12)", ui.ButtonSet.OK_CANCEL);
 if (monthResponse.getSelectedButton() !== ui.Button.OK) {
   return; // Exit if cancel or close is pressed
 }
 var monthNumber = parseInt(monthResponse.getResponseText().trim(), 10);

 // Validate month number
 if (isNaN(monthNumber) || monthNumber < 1 || monthNumber > 12) {
   ui.alert("Invalid month number. Please enter a number between 1 and 12.");
   return;
 }

 var yearResponse = ui.prompt("Enter Year (e.g., 2024)", ui.ButtonSet.OK_CANCEL);
 if (yearResponse.getSelectedButton() !== ui.Button.OK) {
   return; // Exit if cancel or close is pressed
 }
 var year = yearResponse.getResponseText().trim();

 // Filter data based on month and year
 var filteredData = data.filter(function(row, index) {
   if (index === 0) return false; // Skip header row
   return row[monthIndex] == monthNumber && row[yearIndex] == year;
 });

 // Log filtered data for debugging
 Logger.log(filteredData);

 if (filteredData.length === 0) {
   ui.alert('No data found for the specified month and year.');
   return;
 }

 // Separate professional and personal cases
 var professionalCases = filteredData.filter(function(row) {
   return row[typeIndex] === "Professional";
 });

 var personalCases = filteredData.filter(function(row) {
   return row[typeIndex] === "Personal";
 });

 // Calculate number of cases to select
 var totalCases = filteredData.length;
 var personalCasesToAudit = Math.min(Math.ceil(totalCases * 0.1), 30); // 10% of personal cases or max 30
 var professionalCasesToAudit = Math.min(Math.ceil(totalCases * 0.05), 10); // 5% of professional cases or max 10

 // Function to get random samples from an array
 function getRandomSamples(arr, num) {
   var result = [];
   var len = arr.length;
   var taken = new Array(len);
   var minNum = Math.min(num, len); // Ensure we don't take more than available

   if (minNum > len) {
     throw new RangeError("getRandomSamples: more elements taken than available");
   }

   while (result.length < minNum) {
     var x = Math.floor(Math.random() * len);
     if (!taken[x]) {
       result.push(arr[x]);
       taken[x] = true;
     }
   }

   return result;
 }

 // Get random samples for professional cases
 var professionalAuditSamples = getRandomSamples(professionalCases, professionalCasesToAudit);

 // Get random samples for personal cases
var personalAuditSamples = getRandomSamples(personalCases, personalCasesToAudit);

 // 移除随机打乱样本的代码
 // professionalAuditSamples.sort(() => Math.random() - 0.5);
 // personalAuditSamples.sort(() => Math.random() - 0.5);

 // Distribute samples equally between Team A and Team B
 var teamA = [];
 var teamB = [];

 var halfProfIndex = Math.ceil(professionalAuditSamples.length / 2);
 var halfPersIndex = Math.ceil(personalAuditSamples.length / 2);

 teamA = teamA.concat(professionalAuditSamples.slice(0, halfProfIndex));
 teamA = teamA.concat(personalAuditSamples.slice(0, halfPersIndex));

 teamB = teamB.concat(professionalAuditSamples.slice(halfProfIndex));
 teamB = teamB.concat(personalAuditSamples.slice(halfPersIndex));

 // Add header to the audit sheet
 auditSheet.appendRow(headers);

 // Function to append rows to audit sheet
 function appendRowsToAuditSheet(rows) {
   rows.forEach(function(row) {
     auditSheet.appendRow(row);
   });
 }

 // Add samples to teams
 appendRowsToAuditSheet(teamA);
 appendRowsToAuditSheet(teamB);

 ui.alert('Audit samples generated and distributed to Team A and Team B.');
}

关键修改点 #

• 注释/删除了两行随机打乱样本的代码，保留getRandomSamples抽取的原始顺序
• 保持原有的分类型拆分逻辑：每个类型的样本前半部分给Team A，后半部分给Team B

修改方案2：合并所有抽取样本后，整体拆分前半/后半给团队 #

如果需要将所有抽取的样本（不分类型）合并后，前半部分给Team A，后半部分给Team B，可以调整分配逻辑如下：

修改后的分配部分代码 #

// Get random samples for professional cases
var professionalAuditSamples = getRandomSamples(professionalCases, professionalCasesToAudit);

// Get random samples for personal cases
var personalAuditSamples = getRandomSamples(personalCases, personalCasesToAudit);

// 合并所有抽取的样本（移除打乱步骤）
var allAuditSamples = professionalAuditSamples.concat(personalAuditSamples);

// Distribute samples equally between Team A and Team B
var teamA = [];
var teamB = [];

var halfTotalIndex = Math.ceil(allAuditSamples.length / 2);
teamA = allAuditSamples.slice(0, halfTotalIndex);
teamB = allAuditSamples.slice(halfTotalIndex);

// 后续代码（添加表头、写入工作表）保持不变...

关键修改点 #

• 移除随机打乱代码
• 将professional和personal样本合并为一个数组
• 按总样本数的一半拆分，前半给Team A，后半给Team B

内容的提问来源于stack exchange，提问作者Neethu Singuluri