Dissemination & filed-date entry · State Tracker recreation
Connecting…
Missing disseminationMissing filedClear filters
Mailer — dissemination pre-filled from CAPS (editable) Edited (unsaved)
Grassroots entries
Missing actual dateMissing filedClear filters
Pivot — Sum of Actual Cost by State
Mirrors the State Tracker Grassroot Pivot tab (Filed Date = blank, Actual Date = All). Recomputes live as filed dates are entered.
Products that have an invoice but no dissemination date entered. Mail products are excluded — door hangers are included (they need a manually-entered dissemination date). Enter the date inline to clear the exception.
Products with a start date older than 48 hours ago (before ) that still have no dissemination date. Mail excluded, door hangers included. Enter the date inline to clear the exception.
CAPS actual cost hits whose case number has no matching invoice in the system — these need an invoice created/linked. Mirrors the State Tracker CAPS hits w/o invoice tab.
Grassroots lines that have an actual cost but no actual date entered. Enter the actual date inline to clear the exception.
Set up shared entry for the EC team
By default this page saves to the server's shared store (Google Sheets or Postgres, chosen by the server's .env). Only use this if you need to override that and point this browser at a different store — e.g. testing your own Sheet.
Create a Google Sheet. Open Extensions → Apps Script.
Paste the script below, then Deploy → New deployment → Web app, execute as Me, access Anyone. Copy the /exec URL.
Paste that URL below and click Connect, or set it server-side as SHEETS_WEBHOOK_URL with STORAGE_BACKEND=sheets in .env so the whole team shares it automatically.
Apps Script code (click to expand)
const SHEET = 'edits';
function doGet(){ return out_(read_()); }
function doPost(e){
const body = JSON.parse(e.postData.contents || '{}');
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sh = ss.getSheetByName(SHEET) || ss.insertSheet(SHEET);
if(sh.getLastRow()===0) sh.appendRow(['key','disseminationDate','filedDate','actualDate','comment','by','at']);
const v = sh.getDataRange().getValues();
const rowForKey = {}; for(let i=1;i<v.length;i++) if(v[i][0]) rowForKey[v[i][0]]=i+1;
const patch = body.edits || {};
Object.keys(patch).forEach(k=>{
const r=patch[k];
const row=[k, r.disseminationDate||'', r.filedDate||'', r.actualDate||'', r.comment||'', r.by||'', new Date()];
if(rowForKey[k]) sh.getRange(rowForKey[k],1,1,row.length).setValues([row]);
else sh.appendRow(row);
});
return out_(read_());
}
function read_(){
const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET);
const o = {}; if(!sh) return {edits:o};
const v = sh.getDataRange().getValues();
for(let i=1;i<v.length;i++){ const r=v[i]; if(r[0]) o[r[0]]={disseminationDate:r[1],filedDate:r[2],actualDate:r[3],comment:r[4]}; }
return {edits:o};
}
function out_(obj){ return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON); }
Other backends: any endpoint that returns {edits:{...}} on GET and accepts {edits:{...}} on POST (only the changed keys) works — swap the URL above. To identify who made an edit, set your name when prompted.