The idea is as follows : there are three (3) tasks and four (4) workers. Every week, a worker gets assigned a task, with one worker in vacation. In perfect scenario, the tasks rotate between workers. So for example:

In Calendar Week 1, "Worker #1" gets "Task #1", "Worker #2" gets "Task #2", "Worker #3" gets "Task #3" and "Worker #4" gets no task.

In Calendar Week 2, "Worker #1" gets "Task #2", "Worker #2" gets "Task #3", "Worker #4" gets "Task #3" and "Worker #3" gets no task.

In Calendar Week 3, "Worker #1" gets "Task #3", "Worker #3" gets "Task #1", "Worker #4" gets "Task #2" and "Worker #2" gets no task. And so on.

Now when a worker is sick or can't work, his task gets delegated to the worker who has no task that week. Also the abscentee worker gets the task of the worker who replaced them on their rest week. For the example above, "Worker #2" is absent in Week 2, his "Task #3" gets delegated to "Worker #3" who had the week free. And in Week 3 "Worker #2" would have had a free week, so they get "Task #1" and "Worker #3" gets no task.

In case more than one worker are away, the tasks can get reassigned manually, with the following assignments readjusted accordingly if possible.

Is there a way to automate a spreadsheet, always filling the next 12 weeks with the rotating tasks accordingly, and if a task gets reassigned the auto population readjust? The option for workers to check a box for abscence for a specific week, and it automatically reassign the task is a nice feature but not necessary. I am looking to build an Appsheet around this, with different dashboards for workers, task progress and task review from other workers.

The idea is as follows : there are three (3) tasks and four (4) workers. Every week, a worker gets assigned a task, with one worker in vacation. In perfect scenario, the tasks rotate between workers. So for example:

In Calendar Week 1, "Worker #1" gets "Task #1", "Worker #2" gets "Task #2", "Worker #3" gets "Task #3" and "Worker #4" gets no task.

In Calendar Week 2, "Worker #1" gets "Task #2", "Worker #2" gets "Task #3", "Worker #4" gets "Task #3" and "Worker #3" gets no task.

In Calendar Week 3, "Worker #1" gets "Task #3", "Worker #3" gets "Task #1", "Worker #4" gets "Task #2" and "Worker #2" gets no task. And so on.

Now when a worker is sick or can't work, his task gets delegated to the worker who has no task that week. Also the abscentee worker gets the task of the worker who replaced them on their rest week. For the example above, "Worker #2" is absent in Week 2, his "Task #3" gets delegated to "Worker #3" who had the week free. And in Week 3 "Worker #2" would have had a free week, so they get "Task #1" and "Worker #3" gets no task.

In case more than one worker are away, the tasks can get reassigned manually, with the following assignments readjusted accordingly if possible.

Is there a way to automate a spreadsheet, always filling the next 12 weeks with the rotating tasks accordingly, and if a task gets reassigned the auto population readjust? The option for workers to check a box for abscence for a specific week, and it automatically reassign the task is a nice feature but not necessary. I am looking to build an Appsheet around this, with different dashboards for workers, task progress and task review from other workers.

• google-sheets
• automation

• Hi and Welcome to Stackoverflow. Please provide a minimal reproducible example like markdown tables containing your sample input data. Also include what are things you have tried and putting some sample data would help to know what are things to be done so they can be easily read and tweaked by editors, and this then updates the entry in the spreadsheet. – EL SRY Commented yesterday

1 Answer 1

It seems that the general idea of your project is to have 12 weeks of rotating tasks for 3 workers. Now, you want to automate the auto reassigning of tasks once a worker is absent for a specific week.

Since the rotation of tasks is fixed, I made a sheet that has the default designation of tasks. The automated parts are when a few instances happen such as when a worker is absent (checkbox is set to true) his task for the week will be reassigned to the worker who has no tasks, then he will take the task of the worker who took his task when the worker is absent. The other scenario is when there are two absences for the week, the values will be automatically set to "manually assigned". I have made a simple script below based on the criteria you have provided, if this answer isn't the exact solution you've been looking for, I hope you get an idea on how to achieve it.

Script Used:

function onEdit(e) {
 var sheet = e.source.getActiveSheet();
 var range = e.range;
 var column = range.getColumn();
 var row = range.getRow();
 var absentCol = [3, 5, 7, 9];
 var taskCol = [2, 4, 6, 8];
  if (absentCol.includes(column)) {
   var value = range.getValue();
   var checkedCount = 0;

   absentCol.forEach(col => {
     if (sheet.getRange(row, col).getValue() === true) {
       checkedCount++;
     }
   });

   if (checkedCount > 1) {
     for (var i = 0; i < taskCol.length; i++) {
       var taskCell = sheet.getRange(row, taskCol[i]);
       taskCell.setValue("Manual Assign");
       taskCell.setBackground("#FFFF99");
     }
   } else if (value === true) {
     var index = absentCol.indexOf(column);
     var taskCell = sheet.getRange(row, taskCol[index]);
     var taskValue = taskCell.getValue();

     if (taskValue !== "No Task" && taskValue !== "") {
       var noTaskWorker = -1;
       for (var i = 0; i < taskCol.length; i++) {
         if (i !== index) {
           var availTask = sheet.getRange(row, taskCol[i]);
           if (availTask.getValue() === "No Task") {
             noTaskWorker = i;
             availTask.setValue(taskValue);
             taskCell.setValue("No Task");
             break;
           }
         }
       }

       if (noTaskWorker !== -1) {
         var lastRow = sheet.getLastRow();
         for (var r = row + 1; r <= lastRow; r++) {
           var absentWorkerNtC = sheet.getRange(r, taskCol[index]);
           if (absentWorkerNtC.getValue() === "No Task") {
             var swappedWtC = sheet.getRange(r, taskCol[noTaskWorker]);
             var swappedWtV = swappedWtC.getValue();
             absentWorkerNtC.setValue(swappedWtV);
             swappedWtC.setValue("No Task");
             break;
           }
         }
       }
     }
   } else {
     for (var i = 0; i < taskCol.length; i++) {
       var taskCell = sheet.getRange(row, taskCol[i]);
       if (taskCell.getValue() === "Manual Assign") {
         taskCell.setValue("No Task");
         taskCell.setBackground(null);
       }
     }
   }
 }
}

Sample Output

References

• Class Range

• Class Spreadsheet

• onEdit(e)