What it does
Drafts or cases in your spreadsheet need reviewing today. Use a fixed rule: once daily, find READY and REVIEW rows due today or earlier and email their IDs to one internal teammate. The script reads no mailbox, contacts no clients and does not evaluate draft content.
This fits a small queue with one administrator. It requires Google Sheets and pasting a ready-made script into Apps Script; you do not need to write it. If your team already has CRM task reminders, use those rather than creating another queue.
Setup
- Create your own spreadsheet. Download any task’s blank queue below and import the CSV into a tab named Queue. Row one must contain exactly id, owner, source_version, packet, status, draft, review_due, last_notice, in that A:H order. One ID represents one case.
- Format A:H as plain text. Enter review_due as YYYY-MM-DD, such as 2026-10-10, and leave last_notice empty. Avoid ambiguous dates such as 10/11.
- In Sheets open Extensions → Apps Script. Replace Code.gs with the complete script below and save. Set spreadsheetId to the spreadsheet URL portion between /d/ and /edit, and reviewerEmail to your own or an approved internal address only. Set your actual timezone. Keep dryRun true for now.
- Select dailyReview in the function menu and Run. Review the requested permissions and authorize only your inspected project. If your administrator blocks execution, do not bypass that policy. Execution log shows the date, count and selected case IDs; nothing is sent yet.
- After the checks below, set dryRun false and run once manually. Confirm one email at the internal destination and today’s date in last_notice for those rows. Then open Triggers → Add Trigger: dailyReview, Head, Time-driven, Day timer, and your chosen daily window. Match the project timezone to the script. A time window does not guarantee an exact minute.
Complete Apps Script code
// Bound Google Sheets script. Configure only a permitted INTERNAL reviewer.
const REVIEW_CONFIG = {
spreadsheetId: 'REPLACE_WITH_SPREADSHEET_ID',
reviewerEmail: 'REPLACE_WITH_YOUR_INTERNAL_EMAIL',
timezone: 'Europe/Prague', // Use America/New_York or your actual business zone.
dryRun: true,
};
function collectDue(rows, today) {
if (!/^\d{4}-\d{2}-\d{2}$/.test(today)) throw new Error('Invalid current date');
const headers = ['id','owner','source_version','packet','status','draft','review_due','last_notice'];
if (JSON.stringify(rows[0]) !== JSON.stringify(headers)) throw new Error('Expected exact A:H headers');
const seen = new Set(); const due = [];
for (let i=1;i<rows.length;i++) {
const row=rows[i]; if (row.every(v=>!String(v).trim())) continue;
const id=String(row[0]).trim(); if (!id || seen.has(id)) throw new Error('Missing or duplicate case ID');
seen.add(id);
const date=String(row[6]).trim(); const status=String(row[4]).trim();
if (!['READY','REVIEW','HOLD','DONE'].includes(status)) throw new Error('Unknown queue status');
if (!/^\d{4}-\d{2}-\d{2}$/.test(date) || new Date(date+'T00:00:00Z').toISOString().slice(0,10)!==date) throw new Error('Invalid review_due');
if (!String(row[1]).trim()) throw new Error('Missing owner');
if (['READY','REVIEW'].includes(status) && date<=today && String(row[7]).trim()!==today) due.push({row:i+1,id,owner:String(row[1]),status});
}
return due;
}
function dailyReview() {
const cfg=REVIEW_CONFIG;
if (cfg.spreadsheetId.startsWith('REPLACE_') || cfg.reviewerEmail.startsWith('REPLACE_')) throw new Error('Set spreadsheet ID and internal reviewer');
const lock=LockService.getScriptLock(); lock.waitLock(10000);
try {
const properties=PropertiesService.getScriptProperties();
// Ambiguous send outcomes require a human check; never blindly retry email.
if (properties.getProperty('pendingReviewSend')) throw new Error('Check prior email outcome and resolve pendingReviewSend first');
const today=Utilities.formatDate(new Date(),cfg.timezone,'yyyy-MM-dd');
const sheet=SpreadsheetApp.openById(cfg.spreadsheetId).getSheetByName('Queue');
if(!sheet)throw new Error('Missing Queue tab');
const rows=sheet.getDataRange().getDisplayValues();
const due=collectDue(rows,today);
const body=due.map(x=>`${x.id} | ${x.owner} | ${x.status}`).join('\n');
if(cfg.dryRun){console.log(JSON.stringify({today,count:due.length,ids:due.map(x=>x.id)}));return;}
if(!due.length)return;
if(MailApp.getRemainingDailyQuota()<1)throw new Error('No email quota');
properties.setProperty('pendingReviewSend',JSON.stringify({today,rows:due.map(x=>x.row),ids:due.map(x=>x.id)}));
MailApp.sendEmail({to:cfg.reviewerEmail,subject:`Queue review ${today}`,body:`Work awaiting review:\n${body}\n\nOpen the original queue and verify each case. This is an internal reminder.`});
// One writer: do not reorder or edit rows while the script is running.
for(const item of due)sheet.getRange(item.row,8).setValue(today);
SpreadsheetApp.flush();
properties.deleteProperty('pendingReviewSend');
} finally {lock.releaseLock();}
}
- Proposals sit unanswered. — CSV ↓
- We cannot tell which proposal the client accepted. — CSV ↓
- The project is sold, but the delivery team cannot start. — CSV ↓
- Work is waiting for client materials. — CSV ↓
- Reports take too long and the numbers are hard to trust. — CSV ↓
- We cannot tell which delivered projects are paid. — CSV ↓
- Automation misses work or repeats it. — CSV ↓
Verification
Fictional trial: add A due today with REVIEW, B due tomorrow with READY, and C due today with DONE. Give each an owner. dryRun must select only A. After the real internal send, repeat execution: it must not send that row again today.
Duplicate ID A or supply an invalid date. Execution must fail before sending. Correct it and retry in dryRun. HOLD is excluded; the administrator must track its repair separately.
Pause and recovery
To pause, delete only this time-driven trigger in Apps Script, rather than the spreadsheet or client data. Inspect Executions before resuming. Mail and execution quotas depend on your account; exhausted quota prevents sending. No email therefore does not establish an empty queue.
If an error mentions pendingReviewSend, the preceding send outcome is uncertain and further runs stop. In Apps Script Project Settings → Script properties, inspect pendingReviewSend and note its IDs and date. Ask the internal recipient to confirm receipt. If received, enter that date in last_notice for those IDs; otherwise retain the previous value. Only then remove the pendingReviewSend property and run once with the trigger disabled. If uncertain, keep the property and review the queue manually.
The script locks its own executions, rather than manual row sorting or another workflow. Move no rows during a run. Multiple concurrent writers need a different design.
Sources
Checked against documentation October 10, 2026. Row selection and failure cases were verified with local tests; Google-account authorization and delivery were not tested.