Skip to main content

Build an Overtime Timesheet App in Power Apps — Part 13: Create the Monthly Payroll Workbook with Office Scripts

· 38 min read
Jagdish Kumawat
Founder @ Dewiride

Payroll pays overtime from a spreadsheet, so on the 1st of every month they get one: an Excel workbook of the month's approved overtime, entry by entry and person by person, saved in a library only they can open, with a link in their inbox. This part builds it with an Excel template, an Office Script and two flows.

This is Part 13 of a 21-part series.

  1. Part 1: Plan the App and Set Up the Demo Organization
  2. Part 2: Create the SharePoint Site and Lists
  3. Part 3: Keep Overtime Private with SharePoint Permissions
  4. Part 4: Create the Solution, Environment Variables and Canvas App
  5. Part 5: Build the Home Screen
  6. Part 6: Build the Log Overtime Screen
  7. Part 7: Build the My Overtime Screen
  8. Part 8: Build the Approval Flow for Teams and Outlook
  9. Part 9: Show Approvers What's Waiting
  10. Part 10: Build the Departments Screen
  11. Part 11: Build the Reports Screen
  12. Part 12: Import the Old Excel Timesheet
  13. Part 13: Create the Monthly Payroll Workbook with Office Scripts (you are here)
  14. Part 14: Create the Payroll Workbook from the App
  15. Part 15: Build the Power BI Overtime Report (coming soon)
  16. Part 16: Share the App and Add It to Microsoft Teams (coming soon)
  17. Part 17: Test Every Role and Department (coming soon)
  18. Part 18: Build a Setup Flow That Creates the Lists (coming soon)
  19. Part 19: Run the Solution Checker and Export the Solutions (coming soon)
  20. Part 20: Install the App in a New Environment (coming soon)
  21. Part 21: Recap, Production Checklist and Next Steps (coming soon)

The Reports screen from Part 11 shows payroll each month's totals, but payroll's own process runs on spreadsheets. Part 2 created the Payroll Exports library for them, and Part 3 closed it to everyone except the Overtime Payroll group. This part fills it: a workbook per month, built from a template by an Office Script, by a child flow that any other flow can call. A scheduled flow calls it on the 1st and emails payroll; Part 14 adds a button to the Reports screen that calls it on demand. Each generic task has its own guide; this part gives the values for this app, and why.

What You'll Build​

The two flows, action by action:

Create Payroll Workbook (child flow)
Manually trigger a flow Month, such as 2026-10
├─ Get the approved entries Get items: that month, approved twice
├─ Payroll rows Select: one row per entry, times in UK time
├─ Get the template Get file content using path: Setup/Payroll Template.xlsx
├─ Create the workbook Create file: Overtime 2026-10 created 2026-11-20 10.29.xlsx
├─ Fill the workbook Run script from SharePoint library: Fill Payroll Workbook
├─ Get the workbook link Get file properties: the new file's link
└─ Respond to a Power App or flow fileName, link, entries
Monthly Payroll Workbook (scheduled flow)
Recurrence the 1st of every month, 07:00, UK time
├─ Last month Compose: last month as yyyy-MM
├─ Create the workbook Run a Child Flow: Create Payroll Workbook
├─ Get the payroll group setting Get items: Settings, PayrollGroupId
├─ List the payroll members List group members: Overtime Payroll
├─ Payroll emails Select: each member's email
└─ Email payroll Send an email (V2): the link to the workbook

October 2026's workbook, made by the child flow:

The Entries sheet of the October 2026 workbook: the title Approved overtime: October 2026, a table of 11 approved entries with Date, Employee, Email, Department, Start, End, Break (mins), Hours and Approved by, and a Total row of 46.25 hours

How It Works​

The decisions behind it:

  • One child flow does the work. Making the workbook is the same whoever asks for it, so it lives in one child flow, Create Payroll Workbook, that takes a month and returns the file's name, link and number of entries. The monthly flow calls it, and so does Part 14's button.
  • A template keeps the layout out of the code. The workbook's sheets, tables, titles, number formats and total rows are in Payroll Template.xlsx. The flow copies it, and an Office Script only adds the rows.
  • One script call per workbook. The flow sends the month's entries to the script as one list, and the script writes them all. Microsoft's Excel Online (Business) connector reference allows up to 3 calls per 10 seconds and up to 1600 calls per day to Run script, so a call per row would soon hit the limits.
  • The same entries as the reports. An entry counts when it's approved twice, in Status and in SharePoint's approval, as Part 11 decided. The workbook reads them the same way, so its total matches the Reports screen.
  • UK times. SharePoint stores times in UTC. The flow turns them into UK time before the script writes them, so payroll reads the clock times people worked.
  • A new file every time. Each workbook's name has the month and the time it was made, such as Overtime 2026-10 created 2026-11-20 10.29.xlsx, so a second run never overwrites a workbook payroll may already be using.
  • Last month, on the 1st. The monthly flow runs at 07:00 UK time on the 1st and makes the workbook for the month that has just ended.

Prerequisites​

Step 1: Make the Template Workbook​

The template lives with the workbooks, in the Payroll Exports library, but in a folder of its own. Open the library, create a folder called Setup (How to Create a Folder in a SharePoint Document Library), open it, and create an Excel workbook in it called Payroll Template (How to Create an Excel Workbook in a SharePoint Document Library).

The workbook has two sheets, each with a title in A1, in bold, and a table whose header row is row 3 and which has one empty row (How to Rename a Worksheet in Excel for the Web, How to Add a Worksheet in Excel for the Web):

SheetA1TableColumns
EntriesApproved overtimeEntries, A3:I4Date, Employee, Email, Department, Start, End, Break (mins), Hours, Approved by
TotalsApproved overtime by personTotals, A3:E4Employee, Email, Departments, Entries, Hours

To make each table, I typed the headers in row 3, selected the headers and the empty row under them, and chose Insert > Table with My table has headers ticked (How to Format Data as a Table in Excel):

The Create Table dialog: The data for your table is in the following range: A3, with My table has headers ticked, and the OK and Cancel buttons

Then I named the tables Entries and Totals on Table Design (How to Rename a Table in Excel): the script finds them by these names.

Both tables have a Total Row (How to Add a Total Row to an Excel Table), with these totals:

TableColumnTotal
EntriesHours=SUBTOTAL(109,[Hours])
TotalsEntries=SUBTOTAL(109,[Entries])
TotalsHours=SUBTOTAL(109,[Hours])

The total row took two goes. Turned on in the empty template, Excel gave the last column Count, and the first workbook's total under Hours said 4 instead of 46.25 (Fixed: Excel Table Total Row Counts Instead of Adding). Each total is now a sum of its whole column, typed with the column's name, and the other total cells are empty. I gave the Hours columns' totals the Number format, so they show two decimals.

Leave the template open on the Entries sheet, at A1: copies open where the template was left.

Step 2: Write the Office Script​

In the template, select Automate > New Script > Create in Code Editor (How to Create and Run an Office Script in Excel). The first time, Excel offered Turn actions into scripts, and I chose Write a script. Name the script Fill Payroll Workbook.

The script takes two inputs from the flow and returns one number (How to Pass a Value to an Office Script from Power Automate, How to Return a Value from an Office Script to Power Automate). It starts with the shape of an entry, as the flow will send it, and of a person's totals (How to Pass a List of Records to an Office Script from Power Automate):

Fill Payroll Workbook (1 of 5): the records
interface PayrollEntry {
date: string;
employee: string;
email: string;
department: string;
start: string;
end: string;
breakMinutes: number;
hours: number;
approvedBy: string;
}

interface PersonTotal {
employee: string;
email: string;
departments: string[];
entries: number;
hours: number;
}

main checks that both tables exist, fills them when there are entries, writes the titles and returns how many entries it wrote:

Fill Payroll Workbook (2 of 5): main
/**
* Fills a copy of the payroll template with one month's approved overtime.
* @param monthTitle The month in words, for the sheet titles, such as "October 2026".
* @param entries The month's approved entries, with dates and times in UK time.
* @returns The number of entries written.
*/
function main(workbook: ExcelScript.Workbook, monthTitle: string, entries: PayrollEntry[]): number {
const entriesTable = workbook.getTable("Entries");
const totalsTable = workbook.getTable("Totals");
if (!entriesTable || !totalsTable) {
throw new Error("The workbook needs the tables Entries and Totals from the payroll template.");
}

if (entries.length > 0) {
fillWorkbook(entriesTable, totalsTable, entries);
}

// The titles go in last, so that the column widths fit the tables, not the titles.
workbook.getWorksheet("Entries").getRange("A1").setValue(`Approved overtime: ${monthTitle}`);
workbook.getWorksheet("Totals").getRange("A1").setValue(`Approved overtime by person: ${monthTitle}`);
workbook.getWorksheet("Entries").activate();
return entries.length;
}
  • workbook.getWorksheet("Entries"), by name, and never the active sheet: in a flow, Microsoft's troubleshooting article says getActiveWorksheet returns the first sheet or the last one activated.
  • activate() at the end makes the copy open on Entries.
  • The titles go in last for a reason Step 10 explains.

fillWorkbook writes one row per entry, formats the dates, times and hours, then adds up each person's entries and hours, and writes one row per person, the most hours first:

Fill Payroll Workbook (3 of 5): fillWorkbook
// Writes the entries, one row each, and the totals, one row per person.
function fillWorkbook(entriesTable: ExcelScript.Table, totalsTable: ExcelScript.Table, entries: PayrollEntry[]) {
// One row per entry. Dates and times become Excel date numbers.
fillTable(entriesTable, entries.map((e) => [
toExcelDate(e.date), e.employee, e.email, e.department,
toExcelDate(e.start), toExcelDate(e.end), e.breakMinutes, e.hours, e.approvedBy
]));
entriesTable.getColumnByName("Date").getRangeBetweenHeaderAndTotal().setNumberFormat("dd/mm/yyyy");
entriesTable.getColumnByName("Start").getRangeBetweenHeaderAndTotal().setNumberFormat("dd/mm/yyyy hh:mm");
entriesTable.getColumnByName("End").getRangeBetweenHeaderAndTotal().setNumberFormat("dd/mm/yyyy hh:mm");
entriesTable.getColumnByName("Hours").getRangeBetweenHeaderAndTotal().setNumberFormat("0.00");

// One row per person: their departments, number of entries and hours.
const people: PersonTotal[] = [];
for (const e of entries) {
const key = (e.email || e.employee).toLowerCase();
let person = people.find((p) => (p.email || p.employee).toLowerCase() === key);
if (!person) {
person = { employee: e.employee, email: e.email, departments: [], entries: 0, hours: 0 };
people.push(person);
}
if (person.departments.indexOf(e.department) < 0) {
person.departments.push(e.department);
}
person.entries += 1;
person.hours += e.hours;
}
people.sort((a, b) => b.hours - a.hours);
fillTable(totalsTable, people.map((p) => [
p.employee, p.email, p.departments.sort().join(", "), p.entries, Math.round(p.hours * 100) / 100
]));
totalsTable.getColumnByName("Hours").getRangeBetweenHeaderAndTotal().setNumberFormat("0.00");

entriesTable.getRange().getFormat().autofitColumns();
totalsTable.getRange().getFormat().autofitColumns();
}
  • A person is their email, or their name when there's no email: Part 12's leavers have none.
  • getRangeBetweenHeaderAndTotal() is a column's data, without its header and total.
  • autofitColumns() fits the widths to the data (How to AutoFit Columns with an Office Script).

fillTable adds the rows to a table in one call (How to Add Rows to an Excel Table with an Office Script). The template's table starts with one empty row; once the real rows are in, it removes it:

Fill Payroll Workbook (4 of 5): fillTable
// Adds the rows to the table, then removes the empty row the template's table starts with.
function fillTable(table: ExcelScript.Table, rows: (string | number)[][]) {
const rowsBefore = table.getRowCount();
table.addRows(0, rows);
if (rowsBefore === 1) {
const lastRow = table.getRangeBetweenHeaderAndTotal().getLastRow().getValues()[0];
if (lastRow.every((value) => value === "")) {
table.deleteRowsAt(rows.length, 1);
}
}
}

toExcelDate turns the flow's UK dates and times into Excel's day numbers, so the cells hold real dates (How to Write a Date to a Cell with an Office Script):

Fill Payroll Workbook (5 of 5): toExcelDate
// "2026-10-02" or "2026-10-02T19:00", in UK time, to an Excel date number.
// Excel numbers dates in days, and 1 January 1970 is day 25569.
function toExcelDate(text: string): number {
const iso = text.length === 10 ? `${text}T00:00:00Z` : `${text}:00Z`;
return Date.parse(iso) / 86400000 + 25569;
}

The Z here doesn't make the times UTC. It stops JavaScript from shifting them by the time zone of wherever the script runs, so 19:00 UK time stays 19:00 in the cell.

The whole script, as saved:

The Fill Payroll Workbook script's main function in a code window: the JSDoc comment, main with monthTitle and entries, the check for the Entries and Totals tables, fillWorkbook, the two titles, activate and return entries.length

Fill Payroll Workbook
interface PayrollEntry {
date: string;
employee: string;
email: string;
department: string;
start: string;
end: string;
breakMinutes: number;
hours: number;
approvedBy: string;
}

interface PersonTotal {
employee: string;
email: string;
departments: string[];
entries: number;
hours: number;
}

/**
* Fills a copy of the payroll template with one month's approved overtime.
* @param monthTitle The month in words, for the sheet titles, such as "October 2026".
* @param entries The month's approved entries, with dates and times in UK time.
* @returns The number of entries written.
*/
function main(workbook: ExcelScript.Workbook, monthTitle: string, entries: PayrollEntry[]): number {
const entriesTable = workbook.getTable("Entries");
const totalsTable = workbook.getTable("Totals");
if (!entriesTable || !totalsTable) {
throw new Error("The workbook needs the tables Entries and Totals from the payroll template.");
}

if (entries.length > 0) {
fillWorkbook(entriesTable, totalsTable, entries);
}

// The titles go in last, so that the column widths fit the tables, not the titles.
workbook.getWorksheet("Entries").getRange("A1").setValue(`Approved overtime: ${monthTitle}`);
workbook.getWorksheet("Totals").getRange("A1").setValue(`Approved overtime by person: ${monthTitle}`);
workbook.getWorksheet("Entries").activate();
return entries.length;
}

// Writes the entries, one row each, and the totals, one row per person.
function fillWorkbook(entriesTable: ExcelScript.Table, totalsTable: ExcelScript.Table, entries: PayrollEntry[]) {
// One row per entry. Dates and times become Excel date numbers.
fillTable(entriesTable, entries.map((e) => [
toExcelDate(e.date), e.employee, e.email, e.department,
toExcelDate(e.start), toExcelDate(e.end), e.breakMinutes, e.hours, e.approvedBy
]));
entriesTable.getColumnByName("Date").getRangeBetweenHeaderAndTotal().setNumberFormat("dd/mm/yyyy");
entriesTable.getColumnByName("Start").getRangeBetweenHeaderAndTotal().setNumberFormat("dd/mm/yyyy hh:mm");
entriesTable.getColumnByName("End").getRangeBetweenHeaderAndTotal().setNumberFormat("dd/mm/yyyy hh:mm");
entriesTable.getColumnByName("Hours").getRangeBetweenHeaderAndTotal().setNumberFormat("0.00");

// One row per person: their departments, number of entries and hours.
const people: PersonTotal[] = [];
for (const e of entries) {
const key = (e.email || e.employee).toLowerCase();
let person = people.find((p) => (p.email || p.employee).toLowerCase() === key);
if (!person) {
person = { employee: e.employee, email: e.email, departments: [], entries: 0, hours: 0 };
people.push(person);
}
if (person.departments.indexOf(e.department) < 0) {
person.departments.push(e.department);
}
person.entries += 1;
person.hours += e.hours;
}
people.sort((a, b) => b.hours - a.hours);
fillTable(totalsTable, people.map((p) => [
p.employee, p.email, p.departments.sort().join(", "), p.entries, Math.round(p.hours * 100) / 100
]));
totalsTable.getColumnByName("Hours").getRangeBetweenHeaderAndTotal().setNumberFormat("0.00");

entriesTable.getRange().getFormat().autofitColumns();
totalsTable.getRange().getFormat().autofitColumns();
}

// Adds the rows to the table, then removes the empty row the template's table starts with.
function fillTable(table: ExcelScript.Table, rows: (string | number)[][]) {
const rowsBefore = table.getRowCount();
table.addRows(0, rows);
if (rowsBefore === 1) {
const lastRow = table.getRangeBetweenHeaderAndTotal().getLastRow().getValues()[0];
if (lastRow.every((value) => value === "")) {
table.deleteRowsAt(rows.length, 1);
}
}
}

// "2026-10-02" or "2026-10-02T19:00", in UK time, to an Excel date number.
// Excel numbers dates in days, and 1 January 1970 is day 25569.
function toExcelDate(text: string): number {
const iso = text.length === 10 ? `${text}T00:00:00Z` : `${text}:00Z`;
return Date.parse(iso) / 86400000 + 25569;
}

Select Save script. When it saved, the Code Editor reformatted the code to four-space indents and said it had made 66 formatting edits; the code above is the saved version.

Step 3: Keep the Script with the Template​

A new script is saved in your own OneDrive, and would stop working for everyone the day your account goes. Move it into the Setup folder of Payroll Exports, next to the template (How to Save an Office Script to SharePoint). The Code Editor's menu had no Move command, and its Make a copy, which I tried first, made Copy of Fill Payroll Workbook in my OneDrive at once; I deleted the copy. Move script is on the script's details page: All scripts, the script's name, ..., Move script. In the picker, Payroll Exports was under Quick access, as Overtime Timesheet - Payroll Exports; I opened its Setup folder and selected Save:

The Fill Payroll Workbook script after the move: its path starts sites » OvertimeTimesheet » Pa…, the message Script moved successfully., and File path sites » OvertimeTimesheet » PayrollExports »

The library now holds the template, the script, and, from Step 7 on, the workbooks. Only Overtime Payroll can read it, and the flows reach it with their owner's connections.

Step 4: Create the Child Flow​

In the Overtime Timesheet solution, select New > Automation > Cloud flow > Instant, name it Create Payroll Workbook, and choose Manually trigger a flow: a child flow must have that trigger and be in a solution (How to Call a Child Flow in Power Automate). Give the trigger one text input, Month, described as The month as year and month, such as 2026-10 (How to Add a Text Input to a Manually Triggered Flow in Power Automate):

The Manually trigger a flow trigger with a text input called Month, described as The month as year and month, such as 2026-10

The new flow opened in the classic designer. I added the input and an empty Respond to a Power App or flow, saved, and opened the flow again from the solution, with Edit on its own page, to work in the new designer, where the solution's environment variables are in dynamic content (How to Create an Instant Cloud Flow Inside a Solution in Power Automate).

Step 5: Get the Month's Approved Entries​

Add Get items from SharePoint and rename it Get the approved entries. Site Address and List Name are the Overtime Timesheet Site and OvertimeEntries variables (How to Use Environment Variables in a Power Automate Flow). Under Advanced parameters:

ParameterValue
Filter QueryWorkMonth eq 'Month' and Status eq 'Approved' and OData__ModerationStatus eq 0
Order ByStartTime
Top Count5000

The Get the approved entries pane: Site Address and List Name set to the Overtime Times… and OvertimeEntries… variables, Filter Query WorkMonth eq ' then the Month token then ' and Status eq 'Approved' and OData__ModerationStatus eq 0, Order By StartTime and Top Count 5000, connected to Overtime Timesheet SharePoint

Check the line under the action. Every SharePoint action I added picked a connection reference from another solution, and I changed each one to Overtime Timesheet SharePoint (Fixed: A Flow in a Solution Uses Another Solution's Connection Reference in Power Automate).

Step 6: Shape the Rows for the Script​

Add a Select called Payroll rows (How to Keep Several Fields from Each Item with Select in Power Automate). From is the value of Get the approved entries, and Map has one key for each field of the script's PayrollEntry, with exactly its names:

KeyValue
dateWorkDate
employeeEmployeeName
emailcoalesce(item()?['EmployeeEmail'], '')
departmentDepartment
startconvertFromUtc(item()?['StartTime'], 'GMT Standard Time', 'yyyy-MM-ddTHH:mm')
endconvertFromUtc(item()?['EndTime'], 'GMT Standard Time', 'yyyy-MM-ddTHH:mm')
breakMinutesBreakMinutes
hoursHours
approvedBycoalesce(item()?['DecisionBy'], '')

The Payroll rows Select: From body/value, and Map with date WorkDate, employee EmployeeName, email coalesce(...), department Department, start and end convertFromUtc..., breakMinutes BreakMinutes, hours Hours and approvedBy coalesce(...)

  • convertFromUtc(..., 'GMT Standard Time', 'yyyy-MM-ddTHH:mm') turns each UTC time into UK time, in the form the script expects (How to Convert UTC to Local Time in Power Automate with convertFromUtc).
  • coalesce(..., '') gives empty text instead of nothing for a leaver's email or an entry without a decision, so every field the script reads is a string.
  • WorkDate, a date-only column, came through in the run as 2026-10-02: already the form toExcelDate reads.

Step 7: Copy the Template​

Add Get file content using path from SharePoint, called Get the template: the Overtime Timesheet Site variable, and the file picked with the folder icon, /PayrollExports/Setup/Payroll Template.xlsx (How to Read a File from SharePoint in Power Automate):

The Get the template pane: Site Address set to the Overtime Times… variable, File Path /PayrollExports/Setup/Payroll Template.xlsx, and Infer content type Yes

Then add Create file from SharePoint, called Create the workbook (How to Create a File in SharePoint in Power Automate). The search listed OneDrive's Create file first, and I added that one by mistake: it asked me to sign in to OneDrive, so I deleted it and added the one under SharePoint.

ParameterValue
Site AddressThe Overtime Timesheet Site variable
Folder Path/PayrollExports, picked with the folder icon
File NameOvertime Monthcreatedthe expression below.xlsx
File ContentFile Content of Get the template
Create the workbook → File Name (the expression)
convertFromUtc(utcNow(), 'GMT Standard Time', 'yyyy-MM-dd HH.mm')

The time in the name uses a full stop, HH.mm, because a colon can't go in a file name.

The Create the workbook pane: Site Address set to the Overtime Times… variable, Folder Path /PayrollExports, File Name Overtime, the Month token, created, a convertFromUtc… expression and .xlsx, and File Content set to the File Content token

My first File Name ended inside the expression: I typed .xlsx while the expression's editor was still open, and Code view showed …'yyyy-MM-dd HH.mm').xlsx}, which isn't a valid expression. Editing the token didn't fix it, so I emptied the box and built it again, typing .xlsx only after the token, at the end of the box. Check Code view after building a mixed box like this one.

Step 8: Run the Script on the Copy​

Add Run script from SharePoint library from Excel Online (Business), called Fill the workbook (How to Run an Office Script from a SharePoint Library in Power Automate):

ParameterValue
Workbook LocationThe Overtime Timesheet Site variable, as a custom value
Workbook LibraryPayroll Exports
WorkbookId of Create the workbook
Script LocationThe Overtime Timesheet Site variable
Script LibraryPayroll Exports
Script/Setup/Fill Payroll Workbook.osts, picked with the folder icon
monthTitleformatDateTime(concat(triggerBody()?['text'], '-01'), 'MMMM yyyy')
entriesOutput of Payroll rows, after Switch to input entire array

The Fill the workbook pane: Workbook Location and Script Location set to the Overtime Times… variable, Workbook Library and Script Library Payroll Exports, Workbook body/Id, Script /Setup/Fill Payroll Workbook.osts, ScriptParameters/monthTitle formatDateTime..., and ScriptParameters/entries holding the Payroll rows output as a whole array

Workbook Library and Script are stored by their IDs in this environment, not by their names. When the solution is installed somewhere else, in Part 20, they have to be picked again.

Add Get file properties from SharePoint, called Get the workbook link: the Overtime Timesheet Site and Payroll Exports variables, and Id set to ItemId of Create the workbook (How to Get a Link to a SharePoint File in Power Automate):

The Get the workbook link pane: Site Address set to the Overtime Times… variable, Library Name set to the Payroll Exports (… variable, and Id set to body/ItemId

Then fill Respond to a Power App or flow with three outputs:

OutputTypeValue
fileNameTextName of Create the workbook
linkTextLink to item of Get the workbook link
entriesNumberresult of Fill the workbook, the number the script returned

The Respond to a Power App or flow action with the outputs fileName body/Name, link Link to item, and entries body/result

Save and publish the flow.

Step 10: Test the Child Flow​

Test the flow with Month 2026-10 (How to Test a Cloud Flow Manually in Power Automate):

The Run flow panel for Create Payroll Workbook, owner Admin User, with Month 2026-10 and the description The month as year and month, such as 2026-10

The first run succeeded, with 11 entries. Fill the workbook took 11 seconds:

The run of Create Payroll Workbook: Your flow ran successfully., with green ticks on every action, Get the approved entries 0.7s, Create the workbook 2s, Fill the workbook 11s and Get the workbook link 0.1s

A successful run isn't the same as a right workbook. Opening the copy showed three problems:

  • The total of hours on Totals was 4. The total row was set to Count, from when I turned it on in the empty template. Step 1's sums fixed it (Fixed: Excel Table Total Row Counts Instead of Adding).
  • Column A was as wide as the title. My first script wrote the titles, then autofitted the tables, and autofitColumns() fits a column to everything in it, including the title in A1, above the table. The script now autofits first and writes the titles last (How to AutoFit Columns with an Office Script).
  • The copy opened on Totals, where I'd left the template. The script now ends with activate() on Entries, and I saved the template on Entries, at A1.

I deleted the wrong workbook from the library, fixed the template and the script, and ran the flow again. The second workbook's Entries sheet is the one at the top of this part, with every approved October entry in UK time and a total of 46.25 hours. Its Totals sheet has one row per person:

The Totals sheet of the October 2026 workbook: Approved overtime by person: October 2026, and a table of Aisha Khan with 4 entries and 16.50 hours in Emergency Department, Radiology, Ben Harris 3 and 16.00, Maya Patel 3 and 11.75, Admin User 1 and 2.00, and a Total row of 11 entries and 46.25 hours

46.25 hours from 11 entries by 4 people: the same as October on the Reports screen in Part 12.

Step 11: Let Run-Only Users Run It with Its Own Connections​

Part 14's button runs this child flow for the payroll officer, through another flow. Microsoft's Create child flows says a child flow that uses anything other than built-in actions or Dataverse must use the connections embedded in it: child workflows only support embedded connections.

On the child flow's page, select Edit on the Run only users tile, and for SharePoint and Excel Online (Business) choose Use this connection instead of Provided by run-only user (How to Choose the Connections Run-Only Users Use in Power Automate). A dialog, Connections Used, said Users with run-only access will not control or have access to these connections outside of this flow; I selected OK, then Save. Opened again, the panel shows the owner's connections:

The Connections Used panel: SharePoint and Excel Online (Business), each with Access to this connection is provided by the owner of the flow. and Use this connection (admin@example.com)

The child flow itself isn't shared with anyone: people run it only through the flows that call it.

Step 12: Create the Monthly Flow​

In the solution, create a scheduled cloud flow called Monthly Payroll Workbook, starting on 1 December 2026 at 07:00 and repeating every 1 Month (How to Run a Flow Once a Month in Power Automate). The dialog saved the start as a UTC time, with no time zone, so I opened the Recurrence trigger and set:

  • Time zone: (UTC+00:00) Dublin, Edinburgh, Lisbon, London, so 07:00 stays 07:00 UK time after the clocks change.
  • Start time: 2026-11-01T07:00:00, with no Z. A start in the past is fine: Microsoft's schedule reference says the Recurrence trigger discards past run times and runs at the next future one, 1 December. A future monthly start, on the other hand, should be set up at least a month ahead, or the first run may be skipped.

The Recurrence trigger in the classic designer: Interval 1, Frequency Month, Time zone (UTC+00:00) Dublin, Edinburgh, Lisbon, London, and Start time 2026-11-01T07:00:00

Add a Compose called Last month with the month that has just ended (How to Get the First Day of the Month in Power Automate with startOfMonth, How to Add Months to a Date in Power Automate with addToTime):

Last month → Inputs
formatDateTime(addToTime(startOfMonth(convertFromUtc(utcNow(), 'GMT Standard Time')), -1, 'Month'), 'yyyy-MM')

Read from the inside out: the time now in the UK, the first day of its month, one month before that, as 2026-10. Starting from the 1st means the month before always exists, whatever the date.

Then Run a Child Flow, called Create the workbook: Child Flow Create Payroll Workbook, and Month the Outputs of Last month (How to Call a Child Flow in Power Automate):

The Create the workbook action: Run a Child Flow with Child Flow Create Payroll Workbook and Month set to the Outputs token

Step 13: Email the Payroll Group​

The email goes to whoever is in Overtime Payroll that day, read from the group itself:

ActionNameSettings
Get items (SharePoint)Get the payroll group settingThe Settings variable; Filter Query Title eq 'PayrollGroupId'; Top Count 1, as in Part 11
List group members (Office 365 Groups)List the payroll membersGroup Id: first(body('Get_the_payroll_group_setting')?['value'])?['Value'] (How to List the Members of a Group in Power Automate)
SelectPayroll emailsFrom the value of the members; text mode, item()?['mail'] (How to Pick One Field from Each Item with Select in Power Automate)
Send an email (V2)Email payrollBelow

In Email payroll:

Email payroll → Body (code view)
<p>The overtime workbook for @{formatDateTime(concat(outputs('Last_month'), '-01'), 'MMMM yyyy')} is ready. It lists @{body('Create_the_workbook')?['entries']} approved entries.</p><p><a href="@{body('Create_the_workbook')?['link']}">@{body('Create_the_workbook')?['filename']}</a></p>

The Email payroll pane: To join(...), Subject Overtime for formatDateTime... is ready, Body The overtime workbook for formatDateTime... is ready. It lists entries approved entries. and the fileName token, and Importance Normal, connected to Office 365 Outlook

Only the payroll group gets the link, and Part 3 closed the library it points to to everyone else on staff.

Step 14: Test the Monthly Flow​

Save, publish, and test the flow now instead of waiting for the 1st. The run made October's workbook and sent the email:

The run of Monthly Payroll Workbook: Your flow ran successfully., with green ticks on Recurrence, Last month, Create the workbook 12s, Get the payroll group setting, List the payroll members, Payroll emails and Email payroll

In the run, Create the workbook showed No outputs, although Email payroll used its outputs, and the email had them all.

Leo Martin, the payroll officer, had the email in his inbox, with the month, the number of entries and the workbook's link:

The email Overtime for October 2026 is ready from Admin User to Leo Martin at 10:58, saying The overtime workbook for October 2026 is ready. It lists 11 approved entries., and a link Overtime 2026-10 created 2026-11-20 10.58.xlsx

What Payroll Gets​

  • A workbook per month, in Payroll Exports, named with the month and when it was made.
  • Entries: every approved entry, with UK dates and times, break, hours and who approved it, totalled at the bottom.
  • Totals: one row per person, with their departments, number of entries and hours, the most hours first.
  • An email on the 1st at 07:00 UK time, with a link only they can open.

Common Mistakes​

MistakeWhat happensDo this instead
Turning on a table's total row while it's emptyExcel can choose Count, and the total shows the number of rowsSet each total to a sum of the column, SUBTOTAL(109,[Hours])
Writing the titles before autofittingColumn A is as wide as the titleAutofit first, titles last
getActiveWorksheet() in a script for a flowThe script writes on the first sheet, or the last activatedgetWorksheet("<name>")
Running the script on the templateEvery month's rows end up in the templateWorkbook = the new file's Id
A Select key that differs from the script's field, such as Email for emailThe script reads nothing for that fieldUse the interface's names exactly
Leaving the array parameter on Add new itemThe flow builds the list item by itemSwitch to input entire array
Leaving the child flow's connections as Provided by run-only userThe parent fails: child flows only support embedded connectionsUse this connection
A start time with Z and a time zoneThe time zone is ignored2026-11-01T07:00:00, no Z

Troubleshooting​

SymptomLikely causeFix
A total of hours that equals the number of peopleThe total row is Count=SUBTOTAL(109,[Hours])
The script fails: The workbook needs the tables Entries and TotalsThe template's tables have other namesRename them Entries and Totals
Times an hour out in summerThe rows were written in UTCconvertFromUtc(..., 'GMT Standard Time', ...) in Payroll rows
Run script fails with BadGatewayMicrosoft's limits article: the workbook isn't a standard Excel workbookSave the template as a standard .xlsx
The script isn't in ScriptScript Library is a different libraryPick Payroll Exports, then the Setup folder
An empty workbook for a monthNo entries approved twice that monthCheck the month on the Reports screen
Nobody gets the emailOvertime Payroll has no members, or PayrollGroupId is wrongCheck the group and the Settings item
No outputs on Run a Child Flow in the runThe parent's run view doesn't show themCheck the actions that use them, such as the email

FAQ​

Why a template and a script, and not Excel actions in the flow?​

The Excel connector's row actions write one row per call, and Microsoft's reference lists their limits and a delay of up to 30 seconds for changes to take effect. One script call writes every row and the totals at once, and the template keeps the formatting out of the flow.

Can payroll change the template?​

Only someone who can edit Payroll Exports. Changes to formatting carry into the next workbook. Renaming the tables or their columns breaks the script, so keep those.

What if a month has no approved entries?​

The script is written for it: with no entries, it writes only the titles and returns 0, so the email says the workbook lists 0 approved entries.

Does this need premium licences?​

No. SharePoint, Excel Online (Business), Office 365 Groups and Office 365 Outlook are standard connectors. The scheduled flow runs on its owner's licence, and Office Scripts need the business Microsoft 365 licence in the prerequisites.

Can it make a workbook for any other month?​

Yes: run the child flow with that month, or use Part 14's button on the Reports screen.

Conclusion​

Payroll now gets a workbook every month:

  • Payroll Template.xlsx in the Setup folder of Payroll Exports, with an Entries and a Totals table whose total rows add up;
  • Fill Payroll Workbook, an Office Script saved with it, that writes a month's entries and each person's totals in one call;
  • Create Payroll Workbook, a child flow that reads the month's approved entries, turns them into UK times, copies the template, runs the script and returns the new file's name, link and count, with its own connections;
  • Monthly Payroll Workbook, a scheduled flow that runs on the 1st at 07:00 UK time for the month just ended and emails the Overtime Payroll group the link.

Part 14 adds a Create payroll workbook button to the Reports screen, so payroll can make the workbook for any month on demand.

Additional Resources​

← Previous: Part 12: Import the Old Excel Timesheet Next → Part 14: Create the Payroll Workbook from the App