Get Minutes Worked Info to Excel from the Recurring Tasks App

An Excel tutorial by Peter Kalmström

In the previous demo, Peter added Done By and Done At columns to the Excel Tasks table. With that, he could let the Open Recurring Tasks app send information to Excel about who performed the task and at what time, without burdening the app users with adding extra data.

In the demo below, Peter adds another column to the Tasks table: Minutes worked. To get this information from the powerapp, users only need to make one more tap.

Modifications in Excel

In Excel, Peter adds one more column to the Tasks table and modifies the script to include this column in the columns array.

Add a new Number column to the Tasks table: Minutes Worked.
Open the AddTasks script in edit mode.
In the ToNewTaskArray method, add the new column to the array. Instead of just making it emptly, Peter adds a zero to make sure that it is considered a number by Excel: NewTaskArray.push("0").

Now the whole ToNewTaskArray method 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
      NewTaskArray.push("0");//Minutes Worked
      return NewTaskArray;
    }

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 are added to the Tasks table.

Modifications in Power Apps

The Minutes Worked buttons in the Recurring Tasks app sends data to Excel

Peter starts with changing the Done At part of the Patch formula to just 'Done At': Now(), which is enough for Excel to recognize it as a date.

Then Peter adds another gallery with time value buttons and lets these buttons send all data to Excel instead of the Check icon.

The Check icon will be replaced by a Next icon that sends the user to the new gallery. Here users can tap a button to send data to Excel and then go back to the first screen. Peter does not rename that screen, so it will be called by the default Screen1.

Edit the Open Recurring Tasks app.
Refresh the data source (the Excel Tasks table) to include the new Minutes Worked column.
Add a new blank screen and rename it to MinutesWorked.
Insert a text label in the MinutesWorked screen: Minutes Worked.
Insert a vertical gallery in the MinutesWorked screen: MinutesGallery. Do not select a data source.
Create a formula with the Table function for the Items property of the gallery: Table().
Inside the parenthesis, add the names and values for the items that should be added to the table, in this case the name Minutes and then multiple options for how long it takes to perform a task:{Minutes:10}, {Minutes:20} and so on. Peter creates this formula for the gallery Items property:
Table(
          {Minutes:10},
      {Minutes:20},
       {Minutes:30},
          {Minutes:45},
          {Minutes:60},
          {Minutes:120},
          {Minutes:240}
)
Insert a button inside the gallery.
Select the gallery and set the Wrap count to 2, to get two columns for the buttons.
Adjust the button and template size if needed.
Set the button Text property to ThisItem.Minutes. That will give each button its Minutes value.
In the first screen, cut the OnSelect Patch formula from the Check icon.
In the MinutesWorked screen, change the button property to OnSelect and paste the Check icon Patch formula.
Modify the second argument for the Patch function so that it is the selected item on the first screen, Gallery1.Selected, not the selected item in the MinutesWorked screen.
Add the Minutes work value to the Patch formula. It should take the value from the selected item in the MinutesWorked screen: 'Minutes Worked':ThisItem.Minutes
Add a Navigate function after the Patch function, to take the user back to the first screen again when the data has been sent to Excel: Navigate(Screen1). (Peter also adds a transition argument, but that is not necessary. If no argument for the transition is given, the screen quickly replaces the current screen.)
Now the whole Patch formula should be:
Patch(Tasks,Gallery1.Selected,
     {
        Status:"Done",
        'Done By': User().FullName
        'Done At':(Now(),
        'Minutes Worked':ThisItem.Minutes
     }
);
Navigate(Screen1)
Replace the Check icon on the first screen with a right arrow icon.
Select the template. Add a formula for its OnSelect property, so that users are directed to the MinutesWorked screen when they tap anywhere in a template instance: Navigate(MinutesWorked).