Shifter is a Google Sheets Add-On that solves consistently constrained shift assignment problems by formulating them as optimization problems and solving them using linear programming. It is designed to be applied directly to Sheets listing Google Form responses. This page describes how to set up Shifter’s parameters for a particular application and run it to generate optimal assignments.

Installing

In the form responses spreadsheet where you want to use Shifter, select “Extensions → Apps Script” from the top menu. This should create a new Apps Script Project and display a code editor. Copy Shifter’s code from GitHub below into the code editor, replacing the existing boilerplate function definition. Save the project.

shifter/Code.gs at master · broad-well/shifter

Setting up

Setting up Shifter for a new application simply requires you to format all the inputs and label them using standard names. This enables Shifter to find the inputs in your spreadsheet.

Begin by creating a sheet dedicated to Shifter next to the form responses, and name it “Shifter”. Shifter will look for its inputs through this named sheet.

Inputs

Expand each item to set up each category of inputs.

• Worker availability and preferences
• List of shifts, constraints on number of workers assigned to each shift, and hours per shift
• Constraints on each worker’s total shift hours
• Adding to an existing work schedule
• Weighing preference priority by submission time (optional)

Miscellaneous configuration: When displaying the solution, Shifter can visualize whether each shift assignment is preferred by coloring each worker’s name green when that name is under one of their preferred assignments. Designate a cell in the Shifter sheet for whether preferred assignments should be colored green. Using Named Ranges, name this cell PreferredGreen. Write either TRUE or FALSE in this cell.

Running the Solver

Once all the inputs are formatted and named, select “Shifter → Validate” from the top menu to let Shifter check your inputs and ensure correct formatting. If the validation fails, it will point out the exact mistake it found. Fix the mistake and repeat until the validation succeeds.

Then, clear the output space and select “Shifter → Solve” from the top menu to let Shifter solve the optimization problem. Within a few seconds, it should respond with either a successful status or a failing one. If it fails, double check that it is feasible to satisfy all the constraints:

• The total number of workers assigned to each shift is constrained in the Shift list