Export QR attendance from Google Sheets to a SIS

Most schools already have a student information system, and the register that counts is the one in there. A QR scan into a Google Sheet is a fast way to capture attendance; it is not a replacement for the system of record. Treat the sheet as the capture layer and the SIS as the register, and the project stays small enough to finish.

Step 1: make the code carry the SIS identifier

Almost every failed attendance import fails on the identifier. The code says 1234, the SIS expects 001234, and every row is rejected or, worse, silently matched to nobody. Export the student list from the SIS first and generate the codes from that export, so the value printed on each card is the SIS's own ID. The free bulk generator makes one printable code per row from a pasted column.

• Keep leading zeros. Format the ID column as plain text before pasting, or Sheets will strip them.
• Never encode a name. Names are not unique, they change, and a lost card with a name on it is a privacy problem.
• If students already carry a barcode on an ID card, scan that instead of printing new codes, as long as it holds the SIS ID.

The free route: a Google Form, then the same export

A Google Form with a student ID field writes a timestamped row per submission to a linked sheet, and it costs nothing. For the export, that sheet is exactly as useful as any other. Its weak points are upstream: self-reported IDs get mistyped, which the SIS will reject, students without phones cannot submit, and a submission attempted without signal is lost. A mistyped ID is an import failure waiting to happen, which is the strongest argument for scanning a printed code rather than typing one.

Step 2: shape the log into what the SIS expects

The SIS wants one mark per student per session. The scan log has one row per scan, including repeats, and nothing at all for absent students. The marks tab bridges the two. These examples assume the log has the timestamp in column A and the student ID in column B, with a roster tab listing IDs in column A.

• Present or absent for a given date in cell B1 of the marks tab: =IF(COUNTIFS(Log!B:B, A2, Log!A:A, ">="&$B$1, Log!A:A, "<"&$B$1+1)>0, "P", "A").
• The first scan of the day for each student, to compute late marks: =MINIFS(Log!A:A, Log!B:B, A2, Log!A:A, ">="&$B$1, Log!A:A, "<"&$B$1+1).
• Late against a start time in C1: =IF(D2=0, "A", IF(D2>$B$1+$C$1, "L", "P")), where D2 holds that first scan.
• Several sessions a day: add the session boundary times and count scans between them, or give each session its own scanner link, which writes the link name into every row.

Every one of those formulas depends on the timestamps being real dates in the school's own time zone. A timestamp stored as text, or written in UTC, turns late marks into nonsense, and it is the second most common reason these exports go wrong.

Step 3: export and import

With the marks tab selected, File, Download, Comma-separated values exports the current sheet as a CSV file, which is the format most SIS bulk imports accept. Check your SIS's own import documentation for the column order, date format and mark codes it expects; they vary between products and often between schools using the same product.

• Export the marks tab, never the raw log. The SIS wants one mark per student per session, not one row per scan.
• Import one class for one day first and check it in the SIS before sending more.
• Keep the raw log after the import. If the import is wrong, the log is the evidence that lets you correct it.
• Never edit the log to make an import work. Fix the formula or the mapping instead.

Step 4: automate it if you want

A short Google Apps Script with a time-driven trigger can copy the marks tab to a dated CSV file in Drive every afternoon. It is free, it lives in the same Google account as the sheet, and it is a genuinely reasonable use of Apps Script even if a dedicated scanner handles the capture. Two cautions: someone has to own the script when its author leaves, and a school's IT policy may need to approve a script that touches student data.

Where QR to Sheets fits, and where it does not

This is the product we make. It handles the capture step: a teacher opens a scanner link on their phone, scans each student's printed code, and a row lands in the school's own Google Sheet with the timestamp written as a real date in the workspace timezone. Scans made without signal are kept on the phone and sync later. Repeat scans are saved and flagged with an amber badge, so the marks formulas above should count them, not assume one row per student.

It does not connect to any SIS. There is no direct integration, no automatic sync and no import file built for a particular product. The export is ordinary spreadsheet work, the same work the free Google Forms route needs.

Step by step

1. 1

   Put the SIS identifier in the code

   Whatever your SIS calls a student is what belongs inside the QR code, character for character, including leading zeros. Fix this at printing time; it is far more expensive at import time.

2. 2

   Keep a roster tab

   A scan log only records who came. Absence is the roster minus the scans, so copy the class or year list, with the same IDs, into its own tab.

3. 3

   Build a marks tab

   One row per student per session, with a present, absent or late mark computed from the scan log. This is the tab you export, never the raw log.

4. 4

   Match the import format

   Column order, date format and mark codes must match what your SIS import expects. Test with one class and one day before sending a term's worth.

5. 5

   Agree who exports and when

   A daily export nobody owns lapses within a fortnight. Name a person, or automate it with a time-driven Apps Script trigger.

Frequently asked questions