Portfolio
  • Integration and data sync
  • API integration
  • Google Apps Script

Two sign-up tools, one clean roster per location

An automation for a volunteer program in four locations that brings registrations from Google Forms and SignUpGenius into each location's roster, recognizing returning volunteers and never overwriting what coordinators already know.

An automation for a four-location volunteer program that brings Google Forms and SignUpGenius registrations into each location's roster, recognizing returning volunteers and never overwriting what coordinators already know.

SOURCESGoogle FormsOne per locationSignUpGeniusRole sign-ups, via APIWording docsForm titles, descriptionsApps Scriptroute · match · mergeLocation 1 rosterLocation 2 rosterLocation 3 rosterLocation 4 rosterChanged rows highlightedfor coordinators
Google FormsSignUpGeniusApps ScriptLocation 1Location 2Location 3Location 4
Two intake paths, shared rules, a separate roster for each location.Two intake paths, a separate roster for each location.
Sector
Nonprofit volunteer program
Client
Four-location program; identity confidential
Built with
Google Apps Script, Forms, Sheets and Docs, and the SignUpGenius API
About the examples
Every volunteer, email and phone number shown is invented.

A nonprofit volunteer program in four locations; identity confidential. Built with Google Apps Script, Forms, Sheets, Docs and the SignUpGenius API. Every volunteer shown is invented.

The challenge and my role

A volunteer program running in four locations collected volunteers two ways: registration forms for each location, and role sign-ups in SignUpGenius. The two produced differently shaped data, and coordinators needed it all in one organized roster for their location, without losing details they had already added by hand.

I designed the registration forms, connected SignUpGenius through its API, wrote the routing, matching and merge rules in Google Apps Script, documented the system, and trained coordinators with very different levels of comfort with technology.

Volunteers came in through location forms and SignUpGenius, in different shapes. Coordinators needed one organized roster per location without losing details they'd added. I designed the forms, connected the API, wrote the matching and merge rules, documented it and trained the coordinators.

  • Two sources

    Same people, different shapes

    A form response is organized around one submission. A sign-up report is organized around an opportunity and its roles. Neither looks like a roster.

    Neither a form response nor a sign-up report looks like a roster.

  • Working documents

    Rosters people already rely onRosters people rely on

    Coordinators add notes, columns and corrections. An import that rebuilds the sheet, or blanks out a phone number, does more harm than good.

    An import must never blank out what coordinators added.

  • Returning volunteers

    One person, many sign-ups

    The same volunteer signs up for several roles over time, sometimes with different capitalization or a shared family email.

    Different capitalization, sometimes a shared family email.

Two intake paths, one set of rostersTwo intake paths

Each source gets the treatment it needs. Both end in the right location's roster.

  • Path 1 · On submissionOn submission

    Registration forms

    Each location has its own form, with separate question paths for adult and teen volunteers.

    1. A volunteer submits the form; the response is kept in its own sheet.
    2. The submission triggers the script immediately.
    3. Answers are mapped to the roster's columns, whichever question path was taken.
    4. A new row is added to that location's roster.

    One form per location, with adult and teen question paths. Each response is mapped to the roster's columns and added as a new row.

  • Path 2 · On a scheduleOn a schedule

    SignUpGenius sign-ups

    The script checks SignUpGenius regularly, so nobody has to download and reconcile reports.

    1. Find the available sign-ups and pull their filled reports through the API.
    2. Work out the location from the sign-up's title.
    3. Tidy names, emails, phone numbers, dates and roles into one consistent shape.
    4. Match each volunteer to the roster, then update their row or add a new one.

    Reports pulled through the API, routed by the location in the sign-up title, tidied, matched to the roster, then updated or added.

Merge, not overwrite

When a returning volunteer signs up again, the import adds what's new and keeps what the roster already knows. Here's one invented example: a volunteer already on the roster signs up for a second role, with her email typed differently and no phone number.

A returning volunteer signs up for a second role, with her email typed differently and no phone number.

Merging a returning volunteer (illustration)
VersionFirst nameLast nameEmailPhoneLatest sign-upRolesCoordinator notes
Roster todayDanaWhitfielddana.w@example.com(555) 014-22903/2/2026GreeterPrefers mornings
New sign-upDanaWhitfieldDana.W@Example.com(blank)4/18/2026Set-up crewn/a
Roster afterDanaWhitfielddana.w@example.com (kept)(555) 014-2290 (kept)4/18/2026 (updated)Greeter; Set-up crew (updated)Prefers mornings (kept)
Green: kept from the roster. Blue: updated from the sign-up. The changed row is highlighted so the coordinator can find it.
  • Roster today

    Name
    Dana Whitfield
    Email
    dana.w@example.com
    Phone
    (555) 014-2290
    Latest sign-up
    3/2/2026
    Roles
    Greeter
  • New sign-up

    Email
    Dana.W@Example.com
    Phone
    (blank)
    Date
    4/18/2026
    Role
    Set-up crew
  • Roster after

    Email
    dana.w@example.com (kept)
    Phone
    (555) 014-2290 (kept)
    Latest sign-up
    4/18/2026 (updated)
    Roles
    Greeter; Set-up crew (updated)

Green: kept. Blue: updated. The changed row is highlighted.

The merge rules

  • Fill blank fields when the sign-up has a usable valueFill blanks with usable values
  • Replace a phone number only with a valid one, never with a blank or a bad oneReplace a phone only with a valid one
  • Keep the most recent sign-up date that can actually be readKeep the latest readable date
  • Combine distinct roles, without repeating one already listedCombine distinct roles
  • Leave every other cell, column and format exactly as it wasLeave everything else untouched

Why email and first name togetherWhy email and first name

Matching on email alone would merge family members who share one address into a single volunteer. Matching on email plus first name keeps them as separate people. Emails are lowercased first, so capitalization never creates a duplicate.

Email alone would merge family members who share an address. Emails are lowercased first, so capitalization never creates a duplicate.

It's a practical rule for this workflow, not a claim to resolve every possible variation in a name. Form registrations are added as new rows; the matching applies to the SignUpGenius import.

Built around the coordinators

The rosters stayed the spreadsheets coordinators already used. The automation fitted around them rather than the other way round.

  • See what changed

    New and updated rows are highlightedChanged rows highlighted

    After each run, a coordinator can spot additions and changes at a glance instead of rereading the whole roster.

    Spot additions and updates without rereading the roster.

  • Flexible columns

    Headers found by name

    The script recognizes common variations of each column name, so a roster doesn't have to be restructured before the automation can use it.

    Common column-name variations are recognized.

  • No code for wording

    Form text kept in a documentForm wording in a document

    Each location's form title and description live in an editable Google Doc. Change the doc, and the live form follows on the next scheduled update.

    Edit the doc; the live form follows. No code needed.

Training and adoption

The coordinators ranged from very comfortable with spreadsheets to not at all. Training was part of the build, not a single handoff meeting, and what users found confusing changed the workflow itself.

Coordinators ranged from very comfortable with spreadsheets to not at all, so training shaped the build.

  1. Test the whole path

    From form or sign-up to the finished roster row, not just whether the data arrived.

    Source to finished roster row.

  2. Write it down

    Technical documentation plus a plain-language guide for coordinators.

    Technical docs and a plain guide.

  3. Group walkthroughs

    The whole system shown end to end.

    The system end to end.

  4. One-on-one sessions

    Personal time for anyone who needed it.

    For anyone who needed them.

  5. Simplify from feedback

    Steps that confused people were changed, and recurring questions went into the guide.

    Confusing steps changed; questions added to the guide.

  6. Hand over

    Including how to tell a source, form, mapping or access problem from an automation one.

From "how do I" to "could it also"

Within a couple of weeks, coordinators' questions shifted from how to use the system to suggestions for what it could do next.Within a couple of weeks, questions shifted from how to use it to ideas for what it could do next.

If your systems feel like a tangled plate of spaghetti and you’d rather have a neat picket fence, this freelancer is for you.

Ayantu took my spreadsheets, Google Forms, and SignUpGenius and made them all talk to each other seamlessly. What felt complicated in my mind, she turned into an easy, logical flow that delivers exactly the information I need. She explained everything in plain language and even left clear notes and diagrams for future use. My data is now organized, connected, and effortless to manage thanks to her work.

★★★★★Client, verified Upwork review

What changed

What changed: before and with the automation
AreaBeforeWith the automation
Form registrationsResponse sheets reorganized into rosters by handMapped into the right location's roster on submission
SignUpGeniusReports downloaded and reconciled manuallyPulled on a schedule and merged into the rosters
Returning volunteersRisk of duplicates or lost detailsMatched and updated in place, with roles combined
Reviewing changesScanning the whole rosterChanged rows highlighted after each run
Form wordingEdited form by formMaintained in one document per location
  • Form registrations

    Before: reorganized by hand

    Now: mapped into the roster on submission

  • SignUpGenius

    Before: downloaded and reconciled manually

    Now: pulled on a schedule and merged

  • Returning volunteers

    Before: duplicates or lost details

    Now: matched and updated in place

These are delivered capabilities and observed adoption. No time study was done, so no time savings are claimed. Roles record what volunteers signed up for, not attendance or hours served.

Delivered capabilities and observed adoption; no time savings are claimed. Roles record sign-ups, not attendance or hours.

The same pattern, other organizations

Anywhere people arrive through more than one tool and someone keeps a list they rely on, the same matching and merge rules apply.

People arriving through more than one tool, and a list someone relies on.

  • Law firms

    Client intake

    Website inquiries and scheduling bookings combined into one intake list, without duplicating returning clients.

  • Faith and community groupsCommunity

    Member and volunteer lists

    Event sign-ups and registration forms merged into one directory per ministry or program.

  • Youth sports and clubsYouth sports

    Team rosters

    Registrations and volunteer-parent sign-ups routed to each team, keeping coaches' notes intact.

  • Event organizersEvents

    Attendee and staff lists

    Ticketing exports and shift sign-ups reconciled into one list per venue.

  • Schools and PTAsSchools

    Parent volunteer rosters

    Form registrations and sign-up sheets combined by school or grade.

  • Membership organizationsMembership

    Chapter directories

    Renewals and event registrations routed to each chapter's list without overwriting local details.

Technical detail

  • Google Apps Script
  • Google Forms
  • Google Sheets
  • Google Docs
  • SignUpGenius API
  • JSON and CSV
  • Apps Script
  • Forms
  • Sheets
  • Docs
Triggers matched to the job

Form submissions run the moment they arrive. SignUpGenius imports and the form-wording updates run on a schedule. One standalone Apps Script project serves all four locations, with each location's forms, response sheets, wording documents and roster configured separately.

Normalizing incoming data

Sign-up reports arrive as JSON or CSV and are translated into one internal shape. Emails are lowercased, dates are checked and unreadable ones set aside rather than guessed, phone numbers are validated, and role values are cleaned before comparison.

Routing to the right location

Form responses come from a location-specific form. SignUpGenius records are routed by reading the location from the sign-up's title, then written to that location's roster.

Two roles columns

Roles from SignUpGenius are combined into a semicolon-separated list. Roles volunteers chose on the registration form are kept in their own column, so neither source overwrites the other.

Triggers matched to the job

Form submissions run on arrival; SignUpGenius imports and form-wording updates run on a schedule. One script project serves all four locations.

Normalizing incoming data

JSON and CSV reports become one shape. Emails lowercased, dates checked, phones validated, roles cleaned.

Two roles columns

SignUpGenius roles are combined in one column; roles chosen on the form keep their own, so neither source overwrites the other.

People signing up in more than one place?

Tell me which tools they use and what your list needs to look like. We can discuss keeping it current without losing what your team has already added.

Tell me which tools they use and what your list needs to look like.

See other case studies