Sheets-Native Attendance

ONE SHEET, ONE DAY

Who is allowed to type here

Sheets are per division per month, twenty-two columns wide, data from row four. Every cell access in five thousand lines goes through one set of COL_* constants. Switch identity and watch which cells close.

Under 30 minutes, so the hourly trigger leaves the row alone. Rows with no clock-out yet are never locked by it at all.

• 07YOU CAN TYPE HERE
• fxL:O — A FORMULA, NOT A VALUE
• ×PROTECTED
• ~NO GENERATED PROTECTION

MODEL NOTEThis is a model. Nothing on this page is checking a permission — the greying-out is drawn from the same three ranges the script generates, and that is all it is. In the deployed system the answer comes from Google Sheets before a single line of the project’s own code runs, which is the reason it was built this way. A client-side check here would argue the opposite of the design.

WHAT proteksiBarisBaru GENERATES FOR ROW 4

• A:D — Admin only — A:D auto-fill baris 4

  editors: script owner + ADMIN_EMAILS

• E:O — Ayu Prameswari (baris 4)

  editors: Ayu Prameswari + script owner + ADMIN_EMAILS

• P:Q — Admin only — P:Q baris 4

  editors: script owner + ADMIN_EMAILS

Columns R to V get no generated protection. The installable edit trigger is the only thing watching them — and it is deliberately named onEditInstalled rather than onEdit, so Apps Script will not also fire it as a limited simple trigger that cannot see who the editor is.

COLUMNS L TO O

The script writes a formula, then leaves

Nothing recomputes these columns on a schedule. When a row is appended the script places live spreadsheet formulas into L, M, N and O, and the sheet evaluates them from then on. This is that formula, ported.

L4  =IF(E4<>"Hadir",0,IF(AND(B4="Sunday",Q4=""),0,IF(OR(F4="",K4=""),0,IF(AND(G4<>"",H4<>""),IF(AND(I4<>"",J4<>""),IF(K4<F4,K4+1-F4,K4-F4)-(H4-G4)-(J4-I4),IF(K4<F4,K4+1-F4,K4-F4)-(H4-G4)),IF(AND(I4<>"",J4<>""),IF(K4<F4,K4+1-F4,K4-F4)-(J4-I4),IF(K4<F4,K4+1-F4,K4-F4))))))

M4  =IF(E4="Red Day",7/24,IF(OR(P4="RED DAY",P4="RED DAY DOUBLE",P4="SAVING DAY RED DAY/SUNDAY",P4="SWAP RED DAY",P4="VACATION PAID",P4="FLEX DAY",P4="ADDITIONAL PAID",P4="MATERNITY LEAVE",P4="SICK PAID"),7/24,L4))

MODEL NOTEPorted and executing. What is not here is the spreadsheet: in the real system these are cells, recalculated by Google whenever anything they reference changes, and formatted [h]:mm so a night shift can display more than twenty-four hours.

THE MONTHLY RECAP

Classified entirely by two admin-only dropdowns

A payroll month is generated, not re-typed. Which bucket a day lands in is decided by column P (NOTE) and column Q (SUNDAY/RED DAY) — the two columns nobody but an admin can touch. Thirty fabricated days for one fabricated person.

As the code is written, 78 is compared inside a helper that only ever receives one day — a single row would need more than 78 hours of overtime for the bonus bucket to hold anything, so it holds 0.00. Reading the same day on its own: one hour at the first tier and three after it gives totalOT 4, and 0.00 bonus.

The threshold is real and the number is 78 in both systems. Where it is evaluated is the difference between a constant that documents an intention and one that changes a payslip — which is why the successor walks the month chronologically rather than asking each day about it. The second button above is a model of that behaviour, not a port of it: that engine is not running on this page. The system that replaced this one.

THE SAME SUM, WRITTEN TWICE

hitungRekap computes the totals in JavaScript. generateTemplateRekap writes a sheet that computes the same totals in SUMIFS. Both exist on purpose — one produces a static recap, the other a live one an admin can keep working in — and both have to be edited together. This is a cost the design accepted, not a feature.

total = regularHrs;
if (day === 'sunday')            total -= regularHrs;
if (q === 'SWAP')                total += regularHrs;
if (q === 'HALF DAY SUNDAY')     total += regularHrs;
if (note === 'RED DAY DOUBLE')   total -= regularHrs;

=SUMIFS(Rekap_Mar_2026!M:M,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!B:B,"<>Sunday")+SUMIFS(Rekap_Mar_2026!M:M,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!Q:Q,"SWAP")+SUMIFS(Rekap_Mar_2026!M:M,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!Q:Q,"HALF DAY SUNDAY")-SUMIFS(Rekap_Mar_2026!M:M,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!P:P,"RED DAY DOUBLE")

total = ot1Hrs;
if (day === 'sunday')            total -= ot1Hrs;
if (note === 'RED DAY DOUBLE')   total -= ot1Hrs;
if (q === 'SWAP')                total += ot1Hrs;

=SUMIFS(Rekap_Mar_2026!N:N,Rekap_Mar_2026!C:C,$C4)-SUMIFS(Rekap_Mar_2026!N:N,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!B:B,"Sunday")-SUMIFS(Rekap_Mar_2026!N:N,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!P:P,"RED DAY DOUBLE")+SUMIFS(Rekap_Mar_2026!N:N,Rekap_Mar_2026!C:C,$C4,Rekap_Mar_2026!Q:Q,"SWAP")

MODEL NOTEThe helpers on the left run here, ported. The formulas on the right are the strings the template writes; they are shown, not evaluated, because SUMIFS needs a spreadsheet. Ayu Prameswari and the thirty days behind these totals are invented, and the overtime hours are supplied rather than derived, for the reason given above.

LockService

Thirty people at 07:30

One comment in the source explains an entire design decision: thirty staff submitting at the same moment will race each other and the append trigger. Every append and submit path takes a script lock with a ten-second timeout.

1. 1 waited 0.0 seconds
2. 2 waited 0.3 seconds
3. 3 waited 0.6 seconds
4. 4 waited 0.9 seconds
5. 5 waited 1.2 seconds
6. 6 waited 1.4 seconds
7. 7 waited 1.7 seconds
8. 8 waited 2.0 seconds
9. 9 waited 2.3 seconds
10. 10 waited 2.6 seconds
11. 11 waited 2.9 seconds
12. 12 waited 3.2 seconds
13. 13 waited 3.5 seconds
14. 14 waited 3.8 seconds
15. 15 waited 4.1 seconds
16. 16 waited 4.3 seconds
17. 17 waited 4.6 seconds
18. 18 waited 4.9 seconds
19. 19 waited 5.2 seconds
20. 20 waited 5.5 seconds
21. 21 waited 5.8 seconds
22. 22 waited 6.1 seconds
23. 23 waited 6.4 seconds
24. 24 waited 6.7 seconds
25. 25 waited 7.0 seconds
26. 26 waited 7.2 seconds
27. 27 waited 7.5 seconds
28. 28 waited 7.8 seconds
29. 29 waited 8.1 seconds
30. 30 waited 8.4 seconds

Serialised. One execution holds the spreadsheet at a time, nobody is refused, and the last person waits 8.4 seconds — under the 10-second tryLock, so they get a row rather than an error.

MODEL NOTEA model, and a coarse one. Arrivals are evenly spaced, every submit is assumed to take the same 600 ms, and nothing here knows what Google would actually do with two executions writing the same range — Apps Script’s scheduler is not being emulated. What it does show is the shape of the problem the comment describes, and the price the lock charges for solving it.

THE BOUNDARY

What runs, what is modelled, what is only described

This system has no build, no tests and no way to execute outside a Google account bound to a spreadsheet. A demo claiming to run it would be claiming something impossible, so this one does not.

MODEL NOTEWhy a live simulation would misrepresent this system: its central claim is that a staff member cannot edit somebody else’s row because Google refuses the write, not because an interface declined to offer it. Rebuild that as a browser check and the demonstration proves the weaker thing while looking like the stronger one. The honest version says which is which on every panel, and this table says it once more in one place.

THE REASON THIS PAGE EXISTS

Four constants that outlived the spreadsheet

This system was replaced. What could not be replaced was the knowledge inside it: nobody had written the company's timekeeping rules down anywhere else, so they were recovered by reading this code and the payroll workbook it fed.

DAYS_HOUR.REGULAR_DAYS = 7

A full weekday credits seven hours. It is what column M pays for a Red Day and for every NOTE in the paid-off list.CONFIG.JS

DAYS_HOUR.SATURDAY = 5

A Saturday shift is five hours long — while a complete one is still credited as a full regular day. That pair is the rule, and it is only legible with both numbers in front of you.CONFIG.JS

maxOT = 78

Overtime past 78 hours moves into a separate bonus bucket instead of being paid at the normal tier.REKAP.JS, INSIDE CALCULATEOTAFTER1STHRS

SELISIH_MENIT_LOCK = 30

Thirty minutes after a clock-out, the hourly trigger protects the whole row so nobody can quietly revise the day. A different, hard-coded 30 in the web app decides when a late clock-in has to carry a reason.CONFIG.JS, OVERRIDABLE FROM A SEPARATE SETTINGS SPREADSHEET

On its own this is a superseded spreadsheet. Beside its successor it is the provenance: every one of those numbers is marked CONFIRMED in the replacement system because it was found here, in code that had been running the payroll for years, rather than assumed. 7 and 78 are not design choices somebody made twice.

WHAT REPLACED IT →