google-apps-scriptgoogle-sheetsautomationpseudocoderound-robin

Round Robin Lead Assignments


I am trying to assign leads to Account executives by looping through the list of AE's and moving an "X" to the AE who should be assigned next. The way I determine if the lead should be assigned someone is if there is a blank next to the company name.

Essentially the logic should go, find blank, find AE name which is next to the X, use that name to fill in the blank, find next blank, and so on until there are no more blanks. I have written out pseudo but I am not familiar with Google App Scripts at all. Can someone help my figure out what code will work for the pseudo?

***
var STRINGX = 'X';
function main() {
  Logger.log(getNextPerson())
  var person = getNextPerson();
  var leadRow = findNextOpenLead();
  assignPersonToNextLead(person, leadRow);
  moveXDown();
}
function getNextPerson() {
  var sheet = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var startRow = 2;
  var salesReps = sheet[1].splice(0, 1)
}
function moveXDown() {
  // find column with x and save it as a variable
  // delete x from that column
  // add 1 to  column we found 
  // put it in ^ that column
}
function assignPersonToNextLead(person, leadRow) {
  // find row next to lead 
  // put person in the b column and row of lead
}
function findNextOpenLead() {
  // go through column b until you find an open cell
  // use that row in column d to find the lead 
}
***

The A,B,C's are in place of the real AE's names

List of Account Executives names

Blanks next to Company names


Solution

  • Making Round Robin Assignments to Leads List

    function roundRobinLeads() {
      const ss = SpreadsheetApp.getActive();
      const lsh = ss.getSheetByName('Round Robin Leads');
      const lvs = lsh.getRange(2, 1, lsh.getLastRow() - 1, lsh.getLastColumn()).getDisplayValues();
      const rsh = ss.getSheetByName('Round Robin');
      const rvs = rsh.getRange(2, 1, rsh.getLastRow() - 1, 2).getValues();
      let rr = { pA: [], index: 0, incr: function () { return this.index++ % this.pA.length; }, getIndex: function () { return this.index % this.pA.length; } };
      rvs.forEach((r, i) => {
        rr[r[1]] = r[0];
        rr.pA.push(r[1]);//push name in property array
        if (r[0]) {
          rr.index = i;//assign initial selection
          rsh.getRange(rr.index + 2, 1).setValue('');//remove x from current next
        }
    
      });
      lvs.forEach((r, i) => {if (!r[1]) {lsh.getRange(i + 2, 2).setValue(rr.pA[rr.incr()]);}});//assign lead and increment index
      rsh.getRange(rr.getIndex() + 2, 1).setValue('x');//record next assignment from rr.getIndex();
    }
    

    remainder %

    I tried to add additional content but Stack Overflows stupid content checker was flagging tables as improperly formatted code.