Set Tasks in Excel to Missed if not Done in Due Time

An Excel tutorial by Peter Kalmström

In a series of articles we have built and expanded an Excel-Power Automate-Power Apps solution for recurring tasks management.

In the demo below, Peter Kalmström shows how to automatically set the task status to Missed in Excel if they have not been performed the day after the due date. When tasks are marked as Missed, they will disappear from the Open Recurring Tasks powerapp.

For this to happen, Peter needs to make some additions to the AddTasks script that is running in Excel. He creates two classes, Task and Tasks and adds new commands to the Main function:

The Task class reads the values in the ID, Due Date and Status columns from each row in the Excel Tasks table.
The Tasks class counts the rows in the Tasks table and gets the items from each row.
The Main function first executes the Template/Templates classes and then the Task /Tasks classes. (Classes can not be executed. They are instanciated and their methods are called or executed).

The two new classes and the addition to the function are very similar to the earlier Template and Templates classes and their funtion code. They just use the Tasks table instead of the Templates table.

To avoid some typing, Peter therefore makes use of the earlier code by copying and pasting parts that should be nearly the same in the new code. Then he modifies the pasted code to suit the Tasks table and its ID, Due Date and Status columns. In the first article in this Recurring Tasks series, the original script is explained in more detail than here. The principles are the same.

Add a new class to the script: Task.

Add the ID, Due Date and Status columns to the Task class:

class Task{
  public ID:number = 0;
  public DueDateISO:string = "";
  public Status:string = "";

Copy the Row and FromRow code from the Template class and paste them below the column rows in the Task class.

Change the code so that it reads the text in the ID, Due Date and Status columns, that is column number 0, 7 and 3. Now the whole Task class should be:

class Task{
  public ID:number = 0;
  public DueDateISO:string = "";
  public Status:string = "";
  public Row: ExcelScript.Range;
  public FromRow(Row: ExcelScript.Range): void {
    this.ID = parseInt(Row.getCell(0, 0).getText());
    this.DueDateISO = Row.getCell(0, 7).getText();
    this.Status = Row.getCell(0, 3).getText();
    this.Row = Row;
  }
}

Add another class to the script: Tasks.
Copy all rows in the Templates class and paste them in the Tasks class.
In the first row, replace Template with Task, to get the items from the Task class.
Replace "Templates" with "Tasks", to get the tasks from the Tasks table instead of the Templates table.
Replace CurrTemplate: Template with CurrTask:Task and CurrTemplate = new Template() with CurrTask = new Task()
Replace CurrTemplate with CurrTask in the two last rows of the Tasks class, to make the script read from the row in the Task class and add to the items array in the same class.

Now the whole Tasks class should look like this:

class Tasks {
  public Items: Task[] = []
  public GetFromTable(WB: ExcelScript.Workbook) {
    const TemplatesTable: ExcelScript.Table = WB.getTable("Tasks");
    const ContentRows: ExcelScript.Range = TemplatesTable.getRangeBetweenHeaderAndTotal();

    let RowCount: number = TemplatesTable.getRowCount();
    let Counter: number = 0;
    let CurrRow: ExcelScript.Range;
    let CurrTask: Task;
    for (Counter = 0; Counter < RowCount; Counter++) {
      CurrRow = ContentRows.getRow(Counter);
      CurrTask = new Task();
      CurrTask.FromRow(CurrRow);
      this.Items.push(CurrTask)
    }
  }
}

In the Main function, copy the four first function commands and paste them below the for loop.

Replace Templates with Tasks and Template with Task. Now the whole function should look like this: (note that the for loop is not finished yet!)

function main(workbook: ExcelScript.Workbook) {
  const AllTemplates: Templates = new Templates();
  AllTemplates.GetFromTable(workbook);
  let CurrTemplate: Template;
  for (CurrTemplate of AllTemplates.Items) {
    if (CurrTemplate.IsToday()) {
      CurrTemplate.AddAsTask(workbook)
    }
  }

  const AllTasks:Tasks = new Tasks();
  AllTasks.GetFromTable(workbook);
  let CurrTask:Task;
  for(CurrTask of AllTasks.Items){

Insert another variable before the start of the for loop. It should give the ten first caracthers of todays date as a string: let TodayISO: string = new Date().toISOString().substr(0,10); (The ten first characters is the full date in YYYY-MM-DD ISO format, that is, the date but not the time.?
Continue the for loop with a condition that looks if a task's due date is less than today's date and if the task's Status value is not equal to Done. When the condition is true, the status value should be set to Missed:
```
if(CurrTask.DueDateISO>TodayISO){
  if(CurrTask.Status !== "Done"){
     CurrTask.Row.getCell(0,3).setValue("Missed");
```

Now the whole function will look like this:

function main(workbook: ExcelScript.Workbook) {
  const AllTemplates: Templates = new Templates();
  AllTemplates.GetFromTable(workbook);
  let CurrTemplate: Template;
  for (CurrTemplate of AllTemplates.Items) {
    if (CurrTemplate.IsToday()) {
      CurrTemplate.AddAsTask(workbook)
    }
  }

  const AllTasks:Tasks = new Tasks();
  AllTasks.GetFromTable(workbook);
  let CurrTask:Task;
  let TodayISO: string = new Date().toISOString().substr(0,10);
  for(CurrTask of AllTasks.Items){
    if(CurrTask.DueDateISO>TodayISO){
      if(CurrTask.Status !== "Done"){
        CurrTask.Row.getCell(0,3).setValue("Missed");
      }
    }
  }

}

Test the script by changing a number of tasks in the Tasks table so that the due dates are before today's date. Set some of them to Done and leave the Status cells empty for the rest of the tasks. Run the script and make sure that the tasks that were not marked as Done get Missed in the Status column.