VPR: per-vendor fillable Google Sheet — make-sheet.js (CSV→Sheet in shared Drive folder, anyone-can-edit, prefilled mfr_sku/name/color + blank width/repeat/contents/discount/retail/net), /api/sheet endpoint, sheet link auto-woven into the letter, Create-sheet page button

cc4422b085c183743b018e07fdeee32f72a0dd12 · 2026-06-17 13:59:01 -0700 · Steve Abrams
Files touched

A make-sheet.js
M public/index.html
M server.js
Diff

commit cc4422b085c183743b018e07fdeee32f72a0dd12
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jun 17 13:59:01 2026 -0700

    VPR: per-vendor fillable Google Sheet — make-sheet.js (CSV→Sheet in shared Drive folder, anyone-can-edit, prefilled mfr_sku/name/color + blank width/repeat/contents/discount/retail/net), /api/sheet endpoint, sheet link auto-woven into the letter, Create-sheet page button
---
 make-sheet.js     | 46 ++++++++++++++++++++++++++++++++++++++++++++++
 public/index.html |  3 +++
 server.js         | 22 +++++++++++++++++-----
 3 files changed, 66 insertions(+), 5 deletions(-)

diff --git a/make-sheet.js b/make-sheet.js
new file mode 100644
index 0000000..d21d472
--- /dev/null
+++ b/make-sheet.js
@@ -0,0 +1,46 @@
+'use strict';
+/**
+ * Create a per-vendor fillable Google Sheet in the shared "DW Vendors + Price Lists"
+ * Drive folder: pre-filled with our SKUs (MFR SKU / Name / Color), blank columns for
+ * the vendor to fill (Width, Repeat, Contents, Discount %, Retail Price, Net Price).
+ * Uploads CSV→Sheet via rclone, sets anyone-with-link=writer (editable), saves the URL
+ * to vendor_contacts.sheet_url. Usage: node make-sheet.js "<Vendor>"
+ */
+const fs = require('fs');
+const { execSync } = require('child_process');
+const PGENV = { PGHOST: '/tmp', PGPORT: '5432', PGUSER: 'stevestudio2', PGDATABASE: 'dw_unified', PATH: '/opt/homebrew/opt/postgresql@14/bin:/usr/local/bin:/opt/homebrew/bin:/usr/bin:/bin' };
+const FOLDER = 'gdrive:DW Vendors + Price Lists';
+const vendor = process.argv[2];
+if (!vendor) { console.error('usage: node make-sheet.js "<Vendor>"'); process.exit(1); }
+function psql(s) { return execSync('psql -tA', { input: s, env: PGENV, encoding: 'utf8' }); }
+function csvCell(s) { s = String(s == null ? '' : s); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; }
+
+// 1. rows for this vendor (unpriced, not own-brand)
+const raw = psql(`SELECT mfr_sku||''||COALESCE(pattern,'')||''||COALESCE(color,'') FROM price_sourcing WHERE vendor=$v$${vendor}$v$ AND COALESCE(has_price,false)=false AND COALESCE(letter_status,'none')<>'skip-own-brand' ORDER BY pattern, mfr_sku`);
+const rows = raw.split('\n').filter(Boolean).map(l => l.split(''));
+if (!rows.length) { console.error('no rows for vendor'); process.exit(2); }
+
+// 2. CSV: pre-filled MFR SKU/Name/Color; blanks for the vendor
+const header = ['MFR SKU', 'Name', 'Color', 'Width', 'Repeat', 'Contents', 'Discount %', 'Retail Price', 'Net Price'];
+const lines = [header.join(',')].concat(rows.map(r => [r[0], r[1], r[2], '', '', '', '', '', ''].map(csvCell).join(',')));
+const safeName = vendor.replace(/[\/\\]/g, '-');
+const tmp = `/tmp/vpr-sheet-${Date.now()}.csv`;
+fs.writeFileSync(tmp, lines.join('\n'));
+
+// 3. upload CSV -> Google Sheet (this also refreshes rclone's token)
+const sheetName = `${safeName} - Price Request`;
+execSync(`rclone copyto ${JSON.stringify(tmp)} ${JSON.stringify(FOLDER + '/' + sheetName)} --drive-import-formats csv`, { stdio: 'pipe' });
+fs.unlinkSync(tmp);
+
+// 4. resolve the new file id, set anyone-with-link=writer (editable), build link
+const id = execSync(`rclone lsjson ${JSON.stringify(FOLDER)} 2>/dev/null`, { encoding: 'utf8' });
+const item = JSON.parse(id).find(x => x.Name === sheetName);
+if (!item) { console.error('uploaded sheet not found'); process.exit(3); }
+const fileId = item.ID;
+const tok = (() => { const d = JSON.parse(execSync('rclone config dump', { encoding: 'utf8' })); return JSON.parse(d.gdrive.token).access_token; })();
+execSync(`curl -s -X POST "https://www.googleapis.com/drive/v3/files/${fileId}/permissions" -H "Authorization: Bearer ${tok}" -H "Content-Type: application/json" -d '{"role":"writer","type":"anyone"}'`, { stdio: 'pipe' });
+const url = `https://docs.google.com/spreadsheets/d/${fileId}/edit`;
+
+// 5. persist
+psql(`INSERT INTO vendor_contacts(vendor,sheet_url,sheet_created_at) VALUES($v$${vendor}$v$,$v$${url}$v$,now()) ON CONFLICT(vendor) DO UPDATE SET sheet_url=$v$${url}$v$, sheet_created_at=now()`);
+console.log(JSON.stringify({ ok: true, vendor, rows: rows.length, url }));
diff --git a/public/index.html b/public/index.html
index 7dd1ce8..5696d69 100644
--- a/public/index.html
+++ b/public/index.html
@@ -69,13 +69,16 @@ async function pick(v){
     <div class="row"><div style="flex:1"><label>Recipient email</label><input id="to" value="${esc(d.email||'')}" placeholder="purchasing@vendor.com"></div></div>
     <div class="row" style="flex-direction:column;align-items:stretch"><label>Subject</label><input id="subj" value="${esc(d.subject)}"></div>
     <div class="row" style="flex-direction:column;align-items:stretch"><label>Letter (editable)${d.hasDraft?' <span class="badge-d">• saved draft'+(d.draftSavedAt?' '+new Date(d.draftSavedAt).toLocaleString(undefined,{month:'short',day:'numeric',hour:'numeric',minute:'2-digit'}):'')+'</span>':''}</label><textarea id="body">${esc(d.body)}</textarea></div>
+    ${d.sheetUrl?`<div class="row"><label>Price-list sheet</label> <a href="${esc(d.sheetUrl)}" target="_blank" rel="noopener">${esc(d.sheetUrl)}</a></div>`:''}
     <div class="row"><button onclick="saveEmail()">Save email</button>
+      <button class="save" id="sheetBtn" onclick="makeSheet()">📄 ${d.sheetUrl?'Recreate':'Create'} price-list sheet</button>
       <button class="save" onclick="saveDraft()">✎ Save draft</button>
       <button class="send" id="sendBtn" onclick="send()">✉ Approve &amp; Send to vendor</button>
       <span style="font-size:12px;color:var(--muted)">Sends from info@designerwallcoverings.com via George</span></div>`;
 }
 async function saveEmail(){ await fetch("/api/contact/"+encodeURIComponent(cur),{method:"POST",headers:{'Content-Type':'application/json'},body:JSON.stringify({email:$("#to").value})}); toast("Email saved"); load(); }
 async function saveDraft(){ await fetch("/api/draft/"+encodeURIComponent(cur),{method:"POST",headers:{'Content-Type':'application/json'},body:JSON.stringify({subject:$("#subj").value,body:$("#body").value,email:$("#to").value})}); toast("Draft saved"); load(); }
+async function makeSheet(){ const b=$("#sheetBtn"); b.disabled=true; b.textContent="📄 Creating sheet…"; try{ const r=await fetch("/api/sheet/"+encodeURIComponent(cur),{method:"POST"}); const d=await r.json(); if(d.ok){ toast("✓ Sheet created ("+d.rows+" rows)"); pick(cur); } else { toast("⚠ "+(d.error||"failed")); b.disabled=false; b.textContent="📄 Create price-list sheet"; } }catch(e){ toast("⚠ "+e.message); b.disabled=false; } }
 async function send(){
   const to=$("#to").value.trim(); if(!/.+@.+\..+/.test(to)){toast("Enter a valid recipient email");return;}
   if(!confirm("Send this pricing request to "+to+" ?")) return;
diff --git a/server.js b/server.js
index 6bf351a..26e72a6 100644
--- a/server.js
+++ b/server.js
@@ -27,9 +27,12 @@ async function george(p, { method = 'GET', body } = {}) {
   if (!res.ok) throw new Error(d.error || t.slice(0, 200));
   return d;
 }
-function letterBody(vendor, rows) {
+function letterBody(vendor, rows, sheetUrl) {
   const lines = rows.map(r => `  • ${r.mfr_sku || r.dw_sku}${r.pattern ? '  —  ' + r.pattern : ''}${r.color ? ' / ' + r.color : ''}`).join('\n');
-  return `Hello,\n\nDesigner Wallcoverings is preparing to feature the following ${vendor} patterns in our showroom and online catalog, and we'd like to confirm current trade (wholesale) pricing and any applicable dealer discount so we can list them correctly.\n\nCould you please provide current per-unit trade pricing (and unit of measure — per roll / per yard / per panel) for the following items:\n\n${lines}\n\nA current price list covering these SKUs works perfectly if easier on your end. Thank you — we appreciate it and look forward to showcasing the line.\n\nBest regards,\nDesigner Wallcoverings\ninfo@designerwallcoverings.com`;
+  const sheetBlock = sheetUrl
+    ? `We've prepared a shared spreadsheet pre-filled with the ${rows.length} patterns below — please fill in the blank columns (width, repeat, contents, discount, retail price, net price) directly in it:\n${sheetUrl}\n\n(The same SKU list is below for reference.)\n\n`
+    : '';
+  return `Hello,\n\nDesigner Wallcoverings is preparing to feature the following ${vendor} patterns in our showroom and online catalog, and we'd like to confirm current trade (wholesale) pricing and any applicable dealer discount so we can list them correctly.\n\n${sheetBlock}Could you please provide current per-unit trade pricing (and unit of measure — per roll / per yard / per panel) for the following items:\n\n${lines}\n\nA current price list covering these SKUs works perfectly if easier on your end. Thank you — we appreciate it and look forward to showcasing the line.\n\nBest regards,\nDesigner Wallcoverings\ninfo@designerwallcoverings.com`;
 }
 
 // per-vendor summary of gaps (+ draft flag)
@@ -51,11 +54,11 @@ app.get('/api/letter/:vendor', async (req, res) => {
   try {
     const v = req.params.vendor;
     const rows = (await pool.query(`SELECT dw_sku, mfr_sku, pattern, color FROM price_sourcing WHERE vendor=$1 AND crawl_status<>'found' AND COALESCE(has_price,false)=false AND COALESCE(letter_status,'none')<>'skip-own-brand' ORDER BY pattern, mfr_sku`, [v])).rows;
-    const vc = (await pool.query(`SELECT email, draft_subject, draft_body, draft_saved_at FROM vendor_contacts WHERE vendor=$1`, [v])).rows[0] || {};
+    const vc = (await pool.query(`SELECT email, draft_subject, draft_body, draft_saved_at, sheet_url FROM vendor_contacts WHERE vendor=$1`, [v])).rows[0] || {};
     const hasDraft = !!(vc.draft_body && vc.draft_body.trim());
-    res.json({ vendor: v, count: rows.length, email: vc.email || '',
+    res.json({ vendor: v, count: rows.length, email: vc.email || '', sheetUrl: vc.sheet_url || '',
       subject: hasDraft ? vc.draft_subject : `Trade pricing request — ${v} (${rows.length} patterns)`,
-      body: hasDraft ? vc.draft_body : letterBody(v, rows),
+      body: hasDraft ? vc.draft_body : letterBody(v, rows, vc.sheet_url),
       hasDraft, draftSavedAt: vc.draft_saved_at || null, skus: rows.slice(0, 500) });
   } catch (e) { res.status(500).json({ error: e.message }); }
 });
@@ -88,5 +91,14 @@ app.post('/api/send/:vendor', async (req, res) => {
     res.json({ ok: true, sent });
   } catch (e) { res.status(500).json({ error: e.message }); }
 });
+// create the per-vendor fillable Google Sheet in the shared Drive folder
+app.post('/api/sheet/:vendor', (req, res) => {
+  const { execFile } = require('child_process');
+  execFile('node', [path.join(__dirname, 'make-sheet.js'), req.params.vendor], { timeout: 120000 }, (err, stdout, stderr) => {
+    if (err) return res.status(500).json({ error: (stderr || err.message || '').slice(0, 300) });
+    try { res.json(JSON.parse(stdout.trim().split('\n').pop())); }
+    catch (e) { res.status(500).json({ error: 'sheet created but parse failed: ' + stdout.slice(-200) }); }
+  });
+});
 app.get('/api/health', (req, res) => res.json({ ok: true, george: !!GEORGE }));
 app.listen(PORT, () => console.log(`Vendor Price Requests → http://127.0.0.1:${PORT}`));
← 75f5291 VPR: drafts first-class — save-draft persistence, draft/sent · back to Vendor Price Requests · Move default port 9620→9803 to avoid va-schumacher EADDRINUS 3a34121 →