Archive Recurring Tasks in Excel

An Excel tutorial by Peter Kalmström

In an earlier demo, Peter Kalmström showed how to create an app in Power Apps that is connected to the Excel Tasks table. This app shows these of today's tasks that have not already been done.

To show only the tasks that were not marked as Done, Peter used a Filter function with the IsBlank argument for the Status column in the Excel table. The formula gave a warning that it might cause problems in large datasets - which is more than 2000 rows in Power Apps.

The Tasks table gets new tasks for the current date every day. We want to be able to study the tasks for BI purposes, but if no tasks are removed from that table, it will eventually reach the Power Apps row limit. In the demo below, Peter shows how to avoid the problem by sending all tasks to a TasksArchive table and removing the performed or missed tasks from the Tasks table.

As the TasksArchive table contains all tasks it can be used for statistics, and the Tasks table will never get too many items. (If you have more than 2000 tasks each day, you should consider another solution!).

Tables

Peter creates a new TasksArchive table and adds a new column to the Tasks table that shows the connection between the tables.

Copy the Tasks table and paste it in a new sheet. Name the sheet and the table TasksArchive.
Add an Archive column to the Tasks table.
Add a formula with the MATCH function to the cell in the first row in the Archive column. This formula compares the ID column in the TasksArchive sheet with the A column in the Tasks table and returns the row number in the TasksArchive sheet for each exact match: =MATCH([@ID];TasksArchive!A:A;0)
Fill down the formula. Now you can see the row number in the TasksArchive table for each task in the Tasks table.

Script

In the AddTasks script, quite a few additions are needed to make sure that this happens:

Each task that is created in the Tasks table gets copied into the TasksArchive table.
Tasks that are marked Done or Missed is removed from the Tasks table.

Template Class

In the last method of the Template class, AddAsTask, there is a change in the code that creates new task instances in the Tasks table. Earlier, the script just created a new row, but now we want to exclude the last column, Archive, which has a formula.

Change the addRow function so that it excludes the last column TaskTable.addRow(-1).

Before "this.ToNewTaskArray())", insert code that first gets the last row in the Task table, then gets the non formula cells in columns A to H in that last row and finally sets the values of those cells:

const LastRow:ExcelScript.Range = TaskTable.getRangeBetweenHeaderAndTotal().getLastRow();
    const NonFormulaCells = WB.getWorksheet("Tasks").getRange("A" + LastRow.getRowIndex() + ":H" + LastRow.getRowIndex());
    NonFormulaCells.setValues(

Now, the last Template method should look like this:

public AddAsTask(WB: ExcelScript.Workbook) {
    const TaskTable: ExcelScript.Table = WB.getTable("Tasks");
    TaskTable.addRow(-1);
    const LastRow:ExcelScript.Range = TaskTable.getRangeBetweenHeaderAndTotal().getLastRow();
    const NonFormulaCells = WB.getWorksheet("Tasks").getRange("A" + LastRow.getRowIndex() + ":H" + LastRow.getRowIndex());
    NonFormulaCells.setValues(this.ToNewTaskArray());

    let NewDate: Date = new Date();
    NewDate.setDate(NewDate.getDate() + this.Interval);
    this.Row.getCell(0, 2).setValue(NewDate.toISOString().substring(0, 10));
  }
}

Task Class

The Task class must include the Archive column, so code for reading that value must be added to the Task class. Peter also adds a new method that copies all rows from the Tasks table to the TasksArchive table and then removes the Archive column from the TasksArchive table.

Add the Archive column: public ArchiveRow:number = 0;
Read the value from from the Archive column, which is column number 8, in each row: this.ArchiveRow = parseInt(Row.getCell(0, 8).getText());

Now, the first method of the Task class should look like this:

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

Add a new method to the Task class. Here, the CopyToArchive function gets all row values from the Tasks table to the TasksArchive table:

public CopyToArchive(WB:ExcelScript.Workbook): void {
    let ArchiveTable: ExcelScript.Table = WB.getTable("TasksArchive");
    let Values: (string | number | boolean)[][] = this.Row.getValues();

Use the Pop function to remove the Archive column from the TasksArchive table for each object. Add all the other values in the row if the Archive column has no value in the Tasks table.
```
Values[0].pop(); //Remove the last column, the ArchiveRow;
if(isNaN(this.ArchiveRow) || this.ArchiveRow === 0){
  ArchiveTable.addRow(-1,Values[0]);
```
If the Archive column already has a value in the Tasks table, for example if the row is updated, the script should get all values in that row minus 2 (the header and the first row, which is 0, as getRow is zero based).
```
}else{
  let DestinationRow: ExcelScript.Range =           
      ArchiveTable.getRangeBetweenHeaderAndTotal().getRow(this.ArchiveRow-2);
  DestinationRow.setValue(Values);
```

Now the whole Task class will look like this:

class Task{
  public ID:number = 0;
  public DueDateISO:string = "";
  public Status:string = "";
  public Row: ExcelScript.Range;
  public ArchiveRow:number = 0;
  public FromRow(Row: ExcelScript.Range): void {
    this.ID = parseInt(Row.getCell(0, 0).getText());
    this.ArchiveRow = parseInt(Row.getCell(0, 8).getText());
    this.DueDateISO = Row.getCell(0, 7).getText();
    this.Status = Row.getCell(0, 3).getText();
    this.Row = Row;
  }
  public CopyToArchive(WB:ExcelScript.Workbook): void {
    let ArchiveTable: ExcelScript.Table = WB.getTable("TasksArchive");
    let Values: (string | number | boolean)[][] = this.Row.getValues();
    Values[0].pop(); //Remove the last column, the ArchiveRow;
    if(isNaN(this.ArchiveRow) || this.ArchiveRow === 0){
      ArchiveTable.addRow(-1,Values[0]);
    }else{
      let DestinationRow: ExcelScript.Range = 
          ArchiveTable.getRangeBetweenHeaderAndTotal().getRow(this.ArchiveRow-2);
      DestinatioRow.setValue(Values);
    }
  }
}

Main Function

In the Main function, we need to add another for loop. Excel sometimes adds an extra row when rows are added with a script, and these should not be copied to the TasksArchive table. Tasks that have a Status value filled out should be deleted from the Tasks table together with any rows without ID.

To remove the blank row from the Tasks table altogether, Peter adds an extra condition that takes care of it.

Check if the current task has an ID that is larger than 1. (The ID is the millisecond that the task instance was added to the Tasks table.) In that case, copy the row (minus the Archive column) to the TasksArchive table. Delete the task if it has the Status value Done or Missed or if it has an ID less than 1.
```
for(CurrTask of AllTasks.Items){
    if(CurrTask.ID > 1){
      CurrTask.CopyToArchive(workbook);
    }
    if(CurrTask.Status ==="Done" || CurrTask.Status ==="Missed" || CurrTask.ID < 1){
      CurrTask.Row.delete(ExcelScript.DeleteShiftDirection.up);
    }
 }
```

Add a new condition, to remove any blank rows from the Tasks table. This conditions counts the rows in the Tasks table and deletes the row that is one more than the item lenght.

if (AllTasks.Items.length >0){
    const TasksSheet:ExcelScript.Worksheet =  workbook.getWorksheet("Tasks");
    const ExtraRowNumber: number = AllTasks.Items.length +1;
    const ExtraRowRange:ExcelScript.Range = TasksSheet.getRange(ExtraRowNumber + ":" +ExtraRowNumber);
    ExtraRowRange.delete(ExcelScript.DeleteShiftDirection.up);
  }
}