Skills matrix for construction: link Google Sheets to your calendar to block unsafe bookings featured image
Hiring, Training & HR

Skills matrix for construction: link Google Sheets to your calendar to block unsafe bookings

TrainAR Team 1 month ago 6 min read

Contents

Quick answer

You can stop unsafe bookings by keeping a simple Google Sheets skills matrix for your operatives and connecting it to your scheduling calendar with Zapier. The Zap checks whether the assigned engineer has the right, in-date tickets for the job. If something is missing or expired, it blocks the booking and alerts your scheduler. This aligns with CDM 2015 duties to use competent workers and keep training up to date.

Site foreman viewing phone with a blocked booking due to expired CSCS; behind, a Google Sheets skills matrix and a Google Calendar week view

Why link a skills matrix to scheduling

  • Prevent unsafe placements: stop jobs going to someone with an expired CSCS, IPAF or First Aid.
  • Cut admin: expiry checks happen automatically.
  • Cleaner audits: you keep a record of why a booking was allowed or blocked.
  • Fits UK rules: Under CDM 2015, dutyholders must use people with the right skills, knowledge and experience, and give inductions and training where needed. See HSE role pages for principal contractors and contractors, and HSE guidance on training and competence.

What you need

  • Google account for Sheets and Calendar.
  • Zapier account for the automation.
  • 60 to 90 minutes to build the Sheet and first Zap.
  • Optional: a second Zap if you also want to validate bookings that schedulers create straight in the calendar.

Flow diagram: Google Sheets skills matrix feeds Zapier; Zapier either creates calendar events if competent or blocks with an Expired warning

Set up the skills and training matrix in Google Sheets

Start simple. You can add role-based logic later.

  • Columns suggested:
    • A Name
    • B Role
    • C CSCS expiry (date)
    • D IPAF expiry (date)
    • E First Aid expiry (date)
    • F Asbestos Awareness expiry (date)
    • G Bookable today? (TRUE or FALSE)

In G2, use this starter formula to block if any required ticket is expired or missing:

=AND(
  C2>=TODAY(),
  D2>=TODAY(),
  E2>=TODAY(),
  F2>=TODAY()
)
  • Format C to F as dates. Copy the formula down.
  • Add Data validation lists for Role (Data > Data validation) to keep entries tidy.

Tip: Highlight expired cells with conditional formatting so issues stand out for managers.

Automation option A: create jobs from a Sheet, only if competent

This is the cleanest way for small teams. The scheduler types a new row on a second tab, and Zapier only creates a calendar event if the chosen operative is bookable.

  1. Build a Schedule tab with these columns:

    • A Date
    • B Start time
    • C End time
    • D Client or site
    • E Job details
    • F Operative (Data validation list from the Matrix tab names)

  2. Zap in Zapier

    • Trigger: New Spreadsheet Row in Google Sheets (your Schedule tab).
    • Action: Lookup Spreadsheet Row in Google Sheets (find the operative in the Matrix tab).
    • Action: Filter by Zapier. Only continue if Bookable today? equals TRUE.
    • Action: Create Detailed Event in Google Calendar. Use Date and times from the row; include Job details in the description.
    • Path for not-competent: If the Filter fails, send an alert to the scheduler (Email by Zapier or Slack) saying which requirement is out of date. Keep the row as a record.

  3. Optional: write back a status to the Schedule tab (Created or Blocked) so your team can see what happened at a glance.

Video help: Zapier’s short walkthrough on connecting Sheets to Calendar is handy if you are new to Zaps.

Or open it in YouTube: How to Connect Google Sheets to Google Calendar.

Automation option B: validate calendar bookings after they are made

If your dispatcher prefers to add events straight into Google Calendar:

  1. Zap in Zapier

    • Trigger: New Event in Google Calendar (choose your scheduling calendar).
    • Action: Formatter by Zapier to extract the operative name. Easiest is to use a naming pattern like “OP: Jane Smith | Client – Address”.
    • Action: Lookup Spreadsheet Row in Google Sheets (find the operative in the Matrix tab).
    • Action: Filter by Zapier. If Bookable today? is FALSE, continue down a “Block” path.
    • Action on Block path: Email or Slack the scheduler and site manager, and optionally update the event title with “BLOCKED – update assignment”. You can also have a second action that deletes the event if that suits your process.

  2. Add a weekly digest Zap that lists people with expiries in the next 30 days to keep ahead of the problem.

Role-based requirements and expiries

Many firms only require certain tickets for certain roles. You can add a tiny role map to make the check smarter.

  • Create a Role_Requirements tab with columns:

    • Role
    • Requires CSCS? (TRUE or FALSE)
    • Requires IPAF?
    • Requires First Aid?
    • Requires Asbestos Awareness?

  • Replace G2 with a role-aware formula:

=LET(
  role, B2,
  req, FILTER(Role_Requirements!A:E, Role_Requirements!A:A=role),
  reqCSCS, INDEX(req,1,2),
  reqIPAF, INDEX(req,1,3),
  reqFA,   INDEX(req,1,4),
  reqAA,   INDEX(req,1,5),
  AND(
    IF(reqCSCS, C2>=TODAY(), TRUE),
    IF(reqIPAF, D2>=TODAY(), TRUE),
    IF(reqFA,   E2>=TODAY(), TRUE),
    IF(reqAA,   F2>=TODAY(), TRUE)
  )
)
  • Add more columns as you need for PASMA, Gas Safe, ECS, confined spaces, etc.

Pro tip: Add an Expiry in next 30 days helper column so you can chase renewals before they stop work.

Record keeping, GDPR and UK competence duties

  • HSE does not mandate a specific matrix format. You must ensure workers are competent and trained for the tasks and keep records proportionate to risk. See HSE on what competence means and deciding what training you need.
  • CDM 2015 expects principal contractors and contractors to verify skills, knowledge and experience and to deliver site-specific induction. See HSE on site rules and induction.
  • Passport schemes are not proof of competence by themselves. See HSE on passport schemes.
  • GDPR: keep the minimum personal data you need. Use an employee ID column instead of National Insurance numbers. Restrict access to the Sheet. Set a retention policy for expired certificates. See ICO guidance via GOV.UK and HSE’s pages linked above.

Related Academy reads:

Troubleshooting and edge cases

  • Operative not found: make sure names match exactly between Schedule and Matrix tabs. Consider using a unique staff ID instead of names.
  • Time zones and BST: set your Google Calendar time zone to London and keep it consistent across Zapier and Google accounts.
  • Multiple operatives on one job: create one row per operative or store a comma-separated list and iterate using a Zapier Looping step.
  • Not all roles need all tickets: use the Role_Requirements method so labourers are not blocked for not having IPAF, for example.
  • ServiceM8, Jobber or Tradify users: you can adapt the same logic. Trigger on a new job in your system, look up competence in the Sheet, then update or cancel the job if the operative is not bookable.

FAQ

There is no mandated template. Under CDM 2015 you must ensure competence and provide tr…