Payroll rate calculator and master sheet in Google Sheets + Apps Script
Self-service rate sheets and live FX for payroll across 10 countries
Role: Built the sheets, the Apps Script back end, the master sheet roll-up and the live FX conversion
Supply chain problem
A multinational team needed hours from many users turned into rates, converted at live FX and rolled up into one accurate payroll across 10 countries.
- Multi-entity cost roll-up
- Multi-currency cost roll-up
- Cross-border payments
- Labour cost control
Tech I used
Self-service Google Sheets front end + Apps Script back end that calculates the rate and rolls each result up into a master sheet, where live FX from the Google Finance API converts each country's payroll
- Google Sheets
- Google Apps Script
- Google Finance API
Result
The manager reads total payable per department from one master sheet, converted at live FX, to generate the final payroll; payroll was then settled to staff across 10 countries through Airwallex, the company's payment platform. The real figures stay confidential.
Try it
Illustrative recreation, not the original implementation: sample rates and amounts; real figures are confidential to Amplify. Its onEdit trigger, custom function, protected settings tab, master sheet roll-up and GOOGLEFINANCE conversion show one way to build it; departments and currencies are placeholders. Airwallex was the company's payment platform for settling payroll; the demo only shows that hand-off.
- 1Manager shares sheets
- 2User enters hours
- 3Apps Script calculates
- 4Rate returned
- 5Master sheet roll-up
- 6Live FX conversion
- 7Settle via Airwallex
Apps Script (back end)Code.gs
function onEdit(e) { if (e.range.getColumn() !== 2) return; const row = e.range.getRow(); const hours = Number(e.value); const rate = calculateRate(hours); e.range.offset(0, 1).setValue(rate); rollUp(DEPT, hours * rate); // master sheet} // Illustrative recreation, not the original codefunction calculateRate(hours) { const s = getSettings(); // protected tab const cost = s.base + s.overhead; const off = hours >= 40 ? 0.10 : hours >= 10 ? 0.05 : 0; return round2(cost / (1 - s.margin) * (1 - off));} function rollUp(dept, payable) { const cell = findTotal(MASTER, dept); // manager view cell.setValue(cell.getValue() + payable);} function toAud(local, ccy) { const cell = FX.getRange(ccy); // FX tab // =GOOGLEFINANCE("CURRENCY:AUD" & ccy) cell.setFormula(fxFormula(ccy)); return local / cell.getValue(); // live rate}Execution log
Type the hours in the highlighted cell (B3) and press Enter, or press Auto play. The result rolls up into the master sheet below, converts at live FX and is settled via Airwallex.
Enter a number of hours between 0.5 and 200.
- AUD/USD0.6550
- AUD/VND16,850
- AUD/PHP37.40
- AUD/GBP0.5050
Sample rates and amounts; real figures confidential.
- USD
- AUD
- GBP
- PHP
- VND
- +5 countries
Settled to staff across 10 countries. Sample amounts; real figures confidential.
Admin settingssample USD, try them
How the rate is calculated
- Base cost
- 42.00/h
- Overhead
- 12.00/h
- Before discount, 25% margin
- 72.00/h
- Volume discount
- enter hours
- Rate returned
- -
- Rolled up to Operations, USD (hours x rate)
- -
Sample formula in sample USD: (base + overhead) / (1 - margin), less 5% from 10 hours and 10% from 40 hours. The master sheet then converts each department's total to AUD at live FX.
From hours to payroll
01Step 1 of 7, Google Sheets
Manager shares sheets
The manager sends out a Google Sheet to each user.
02Step 2 of 7, Google Sheets
User enters hours
The user types the number of hours into their row.
03Step 3 of 7, Google Apps Script
Apps Script calculates
Apps Script receives the entered hours, calculates the rate and writes it back.
04Step 4 of 7, Google Sheets
Rate returned
The rate is written back into the sheet for the user.
05Step 5 of 7, Google Sheets
Roll up to master sheet
Each result rolls up into the manager's master sheet, which shows the total payable for each department.
06Step 6 of 7, Google Finance API
Live FX conversion
Live Google Finance API rates convert each country's payroll in the master sheet.
07Step 7 of 7
Settled via Airwallex (10 countries)
The final payroll was settled to staff through Airwallex, the company's payment platform.
Tech stack in this project
- Google SheetsFront end: the shared sheets where users enter hours and read their rate, plus the manager's master sheet
- Google Apps ScriptBack end: calculates the rate, writes it back, rolls the result up to the master sheet and pulls live exchange rates
- Google Finance APILive exchange rates that convert each country's payroll in the master sheet
- JavaScriptLanguage of the Apps Script code
Why it matters for supply chain
- Department and country roll-up = multi-entity cost consolidation
- Live FX conversion = multi-currency landed cost
- Cross-border payroll settlement = cross-border supplier payments
Linked to my Master of Logistics & SCM, University of Sydney
Read the full case study
Problem
Amplify is multinational: the manager needed hours from many users turned into rates and rolled up into an accurate total payable per department, converted across currencies, so she could generate the final payroll across 10 countries.
Approach
- I built a Google Sheet where users entered hours and Apps Script calculated and returned a rate. The interactive code and formula below are an illustrative recreation, not the original implementation.
- The manager sends out the sheets; each user enters the number of hours.
- Each result rolls up into the manager's master sheet, which shows the total payable for each department.
- Live FX from the Google Finance API, pulled by Apps Script, converted each country's payroll in the master sheet.
- Payroll was then settled to staff across 10 countries through Airwallex, the company's payment platform.
- The original sheets, formula and figures are confidential to Amplify, so the demo uses sample rates and amounts.