Log daily Mindbody sales and class attendance to Sheets

Every night at 11:30pm, log the day's numbers from Mindbody into a Google Sheet so I can track trends without pulling manual reports out of Mindbody by hand. Build this as a scheduled code workflow on a cron trigger. The target window is the current calendar day, from 00:00:00 to 23:59:59 in the site's local time. Because the run itself happens at 11:30pm, also re-process the previous calendar day on every run, so late evening sales and any attendance statuses that were changed after last night's run still get picked up.

Mindbody authentication: every request sends the API-Key and SiteId headers. Get Sales, Get Transactions, and Get Class Visits all return sales, client, and staff specific data, so they additionally require a staff user token. Issue a fresh staff user token at the start of each run and pass it as the raw Authorization header value with no Bearer prefix. Tokens are only valid for 24 hours, so a nightly job must mint its own rather than relying on a cached one.

For sales, call Mindbody Get Sales for the date window. Mindbody list endpoints paginate with Limit and Offset and return a PaginationResponse containing TotalResults, so page through by increasing Offset until Offset plus PageSize is greater than or equal to TotalResults, rather than assuming a single call returns the whole day. A busy studio will have more sales than one page holds. Then call Get Transactions for the same window to pick up payment detail, and group those transactions by their sale ID so each sale row carries the payment method, the transaction status, and the settled amount. Where one sale has several transactions against it, fold them into that single sale row instead of emitting duplicate rows.

For attendance, call Get Classes for the same date window to get every class that actually ran, then for each class returned call Get Class Visits using that class ID. Derive three counts from the visit list rather than just counting roster size. Booked is the number of visits on the class. Attended is the number of visits marked as signed in. No show is the number of visits that were booked, are not marked signed in, and were not late cancelled. No show is the metric studio owners care most about, so late cancels must be excluded from it and reported as their own column when the visit data distinguishes them.

Write to Google Sheets using Append Values, always appending so the history builds up and nothing is overwritten. One row per sale goes to a tab named Sales, and one row per class goes to a tab named Attendance. Every row on both tabs must carry the business date, the location, and the staff member, so the sheet can be pivoted by instructor or by site. The Sales tab should also carry the sale ID, the client ID, the item or service sold, the quantity, the gross amount, the payment method, and the transaction status. The Attendance tab should also carry the class ID, the class name, the class start time, the booked count, the attended count, and the no show count. Use USER_ENTERED as the value input option so dates and numbers land as real dates and numbers and stay pivotable, instead of arriving as text.

The run has to be safe to repeat without duplicating rows for a date that was already logged. Before appending anything, read the existing rows back with Get Values on each tab and build a set of the keys already present. Key the Sales tab on the business date plus the sale ID, and key the Attendance tab on the business date plus the class ID. Filter the rows you are about to write down to only the keys that are not already in the sheet, then append what is left. If a tab has nothing new, skip its append call entirely rather than writing an empty row. This guard is what makes both retries and the previous day re-check safe, so a nightly job that gets retried never double counts revenue.

Mindbody returns monetary amounts as decimal numbers in the site's currency rather than minor units, so write the amounts through as they come back and do not convert from cents. Stay within the Mindbody rate limit of roughly 2000 requests per minute per site while paging through a busy day. If a run fails partway through, the idempotency guard means re-running it simply fills in whatever did not get written the first time.