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