Dealing Pro - Discount, affiliate, sheet automations, records saving logic backend 2✅
// ==========================================
// CHANNEL DEALS & PT SEVA DEALS - COMPLETE BACKEND (v1.36 - Negative Wallet Recovery & Auto-Archive Fix)
// ==========================================
const SHEET_ID = '1YY4ww8j_XQqPYoAr-AiQz2LNW6vQ24615XG101puhak';
const ADMIN_WHATSAPP = "917987744656";
function getValidPrice(saleVal, purchVal) {
let s = parseFloat(saleVal);
if (!isNaN(s) && s > 0) return s;
let p = parseFloat(purchVal);
if (!isNaN(p) && p > 0) {
if (p <= 5000) return Math.round(p * 1.35);
if (p <= 10000) return Math.round(p * 1.31);
if (p <= 30000) return Math.round(p * 1.28);
if (p <= 50000) return Math.round(p * 1.24);
if (p <= 80000) return Math.round(p * 1.21);
if (p <= 100000) return Math.round(p * 1.17);
return Math.round(p * 1.14);
}
return "";
}
function doGet(e) {
try {
const ss = SpreadsheetApp.openById(SHEET_ID);
const action = (e.parameter && e.parameter.action) ? e.parameter.action : "getChannels";
if (action === "getDashboardData") {
const channelSheet = ss.getSheetByName("New Channels");
if (!channelSheet) return jsonResponse({ error: "Channels Sheet not found" });
const cData = channelSheet.getDataRange().getValues();
let channelsList = [], activeCount = 0, pendingCount = 0, removedCount = 0, soldCount = 0;
for (let i = 1; i < cData.length; i++) {
let row = cData[i];
if (row[0] === "") continue;
let status = row[3] ? row[3].toString().trim().toUpperCase() : "";
channelsList.push({
date: row[0].toString(), channelId: row[4] || "N/A", status: status,
purchasePrice: row[18] ? row[18].toString().trim() : "", sellerWa: row[1] || "N/A"
});
if (status === "APPROVED") activeCount++;
else if (status === "PENDING" || status === "HOLD" || status === "INVITED 📩" || status === "NEW INVITE ❓") pendingCount++;
else if (status === "ACCESS_REMOVED 🚨" || status === "ACCESS_REMOVED" || status === "REFUNDED" || status === "DEAL CANCELLED") removedCount++;
else if (status === "SOLD OUT") soldCount++;
}
return jsonResponse({
metrics: { active: activeCount, pending: pendingCount, removed: removedCount, sold: soldCount },
operations: channelsList.reverse(),
finance: { totalSales: 0, totalExpense: 0, netProfit: 0, recentTransactions: [], chartData: {} }
});
}
if (action === "getAdminDashboard") {
const sheet = ss.getSheetByName("New Channels");
if (!sheet) return jsonResponse({ error: "Sheet not found" });
const data = sheet.getDataRange().getValues();
let dashboardData = { totalChannels: 0, activeStock: 0, accessPending: 0, accessRemoved: 0, soldOut: 0, monetizedCount: 0, nonMonetizedCount: 0, recentList: [] };
for (let i = data.length - 1; i > 0; i--) {
let row = data[i];
if (!row[4]) continue;
dashboardData.totalChannels++;
let status = row[3] ? row[3].toString().trim().toUpperCase() : "";
let isMonetized = row[8] ? row[8].toString().trim().toLowerCase() : "no";
if (status === "APPROVED") dashboardData.activeStock++;
else if (status === "PENDING" || status === "INVITED 📩" || status === "NEW INVITE ❓") dashboardData.accessPending++;
else if (status === "SOLD OUT") dashboardData.soldOut++;
else if (status === "ACCESS_REMOVED 🚨" || status === "ACCESS_REMOVED" || status === "REFUNDED" || status === "DEAL CANCELLED") dashboardData.accessRemoved++;
if (status === "APPROVED") {
if (isMonetized === "yes" || isMonetized === "half") dashboardData.monetizedCount++;
else dashboardData.nonMonetizedCount++;
}
if (dashboardData.recentList.length < 10) {
dashboardData.recentList.push({ date: row[0] ? new Date(row[0]).toLocaleDateString('en-IN') : "N/A", id: row[4], name: row[5] || "Unknown", status: status, ownerWa: row[1] || "N/A" });
}
}
return jsonResponse(dashboardData);
}
if (action === "getInventory") {
let realDiscount = 0, fakeDiscount = 0, currentOfferName = "Special Offer";
const settingsSheet = ss.getSheetByName("Settings");
if (settingsSheet) {
realDiscount = parseFloat(settingsSheet.getRange("B1").getValue()) || 0;
fakeDiscount = parseFloat(settingsSheet.getRange("E4").getValue()) || 0;
let fetchedName = settingsSheet.getRange("B2").getValue();
if (fetchedName) currentOfferName = fetchedName.toString().trim();
}
function fetchSheet(sheetName, mapper) {
const sheet = ss.getSheetByName(sheetName);
if(!sheet) return [];
const data = sheet.getDataRange().getValues();
let res = [];
for(let i=1; i ({
id: r[4], name: r[5], subs: r[6], views: r[7], monetized: r[8], earning: r[9], watchTime: r[10], strike: r[11], category: r[12], type: r[13], topGeo: r[2], status: s,
img1: r[14], img2: r[15], img3: r[16], link: r[17], salePrice: getValidPrice(r[19], r[18]), adminWhatsapp: ADMIN_WHATSAPP
}));
const igData = fetchSheet("Insta_Deals", (r, s) => ({
id: r[4], name: r[5], followers: r[6], reach: r[7], storyViews: r[8], oge: r[9], category: r[10], topGeo: r[2], status: s,
img1: r[11], img2: r[12], img3: r[13], link: r[14], salePrice: getValidPrice(r[16], r[15]), adminWhatsapp: ADMIN_WHATSAPP
}));
const fbData = fetchSheet("FB_Deals", (r, s) => ({
id: r[4], name: r[5], followers: r[6], reach: r[7], monetized: r[8], bmStatus: r[9], quality: r[10], earning: r[11], watchTime: r[12], category: r[13], topGeo: r[2], status: s,
img1: r[14], img2: r[15], img3: r[16], img4: r[17], link: r[18], salePrice: getValidPrice(r[20], r[19]), adminWhatsapp: ADMIN_WHATSAPP
}));
return jsonResponse({ realDiscount: realDiscount, fakeDiscount: fakeDiscount, offerName: currentOfferName, yt: ytData, ig: igData, fb: fbData });
}
if (action === "getPartnerStats") {
const partnerId = e.parameter.partnerId;
const pData = ss.getSheetByName("Partners_DB").getDataRange().getValues();
for (let i = 1; i < pData.length; i++) {
if (pData[i][1].toString().trim() === partnerId) {
return jsonResponse({
name: pData[i][2],
totalLeads: pData[i][6],
successDeals: pData[i][7],
wallet: pData[i][8],
paymentStatus: pData[i][10],
cancelledCount: pData[i][11] || 0,
cancelledIds: pData[i][12] || ""
});
}
}
return jsonResponse({ error: "Partner ID not found" });
}
return doGet({ parameter: { action: "getInventory" } });
} catch (error) { return jsonResponse({ "error": error.message }); }
}
function doPost(e) {
try {
const ss = SpreadsheetApp.openById(SHEET_ID);
let formData = JSON.parse(e.postData.contents || "{}");
const action = formData.action;
const timestamp = new Date();
const getSlabFormula = (colLetter, rowNum) => {
const c = colLetter + rowNum;
return `=IF(ISNUMBER(${c}), ROUND(IF(${c}<=5000, ${c}*1.35, IF(${c}<=10000, ${c}*1.31, IF(${c}<=30000, ${c}*1.28, IF(${c}<=50000, ${c}*1.24, IF(${c}<=80000, ${c}*1.21, IF(${c}<=100000, ${c}*1.17, ${c}*1.14)))))), 0), "")`;
};
const applyStatusDropdown = (sheetObj, targetRow) => {
let rule = sheetObj.getRange(2, 4).getDataValidation();
if (rule) sheetObj.getRange(targetRow, 4).setDataValidation(rule);
};
if (action === "sellChannel") {
const dbSheet = ss.getSheetByName("New Channels");
const newRow = dbSheet.getLastRow() + 1;
const channelId = "CH" + (1000 + newRow);
const salePriceFormula = getSlabFormula("S", newRow);
const msgFormula = '=IF(INDIRECT("D" & ROW())="APPROVED", "🔔 YouTube Channel for Sale ! 🔔" & CHAR(10) & "-----" & CHAR(10) & "👉 Channel ID: " & INDIRECT("E" & ROW()) & CHAR(10) & "👉 Current Status: AVAILABLE🟢" & CHAR(10) & "👉 Total Subscribers: " & TEXT(INDIRECT("G" & ROW()), "#,##0") & CHAR(10) & "👉 Life Time Views: " & TEXT(INDIRECT("H" & ROW()), "#,##0") & CHAR(10) & "👉 Monetize Status: " & INDIRECT("I" & ROW()) & CHAR(10) & "👉 Video type: " & INDIRECT("N" & ROW()) & CHAR(10) & "👉 Restrictions/Strikes: " & INDIRECT("L" & ROW()) & CHAR(10) & "👉 Lifetime Earnings: " & IF(OR(INDIRECT("J" & ROW())="", INDIRECT("J" & ROW())=0), "0", TEXT(INDIRECT("J" & ROW()), "$#,##0.00")) & CHAR(10) & CHAR(10) & "⚖️ Pricing Transparency:" & CHAR(10) & "🛡️ Platform Fee (Escrow + Safety Guarantee + GST): INCLUDED" & CHAR(10) & "✅ Final Offer Price: " & IF(INDIRECT("T" & ROW())="","Ask in Inbox","Rs. " & TEXT(INDIRECT("T" & ROW()), "#,##0")) & CHAR(10) & CHAR(10) & "♦️Check Real Images and Links.♦️" & CHAR(10) & "Dashboard: " & INDIRECT("O" & ROW()) & CHAR(10) & "Analytics: " & INDIRECT("P" & ROW()) & CHAR(10) & "Earn ($): " & INDIRECT("Q" & ROW()) & CHAR(10) & CHAR(10) & "Channel Link: " & INDIRECT("R" & ROW()) & CHAR(10) & CHAR(10) & "📌 Interested?, To buy this channel, Send us a message on What\'s App: ' + ADMIN_WHATSAPP + ' with Screenshot or Channel ID Number: " & INDIRECT("E" & ROW()), "")';
dbSheet.appendRow([
timestamp, trimData(formData.sellerWhatsapp), trimData(formData.topGeo), "PENDING", channelId, trimData(formData.channelName), trimData(formData.subscribers),
trimData(formData.views), trimData(formData.monetized || "no"), trimData(formData.earning || "0"), trimData(formData.watchTime || "0"),
trimData(formData.strikeInfo || "No Strike"), trimData(formData.category), trimData(formData.videoType || ""), trimData(formData.images[0]),
trimData(formData.images[1]), trimData(formData.images[2]), trimData(formData.channelLink), "", salePriceFormula, trimData(formData.partnerId || ""), "", msgFormula
]);
applyStatusDropdown(dbSheet, newRow);
return jsonResponse({ status: "success", message: "Channel listed!" });
}
if (action === "sellInsta") {
const dbSheet = ss.getSheetByName("Insta_Deals");
const newRow = dbSheet.getLastRow() + 1;
const accId = "IG" + (1000 + newRow);
const salePriceFormula = getSlabFormula("P", newRow);
const msgFormula = '=IF(INDIRECT("D" & ROW())="APPROVED", "⭐ Instagram Account for Sale ! ⭐" & CHAR(10) & "-----" & CHAR(10) & "👉 Account ID: " & INDIRECT("E" & ROW()) & CHAR(10) & "👉 Current Status: AVAILABLE🟢" & CHAR(10) & "👉 Total Followers: " & TEXT(INDIRECT("G" & ROW()), "#,##0") & CHAR(10) & "👉 Monthly Reach: " & TEXT(INDIRECT("H" & ROW()), "#,##0") & CHAR(10) & "👉 Avg. Story Views: " & TEXT(INDIRECT("I" & ROW()), "#,##0") & CHAR(10) & "👉 OGE Status: " & INDIRECT("J" & ROW()) & CHAR(10) & "👉 Top Audience: " & INDIRECT("C" & ROW()) & CHAR(10) & "👉 Category: " & INDIRECT("K" & ROW()) & CHAR(10) & CHAR(10) & "⚖️ Pricing Transparency:" & CHAR(10) & "🛡️ Platform Fee (Escrow + Safety Guarantee + GST): INCLUDED" & CHAR(10) & "✅ Final Offer Price: " & IF(INDIRECT("Q" & ROW())="","Ask in Inbox","Rs. " & TEXT(INDIRECT("Q" & ROW()), "#,##0")) & CHAR(10) & CHAR(10) & "♦️Check Real Images and Links.♦️" & CHAR(10) & "Reach: " & INDIRECT("L" & ROW()) & CHAR(10) & "Audience: " & INDIRECT("M" & ROW()) & CHAR(10) & "History: " & INDIRECT("N" & ROW()) & CHAR(10) & CHAR(10) & "Profile Link: " & INDIRECT("O" & ROW()) & CHAR(10) & CHAR(10) & "📌 Interested?, To buy this account, Send us a message on What\'s App: ' + ADMIN_WHATSAPP + ' with Screenshot or Account ID Number: " & INDIRECT("E" & ROW()), "")';
dbSheet.appendRow([
timestamp, trimData(formData.sellerWhatsapp), trimData(formData.topGeo), "PENDING", accId, trimData(formData.instaUsername), trimData(formData.followers), trimData(formData.monthlyReach),
trimData(formData.storyViews), trimData(formData.ogeStatus), trimData(formData.category), trimData(formData.images[0]), trimData(formData.images[1]), trimData(formData.images[2]),
"https://instagram.com/" + formData.instaUsername.replace('@',''), "", salePriceFormula, trimData(formData.partnerId || ""), "", msgFormula
]);
applyStatusDropdown(dbSheet, newRow);
return jsonResponse({ status: "success", message: "Insta listed!" });
}
if (action === "sellFacebook") {
const dbSheet = ss.getSheetByName("FB_Deals");
const newRow = dbSheet.getLastRow() + 1;
const pgId = "FB" + (1000 + newRow);
const salePriceFormula = getSlabFormula("T", newRow);
const msgFormula = '=IF(INDIRECT("D" & ROW())="APPROVED", "📢 Facebook Page for Sale ! 📢" & CHAR(10) & "-----" & CHAR(10) & "👉 Page ID: " & INDIRECT("E" & ROW()) & CHAR(10) & "👉 Current Status: AVAILABLE🟢" & CHAR(10) & "👉 Total Followers: " & TEXT(INDIRECT("G" & ROW()), "#,##0") & CHAR(10) & "👉 Last 28D Reach: " & TEXT(INDIRECT("H" & ROW()), "#,##0") & CHAR(10) & "👉 Monetize Status: " & INDIRECT("I" & ROW()) & CHAR(10) & "👉 BM Status: " & INDIRECT("J" & ROW()) & CHAR(10) & "👉 Page Quality: " & INDIRECT("K" & ROW()) & CHAR(10) & "👉 Category: " & INDIRECT("N" & ROW()) & CHAR(10) & "👉 Earning / WT: " & IF(INDIRECT("L" & ROW())="", INDIRECT("M" & ROW()), INDIRECT("L" & ROW())) & CHAR(10) & CHAR(10) & "⚖️ Pricing Transparency:" & CHAR(10) & "🛡️ Platform Fee (Escrow + Safety Guarantee + GST): INCLUDED" & CHAR(10) & "✅ Final Offer Price: " & IF(INDIRECT("U" & ROW())="","Ask in Inbox","Rs. " & TEXT(INDIRECT("U" & ROW()), "#,##0")) & CHAR(10) & CHAR(10) & "♦️Check Real Images and Links.♦️" & CHAR(10) & "Quality: " & INDIRECT("O" & ROW()) & CHAR(10) & "Roles: " & INDIRECT("P" & ROW()) & CHAR(10) & "Insights: " & INDIRECT("Q" & ROW()) & CHAR(10) & "Name Box: " & INDIRECT("R" & ROW()) & CHAR(10) & CHAR(10) & "Page Link: " & INDIRECT("S" & ROW()) & CHAR(10) & CHAR(10) & "📌 Interested?, To buy this page, Send us a message on What\'s App: ' + ADMIN_WHATSAPP + ' with Screenshot or Page ID Number: " & INDIRECT("E" & ROW()), "")';
dbSheet.appendRow([
timestamp, trimData(formData.sellerWhatsapp), trimData(formData.topGeo), "PENDING", pgId, trimData(formData.pageName), trimData(formData.followers), trimData(formData.reach),
trimData(formData.monetization), trimData(formData.bmStatus), trimData(formData.pageQuality), trimData(formData.earning || "0"), trimData(formData.watchTime || "0"), trimData(formData.category),
trimData(formData.images[0]), trimData(formData.images[1]), trimData(formData.images[2]), trimData(formData.images[3]), trimData(formData.pageLink), "", salePriceFormula, trimData(formData.partnerId || ""), "", msgFormula
]);
applyStatusDropdown(dbSheet, newRow);
return jsonResponse({ status: "success", message: "FB listed!" });
}
if (action === "partnerAuth" || action === "registerPartner") {
const type = formData.type || (action === "registerPartner" ? "register" : "login");
const waNum = trimData(formData.whatsapp).replace(/\D/g, '');
const pin = trimData(formData.pin) || "";
const pSheet = ss.getSheetByName("Partners_DB");
const pData = pSheet.getDataRange().getValues();
if (type === "register") {
for (let i = 1; i < pData.length; i++) {
if (pData[i][3].toString().trim() === waNum) {
return jsonResponse({
status: "success", message: "Already registered.",
data: { name: pData[i][2], partnerId: pData[i][1], totalLeads: pData[i][6], successDeals: pData[i][7], wallet: pData[i][8], totalPaid: pData[i][9], cancelledCount: pData[i][11] || 0, cancelledIds: pData[i][12] || "" }
});
}
}
const partnerId = "pts-" + waNum.slice(-5);
pSheet.appendRow([timestamp, partnerId, trimData(formData.name), waNum, pin, trimData(formData.upi), 0, 0, 0, 0, "Active", 0, ""]);
return jsonResponse({ status: "success", partnerId: partnerId, data: { name: trimData(formData.name), partnerId: partnerId, totalLeads: 0, successDeals: 0, wallet: 0, totalPaid: 0, cancelledCount: 0, cancelledIds: "" } });
}
if (type === "login") {
for (let i = 1; i < pData.length; i++) {
if (pData[i][3].toString().trim() === waNum && pData[i][4].toString().trim() === pin) {
return jsonResponse({
status: "success",
data: { name: pData[i][2], partnerId: pData[i][1], totalLeads: pData[i][6], successDeals: pData[i][7], wallet: pData[i][8], totalPaid: pData[i][9], cancelledCount: pData[i][11] || 0, cancelledIds: pData[i][12] || "" }
});
}
}
return jsonResponse({ status: "error", message: "Invalid WhatsApp Number or PIN." });
}
}
if (action === "submitLead") {
const pId = trimData(formData.partnerId);
ss.getSheetByName("Leads_DB").appendRow([ timestamp, pId, trimData(formData.leadType), trimData(formData.accountType), trimData(formData.customerWhatsapp), "Pending", "" ]);
updatePartnerLeadCount(ss, pId);
return jsonResponse({ status: "success" });
}
return jsonResponse({ status: "error", message: "Invalid action." });
} catch (error) { return jsonResponse({ status: "error", message: error.message }); }
}
function sheetAutomations(e) {
if (!e || !e.range) return;
const sheet = e.range.getSheet();
const sheetName = sheet.getName();
const row = e.range.getRow();
const col = e.range.getColumn();
const value = e.value;
if (row < 2) return;
let purchCol = 0, saleCol = 0, colLetter = "";
if (sheetName === "New Channels") { purchCol = 19; saleCol = 20; colLetter = "S"; }
else if (sheetName === "Insta_Deals") { purchCol = 16; saleCol = 17; colLetter = "P"; }
else if (sheetName === "FB_Deals") { purchCol = 20; saleCol = 21; colLetter = "T"; }
// 1. AUTO-SALE PRICE GENERATOR
if (purchCol > 0 && col === purchCol && value) {
const c = colLetter + row;
const autoFormula = `=IF(ISNUMBER(${c}), ROUND(IF(${c}<=5000, ${c}*1.35, IF(${c}<=10000, ${c}*1.31, IF(${c}<=30000, ${c}*1.28, IF(${c}<=50000, ${c}*1.24, IF(${c}<=80000, ${c}*1.21, IF(${c}<=100000, ${c}*1.17, ${c}*1.14)))))), 0), "")`;
sheet.getRange(row, saleCol).setFormula(autoFormula);
}
if ((sheetName === "New Channels" || sheetName === "Insta_Deals" || sheetName === "FB_Deals") && col === 4) {
const statusValue = value ? value.toString().trim().toUpperCase() : "";
// 🚀 THE REFUND / DEAL CANCELLED LOGIC (Auto-Revert Commission)
if (statusValue === "REFUNDED" || statusValue === "DEAL CANCELLED") {
let channelId = sheet.getRange(row, 5).getValue().toString().trim();
if (!channelId) return;
let leadsSheet = e.source.getSheetByName("Leads_DB");
let pSheet = e.source.getSheetByName("Partners_DB");
let trackerSheet = e.source.getSheetByName("Deals_Tracker");
let affiliateReverted = false;
if (leadsSheet) {
let lData = leadsSheet.getDataRange().getValues();
for(let i=1; i < lData.length; i++){
let lType = lData[i][3] ? lData[i][3].toString() : "";
let lStatus = lData[i][5] ? lData[i][5].toString().trim() : "";
if(lType.includes(channelId) && lStatus === "Closed ✅") {
let pId = lData[i][1].toString().trim();
let commAmt = parseFloat(lData[i][6]) || 0;
leadsSheet.getRange(i+1, 6).setValue("Refunded ❌");
if (pSheet && pId) {
let pData = pSheet.getDataRange().getValues();
for(let j=1; j maxNum) { maxNum = num; padLen = numPart.length; }
}
}
}
}
if (prefix !== "") {
let nextId = prefix + (maxNum + 1).toString().padStart(padLen, '0');
sheet.getRange(row, 5).setValue(nextId);
}
let targetDestination = lastApprovedRow + 1;
if (row !== targetDestination) {
let moveIndex = (row > targetDestination) ? targetDestination : targetDestination + 1;
sheet.moveRows(sheet.getRange(row, 1), moveIndex);
}
} catch(e) { console.log(e); }
}
// 🚀 THE ULTIMATE "SOLD OUT" MASTER FLOW
if (statusValue === "SOLD OUT") {
let purchPrice = 0, salePrice = 0, soldCol = 0, buyerCol = 0;
if (sheetName === "New Channels") {
purchPrice = sheet.getRange(row, 19).getValue(); salePrice = sheet.getRange(row, 20).getValue();
soldCol = 22; buyerCol = 25;
} else if (sheetName === "Insta_Deals") {
purchPrice = sheet.getRange(row, 16).getValue(); salePrice = sheet.getRange(row, 17).getValue();
soldCol = 19; buyerCol = 22;
} else if (sheetName === "FB_Deals") {
purchPrice = sheet.getRange(row, 20).getValue(); salePrice = sheet.getRange(row, 21).getValue();
soldCol = 23; buyerCol = 26;
}
if (soldCol > 0 && sheet.getRange(row, soldCol).getValue() !== "") return;
let pPriceNum = parseFloat(purchPrice) || 0;
let sPriceNum = parseFloat(salePrice) || 0;
let ui = SpreadsheetApp.getUi();
let contactResponse = ui.prompt("🎉 Deal Done!", "Buyer ka WhatsApp number yaha paste karein:", ui.ButtonSet.OK_CANCEL);
if (contactResponse.getSelectedButton() != ui.Button.OK) { sheet.getRange(row, col).setValue("APPROVED"); return; }
let buyerContactNum = contactResponse.getResponseText().trim();
if (buyerContactNum === "") { ui.alert("⚠️ Number khali nahi ho sakta."); sheet.getRange(row, col).setValue("APPROVED"); return; }
let affiliateFound = false;
let affiliatePartnerId = "";
let affiliateCommAmount = 0;
let affiliateRowIndex = -1;
let leadsSheet = e.source.getSheetByName("Leads_DB");
if (leadsSheet) {
let lData = leadsSheet.getDataRange().getValues();
for(let i=1; i < lData.length; i++){
let lBuyer = lData[i][4] ? lData[i][4].toString().trim() : "";
let lStatus = lData[i][5] ? lData[i][5].toString().trim().toLowerCase() : "";
if(lBuyer === buyerContactNum && lStatus === "pending") {
affiliatePartnerId = lData[i][1].toString().trim();
affiliateRowIndex = i + 1;
let commRes = ui.prompt("🚀 Affiliate Lead Found!", `Yeh buyer Partner [ ${affiliatePartnerId} ] ke through aaya hai.\n\nPartner ko kitna Commission dena hai? (Amount dalein):`, ui.ButtonSet.OK_CANCEL);
if(commRes.getSelectedButton() != ui.Button.OK) { sheet.getRange(row, col).setValue("APPROVED"); return; }
affiliateCommAmount = parseFloat(commRes.getResponseText().trim());
if(!isNaN(affiliateCommAmount) && affiliateCommAmount > 0) {
affiliateFound = true;
}
break;
}
}
}
let profitResponse = ui.prompt("📈 Extra Profit", `System Sale Price: ₹${sPriceNum}\n\nAgar is deal me koi EXTRA PROFIT hua hai, toh amount dalein. Warna 0 likh dein:`, ui.ButtonSet.OK_CANCEL);
if (profitResponse.getSelectedButton() != ui.Button.OK) { sheet.getRange(row, col).setValue("APPROVED"); return; }
let extraProfitStr = profitResponse.getResponseText().trim();
let extraProfitVal = (extraProfitStr === "") ? 0 : parseFloat(extraProfitStr);
if (isNaN(extraProfitVal)) extraProfitVal = 0;
let adsensePrice = 0;
let adsenseStatus = "N/A";
if (sheetName === "New Channels") {
let adsenseResponse = ui.prompt("🌐 Adsense Info", "Kya buyer ne Adsense bhi kharida hai?\n\nAgar HAAN, to kitne me bika? (Number dalein)\nAgar NAHI, to 0 likhein:", ui.ButtonSet.OK_CANCEL);
if (adsenseResponse.getSelectedButton() != ui.Button.OK) { sheet.getRange(row, col).setValue("APPROVED"); return; }
adsensePrice = parseFloat(adsenseResponse.getResponseText().trim()) || 0;
if (adsensePrice > 0) {
let verifyResponse = ui.prompt("✅ Adsense Verification", "Adsense Verified hai ya Not Verified?\n\n'v' likhein = Verified\n'nv' likhein = Not Verified", ui.ButtonSet.OK_CANCEL);
if (verifyResponse.getSelectedButton() != ui.Button.OK) { sheet.getRange(row, col).setValue("APPROVED"); return; }
let vText = verifyResponse.getResponseText().trim().toLowerCase();
if (vText === 'v' || vText === 'verified') adsenseStatus = "yes (Verified)";
else adsenseStatus = "yes (Not Verified)";
} else {
adsenseStatus = "no";
}
}
let finalDealPrice = sPriceNum + extraProfitVal + adsensePrice;
let advanceResponse = ui.prompt("💵 Advance Payment", `Total Net Price: ₹${finalDealPrice} (Asset + Extra + Adsense)\n\nAbhi Advance payment kitna aaya hai?`, ui.ButtonSet.OK_CANCEL);
if (advanceResponse.getSelectedButton() != ui.Button.OK) { sheet.getRange(row, col).setValue("APPROVED"); return; }
let advanceVal = parseFloat(advanceResponse.getResponseText().trim()) || 0;
if (affiliateFound && affiliateCommAmount > 0 && leadsSheet) {
leadsSheet.getRange(affiliateRowIndex, 7).setValue(affiliateCommAmount);
leadsSheet.getRange(affiliateRowIndex, 6).setValue("Closed ✅");
let pSheet = e.source.getSheetByName("Partners_DB");
if (pSheet) {
let pData = pSheet.getDataRange().getValues();
for(let j=1; j 0) sheet.getRange(row, soldCol).setValue(todayDate);
if (affiliateFound && affiliateCommAmount > 0) {
ui.alert("✅ Deal Done & Partner ko commission send ho gaya!");
}
cancelPendingLeads(e.source, channelId);
}
}
if (sheetName === "Partners_DB" && col === 11) {
if (value && value.toString().trim().toLowerCase() === "settled") {
const walletRange = sheet.getRange(row, 9), paidRange = sheet.getRange(row, 10);
const curWallet = parseFloat(walletRange.getValue()) || 0, curPaid = parseFloat(paidRange.getValue()) || 0;
if (curWallet > 0) { paidRange.setValue(curPaid + curWallet); walletRange.setValue(0); }
}
}
if (sheetName === "Leads_DB" && (col === 6 || col === 7)) {
const statusValue = sheet.getRange(row, 6).getValue().toString().trim().toLowerCase();
const commission = parseFloat(sheet.getRange(row, 7).getValue()) || 0;
if (statusValue === "cancelled" || statusValue === "rejected") {
const failedLeadsSheet = e.source.getSheetByName("Failed Leads");
if (failedLeadsSheet) failedLeadsSheet.appendRow(sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0]);
sheet.deleteRow(row);
return;
}
if (statusValue === "closed" && commission > 0) {
const pId = sheet.getRange(row, 2).getValue().toString().trim();
if (pId) {
const pSheet = e.source.getSheetByName("Partners_DB");
const pData = pSheet.getDataRange().getValues();
for (let i = 1; i < pData.length; i++) {
if (pData[i][1].toString().trim() === pId) {
pSheet.getRange(i + 1, 8).setValue((parseInt(pData[i][7]) || 0) + 1);
pSheet.getRange(i + 1, 9).setValue((parseFloat(pData[i][8]) || 0) + commission);
pSheet.getRange(i + 1, 11).setValue("Pending");
sheet.getRange(row, 6).setValue("Closed ✅");
break;
}
}
}
}
}
}
function cancelPendingLeads(ss, channelId) {
if (!channelId) return;
const leadsSheet = ss.getSheetByName("Leads_DB");
const failedLeadsSheet = ss.getSheetByName("Failed Leads");
if (!leadsSheet || !failedLeadsSheet) return;
const data = leadsSheet.getDataRange().getValues();
let rowsToDelete = [];
for (let i = data.length - 1; i > 0; i--) {
let lType = data[i][3] ? data[i][3].toString() : "";
let lStatus = data[i][5] ? data[i][5].toString().trim().toLowerCase() : "";
if (lType.includes(channelId) && lStatus === "pending") {
let rowData = [...data[i]];
rowData[5] = "Cancelled (Asset Sold/Rejected)";
failedLeadsSheet.appendRow(rowData);
rowsToDelete.push(i + 1);
}
}
rowsToDelete.forEach(r => leadsSheet.deleteRow(r));
}
function trimData(val) { return val ? val.toString().trim() : ""; }
function jsonResponse(data) { return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON); }
function updatePartnerLeadCount(ss, pId) {
const sheet = ss.getSheetByName("Partners_DB"), data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1].toString().trim() === pId) { sheet.getRange(i+1, 7).setValue((parseInt(data[i][6])||0)+1); break; }
}
}
