Get Done By and Done At data to Excel from the App

An Excel tutorial by Peter Kalmström

In the previous demo, Peter showed how to use Power Apps to build an app that shows today's tasks from an Excel Tasks table. That app only sent back information if the task had been done or not, but often managers also want to know who performed the task and a what time.

However, we don't want to burden the app users with more fields to fill out. Instead, we will change the AddTasks script in Excel and the Patch formula in the app, so that the additional data is sent to the Excel Tasks table together with the Status update when the user tap the app's Check icon. In the demo below, Peter shows how to do that.

Modifications in Excel

In Excel, Peter adds two more columns to the Tasks table and modifies the script to include them in the columns array. Instead of just adding two more blank rows to the array in the existing method, Peter wants to make the script more readable and expandable by adding a new public method to the Template class.

Add two columns to the Tasks table: Done By and Done At.
Open the AddTasks script in edit mode.
Add a new public public method to the Template class: ToNewTaskArray. It should take no parameters but return an array of strings: public ToNewTaskArray(): Array<string>{}
Inside the array object, declare a variable, NewArray, as an empty array of strings: let NewTaskArray:Array<string>=[]

Push the column data into the array:

NewTaskArray.push(new Date().getTime().toString()); //ID
      NewTaskArray.push(this.Title);
      NewTaskArray.push(new Date().toISOString().substring(0,10));//Date
      NewTaskArray.push(""); // Status
      NewTaskArray.push("");//Done By
      NewTaskArray.push("");//Done At

Return the array: return NewTaskArray
In the AddAsTasks method, delete the array after "TaskTable.addRow(null," and replace it with "this.ToNewTaskArray()" to call the new method.

Now, the both Template methods should look like this:

public ToNewTaskArray(): Array<string>{
      let NewTaskArray:Array<string>=[];
      NewTaskArray.push(new Date().getTime().toString()); //ID
      NewTaskArray.push(this.Title);
      NewTaskArray.push(new Date().toISOString().substring(0,10));//Date
      NewTaskArray.push(""); // Status
      NewTaskArray.push("");//Done By
      NewTaskArray.push("");//Done At
      return NewTaskArray;
    }

    public AddAsTask(WB:ExcelScript.Workbook){
      const TaskTable:ExcelScript.Table = WB.getTable("Tasks");
      TaskTable.addRow(null,this.ToNewTaskArray());
      let NewDate: Date = new Date();
      NewDate.setDate(NewDate.getDate() + this.Interval);
      this.Row.getLastCell().setValue(NewDate.toISOString().substring(0,10));
    }
}

Test the script by changing a couple of dates in the Templates table to today's date. Run the script and make sure that the new rows have been added to the Tasks table.

Modifications in Power Apps

To update the Tasks table with the Done By and Done At values, Peter adds these two columns and their values as arguments to the Check icon's OnSelect formula.

Edit the Open Recurring Tasks app.
Refresh the data source (the Excel Tasks table) to include the two new columns.
To update the Tasks table with the Done By value, add the argument 'Done By': User().FullName' to the formula.
To update the Tasks table with the Done At value, add the argument 'Done At':Text(Now(),"yyyy-mm-dd") to the formula.
Now the whole formula should look like this:
Patch(Tasks,ThisItem,
{
Status:"Done",
'Done By': User().FullName
'Done At':Text(Now(),"yyyy-mm-dd"),
}
)

Note: for Excel to recognize the date, it is actually enough to add Now() to the 'Done At' formula: 'Done At': Now(). Peter makes that change in the next demo.
Play the app and check one of the tasks as finished. Make sure that the task's Done, Done By and Done At values has been propagated to the Excel Tasks table.