How I Track a 14-Day Google Play Closed Test in a Spreadsheet

Play Console shows your progress toward the testing requirement. It doesn't tell you who joined when, who quietly left, or the first day you can apply for production access. For Google Play closed testing those dates are the whole game, so I keep them myself in a plain spreadsheet.

This post is the sheet I use: the columns, formulas that work in Google Sheets, Excel and LibreOffice, a short Python version if you'd rather keep a CSV in your repo, and the five-minute check I run in Play Console every morning.

What the rule needs from your data

Personal developer accounts created after November 13, 2023 need a closed test with at least 12 testers opted in continuously for the 14 days before they apply. Google's Play Console Help page adds two details that matter for tracking:

• A tester who opts out before 14 days doesn't count.
• If they opt back in, the 14 days have to be consecutive again.

So each tester boils down to two dates: when they opted in, and when (if ever) they opted out. Everything else in the sheet is context that helps you later.

The sheet: one row per opt-in

One row per opt-in. The summary cells on the right do the 12 testers for 14 days math for you.

One rule keeps it honest: when someone rejoins, don't edit their old row. Add a new row with the new opt-in date. Their first stretch is dead anyway, and keeping it gives you a clean history if anyone asks.

The formulas

Data starts in row 2. Put these in row 2 and fill them down.

Days in (column H):

=IF(C2="","",IF(D2="",TODAY(),D2)-C2)

Status (column I) shows "counts", "out", or how far along the tester is:

=IF(C2="","",IF(D2<>"","out",IF(TODAY()-C2>=14,"counts","day "&(TODAY()-C2))))

Active since (column J) holds the opt-in date only for people still opted in, and is blank for everyone else:

=IF(OR(C2="",D2<>""),"",C2)

Then three summary cells, anywhere you like. Format the last one as a date.

Opted in now:        =COUNT(J2:J100)
Opted in 14+ days:   =COUNTIF(J2:J100,"<="&(TODAY()-14))
Earliest apply date: =IFERROR(SMALL(J2:J100,12)+14,"fewer than 12 opted in")

COUNT and SMALL skip the blank text the helper column returns, which is why it's there. If you're on Google Sheets or Excel 365 and want to skip the helper column, this gives the same date:

=IFERROR(SMALL(FILTER(C2:C100,(C2:C100<>"")*(D2:D100="")),12)+14,"fewer than 12 opted in")

The earliest apply date is the twelfth-earliest opt-in among people still in, plus 14 days. It's your earliest date if nobody else leaves. I tested all of this against sample data in LibreOffice and against the Python script below, and the numbers matched.

Example data: one tester leaves on Oct 10 and rejoins on Oct 12, so the twelfth opt-in moves from Oct 5 to Oct 6 and the earliest apply date moves to Oct 20.

I treat that date as the earliest, not the plan. Google doesn't publish how it handles time zones or the hour someone joined, so I add a day or two before I click apply.

The same thing in Python

If you'd rather keep testers.csv next to your code (columns tester,account,opted_in,opted_out,device, dates as YYYY-MM-DD):

import csv
from datetime import date, timedelta

today = date.today()
active = []  # opt-in dates of testers who are still opted in

with open("testers.csv", newline="") as f:
    for row in csv.DictReader(f):
        if not row["opted_in"] or row["opted_out"]:
            continue  # never joined, or left (a rejoin gets its own row)
        active.append(date.fromisoformat(row["opted_in"]))

qualified = [d for d in active if today - d >= timedelta(days=14)]
print(f"Opted in now:      {len(active)}")
print(f"Opted in 14+ days: {len(qualified)}")

if len(active) >= 12:
    earliest = sorted(active)[11] + timedelta(days=14)
    print(f"Earliest apply date if nobody leaves: {earliest}")
else:
    print(f"Short by {12 - len(active)} testers")

On my 16 sample rows (one tester dropped out and rejoined, so 15 are still in), run on Oct 19 it prints 15, 11 and 2026-10-20, the same as the sheet.

The 5-minute morning check in Play Console

The sheet is only as good as what you put in it, so I spend five minutes in Play Console every morning before I touch code.

The order I go in each morning. Anything odd goes straight into the sheet.

1. Dashboard. Look at the closed testing requirement section and compare its progress with your "Opted in now" cell. If your sheet says 12 or more but the tester step still isn't met, someone left or never opted in properly. Find them in the sheet.
2. Closed testing track. Check the release is still available to testers and the email list or Google Group is still attached on the Testers tab. Glance at countries if you've added testers from somewhere new.
3. Testing feedback. Monitor and improve > Ratings and reviews > Testing feedback. Copy anything new into column G with the date and app version.
4. Android vitals. Look for crashes or ANRs on the closed-track build and note the device in the sheet.
5. Activity. Update "Last active" from your analytics or your tester chat. Anyone quiet for two or three days gets a short, friendly nudge.

If the count dropped, find out who left, recruit a replacement the same day and give them a new row. The earliest apply date updates on its own. This is why I start with more than 12. Someone who joins on day 10 still needs 14 days of their own, and the formula will show you that without any mental math.

Why this pays off on the application form

When you apply, Google's form asks how easy recruiting was, whether testers used all your features, whether their usage matched what you'd expect from real users, what feedback you got and how you collected it, and what you changed before production. With the sheet, most of that is already written down: devices in column E, feedback in G, activity in F.

I add one more tab, a tiny changelog: version code, date, what changed, and which piece of feedback caused it. That tab answers the "what did you change" question by itself.

When the sheet isn't the problem

If the sheet keeps telling you you're short, your tracking is fine. The real question is how to get 12 testers for 14 days in the first place. That's the part TestMyApps handles: 14 testers on physical Android phones join your closed track through the official opt-in, usually within about 6 hours, and stay for 16 days. It starts at $15, and you get a production-access report at the end. Google still makes the production access call, but at least your sheet will be full.