Subcontractor compliance tracker: free Google Sheets template with QR capture and expiry alerts featured image
Templates & Resources

Subcontractor compliance tracker: free Google Sheets template with QR capture and expiry alerts

TrainAR Team 22 days ago 5 min read

Subcontractor compliance tracker: free Google Sheets template with QR capture and expiry alerts

Category: Templates & Resources • Niche: subcontractor compliance, CSCS, right to work, insurance, Google Sheets, QR workflows, GDPR

Contents

Site manager with a phone showing a Google Sheets subcontractor compliance dashboard with traffic-light statuses

Quick answer

  • Use a single Google Sheet as your source of truth, with one row per subcontractor and columns for CSCS, right to work, insurance, RAMS, inductions and expiry dates.
  • Collect documents via a Google Form that you link to a QR sign at the site gate. Form uploads feed straight into the Sheet.
  • Set conditional formatting for traffic lights (green/amber/red) and use a simple Apps Script to email you 30 days before any expiry.
  • Keep it simple: one sheet for roster, one for submissions, one for a dashboard. Share view-only with clients if needed.

Who this is for

Small contractors, site managers and QSs who need a quick, low-cost way to stay compliant without buying software. Works for one site or multiple crews. If you already run WhatsApp groups for teams, this plugs in easily.

What you need to track for UK compliance

At a minimum track:

  • Identity and contact: company name, trade, lead contact, phone, email.
  • CSCS/CPCS and competence: card number, type, expiry date. Use CSCS Card Checker to verify.
  • Right to work: status checked, evidence retained note, next review date. Follow GOV.UK right to work checks guidance.
  • Insurance: public liability and, if applicable, employers’ liability. Record insurer, policy number, cover level, expiry date.
  • RAMS/permits: latest RAMS received date, version, permit to work where applicable.
  • Induction and toolbox talks: last induction date, key topics.
  • CIS/VAT basics: CIS status and UTR (do not store full NI numbers), VAT reg and reverse charge applicability.

Tip: Keep personal data to a minimum and store links to files in Drive rather than attaching everything in the Sheet.

Set up the tracker in Google Sheets

Create a spreadsheet with three tabs:

  1. Roster
  • Columns: subcontractor name, company, trade, CSCS number, CSCS expiry, right-to-work check date, insurance expiry, RAMS received, induction date, CIS status, VAT reg, notes, folder link, status.
  • Conditional formatting: set rules so any expiry within 30 days shows amber, expired shows red, valid shows green.
  • Data validation: dropdowns for status (OK, expiring soon, expired, missing).
  1. Submissions
  • This is the live feed from your Google Form (see next section). Do not edit this tab.
  1. Dashboard
  • Summary counts: total subs, number expiring in 30 days, expired, missing.
  • Filters by site/trade.
  • Small chart showing expiries by week.

Naming: use a simple ISO-like naming for folders and files, for example 2025-Subs/Company-Trade/Insurance-2025-12-31.pdf.

Add QR capture with Google Forms

  • Create a Google Form with fields: company name, contact, email, phone, CSCS number, upload CSCS photo, right to work confirmation, upload insurance certificate, insurance expiry, RAMS upload, induction date.
  • Turn on file uploads to Drive. Link the Form to the Sheet (Responses -> select existing spreadsheet -> Submissions tab).
  • Generate a QR code that links to the Form URL. Print it on an A4 site-gate poster with a short instruction: “Scan to upload cards and insurance before entry”.
  • Add a required declaration: “I confirm documents are accurate, and consent to processing for site compliance.”

Form tips

  • Use short help text to explain acceptable file types and max size.
  • Add a hidden field for site name via pre-filled link if you manage multiple sites.
  • Include a field for folder link back to Drive once triaged.

Automatic expiry alerts (email)

Use a small Apps Script in the Google Sheet to email reminders 30 days before expiry and on the day of expiry.

Paste this into Extensions -> Apps Script:

function sendExpiryAlerts() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Roster');
  const data = sheet.getDataRange().getValues();
  const headers = data.shift();
  const emailCol = headers.indexOf('email');
  const nameCol = headers.indexOf('subcontractor name');
  const insExpCol = headers.indexOf('insurance expiry');
  const cscsExpCol = headers.indexOf('cscs expiry');
  const today = new Date();
  data.forEach((row, i) => {
    const email = row[emailCol];
    const name = row[nameCol];
    [insExpCol, cscsExpCol].forEach(col => {
      const exp = row[col];
      if (exp && exp instanceof Date) {
        const days = Math.ceil((exp - today) / (1000*60*60*24));
        if (days === 30 || days === 0) {
          MailApp.sendEmail({
            to: email,
            subject: 'Compliance expiry reminder',
            htmlBody: `<p>Hi ${name || 'there'},</p><p>One of your compliance items expires on ${exp.toDateString()}. Please upload the renewal via the site QR or reply to this email with the document.</p>`
          });
        }
      }
    });
  });
}
  • Set a time-driven trigger to run daily.
  • If you prefer a digest, collect expiring items into one email per day.

Simple workflows that save time

  • WhatsApp intake: pin a message in your subcontractor WhatsApp group with the QR/Form link and instructions. Ask subs to send photos from their camera roll with location on. See our article on WhatsApp subcontractor compliance.
  • CSCS checks: spot-check numbers against the official CSCS checker. Record “verified date” in the Sheet.
  • Right to work: follow the step-by-step on GOV.UK – right to work checks. For online share codes, record the check reference.
  • RAMS and permits: link each company to their latest RAMS file; for hot work or confined space permits see our permit to work guide.
  • Handover: at the end of the job, export the dashboard as PDF for the client and include in your golden thread pack. See Golden thread made simple.

Privacy and GDPR tips

  • Minimise data: store only what you need to prove competence and compliance.
  • Access control: restrict the Sheet and Drive folders to project staff. Use view-only links for clients.
  • Retention: set a retention period and delete old copies after handover unless there is a legal reason to keep them.
  • Transparency: include a short privacy notice in the Form linking to your policy.

FAQs

Want to slash training times and increase revenue per Engineer? Join our Waitlist: https://trainar.ai/waitlist