A Guide to Auto-Assignments in Google Sheets

In this guide, we'll look into using the `onFormSubmit(e)` installable trigger which will let us apply automation to any new tasks being created through the Google Form we created in the guide to basic task management

Similar to our guide on checkbox automation, this guide will also require some knowledge of javascript.

There are several ways to approach auto-assignments. At the end of the day, the point is just to make sure that someone on your team is assigned to any new tasks coming, even if they might re-assign it to someone else. In this guide, we'll go over three types of auto-assigning logic:

Random
Round Robin
Dynamic Equal Distribution

But first, let's make sure our spreadsheet is set up properly.

Setup

Create a Named Range for your Assignees

Doing this is pretty simple. In your "Settings" tab, create a list of Assignees if you haven't already. Then highlight the entire column, click on the "Data" option in the menu bar, and select Named Ranges. Let's give it the name "Assignees".

Not sure how to make a named range? Check out our how-to on named ranges for detailed instructions

Set up the trigger

Before diving into the code, let's set up the `onFormSubmit` trigger you'll be using. It'll be handy to have when you start writing the code and need to test it. To set up this trigger, you'll have to create something called an "installable trigger". It's easy to do this though - click on the "Triggers" option in your Script Editor and then find the "Add Trigger" button on the bottom right.

triggers

On the options, make sure to set the "event source" as "From spreadsheet" and the "event type" as "On form submit". For the "which function to run" option, you'll have to come back to this later on to set that.

Create the Logic

At a high-level, the logic is pretty straight forward. If we were to explain it in words, it would be:

- Get a list of the assignees available

- Figure out who the next assignee is

- Find the newly created task

- Set the "Assignee" field to the next assignee

Each approach will have their little nuances, but they'll all follow these basic steps.

Shared Logic

Assignees

For the list of assignees, we're going to retrieve the named range we created by calling the `SpreadsheetApp` library in our script editor and using some pre-built functions to find the named range. It'll look like this:

SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Assignees').getValues()

Once we have this range, we'll need to filter it down so that we just have the names of our assignees in it. This means we'll need to remove the header row and also any blanks that exist. We'll dot his using javascript's `.filter()` function.

assignees = assignees.filter(row => row != '' && row != 'Assignee')

Keep this somewhere safe for now. We'll be using it in the next three parts.

Value Setting

The other bit of logic that will be shard across all methods is how we set the "Assignee" field to the qualifying assignee. To do this we'll have to find the cell we want to update and then set it to the output of our assignment logic. Just like the `OnEdit(e)` function, we'll be utilizing the `e` parameter which is just short for event. We just need the row for this logic, so we'll be doing something like this:

var row = e.range.getRow();

We're also going to make use of the `getColumnByName()` we wrote in the previous guide. Here's what the line of code will look like:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, getColumnByName('Assignee', 1)).setValue(assignment)

We'll be using this in the next 3 parts, so put this in a safe place like you did with the logic for assignees.

Random Assignment

The differentiating part for randomly assigning tasks to team members is being able to randomly pick someone from the list of team members. To do this, we'll need the list of assignees (using the code above) and also a way to generate a random number. Luckily, javascript gives us a way to do this by using `Math.random()` which will give us a random value between 0 and 1. We're going to use that in combination with `Math.floor()` which will give us the largest integer based on what we give it. For example, if we did `Math.floor(5.5)`, it would give us 5.

Since `Math.random()` only gives us values between 0 and 1, we'll also need to multiply it by something so that we get something larger than 0. Since we need the number generated to match up with the number of assignees we have available, we'll just use the count of assignees. Here's how our line of code will look:

var rand = Math.floor(Math.random() * assignees.length);

The output of this should be an integer between 0 and the number of assignees you have. To figure out the assignee's name, all you need to do is set our `assignment` variable to `assignee[rand]` which will give you the assignee in the Nth index position of your assignees array.

When you combine this logic with the assignees and the value setting logic, your entire `assignByRandom()` function should look like this:

function assignByRandom(e) {
  var assignees = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Assignees').getValues();
  assignees = assignees.filter(row =>
    row != '' && row != 'Assignee'
  )

  var rand = Math.floor(Math.random() * assignees.length);
  var assignment = assignees\[rand]
  var row = e.range.getRow();

  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, getColumnByName('Assignee', 1)).setValue(assignment)
}

To test this, go back to the triggers page and update the trigger we created to use the `assignByRandom()` function.

Round Robin Assignment

The round robin logic is going to be a lot more involved than the random logic. For the round robin, we'll have to keep track of who the last team member was that got assigned to a task. In order to do that, we'll be using a built-in feature of Google Apps Script called "Properties Service". In a nutshell, this service let's us store information in the script without having to save it in a database - it's perfect for remembering who the last assignee was.

To call the "Properties Service" we'll do something similar to how we call the `SpreadsheetApp`. For this, the command will be `PropertiesService` and we'll call the `getScriptProperties()` function. Let's set that to the variable `script_properties` and then try to retrieve a property called "previous":

var script_properties = PropertiesService.getScriptProperties()
var previous_assignee = parseInt(script_properties.getProperty('previous'));
var next_assignee = 0

Notice that we used `parseInt()` when grabbing the "previous" property. This is just a way to make sure that what we're getting back is in integer since we'll need it to get the index position of our assignee array. Please note, if there is no property for "previous" that it'll just get returned to us as "undefined". We'll have to take that into account in our next block of code.

if(previous_assignee == undefined || previous_assignee == assignees.length - 1) {
  script_properties.setProperties({
    'previous': 0
  });
} else {
  next_assignee = previous_assignee + 1
  script_properties.setProperties({
    'previous': next_assignee
  });
}

A lot to unpack here. Let's start with the `if` statement. In our criteria, we're checking to see if the "previous" property is undefined and also to see if the "previous" property is equal to the number of assignees on our list. We're subtracting one from the count of assignees because arrays start from 0.

If our criteria is met in the `if` statement, we're setting the "previous" property to 0 since we're either running this logic for the very first time, or we have to reset the counter because we've reached the end of the assignee list. We're using `script_properties.setProperties()` to update the "previous" property.

When the "previous" property is not undefined and it is less than the number of assignees we have, we want to iterate it by one and set that as the new value. This will make it so that our next run gives us the index value of the assignee we're about to assignee to the new task during this run of the logic.

The next part is similar to what we did in the random logic - we're going to set the `assignments` variable to `assignee[next_assignee]` which will give us the person that's next in line to get assigned. once you combine this logic with the assignees and the value setting, it'll look like this:

function assignByRoundRobin(e) {
  var assignees = preadsheetApp.getActiveSpreadsheet().getRangeByName('Assignees').getValues();
  assignees = assignees.filter(row =>
    row != '' && row != 'Assignee'
  )

  var script_properties = PropertiesService.getScriptProperties()
  var previous_assignee = parseInt(script_properties.getProperty('previous'));
  var next_assignee = 0
  if(previous_assignee == undefined || previous_assignee == assignees.length - 1) {
    script_properties.setProperties({
      'previous': 0
    });
  } else {
    next_assignee = previous_assignee + 1
    script_properties.setProperties({
      'previous': next_assignee
    });
  }
  var assignment = assignees\[next_assignee]
  var row = e.range.getRow();
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, getColumnByName('Assignee', 1)).setValue(assignment)
}

That's it for the round robin logic. If you have 3 team members, it'll go in order of the list -- 1, 2, 3, 1, 2, 3 and so on. This is a very popular way of auto-assigning tasks. The only problem with it is that it doesn't care if someone is overburdened with tasks or not.

To test this, go back to the triggers page and update the trigger we created to use the `assignByRoundRobin()` function.

Dynamic Equal Distribution

This method will take a look at the distribution of tasks and then assign the new task to whomever has the least amount of tasks. I personally like this method because it ensures that everyone is getting equal treatment at all times even if the team re-assigned tickets around and ended up giving someone more than the others. It's a great way to make sure that everyone on the team pulls their weight.

In order to figure out the distribution of tasks, we'll need to get the full list of tasks we have. We'll be using the `getRange()` function, the `getColumnByName()` function, the `getLastRow()` function, and also the `flat()` function. Our line of code will look like this:

var assignments = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,getColumnByName('Assignee', 1),ss.getActiveSheet().getLastRow(), 1).getValues().flat();

Notice how we're using 4 paramters in the `getRange()` function instead of the usual 2 that we did in the checkbox automation guide. The other two parameters is the `number_of_rows` and the `number_of_columns`. This lets us get a range of data instead of a single cell. The `getLastRow()` function will give us very last row index available on the sheet that has data. We only care about the "Assignees" column, so the number of columns is set to 1.

The `flat()` function we're using is just to make it so that our array of assignments doesn't have any nested arrays in it. It'll make it easier for us to filter things out in the next block of code:

var assignment_counts = []
for(i=0; i < assignees.length;i++) {
  var filtered_assignments = assignments.filter(function(row) {
    return row == assignees\[i]
  })
  assignment_counts.push(filtered_assignments.length)
}

In this code block, we're doing a couple of things that are important. The first thing is declaring a brand new array that we're going to use to store the count of tasks for each assignee. The second thing we're doing is looping through each assignee and filtering down the list of tasks for items that are assigned to them. We're using javascript's `filter()` function to do this, similar to how we cleaned up the assigneees list earlier.

Now that we have a set of assignment counts for each assignee, the next step here is to figure out which one has the lowest count. To do that, we're going to set a variable to the lowest amount and compare each index in the array to the amount - if the value in the array is lower than the amount, then we'll use that as the new benchmark and save the index. We'll keep doing this until we've gone through the entire list and the end result should tell us what the lowest amount was and which index position it was in within the array.

var index = 0;
var value = assignment_counts\[0]
for(i=1; i < assignment_counts.length; i++) {
  if (assignment_counts[i] < value) {
    value = assignment_counts[i];
    index = i;
  }
}

Since we filled in the `assignees_count` array in the same order as the `assignees` array, the index position of the arrays will match up. Because they match up, we can use the index position of the `assignees_count`'s lowest value to find the matching assignee's name on the `assignees` array. Allowing us to do this:

var assignment = assignees[index]

Looks familiar right? the next and final step here is to combine it with the assignees logic and the value setting logic - giving us this function:

function assignByEqualDistribution(e) {
  var assignees = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Assignees').getValues();
  assignees = assignees.filter(row =>
    row != '' && row != 'Assignee'
  )
  var assignments = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,getColumnByName('Assignee', 1),ss.getActiveSheet().getLastRow(), 1).getValues().flat();
  var assignment_counts = \[]
  for(i=0; i < assignees.length;i++) {
    var filtered_assignments = assignments.filter(function(row) {
      return row == assignees\[i]
    })
    assignment_counts.push(filtered_assignments.length)
  }
var index = 0;
var value = assignment_counts\[0]
for(i=1; i < assignment_counts.length; i++) {
  if (assignment_counts\[i] < value) {
    value = assignment_counts\[i];
    index = i;
  }
}
  var assignment = assignees\[index]
  var row = e.range.getRow();
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, getColumnByName('Assignee', 1)).setValue(assignment)
}

To test this, go back to the triggers page and update the trigger we created to use the `assignByEqualDistribution()` function. If everything was set up right, every new form submission made through the Google Form willget assigned to whomever has the least number of tasks.

Clean up Your Code

It's always good to clean up your code so that they're not repeating code in different places and also so that you can read it better. In our cleanup, we're going to explore using multiple files in the Script Editor and refactoring our code so that we're following a DRY method (aka "don't repeat yourself")

Using Multiple Files in Google Apps Script

The first step of cleaning up your Google Apps Script project is to separate your functions out from a single file and into multiple files that are grouped by type. You can create new files by clicking on the "+" icon and selecting the "Script" option. The "HTML" option is for UI options, which we'll cover in another guide.

If you need to rename a file, just hover over the file name, click on the 3 dots, and then select the "rename" option:

Based on everything we've done in this guide, you'll want about 4 files of separation to have it nice and neat.

refactored

### DRY'ing Our Code

There are two code blocks that we consistently used in this guide. The first one is for retrieving the assignees list and cleaning it up. The second is finding the row we want to update and setting the new assignee value to it. To clean this up, we'll want to extract that code from the 3 different assignment functions and make it more generalized.

If we create a new function to handle this, it'll look like this:

function getAssignees(spreadsheet) {
  var assignees = spreadsheet.getRangeByName('Assignees').getValues();
  assignees = assignees.filter(row => 
    row != '' && row != 'Assignee'
  )
  return assignees
}

This function will handle retrieving the list and then cleaning it up. This will let us set up the beginning of the other 3 functions like this:

function assignByRandom(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var assignees = getAssignees(spreadsheet);
  ...
}

function assignByRoundRobin(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var assignees = getAssignees(spreadsheet);
  ...
}

function assignByEqualDistribution(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var assignees = getAssignees(spreadsheet);
  ...
}

By doing this, any changes we make to the `getAssignees()` function will be shared across all 3 functions.

Next up is the value setting logic.

Just like the `getAssignees()` function, this change will let us update all 3 functions by updating the logic in one spot.

function setAssignee(spreadsheet, e, assignment) {
  spreadsheet.getActiveSheet().getRange(e.range.getRow(), getColumnByName('Assignee', 1)).setValue(assignment)
}