The workflow at a glance
Put the fixed date November 1, 2026 in Settings!B1. Apply completion first, then missing due dates, overdue, due today and upcoming. The five initial states must be done, overdue, due_today, upcoming and needs_due_date, with an overdue count of 1. Independently change a deadline, add a completion record and advance the reporting date; only the actual file’s observed updates can pass the editable-handoff checks.
The October 29 update improves file editing; the final file still needs checking
Google’s announcement dated October 29, 2026 introduces in-chat file generation, preview and editing in the Gemini app. These capabilities for spreadsheets and other non-PDF formats are web-only; PDFs support web and mobile. That is the original announcement date, not a November 1 launch.
The same-day Workspace post describes previewing trackers and opening them directly in Google Sheets. It lists the feature as available now for Workspace, Individual and personal accounts with Gemini app access. Actual accounts must still meet the service’s access conditions; the announcement does not guarantee a correct result for this case.
Use the change for one concrete task: deliver a task list that remains editable after the conversation. All inputs, formulas, reference results and faulty drafts below are original editorial teaching materials, not observations from a tested Gemini account. No real project, calendar or reminder service was connected.
Choose what the recipient actually needs
Continuing tracker
Keep the native editable sheet and formulas so later deadlines and completion records can update its calculations.
Fixed daily report
Keep a separately dated snapshot for reviewing the state at that time. It does not maintain future dependencies.
Unavailable file generation
Retain the text draft and unfinished steps. Code or a chat preview is not a delivered Google Sheet.
Five source tasks, one fixed date and five distinct states
Fix the reporting date at 2026-11-01 and compare calendar dates, without business-day or time-of-day calculations. completed_date means an actual completion already confirmed in the source. A missed deadline is not evidence that work finished. Only A01 has a completion record; the other four have none.
Use an explicit priority: completed first; otherwise a missing deadline needs_due_date; otherwise compare due_date with the reporting date. A task due today is due_today, not yet overdue. A blank deadline is neither a zero date nor automatically upcoming.
Use Tasks!A1:E6 with task_id, due_date, completed_date, state and calendar_days_to_due. Create a Settings sheet with as_of in A1 and =DATE(2026,11,1) in B1. Store nonblank B/C entries as real date values displayed as yyyy-mm-dd. Preserve the IDs as text.
task_id,due_date,completed_date
A01,2026-10-30,2026-10-29
A02,2026-10-31,
A03,2026-11-01,
A04,2026-11-03,
A05,,| First applicable condition | State | calendar_days_to_due |
|---|---|---|
| Confirmed completion date present | done | Blank; no remaining countdown |
| Incomplete and due date missing | needs_due_date | Blank; unresolved |
| Incomplete and due date before as_of | overdue | due date minus as_of; retain negative values |
| Due date equals as_of | due_today | 0 |
| Due date after as_of | upcoming | due date minus as_of; positive |
Request a real editable file and specify its dependencies
In a new Gemini app conversation on a computer browser where it is available, paste the complete CSV above followed by the request below. Start with fictional records; connecting a real business system is unnecessary. Ask for an editable file rather than an image containing a table.
Keep the English state codes for precise formula matching; explanatory labels can be localized. A prompt is not a technical guarantee. Inspect the formula bar after opening the generated file. If your account has no file entry point, record the unfinished step rather than treating a purchase, permission change or different dataset as a successful trial.
Create an editable Google Sheets task tracker from the five fictional CSV records above, not only an image or Markdown table.
Create Tasks and Settings sheets. Tasks columns A:E are task_id, due_date, completed_date, state and calendar_days_to_due. Source records occupy rows 2–6. Settings!A1 is as_of and B1 is =DATE(2026,11,1).
Store nonblank source dates as true date values displayed yyyy-mm-dd. Preserve task_id as text. Do not fill missing dates.
D must contain formulas referencing each row’s B/C and Settings!$B$1: nonblank C is done; otherwise blank B is needs_due_date; otherwise B<as_of is overdue, B=as_of is due_today, and B>as_of is upcoming.
E is blank for completed tasks or missing due dates; otherwise calculate B-as_of in calendar days and retain negative values.
Settings!A3 is overdue_count. Use COUNTIF in B3 to count overdue in Tasks!D2:D6.
Do not use TODAY or NOW. Do not substitute fixed reference-state text for formulas. Use simple styling to distinguish input and derived columns. Add no chart or invented progress.
Create only the exercise file. Do not send reminders, share it with others or modify the source. List formulas and unfinished steps. If a file cannot be created, explicitly report that incompletion rather than claiming it was saved.Open Sheets, inspect formulas and compare the complete reference result
First compare the five IDs and source dates in Gemini’s preview, then use the available option to open the file in Sheets. In the actual spreadsheet, inspect Settings!B1 and Tasks!D2/E2 in the formula bar. They should contain real cell-reference formulas rather than formula-like text or fixed labels. The candidates below can also be used to inspect or build the example manually.
Check date types first: A04’s due-date cell minus Settings!B1 should give 2. An error or text string requires an import-type repair, not an error wrapper that silently returns zero. These formulas assume valid source dates and genuine completion records no later than as_of. Invalid dates or future completion records in real data need a separate exception list.
Settings!B1:
=DATE(2026,11,1)
Tasks!D2, fill through D6:
=IF(C2<>"","done",IF(B2="","needs_due_date",IF(B2<Settings!$B$1,"overdue",IF(B2=Settings!$B$1,"due_today","upcoming"))))
Tasks!E2, fill through E6:
=IF(OR(B2="",C2<>""),"",B2-Settings!$B$1)
Settings!B3:
=COUNTIF(Tasks!$D$2:$D$6,"overdue")task_id,state,calendar_days_to_due
A01,done,
A02,overdue,-1
A03,due_today,0
A04,upcoming,2
A05,needs_due_date,Change one input at a time to check the dependency chain
Start each test from the original five tasks and the November 1 date. Restore the source before the next test. These are editorially derived expectations, not recorded Gemini or Sheets runs; save the actual values after operating the file.
The first test changes a deadline and checks both that row’s status and day count, then the cross-sheet summary. The second tests completion priority: completed work must stop appearing as overdue. The third advances only the reporting date, exposing a table that still displays the generation-time snapshot instead of referencing its parameter.
| Only change | Row-level expectation | Overdue count | Other checks |
|---|---|---|---|
| Tasks!B3: A02 due date Oct 31 → Nov 4 | A02 becomes upcoming; days 3 | 1 → 0 | Other four records unchanged |
| Tasks!C3: add confirmed A02 completion 2026-11-01 | A02 becomes done; days blank | 1 → 0 | Do not retain -1 |
| Settings!B1: as_of becomes 2026-11-02 | A02 overdue/-2; A03 overdue/-1; A04 upcoming/1 | 1 → 2 | A01 stays done; A05 needs_due_date |
Why can a polished preview still be the wrong deliverable?
A faulty teaching candidate might put fixed text overdue in A02’s state cell, mark A03 overdue because its due date is less than or equal to today, and use TODAY() for day counts. It can show an initial overdue count of 2 and then leave a label unchanged when its deadline moves. Even a coincidentally correct display on another day would not reproduce the November 1 report.
Repair the inputs, formulas and summary separately; asking AI to fix the colors does not establish any of them. Use strictly earlier than as_of for overdue so A03 becomes due_today. Restore the per-row B/C/Settings references, then count the derived state column in the summary. Use all three independent changes above to verify the repair, rather than checking the initial total alone.
Replace fixed labels with an inspectable dependency
A02’s D3 contains the text overdue. A03 is overdue under B4<=TODAY(). The preview card says 2 overdue without an as-of date.
D2:D6 use the same state formula with their own B/C row and Settings!$B$1. Due-today records remain due_today. Initial overdue count is 1; moving only A02 to Nov 4 makes it 0.
Deliver the native tracker and keep a separately dated snapshot
The tracker supports continuing work; the report snapshot supports reviewing a past state. Retain the native sheet, source inputs, as-of date, rule version, formulas and observed trial results. Mark a separate snapshot with 2026-11-01 in its name or description. A state-only CSV cannot maintain later spreadsheet-cell dependencies.
For CSV delivery, first read the distinction between formulas, saved values and multi-sheet exports. This article does not promise that every export preserves all Sheets features. Open downloaded formats in the receiving software and check them separately. A sixth task also lies outside the fixed rows 2–6 used here; extend both derived formulas and summary ranges, then retest.
Gemini’s official help lists supported spreadsheet/download formats and account or institutional access conditions. This article has not tested a particular account, region or exported version. A task label is not proof of completion. Follow your organization’s submission and retention rules before using real records.
If the file disagrees with the reference, use row-level and independent change checks to locate the first difference. Passing this example covers only five inputs and three changes. It does not establish unlimited rows, every date format or reliable automatic reminders.
What the recipient should be able to inspect
- All five source records, both sheets and the Settings as-of date are visible; missing dates remain missing.
- Tasks D/E contain readable formulas with the right row references; initial states match the complete reference CSV.
- Each independent change has expected and actual values plus any failure record. Unexecuted tests are marked unexecuted.
- The delivered version restores the initial data or clearly records its final as-of date and changed inputs.
- File location and access are appropriate. Other downloaded formats receive their own formula and value checks in the receiving application.
Common questions
Why not use TODAY() so it is always current?
A live dashboard may use a dynamic date, while a reproducible daily report needs a stable as_of. Validate this example with a fixed date first. If you adopt a dynamic date, keep snapshots and explain that reopening the file can change its results.
Can it send overdue reminders automatically every day?
This workflow creates and checks a file. It does not implement scheduling, recipient verification or message delivery. Reminders require a separate authorized and tested workflow; a state formula does not mean notifications are enabled.
