How to Automate Attendance System with Google Sheets and Apps Script

ATTEAUT is an attendance form built around one idea: the person marking attendance should not be able to type anything that matters. No name, no date, no time. They enter a PIN and press submit, and the record writes itself.

The PIN does the identifying

Each employee has a PIN held against their name in the sheet. Type it and the name appears on the form; nobody chooses a name from a list and nobody spells one. That closes the two usual holes at once — marking somebody else present, and creating a second version of a person through a typo. Adding staff is a name and a PIN in a row.

The clock is not theirs to set

The date and time show on the form but are read only, so an attendance stamped at 9:02 was genuinely submitted at 9:02. There is a deliberate exception: a manual mode where a real arrival time can be typed in, for the late entry that has to be recorded after the fact. It is a separate action, which is exactly how an exception should work — available, but visible.

What the sheet keeps

Every submission lands with a timestamp, the date, whether it was a sign in or a sign out, the log time and the name, plus the Google account of whoever submitted it. A separate sheet uses a UNIQUE formula to list each employee once no matter how many entries they have, which is the view you want when you are looking at people rather than punches.

Setting it up

Unusually for these files, there is no spreadsheet ID to replace. Copy the sheet, open Apps Script from Extensions, press Run, grant the permissions and deploy. The layout is Bootstrap, so restyling it for a school or an office is ordinary CSS.