All guides
Attendance guide

Export QR attendance from Google Sheets to a SIS

The sheet captures, the SIS is the record. Match the student ID, turn scans into one mark per student per session, export a CSV, and keep the raw scan log.

By the QR to Sheets team 8 min readUpdated

Short answer

To move QR attendance from Google Sheets into a student information system, turn the raw scan log into one mark per student per session, export that tab as CSV, and load it through the SIS's own attendance import.

The step that decides success is the identifier: the value inside each QR code must be the exact student ID the SIS uses, or rows fail to match.

QR to Sheets and the free Google Forms route both end in the same Google Sheet, so the export work is identical, and Google Apps Script can write the CSV on a schedule.

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.

Written by QR to Sheets. The export steps below are the same whether a Google Form or our scanner wrote the rows, because both end in an ordinary Google Sheet. We do not integrate directly with any SIS, and say so again below.

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 QR code 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. A roster tab can still show the name for each scanned ID in your reports.
  • 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.

Does the free Google Form route export to an SIS?

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 with no 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. For a quick list of who is missing on one day, rather than a mark per session, see finding who is absent. 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. First mirror the log onto a Calc tab with the timestamp converted, by putting =FILTER({ARRAYFORMULA(IFERROR(VALUE(Log!A2:A), "")), Log!B2:B}, Log!A2:A<>"") in Calc!A2. QR to Sheets writes the timestamp as YYYY-MM-DD HH:MM:SS, which Google Sheets stores as text, and COUNTIFS or MINIFS date criteria match nothing on text; VALUE() fixes that and also works on a column that already holds dates.

  • Present or absent for a given date in cell B1 of the marks tab: =IF(COUNTIFS(Calc!B:B, A2, Calc!A:A, ">="&$B$1, Calc!A:A, "<"&$B$1+1)>0, "P", "A").
  • The first scan of the day for each student, to compute late marks: =MINIFS(Calc!A:A, Calc!B:B, A2, Calc!A:A, ">="&$B$1, Calc!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. Late arrivals signed in at the front office are a separate log: tracking tardies in Google Sheets.
  • 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 two things: the timestamps converted from text with VALUE(), as the Calc tab does, and written in the school's own time zone. A timestamp compared 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 YYYY-MM-DD HH:MM:SS in the workspace timezone, stored by Google Sheets as text that VALUE() converts. If the signal drops, the open scanner keeps saving scans on the phone and sends them when the signal is back. 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.

Google Forms / Apps Script (DIY) compared with QR to Sheets
CapabilityGoogle Forms / Apps Script (DIY)QR to Sheets
CostFreeFree for 300 scans, then 7 USD per phone per month
Student ID entryTyped by the student, so mistypes reach the importScanned from the printed code
Students without phonesCannot submitNeed only a printed code; staff scan
No signalSubmission lostHeld on the phone, synced later
Timestamps as real datesYes, in the spreadsheet's time zoneNo, text in the workspace timezone; wrap it in VALUE()
Direct SIS integrationNoneNone
The export itselfCSV from Google SheetsCSV from Google Sheets

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

Does QR to Sheets connect directly to our SIS?+

No. It writes attendance scans into your own Google Sheet, and the export to a SIS is a CSV from that sheet. There is no direct integration with any student information system.

How do I mark students absent from a scan log?+

Keep a roster tab with every student's ID. A student with no scan for the session is absent, which a COUNTIFS against the log works out per student and date. A scan log alone records only who came.

What goes wrong most often with the import?+

The identifier. The value in the QR code must match the SIS's student ID exactly, leading zeros included. Generate the codes from an export of the SIS's own student list and most import failures never happen.

Can the export run automatically?+

Yes, with a Google Apps Script on a time-driven trigger that saves the marks tab as a CSV in Drive on a schedule. It is free. Someone still has to import the file into the SIS unless the SIS offers its own automated import.

How do I record late arrivals?+

Take each student's first scan of the session with MINIFS and compare it with the session start time. Anything later is a late mark. This only works on timestamps in the school's time zone, converted from text with VALUE() first, which is what the Calc tab above does.

Try it on your own Google Sheet

Connect Google, create a scanner link, and open it on the phone that will scan. Have that phone at hand: setup takes a few minutes and ends with your first row in the Sheet. Free covers 300 scans on one phone, and the people you scan never install anything.

Start for free

No credit card required

Taking attendance? See QR code attendance: who holds the phone, what is free, what a second phone costs

A second door, booth or classroom is a second phone

Free covers one phone and one scanner link. Premium lets several phones scan at the same time, one per door, booth or classroom, into the same Sheet: $7 per phone per month. Running several classes, events or lists from one phone? Premium on that one phone is $7 a month, with a separate scanner link for each. Paid yearly, it is $70 per phone per year (2 months free).

How many phones will scan at the same time?

$7 x 2 = $14 a month

Sign in with Google and, on the free plan, the billing page opens with this choice. Nothing is charged until you confirm the card checkout, and you can change the number of phones later.

Keep reading