Google Sheets Setup

Documentation

Google Sheets Setup

This guide will help you set up the Google Sheets work schedule with automatic color coding and live updates.

Step 1: Create the Google Sheet

Go to Google Sheets
Click “Blank” to create a new spreadsheet
Name it: “Arkade-Sommerfest 2026 - Arbeitsplan”

Step 2: Create the Structure

Sheet 1: “Schedule Overview” (Main View)

⚠️ Note: Areas may change year to year! This structure is designed to be easy to update.

Header Row (Row 1)

A	B	C	D	E
Area/Bereich	09:00-10:00	11:00-13:00	13:00-15:00	16:30+ (Abbau)

Area Rows (Starting from Row 2)

Current areas (update as needed!):

Aufbau (09:00-10:00) - Row 2
BONKASSE - Row 3
Getränkeausgabe - Row 4
Kaffeebar - Row 5
Springer/Aufräumer - Row 6
Abbau (16:30+) - Row 7

Not all areas operate during all time slots! Leave certain cells as “N/A” or gray them out:

Aufbau: Only column B (09:00-10:00)
BONKASSE, Getränkeausgabe, Kaffeebar, Springer: Only columns C and D (11-13, 13-15)
Abbau: Only column E (16:30+)

Sheet 2: “Registrations” (Form Responses)

This sheet will be automatically created when you link Google Forms. It will contain:

Timestamp
Email
Name
Area / Bereich
Time Slot / Zeitslot
Gender (if collected)
Comments (if collected)

Sheet 3: “Helper Formulas” (Optional)

For counting registrations per area/time combination.

Step 3: Add Formulas to Count Registrations

For active area/time combinations:

=COUNTIFS(Registrations!$C:$C, $A2, Registrations!$D:$D, B$1) & "/2"

For Abbau (6 people needed, not 2):

In the Abbau row, use this formula in the 16:30+ column:

=COUNTIFS(Registrations!$C:$C, $A2, Registrations!$D:$D, E$1) & "/6"

For N/A cells (areas that don’t operate during that time):

Simply type N/A or leave blank - then gray out the cell manually (Format > Background color > Gray)

The Formula Logic

Each formula counts how many people registered for exactly this area AND this time slot:

The area in column A (e.g., "Kaffeebar")
The time slot in row 1 (e.g., "11:00-13:00")

Because people submit the form once per area/time combination, a person working two shifts appears twice in the “Registrations” sheet - which is correct!

With Names Displayed

=IFERROR(
  COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)&"/2: "&
  JOIN(", ",FILTER(Registrations!$B:$B,(Registrations!$C:$C=$A2)*(Registrations!$D:$D=B$1))),
  "0/2"
)

Example output: 2/2: Max Müller, Anna Schmidt

Step 4: Apply Conditional Formatting (Color Coding)

For regular area/time cells (B2:D6 or similar - NOT Abbau):

Select the range of active cells (e.g., C3:D6 for the 11-15 shifts)
Click Format > Conditional formatting
Add three rules:

Rule 1: Green (Fully Staffed - 2/2)

Formula: =COUNTIFS(Registrations!$C:$C, $A2, Registrations!$D:$D, B$1) = 2
Formatting: Background color Green (#34A853), text White

Rule 2: Yellow (Partially Staffed - 1/2)

Formula: =COUNTIFS(Registrations!$C:$C, $A2, Registrations!$D:$D, B$1) = 1
Formatting: Background color Yellow (#FBBC04), text Black

Rule 3: Red (Unstaffed - 0/2)

Formula: =COUNTIFS(Registrations!$C:$C, $A2, Registrations!$D:$D, B$1) = 0
Formatting: Background color Red (#EA4335), text White

For the Abbau row (different requirement: 6 people, minimum 3 men):

Select only the Abbau cell (e.g., E7) and add separate rules:

Abbau Green (6/6 - full)

Formula: =COUNTIFS(Registrations!$C:$C, $A7, Registrations!$D:$D, E$1) >= 6
Formatting: Green

Abbau Yellow (partially staffed, 1-5 people)

Formula: =AND(COUNTIFS(Registrations!$C:$C, $A7, Registrations!$D:$D, E$1) > 0, COUNTIFS(Registrations!$C:$C, $A7, Registrations!$D:$D, E$1) < 6)
Formatting: Yellow

Abbau Red (0 people)

Formula: =COUNTIFS(Registrations!$C:$C, $A7, Registrations!$D:$D, E$1) = 0
Formatting: Red

For N/A cells (areas that don’t operate at that time):

Select those cells and manually:

Set background color: Light gray (#CCCCCC)
Set text: N/A
No conditional formatting needed - they’re inactive

Step 5: Add Name Lists (Optional but Recommended)

You can add a helper column to show who registered:

=JOIN(", ", FILTER(Registrations!$B:$B, (Registrations!$C:$C=$A2)*(Registrations!$D:$D=B$1)))

This will display names like: “Max Müller, Anna Schmidt”

Step 6: Formatting for Printing

Set Print Area: File > Print > Set print area
Page Setup:
- Orientation: Landscape
- Paper size: A4 or A3 (depending on number of areas)
- Scaling: Fit to width
Headers/Footers:
- Header: “Arkade-Sommerfest 2026 - Arbeitsplan”
- Footer: Print date
Borders:
- Select all cells with data
- Click borders icon
- Choose “All borders”

Step 7: Sharing Settings

Click Share button (top right)
Set to: “Anyone with the link can view”
For editing (form responses): Keep restricted
Copy the link to share with team

Step 8: Enable Auto-Update

The sheet will automatically update when someone submits the form. To ensure real-time updates:

Share the view-only link with staff
Recommend they refresh the page or keep it open
Consider using Google Sheets mobile app for live updates

Advanced: Highlight Overstaffed Slots

If someone accidentally registers when already full (2/2):

Rule 4: Orange (Overstaffed - 3+ people)

Format cells if: Custom formula is
Formula: =COUNTIFS(Registrations!$C:$C, $A2, Registrations!$D:$D, B$1) > 2
Formatting: Background color Orange (#FF9900)
Text color: Black

Troubleshooting

Formulas not working?

Check that the “Registrations” sheet name matches exactly
Verify column references ($C:$C for Area, $D:$D for Time)
Make sure there are no extra spaces in area/time names

Colors not updating?

Check conditional formatting rules order (drag to reorder)
Verify formula syntax
Refresh the page

Print layout issues?

Try A3 paper size for many areas
Adjust font size (9-11pt works well)
Use “Fit to page” scaling option

Example Final Formula (Complete)

For a cell showing count, names, and ratio:

=IF(COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)>0,
  COUNTIFS(Registrations!$C:$C,$A2,Registrations!$D:$D,B$1)&"/2: "&
  JOIN(", ",FILTER(Registrations!$B:$B,(Registrations!$C:$C=$A2)*(Registrations!$D:$D=B$1))),
  "0/2")

This shows: “2/2: Max Müller, Anna Schmidt”

Quick Start Google Forms Setup