Recurring Tasks Script

An Excel tutorial by Peter Kalmström

Most organizations have tasks that must be performed with specific intervals, such as every day, week or month. There are two use cases for such recurring tasks:

Managers/administrators define which work needs to be done and how often. This data is contained in a "Templates" list or table.
Other users will work with the tasks in a "Daily Tasks" or "To Do" list or table. They will update the task's status and maybe add comments, time worked and a photo to each task.

Recurring tasks is one of Peter's favorite examples, and we have already shown how they can be managed in three earlier articles:

In each of the examples there is a Templates list/table and a Tasks list/table. A flow, workflow or query takes information from the Templates list/table to create instances of specific tasks in the Tasks list/table. For example, if a task is to be done every 3 days, 10 tasks should be created monthly from that template.

In the demo below, Peter Kalmström uses the Office Scripts automation features in Excel for the recurring tasks management. The solution he starts building here, will be expanded in later Tips articles.

In an Excel workbook, Peter creates two tables in different sheets:

The Templates table shows all recurring tasks, the interval and the date when the task must be performed the next time
The Tasks table shows tasks that are due today. This is the table that people who perform the tasks should use, so we could add this Excel table to a SharePoint page that is often visited, and let people interact with the tasks there.

However, in later demonstrations we will add a Power Apps application for that purpose. This app will send the values in the Status column to Excel. We will also add more columns to this table and get the values in them from the powerapp.

Peter creates a script that runs through the Templates table and creates instances of the tasks for the current date in the Tasks table. The script also changes the Next date value in the Templates table according to the specified interval.

In this first demo, the script is run by a button, which is added to the workbook with the script, as shown in the previous demo. In the next demo Peter shows how to make the script run on a schedule.

Peter uses a TypeScript script with two classes, Template and Templates, and a Main function. The function reads from the classes and executes their methods.

The Template Class

The Template class has three methods:

Read the rows in the Templates table.
Check if today's ISO date equals the ISO date value in the Next column.
For tasks where the second method returns true, create new instances in the Tasks table and add the interval to the current date in the Templates table, to set a new Next date.

class Template {
    public Title: string = "";
    public NextDateISO: string = "";
    public Interval: number = 0;
    public Row: ExcelScript.Range;
    public FromRow(Row: ExcelScript.Range): void {
        this.Title = Row.getCell(0, 0).getText()
        this.Interval = parseInt(Row.getCell(0, 1).getText())
        this.NextDateISO = Row.getCell(0, 2).getText();
        this.Row = Row;
    }
    public IsToday(): boolean {
        const TodayISO: string = new Date().toISOString().substring(0, 10);
        return TodayISO === this.NextDateISO;
    }
    public AddAsTask(WB: ExcelScript.Workbook) {
        const TaskTable: ExcelScript.Table = WB.getTable("Tasks");
        TaskTable.addRow(null, [this.Title, new Date().toISOString().substring(0, 10), ""]);
        let NewDate: Date = new Date();
        NewDate.setDate(NewDate.getDate() + this.Interval);
        this.Row.getLastCell().setValue(NewDate.toISOString().substring(0, 10));
    }
}

The Templates Class

The Templates class method is to count the rows in the Templates table and get the items from each row.

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

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

The Main Function

In TypeScript, each script must contain a main function with the ExcelScript.Workbook type as its first parameter. It is the starting point for everything that you need to do, and from the workbook instance you can get to sheets, tables, cells, images etc.

Here, the main function does the following:

Creates an instance of the Templates class and puts it in a variable, All Templates.
Reads from the Templates table to get all items into the variable.
Loops throught the items checking if the Next date is today.
If the Next date is today, the function executes the AddAsTask method in the Template class. This creates the new task instances in the Tasks table and updates the Next date column in the Templates table.

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)
        }
    }
}