CRE: comprehensive 5-tab Google Sheet exporter (commercial brokers + residential agents + firms + warrantable condos + 17k closed sales), shares to steve@ as writer

2b99293d31fa31ea581c40078b1f475b521f7e93 · 2026-06-28 14:44:01 -0700 · Steve
Files touched

M scripts/export-to-gsheet.js
Diff

commit 2b99293d31fa31ea581c40078b1f475b521f7e93
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Jun 28 14:44:01 2026 -0700

    CRE: comprehensive 5-tab Google Sheet exporter (commercial brokers + residential agents + firms + warrantable condos + 17k closed sales), shares to steve@ as writer
---
 scripts/export-to-gsheet.js | 36 ++++++++++++++++++++++++++----------
 1 file changed, 26 insertions(+), 10 deletions(-)

diff --git a/scripts/export-to-gsheet.js b/scripts/export-to-gsheet.js
index 8decd41..1f2998a 100644
--- a/scripts/export-to-gsheet.js
+++ b/scripts/export-to-gsheet.js
@@ -30,31 +30,47 @@ async function token() {
 }
 
 // ---- gather data from the cre DB + FHA file ----
+const COLS = async (name) => (await pool.query(`SELECT 1 FROM information_schema.columns WHERE table_name=$1 AND column_name='website'`, [name])).rowCount;
 async function gather() {
-  const brokers = (await pool.query(`SELECT name, firm, listings, total_assets, phone, email FROM broker_node ORDER BY listings DESC NULLS LAST LIMIT 500`)).rows;
-  const firms = (await pool.query(`SELECT f.name firm, count(DISTINCT bl.listing_id)::int listings, count(DISTINCT b.id)::int brokers FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY f.name ORDER BY 2 DESC LIMIT 100`)).rows;
+  const webCol = await COLS('broker') ? ', b.website' : ', NULL website';
+  const commercial = (await pool.query(`SELECT b.name, f.name firm,
+      (SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)::int listings, b.total_assets, b.phone, b.email ${webCol}
+      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='commercial' OR b.agent_type IS NULL
+      ORDER BY listings DESC NULLS LAST`)).rows;
+  const agents = (await pool.query(`SELECT b.name, f.name firm,
+      (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)::int condos, b.phone, b.email ${webCol}, b.license
+      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='residential'
+      ORDER BY condos DESC NULLS LAST`).catch(() => ({ rows: [] }))).rows;
+  const firms = (await pool.query(`SELECT f.name firm, count(DISTINCT bl.listing_id)::int listings, count(DISTINCT b.id)::int brokers FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY f.name ORDER BY 2 DESC LIMIT 200`)).rows;
+  const sales = (await pool.query(`SELECT address, city, zip, sold_price, sold_date, beds, baths, sqft FROM closed_sale WHERE sold_price>0 ORDER BY sold_date DESC NULLS LAST`).catch(() => ({ rows: [] }))).rows;
   let condos = [];
-  try { condos = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'fha-approved-condos.json'), 'utf8')).condos.filter(c => c.warrant_signal === 'fha_approved').slice(0, 400); } catch (_) {}
-  return { brokers, firms, condos };
+  try { condos = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'fha-approved-condos.json'), 'utf8')).condos.filter(c => c.warrant_signal === 'fha_approved'); } catch (_) {}
+  return { commercial, agents, firms, sales, condos };
 }
 const A1 = rows => rows.map(r => r.map(v => v == null ? '' : v));
 
 (async () => {
   const tok = await token();
   const auth = { Authorization: 'Bearer ' + tok };
-  const { brokers, firms, condos } = await gather();
+  const { commercial, agents, firms, sales, condos } = await gather();
+  const d = v => v ? String(v).slice(0, 10) : '';
 
   const sheets = [
-    { title: 'Brokers', header: ['Broker', 'Firm', 'Listings (our set)', 'Total Crexi book', 'Phone', 'Email'],
-      rows: brokers.map(b => [b.name, b.firm, b.listings, b.total_assets, b.phone, b.email]) },
+    { title: 'Commercial Brokers', header: ['Broker', 'Firm', 'Listings', 'Total Crexi book', 'Phone', 'Email', 'Website'],
+      rows: commercial.map(b => [b.name, b.firm, b.listings, b.total_assets, b.phone, b.email, b.website]) },
+    { title: 'Residential Agents', header: ['Agent', 'Firm', 'Condos', 'Phone', 'Email', 'Website', 'License'],
+      rows: agents.map(a => [a.name, a.firm, a.condos, a.phone, a.email, a.website, a.license]) },
     { title: 'Firms', header: ['Firm', 'Listings', 'Brokers'], rows: firms.map(f => [f.firm, f.listings, f.brokers]) },
-    { title: 'Warrantable Condos (FHA-approved)', header: ['Project', 'City', 'ZIP', 'Status', 'Expiration'],
-      rows: condos.map(c => [c.project_name, c.city, c.zip, 'FHA-approved (proxy, not lender-verified)', c.expiration_date]) }
+    { title: 'Warrantable Condos (FHA)', header: ['Project', 'City', 'ZIP', 'Status (proxy)', 'Expiration'],
+      rows: condos.map(c => [c.project_name, c.city, c.zip, 'FHA-approved (proxy, not lender-verified)', c.expiration_date]) },
+    { title: 'Closed Sales (Redfin ~5yr)', header: ['Address', 'City', 'ZIP', 'Sold Price', 'Sold Date', 'Beds', 'Baths', 'SqFt'],
+      rows: sales.map(s => [s.address, s.city, s.zip, s.sold_price, d(s.sold_date), s.beds, s.baths, s.sqft]) }
   ];
+  console.log('tabs:', sheets.map(s => `${s.title}(${s.rows.length})`).join(', '));
 
   let id = process.env.GSHEET_ID;
   if (!id) {
-    const create = JSON.stringify({ properties: { title: 'LA County CRE — Brokers · Firms · Warrantable Condos' }, sheets: sheets.map(s => ({ properties: { title: s.title } })) });
+    const create = JSON.stringify({ properties: { title: 'LA County CRE — Brokers · Agents · Firms · Condos · Sales' }, sheets: sheets.map(s => ({ properties: { title: s.title } })) });
     const r = await req('POST', 'sheets.googleapis.com', '/v4/spreadsheets', { ...auth, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(create) }, create);
     if (r.status !== 200) throw new Error('create ' + r.status + ' ' + r.body.slice(0, 300));
     id = JSON.parse(r.body).spreadsheetId;
← a880a29 afternoon CRE update 2026-06-28 · back to Commercialrealestate · CRE: local XLSX exporter (no Drive API) — 5-tab workbook of 99ec9a7 →