/** * PIHU — Google Sheet backend + AI proxy * NKB Works · v1.1 * * 1. Paste this into Extensions > Apps Script of a new Google Sheet. * 2. Run setup() once (creates tabs with sample rows). * 3. Project Settings > Script Properties: add AI_PROVIDER, AI_KEY, AI_MODEL (optional), PIHU_TOKEN (recommended). * 4. Deploy > New deployment > Web app > Execute as: Me · Who has access: Anyone > copy the URL into Pihu Settings. * * Your AI key stays inside Google (Script Properties). It never goes to the phone or browser. */ const TABS = { Config: ['Key', 'Value'], Orders: ['ID', 'Customer', 'Issue', 'Status', 'Amount'], Stock: ['Item', 'Qty', 'Unit', 'ReorderLevel', 'Supplier', 'SupplierPhone'], Deliveries: ['Ref', 'Detail', 'Status'], FollowUps: ['Name', 'Detail', 'Status'], Tasks: ['ID', 'Task', 'Done', 'Created'], Log: ['Time', 'Action', 'Detail'], Bills: ['ID', 'Name', 'Category', 'Amount', 'Frequency', 'NextDue', 'PayTo', 'PayLink', 'Keyword', 'LastPaid'], Payments: ['Date', 'Payee', 'Amount', 'Method', 'Ref', 'Source', 'BillID'] }; const SAMPLE = { Config: [['BusinessName', 'Santiniketan'], ['DataMode', 'DEMO'], ['Note', 'Change DataMode to LIVE once these are your real records']], Orders: [ ['SN-1042', 'Hotel Sunrise, Andheri', 'Payment pending, dispatch on hold', 'Blocked', 18400], ['SN-1047', 'Green Bowl Café', 'Asked to change delivery to 4 PM', 'Change', 3200], ['SN-1051', 'Mehta family (subscription)', 'Paneer short by 2 kg', 'Short', 1850], ['SN-1053', 'Joshi Caterers', 'Bulk order 60 kg veg, confirm by 1 PM', 'Confirm', 22800], ['SN-1055', 'Kapoor residence', 'Weekly veg box', 'OK', 1450] ], Stock: [ ['A2 cow ghee (500 ml)', 4, 'jars', 15, 'Shree Dairy', ''], ['Organic spinach', 6, 'kg', 20, 'Patil Farms', ''], ['Fresh paneer', 9, 'kg', 12, 'Shree Dairy', ''], ['Cold-pressed groundnut oil', 11, 'L', 10, 'Kolhapur Oils', ''], ['Brown rice', 80, 'kg', 25, 'Patil Farms', ''] ], Deliveries: [ ['Route B – Goregaon', 'Van 2 delayed 45 min (traffic)', 'Delayed'], ['SN-1039 · Malad', 'Customer not reachable, returned', 'Failed'] ], FollowUps: [ ['Hotel Sunrise', '₹18,400 overdue 6 days', 'Overdue'], ['Patil Farms', 'Confirm tomorrow’s spinach quantity', 'Today'], ['Joshi Caterers', 'Send revised quote', 'Soon'] ], Tasks: [ ['t1', 'Clear Hotel Sunrise payment before dispatch', false, new Date()], ['t2', 'Reorder ghee & spinach from suppliers', false, new Date()], ['t3', 'Confirm Joshi Caterers bulk order by 1 PM', false, new Date()] ], Log: [], Bills: [ ['b1', 'Credit card bill', 'Credit card', 24500, 'Monthly', dPlus(3), '', '', 'credit card', ''], ['b2', 'LIC premium', 'Insurance', 8240, 'Quarterly', dPlus(9), '', 'https://licindia.in', 'LIC', ''], ['b3', 'Electricity', 'Utility', 3100, 'Monthly', dPlus(-2), '', '', 'electricity', ''], ['b4', 'Mobile postpaid', 'Utility', 599, 'Monthly', dPlus(12), '', '', 'postpaid', ''], ['b5', 'Broadband', 'Utility', 1178, 'Monthly', dPlus(20), '', '', 'broadband', ''], ['b6', 'House help salary', 'Salary', 9000, 'Monthly', dPlus(6), 'example@upi', '', 'house help', ''] ], Payments: [ [dPlus(-6), 'Water tax', 1450, 'UPI', '', 'Sample', ''], [dPlus(-11), 'Gas cylinder', 905, 'UPI', '', 'Sample', ''] ] }; function dPlus(n) { const d = new Date(); d.setHours(0, 0, 0, 0); d.setDate(d.getDate() + n); return d; } const DEFAULT_MODEL = { gemini: 'gemini-2.5-flash', groq: 'llama-3.3-70b-versatile', openrouter: 'meta-llama/llama-3.3-70b-instruct:free', openai: 'gpt-5-mini', anthropic: 'claude-haiku-5-5', custom: '' }; const BASE = { groq: 'https://api.groq.com/openai/v1', openrouter: 'https://openrouter.ai/api/v1', openai: 'https://api.openai.com/v1' }; /* ---------- One-time setup ---------- */ /** START HERE: one empty sheet for everything (business + bills). No sample rows. */ function setupFresh() { makeTabs(['Config', 'Tasks', 'Bills', 'Payments', 'Orders', 'Stock', 'Deliveries', 'FollowUps', 'Log'], true); const cfg = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config'); if (cfg.getLastRow() < 2) cfg.getRange(2, 1, 3, 2).setValues([['BusinessName', 'My Business'], ['YourName', ''], ['DataMode', 'LIVE']]); } /** Business sheet (orders, stock, deliveries…) */ function setup() { makeTabs(['Config', 'Orders', 'Stock', 'Deliveries', 'FollowUps', 'Tasks', 'Log']); } /** Personal money sheet (bills, payments) — use a SEPARATE Google Sheet for this */ function setupMoney() { makeTabs(['Config', 'Bills', 'Payments', 'Tasks', 'Log']); } function makeTabs(names, empty) { const ss = SpreadsheetApp.getActiveSpreadsheet(); names.forEach(name => { let sh = ss.getSheetByName(name); if (!sh) sh = ss.insertSheet(name); if (sh.getLastRow() === 0) { sh.appendRow(TABS[name]); sh.getRange(1, 1, 1, TABS[name].length).setFontWeight('bold').setBackground('#e8ecfb'); sh.setFrozenRows(1); const rows = empty ? [] : SAMPLE[name]; if (rows && rows.length) sh.getRange(2, 1, rows.length, rows[0].length).setValues(rows); } }); const def = ss.getSheetByName('Sheet1'); if (def && def.getLastRow() === 0 && ss.getSheets().length > 1) ss.deleteSheet(def); Logger.log('Pihu setup done.'); } /* ---------- Web endpoints ---------- */ function doGet(e) { const p = (e && e.parameter) || {}; if (!authOk(p.token)) return json({ ok: false, error: 'Wrong access code' }); if ((p.action || 'data') === 'data') return json({ ok: true, data: readAll(), updated: new Date().toISOString() }); if (p.action === 'ping') return json({ ok: true, pong: true }); return json({ ok: false, error: 'Unknown action' }); } function doPost(e) { let b = {}; try { b = JSON.parse(e.postData.contents || '{}'); } catch (err) { return json({ ok: false, error: 'Bad request' }); } if (!authOk(b.token)) return json({ ok: false, error: 'Wrong access code' }); try { if (b.action === 'chat') return json(chat(b)); if (b.action === 'batch') return json(runOps(b.ops || [])); if (b.action === 'ping') return json({ ok: true, pong: true, ai: !!props().getProperty('AI_KEY') }); return json({ ok: false, error: 'Unknown action' }); } catch (err) { return json({ ok: false, error: String(err && err.message || err) }); } } /* ---------- Helpers ---------- */ function props() { return PropertiesService.getScriptProperties(); } function authOk(t) { const need = props().getProperty('PIHU_TOKEN'); return !need || t === need; } function json(o) { return ContentService.createTextOutput(JSON.stringify(o)).setMimeType(ContentService.MimeType.JSON); } function readAll() { const ss = SpreadsheetApp.getActiveSpreadsheet(), out = {}; Object.keys(TABS).forEach(name => { if (name === 'Log') return; const sh = ss.getSheetByName(name); if (!sh || sh.getLastRow() < 2) { out[name] = []; return; } let v = sh.getDataRange().getValues(); const h = v.shift(); if (name === 'Payments') v = v.slice(-200); out[name] = v.filter(r => r.join('') !== '').map(r => { const o = {}; h.forEach((k, i) => o[k] = r[i] instanceof Date ? r[i].toISOString() : r[i]); return o; }); }); return out; } function runOps(ops) { const lock = LockService.getScriptLock(); lock.waitLock(10000); const ss = SpreadsheetApp.getActiveSpreadsheet(), done = []; try { ops.forEach(op => { if (op.op === 'addTask') { const tsh = ss.getSheetByName('Tasks'); const have = tsh.getLastRow() > 1 ? tsh.getRange(2, 1, tsh.getLastRow() - 1, 1).getValues().map(r => String(r[0])) : []; if (have.indexOf(String(op.id)) === -1) tsh.appendRow([op.id, op.task, false, new Date()]); } else if (op.op === 'toggleTask') { const sh = ss.getSheetByName('Tasks'), ids = sh.getRange(1, 1, sh.getLastRow(), 1).getValues(); for (let i = 1; i < ids.length; i++) if (String(ids[i][0]) === String(op.id)) { sh.getRange(i + 1, 3).setValue(!!op.done); break; } } else if (op.op === 'payBill' || op.op === 'addPayment') { ss.getSheetByName('Payments').appendRow([new Date(), op.payee || '', +op.amount || 0, op.method || 'UPI', op.ref || '', 'Pihu', op.billId || '']); if (op.billId) advanceBill(ss, op.billId, new Date()); } else if (op.op === 'log') { ss.getSheetByName('Log').appendRow([new Date(), op.what || '', op.detail || '']); } done.push(op.qid || null); }); } finally { lock.releaseLock(); } return { ok: true, done: done }; } /* ---------- AI proxy (provider adapters) ---------- */ function chat(b) { const p = props(); const prov = (p.getProperty('AI_PROVIDER') || 'gemini').toLowerCase(); const key = p.getProperty('AI_KEY'); const model = p.getProperty('AI_MODEL') || DEFAULT_MODEL[prov]; const base = p.getProperty('AI_BASE_URL') || BASE[prov]; if (!key) return { ok: false, error: 'AI_KEY is not set in Script Properties' }; const msgs = (b.messages || []).slice(-12); let url, body, headers = {}, pick; if (prov === 'gemini') { url = 'https://generativelanguage.googleapis.com/v1beta/models/' + model + ':generateContent'; headers['x-goog-api-key'] = key; body = { systemInstruction: { parts: [{ text: b.system || '' }] }, contents: msgs.map(m => ({ role: m.role === 'assistant' ? 'model' : 'user', parts: [{ text: m.content }] })) }; pick = j => j.candidates[0].content.parts.map(x => x.text || '').join(''); } else if (prov === 'anthropic') { url = 'https://api.anthropic.com/v1/messages'; headers['x-api-key'] = key; headers['anthropic-version'] = '2023-06-01'; body = { model: model, max_tokens: 1200, system: b.system || '', messages: msgs }; pick = j => j.content.map(x => x.text || '').join(''); } else { if (!base) return { ok: false, error: 'Set AI_BASE_URL for provider ' + prov }; url = base.replace(/\/$/, '') + '/chat/completions'; headers['Authorization'] = 'Bearer ' + key; body = { model: model, messages: [{ role: 'system', content: b.system || '' }].concat(msgs) }; pick = j => j.choices[0].message.content; } const r = UrlFetchApp.fetch(url, { method: 'post', contentType: 'application/json', headers: headers, payload: JSON.stringify(body), muteHttpExceptions: true }); let j; try { j = JSON.parse(r.getContentText()); } catch (e) { return { ok: false, error: 'AI returned an unreadable reply' }; } if (r.getResponseCode() >= 300) { const m = j.error ? (j.error.message || JSON.stringify(j.error)) : ('HTTP ' + r.getResponseCode()); return { ok: false, error: m }; } return { ok: true, text: pick(j), provider: prov, model: model }; } /* ---------- Bills helpers ---------- */ function advanceBill(ss, billId, paidOn) { const sh = ss.getSheetByName('Bills'); if (!sh || sh.getLastRow() < 2) return; const v = sh.getDataRange().getValues(), h = v[0]; const c = k => h.indexOf(k); for (let i = 1; i < v.length; i++) { if (String(v[i][c('ID')]) !== String(billId)) continue; const f = String(v[i][c('Frequency')] || 'Monthly').toLowerCase(); let due = v[i][c('NextDue')] instanceof Date ? new Date(v[i][c('NextDue')]) : new Date(); const add = f.indexOf('quarter') > -1 ? 3 : f.indexOf('half') > -1 ? 6 : f.indexOf('year') > -1 || f.indexOf('annual') > -1 ? 12 : f.indexOf('once') > -1 ? 0 : 1; if (add) { do { due.setMonth(due.getMonth() + add); } while (due <= paidOn); sh.getRange(i + 1, c('NextDue') + 1).setValue(due); } else sh.getRange(i + 1, c('NextDue') + 1).setValue(''); sh.getRange(i + 1, c('LastPaid') + 1).setValue(paidOn); return; } } /* ---------- Gmail payment alerts (optional, your consent) ---------- * Run setupGmailScan() once to allow Pihu to read bank / UPI alert emails in THIS Google account * and log them in the Payments tab every hour. Run stopGmailScan() to switch it off. * Only messages that look like debit/payment alerts are read; each is labelled "Pihu-logged". */ function setupGmailScan() { stopGmailScan(); ScriptApp.newTrigger('scanGmail').timeBased().everyHours(1).create(); scanGmail(); } function stopGmailScan() { ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() === 'scanGmail').forEach(t => ScriptApp.deleteTrigger(t)); } function scanGmail() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const label = GmailApp.getUserLabelByName('Pihu-logged') || GmailApp.createLabel('Pihu-logged'); const q = 'newer_than:7d -label:Pihu-logged (debited OR "payment of" OR "paid to" OR "successfully paid" OR "transaction of") (Rs OR INR OR ₹)'; const threads = GmailApp.search(q, 0, 40); const bills = (ss.getSheetByName('Bills').getLastRow() > 1) ? ss.getSheetByName('Bills').getDataRange().getValues() : []; const bh = bills.shift() || []; threads.forEach(th => { th.getMessages().forEach(m => { const txt = (m.getSubject() + ' ' + m.getPlainBody()).replace(/\s+/g, ' ').slice(0, 3000); if (/credited to your|has been credited|received from/i.test(txt) && !/debited/i.test(txt)) return; const am = txt.match(/(?:Rs\.?|INR|₹)\s?([\d,]+(?:\.\d{1,2})?)/i); if (!am) return; const amount = parseFloat(am[1].replace(/,/g, '')); if (!amount) return; const pm = txt.match(/(?:paid to|to VPA|to|towards|for)\s+([A-Za-z0-9@._& -]{3,40}?)(?:\s+on\b|\s+via\b|\s+ref|\.|,|$)/i); const rf = txt.match(/(?:UPI\s*Ref(?:erence)?(?:\s*No\.?)?|Ref(?:erence)?\s*(?:No|Number)\.?|RRN)[:\s#]*([0-9A-Z]{6,})/i); let billId = ''; for (const b of bills) { const kw = String(b[bh.indexOf('Keyword')] || '').trim(); if (kw && txt.toLowerCase().indexOf(kw.toLowerCase()) > -1) { billId = b[bh.indexOf('ID')]; break; } } ss.getSheetByName('Payments').appendRow([m.getDate(), pm ? pm[1].trim() : (m.getFrom().replace(/<.*>/, '').trim()), amount, /upi/i.test(txt) ? 'UPI' : 'Bank/Card', rf ? rf[1] : '', 'Gmail', billId]); if (billId) advanceBill(ss, billId, m.getDate()); }); th.addLabel(label); }); }