Get Due Date Info from Excel to the Recurring Tasks App

An Excel tutorial by Peter Kalmström

In the demo below, Peter Kalmström continues to build on the Excel-Power Automate-Power Apps solution for recurring tasks management. Now, Peter wants to show app users when each task needs to be finished.

Peter adds a Days To Complete column to the Templates table and due date columns to both tables. In the Templates table, he lets Excel calculate the Next Due Date value for each task from the Next and Days To Complete values.

Peter also modifies the AddTasks script to include the new columns and make it add due dates in the Tasks table together with the other data from the Templates table. Finally, he fetches the Due Date information from the Tasks table to a new text label in the powerapp's first screen.

Modifications in Excel

In Excel, Peter adds more columns to the Tasks and Templates tables and modifies the script to include and update these columns.

Add a new date column to the Tasks table: Due Date.
Add a new number column to the Templates table: Days To Complete.
Add a new date column to the Templates table: Next Due Date.
In the Templates table, calculate the Next Due Date value for the first task with the formula: =[@Days To Complete]+[@Next]
Fill down the formula to all the cells in the Next Due Date column.
Open the AddTasks script in edit mode.
Add the Next Due Date column in the Templates table to the Template class. It should be a string and the items should have no value: public NexDueDateIso:string = ""
In the FromRow method, copy the code for the Next Date column and paste it in a new row below. Change the column name to NextDueDateISO and the column number to 4: this.NexDueDateIso = Row.getCell(0, 4).getText()

Now the first parts of the Template class will look like this:

class Template {
  public Title: string = "";
  public NextDateISO: string = "";
  public Interval: number = 0;
  public NexDueDateIso:string = "";
  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.NexDueDateIso = Row.getCell(0, 4).getText();
    this.Row = Row;
  }

In the ToNewTaskArray method, add the Due Date column in the Tasks table to the array and push the Template object this.NextDateISO into the cells: NewArray.push(this.NexDueDateIso).

Now, the ToNewTaskArray method looks like this:

public ToNewTaskArray(): Array<string> {
    let NewArray: Array<string> = [];

    NewArray.push(new Date().getTime().toString()); //ID
    NewArray.push(this.Title);
    NewArray.push(new Date().toISOString().substring(0, 10)); //Date
    NewArray.push(""); //Status
    NewArray.push(""); //Done By
    NewArray.push(""); //Done At
    NewArray.push("0"); //Minutes Worked
    NewArray.push(this.NexDueDateIso); //Due Date
    return NewArray;  }

Test the script by changing a couple of dates in the Templates table to today's date. Run the script and make sure that due dates for today's tasks are added to the Tasks table together with the other data from the Templates table.

Modifications in Power Apps

Peter adds a text label for the due date information from the Excel Tasks table. This time, he only works in the first screen, Screen1.

Edit the Open Recurring Tasks app.
Refresh the data source to include the Due Date column.
Insert a text label in the first screen's template.
Select the Date field and copy the formula for its Text property. Then select the Due Date field's Text property and paste the formula there. Change the column name from Date to Due Date:
Date(ThisItem.'Due Date'),Month(ThisItem.'Due Date'),Day(ThisItem.'Due Date').
Insert another text label in the template, between the two date fields.
Modify the new label's Text property to show a dash:"–".

Download the whole script