Weekly Pipeline Report

Step 3 · Code by Zapier · JavaScript

const deals = JSON.parse(inputData.dealsJson).data || [];
const openStages = ["Qualification", "Needs Analysis", "Value Proposition",
  "Identify Decision Makers", "Proposal/Price Quote", "Negotiation/Review"];
const today = new Date().toISOString().split('T')[0];
const summary = {};
openStages.forEach(s => summary[s] = { count: 0, total: 0, weighted: 0 });
deals.forEach(d => {
  if (!summary[d.Stage]) return;            // skips Closed Won / Closed Lost
  const amt = parseFloat(d.Amount) || 0;
  const prob = parseFloat(d.Probability) || 0;
  summary[d.Stage].count += 1;
  summary[d.Stage].total += amt;
  summary[d.Stage].weighted += amt * (prob / 100);
});
// parallel arrays: index i of every array = one sheet row
const dates = [], stages = [], counts = [], totals = [], weighted = [];
let gC = 0, gT = 0, gW = 0;
openStages.forEach(s => {
  const v = summary[s];
  dates.push(today); stages.push(s); counts.push(v.count);
  totals.push(v.total.toFixed(2)); weighted.push(v.weighted.toFixed(2));
  gC += v.count; gT += v.total; gW += v.weighted;
});
dates.push(today); stages.push("TOTAL"); counts.push(gC);
totals.push(gT.toFixed(2)); weighted.push(gW.toFixed(2));
return { dates, stages, deal_counts: counts, total_values: totals,
  weighted_values: weighted, grand_count: gC,
  grand_total: gT.toFixed(2), grand_weighted: gW.toFixed(2) };