Track med spa packages so the sessions-left number counts…

Why a typed-in balance drifts

Most package spreadsheets start the same way. Someone sells Package A, 6 sessions, types a 6 in a cell, and after the first visit someone else changes it to 5. Every visit after that depends on a person editing the right cell on a busy day.

The number drifts in quiet ways. Two people edit it on the same afternoon, or a visit goes in a notebook and never reaches the sheet. Then a client is sure they have two left, the sheet says one, and nobody can prove either.

The fix is to stop typing the balance at all. Record two plain facts, a package was sold and a visit was used, and let the sheet work out the rest. The steps below use Google Forms and Google Sheets, which you may already have, and close with the Airtable version. For the rest of your site, see websites for med spas.

Before you build: whose data goes in the sheet

A package tracker holds client names next to what they bought, so settle the privacy question first. Google says each Google Workspace customer is responsible for deciding whether HIPAA applies to them and whether they put protected health information in Google services. Google also says customers who have not signed a business associate agreement with Google must not put protected health information in Google Workspace.

If HIPAA applies to you, the agreement is accepted electronically by an administrator in the Google Admin console, under Account settings and then Legal and compliance. If you are not sure whether it applies, ask the person who handles compliance for your spa before the first real name goes in. The same question comes up for the forms on your website, and the answer carries over here.

Whatever the answer, keep the sheet thin. It needs the client's name, a phone number or client ID, the package, the count and the dates. Treatment notes, photos and anything clinical stay out of it.

Step 1: two forms at the desk, one sheet

Build two short Google Forms.

Form 1, Package sold, asks for:

Client name
Client phone or client ID
Package name, for example Package A, 6 sessions
Sessions bought, as a number
Date sold
Package code

The package code keeps two packages for the same client apart. Use something the desk can write the same way every time, like the client's initials plus the sale date. A client who buys Package A in March and again in June then has two codes, and each visit counts against the right one.

Form 2, Visit used, asks for:

Package code
Visit date
Staff initials

Now point both forms at the same file. In Google Forms you can choose where responses are saved, including an existing spreadsheet you pick. Create the response spreadsheet from Form 1, then set Form 2 to save into that same spreadsheet, so both forms feed one Google Sheets file. When responses are stored in a spreadsheet, Google Sheets puts them in a table on its own, so each sale or visit lands as a structured row.

Rename the two response tabs so you can tell them apart. In the examples below they are called Packages and Visits.

Step 2: a sessions-left column that counts itself

On the Packages tab, go to the first empty column to the right of the form answers. This column holds the balance, and one formula in its top cell fills the whole thing.

COUNTIFS in Google Sheets counts the rows that match one or more conditions, which is how the sheet counts the visits logged against one package code. ARRAYFORMULA lets one formula fill its results down many rows, so the balance covers every new package without anyone copying the formula down.

Here is an example, for a sheet where column E on Packages holds sessions bought, column G holds the package code, and column B on Visits holds the package code:

txt

={"Sessions left"; ARRAYFORMULA(IF(G2:G="", "", E2:E - COUNTIFS(Visits!B:B, G2:G)))}

The column letters in that line have to match your own sheet. Look at the top row of each tab, find which letter holds sessions bought and which holds the package code, and change E, G and B to those letters before you paste it.

Then test it. Submit Form 1 once with a made-up client and 6 sessions bought, then submit Form 2 twice with that package code. The new column should show 4. If it still shows 6, check that the code was typed the same way in both forms.

Step 3: lock the columns nobody should type in

Google Sheets lets you protect a sheet or a range so people cannot change its content. Use that on the balance column, so only you can edit it.

Staff still record every sale and every visit through the two forms, and the balance updates from those entries. If a number looks wrong, the cause is a missing or mistyped form entry. Find it in the Visits tab by its date and staff initials, and fix it there, not by typing over the balance.

Step 4: the one tab the front desk reads before a visit

Add a new tab and call it Desk. This is the only tab the front desk needs to open.

In cell B1, the desk types a client name. In cell A3, paste a formula that pulls that client's open packages. The FILTER function returns only the rows that meet the conditions you set, so it can show one client's packages that still have sessions left.

txt

=FILTER(Packages!B2:H, Packages!B2:B=B1, Packages!H2:H>0)

Same rule as before: in this example B is the client name column and H is the balance column on Packages, so change them to match your sheet. Then protect cell A3 and the area below it the way you did in Step 3, so nobody types over the results. Leave B1 open so the desk can type in it.

Before each visit, the desk types the name and reads the package code and the sessions left. After the visit, they fill in Form 2.

If you already use Airtable

The same idea works in Airtable with two linked tables, one for packages and one for visits. A count field counts the linked records on its own, and a rollup field updates as records are linked or unlinked, so a count field on the packages table keeps the used count without anyone typing it. From there, the balance is sessions bought minus that count.

The privacy question changes, though. Airtable's HIPAA page says HIPAA is available on its Enterprise Scale plan, and the customer has to sign Airtable's Business Associate Addendum. Plans change, so read that page again before you decide.