循环分配

该脚本提供以下任务：

如果任务标题重复，则将该任务分配给原始受让人。
如果任务标题是新的，则它将该任务分配给任务最少的受让人。
如果标题是新的并且所有受让人都有相同数量的任务，那么它会随机选择Math.floor(Math.random() * assigneeArray.length);

这是代码：

代码.gs：

function onOpen() {
  SpreadsheetApp.getUi().createMenu('My Tools')
  .addItem('Add Task', 'addTask')
  .addItem('Add Assignee', 'addAssignee')
  .addSubMenu(SpreadsheetApp.getUi().createMenu('Utility')
  .addItem('Select Columns Skip Header', 'jjeSUS1.selectColumnsSkipHeader')
  .addItem('Create Named Range', 'jjeSUS1.createNamedRange'))
  .addToUi();
}
function addAssignee() {
  showFormDialog({filename:'addAssignee',title:'Add Assignee'});
}

function postAssigneeData() {
}

function addTask() {
  showFormDialog({filename:'addTask',title:'Add Task'}); 
}

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function showFormDialog(dObj){
  var form=HtmlService.createHtmlOutputFromFile(dObj.filename).getContent();
  var ui=HtmlService.createTemplateFromFile('html').evaluate();
  ui.append(form);
  ui.append("</body></html>");
  SpreadsheetApp.getUi().showModelessDialog(ui, dObj.title);
}

function saveData(dObj) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(dObj.sheetName);
  var hrg=sh.getRange(1,1,1,sh.getLastColumn());
  var hA=hrg.getValues()[0];
  var vA=[];
  for(var i=0;i<hA.length;i++) {
    vA.push((dObj[hA[i]])?dObj[hA[i]]:'');//Column headers must agree with form names
  }
  dObj['row']=sh.getLastRow()+1;
  var cA=Object.keys(dObj).filter(function(el){return (el!=='row' && el !='sheetName')});
  for(var i=0;i<cA.length;i++) {
    saveValue(dObj.row,cA[i],dObj[cA[i]],dObj.sheetName,1);
  }
  return dObj;
}

function makeAssignment(aObj) {
  Logger.log(aObj);
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Assignments')
  var title=aObj.Title;
  var taskObj=getTasks();
  //Check to see if someone has already done this once
  if(taskObj.taskA.indexOf(title)>-1) {
    saveValue(aObj.row,'Assignment',taskObj[aObj.Title],'Assignments',1);
    saveValue(aObj.row,'Date',Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "E MM d, yyyy HH:mm"),'Assignments',1);
    postTaskData(aObj.Title,taskObj[aObj.Title]);
  }else{
    var assA=getAssigneeTasks();
    if(assA[0].allCountsEqual=='false') {
      //they don't have the same number of tasks so take the lowest one
      saveValue(aObj.row,'Assignment',assA[0].email,'Assignments',1);
      saveValue(aObj.row,'Date',Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "E MM d, yyyy HH:mm"),'Assignments',1);   
      postTaskData(aObj.Title,assA[0].email);
    }else{
      //they all have the same number of task so take a random one
      var n=Math.floor(Math.random()*assA.length);
      saveValue(aObj.row,'Assignment',assA[n].email,'Assignments',1);
      saveValue(aObj.row,'Date',Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "E MM d, yyyy HH:mm"),'Assignments',1);
      postTaskData(aObj.Title,assA[n].email);
    }
  }
  return true;
}

function getTasks() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Data');
  var taskObj={'taskA':[]};
  var h=jjeSUS1.getColumnHeight(1, sh, ss);
  if(h>2) {
    var rg=sh.getRange(3,1,h-2,2);  
    var vA=rg.getValues();
    for(var i=0;i<vA.length;i++) {
      taskObj[vA[i][0]]=vA[i][1];
      if(taskObj.taskA.indexOf(vA[i][0])==-1) {
        taskObj.taskA.push(vA[i][0]);//Unique Task Array
      }    
    }
  }
  return taskObj
}

function postTaskData(key,value) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Data');
  sh.appendRow([key,value]);

}

function getAssigneeTasks() {
  var taskObj=getTasks();
  var aeqA=getAssignees().map(function(el){return {email:el,count:0,allCountsEqual:'false'}});
  var keysA=Object.keys(taskObj).filter(function(el){return (el != 'taskA')});
  for(var i=0;i<aeqA.length;i++) {
    for(var j=0;j<keysA.length;j++) {
      if(taskObj[keysA[j]]==aeqA[i].email){
        aeqA[i].count+=1;
      }
    }
  }
  aeqA.sort(function(a,b){return a.count - b.count;});
  var isTrue=true;
  var maxCount=aeqA[aeqA.length-1].count;
  aeqA.forEach(function(el){if(el.count!=maxCount){isTrue=false;}});
  if(isTrue) {
    aeqA.map(function(el){return el.allCountsEqual='true';});
  }
  return aeqA;
}

function saveValue(row,columnName,value,sheetName,headerRow) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(sheetName);
  var hA=sh.getRange(headerRow,1,1,sh.getLastColumn()).getValues()[0];
  sh.getRange(row,hA.indexOf(columnName)+1).setValue(value);
}

function getAssignees() {
  var ss=SpreadsheetApp.getActive();  
  var sh=ss.getSheetByName('Assignees');
  var hrg=sh.getRange(1,1,1,sh.getLastColumn());
  var hA=hrg.getValues()[0];
  return sh.getRange(2, hA.indexOf('Email')+1, sh.getLastRow()-1,1).getValues().map(function(r){return r[0]});
}

function closeDialog() {
  var userInterface=HtmlService.createHtmlOutputFromFile('dummy');
  SpreadsheetApp.getUi().showModelessDialog(userInterface,'Closing');
}

css.html：

<style>
body {background-color:#ffffff;}
input[type="button"],input[type="text"]{margin:0 0 2px 0;}
</style>

资源.html：

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

html.html：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('resources') ?>
    <?!= include('css') ?>
    <?!= include('script') ?>
  </head>
  <body>

脚本.html：

<script>    
     $(function(){  
       document.getElementById('txt1').focus();

      });
    function getInputObject(obj) {
      var rObj={};
      var length=Object.keys(obj).length;
      for(var i=0;i<length;i++){
        console.log('Name: %s Type: %s',obj[i].name,obj[i].type);
        if(obj[i].type=="text"){
          rObj[obj[i].name]=obj[i].value;
        }
        if(obj[i].type=="select-one"){
          rObj[obj[i].name]=obj[i].options[obj[i].selectedIndex].value;
        }
        if(obj[i].type="hidden"){
          if(obj[i].name) {
            rObj[obj[i].name]=obj[i].value;
          }
        }
      }
      return rObj;
    }

    function processForm(obj){
      var fObj=getInputObject(obj);
      console.log(JSON.stringify(fObj));
      google.script.run
      .withSuccessHandler(function(rObj){
        document.getElementById("btn").disabled=true;
        $('#msg').html('<br /><h1>Data Saved.</h1>');
        if(rObj.sheetName=='Assignments') {
          google.script.run
          .withSuccessHandler(function(){
            $('#msg').html('<br /><h1>Assignments Complete.</h1>');
            google.script.host.close();
          })
          .makeAssignment(rObj);
          }else{
            google.script.host.close();
          }
        })
        .saveData(fObj);
      }
     console.log('My Code');
</script>

addAssignee.html：

<div id="heading"><h1>Add Assignee</h1></div>
<div id="content">
<h3>Please Enter First Name, Last Name, Phone and Email into the text areas adjacent to the text box labels.</h3>
    <form id="assigneeForm" onsubmit="event.preventDefault();processForm(this);" >
      <br /><input type="text" id="txt1" name="First" /> First
      <br /><input type="text" id="txt2" name="Last" /> Last
      <br /><input type="text" id="txt3" name="Phone" /> Phone
      <br /><input type="text" id="txt3" name="Email" /> Email
      <br /><input type="hidden" value="Assignees" name="sheetName" />
      <br /><input id="btn" type="submit" value="Submit" />
      <br />
    </form>
</div>
<div id="msg"></div>
<div id="cntl"><input type="button" id="btn" value="Close" onClick="google.script.host.close();" ></div>

添加任务.html：

<div id="heading"><h1>Add Task</h1></div>
<div id="content">
<h3>Please Enter Title and Description into the text areas adjacent to the text box labels.</h3>
    <form id="assigneeForm" onsubmit="event.preventDefault();processForm(this);" >
      <br /><input type="text" id="txt1" name="Title" /> Title
      <br /><input type="text" id="txt2" name="Description" /> Description
      <br /><input type="hidden" value="Assignments" name="sheetName" />
      <br /><input id="btn" type="submit" value="Submit" />
      <br />
    </form>
</div>
<div id="msg"></div>
<div id="cntl"><input type="button" id="btn" value="Close" onClick="google.script.host.close();" ></div>

我的电子表格的三页如下所示：（名称在图像上）

google-apps-script - 任务的循环分配 - AppScript 解决方案？

问题描述

解决方案

循环分配

推荐阅读