Channel Dealing - Discount and affiliate and sheet automations logic backend 2✅
// ==========================================
// CHANNEL DEALS & PT SEVA DEALS - COMPLETE BACKEND (v1.11 - Final Auto-Sync Update)
// ==========================================
const SHEET_ID = '1zLtoIoTeWDYUY8RXSCESpPuSYf6jNkpGw_HRAKOMzJo';
const ADMIN_WHATSAPP = "917987744656";
// 🔥 7-Tier Commission Auto-Calculator (Failsafe)
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");
const financeSheet = ss.getSheetByName("Finance");
if (!channelSheet || !financeSheet) return jsonResponse({ error: "Finance or 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 📩") pendingCount++;
else if (status === "ACCESS_REMOVED 🚨" || status === "ACCESS_REMOVED") removedCount++;
else if (status === "SOLD OUT") soldCount++;
}
const fData = financeSheet.getDataRange().getValues();
let totalIncome = 0, totalExpense = 0, recentTransactions = [], chartDataObj = {};
for (let j = fData.length - 1; j > 0; j--) {
let fRow = fData[j];
if (fRow[0] === "") continue;
let dateString = new Date(fRow[0]).toLocaleDateString('en-IN');
let amtIn = parseFloat(fRow[4]) || 0, amtOut = parseFloat(fRow[5]) || 0;
totalIncome += amtIn; totalExpense += amtOut;
if (recentTransactions.length < 1500) {
recentTransactions.push({ date: dateString, channelId: fRow[1], type: fRow[2], category: fRow[3], amountIn: amtIn, amountOut: amtOut, remarks: fRow[6] });
}
if (!chartDataObj[dateString]) chartDataObj[dateString] = { income: 0, expense: 0 };
chartDataObj[dateString].income += amtIn;
chartDataObj[dateString].expense += amtOut;
}
return jsonResponse({
metrics: { active: activeCount, pending: pendingCount, removed: removedCount, sold: soldCount },
operations: channelsList.reverse(),
finance: { totalSales: totalIncome, totalExpense: totalExpense, netProfit: totalIncome - totalExpense, recentTransactions: recentTransactions, chartData: chartDataObj }
});
}
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 📩") dashboardData.accessPending++;
else if (status === "SOLD OUT") dashboardData.soldOut++;
else if (status === "ACCESS_REMOVED 🚨" || status === "ACCESS_REMOVED") 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;
let fakeDiscount = 0;
let 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] });
}
}
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();
// 🔥 7-Tier Commission Formula (For Google Sheets Cells)
const getSlabFormula = (col) => `=IF(ISNUMBER(INDIRECT("${col}" & ROW())), ROUND(IF(INDIRECT("${col}" & ROW())<=5000, INDIRECT("${col}" & ROW())*1.35, IF(INDIRECT("${col}" & ROW())<=10000, INDIRECT("${col}" & ROW())*1.31, IF(INDIRECT("${col}" & ROW())<=30000, INDIRECT("${col}" & ROW())*1.28, IF(INDIRECT("${col}" & ROW())<=50000, INDIRECT("${col}" & ROW())*1.24, IF(INDIRECT("${col}" & ROW())<=80000, INDIRECT("${col}" & ROW())*1.21, IF(INDIRECT("${col}" & ROW())<=100000, INDIRECT("${col}" & ROW())*1.17, INDIRECT("${col}" & ROW())*1.14))))))), 0), "")`;
if (action === "sellChannel") {
const dbSheet = ss.getSheetByName("New Channels");
const newRow = dbSheet.getLastRow() + 1;
const channelId = "CH" + (1000 + newRow);
const salePriceFormula = getSlabFormula("S");
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
]);
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");
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
]);
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");
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
]);
return jsonResponse({ status: "success", message: "FB listed!" });
}
if (action === "partnerAuth") {
const type = formData.type;
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]
}
});
}
}
const partnerId = "pts-" + waNum.slice(-5);
pSheet.appendRow([timestamp, partnerId, trimData(formData.name), waNum, pin, trimData(formData.upi), 0, 0, 0, 0, "Active"]);
return jsonResponse({
status: "success",
data: { name: trimData(formData.name), partnerId: partnerId, totalLeads: 0, successDeals: 0, wallet: 0, totalPaid: 0 }
});
}
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]
}
});
}
}
return jsonResponse({ status: "error", message: "Invalid WhatsApp Number or PIN." });
}
}
// 🔥 LEADS FORMAT: Only 7 Columns (A to G)
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 });
}
}
// ==========================================
// 3. MAGIC AUTOMATION (ON EDIT TRIGGER)
// ==========================================
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;
// 🔥 1. AUTO-SALE PRICE GENERATOR ON MANUAL EDIT (Purchase Price)
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"; }
if (purchCol > 0 && col === purchCol && value) {
// Inject exact 7-slab formula into Sale Price column automatically
const autoFormula = `=IF(ISNUMBER(INDIRECT("${colLetter}" & ROW())), ROUND(IF(INDIRECT("${colLetter}" & ROW())<=5000, INDIRECT("${colLetter}" & ROW())*1.35, IF(INDIRECT("${colLetter}" & ROW())<=10000, INDIRECT("${colLetter}" & ROW())*1.31, IF(INDIRECT("${colLetter}" & ROW())<=30000, INDIRECT("${colLetter}" & ROW())*1.28, IF(INDIRECT("${colLetter}" & ROW())<=50000, INDIRECT("${colLetter}" & ROW())*1.24, IF(INDIRECT("${colLetter}" & ROW())<=80000, INDIRECT("${colLetter}" & ROW())*1.21, IF(INDIRECT("${colLetter}" & ROW())<=100000, INDIRECT("${colLetter}" & ROW())*1.17, INDIRECT("${colLetter}" & ROW())*1.14))))))), 0), "")`;
sheet.getRange(row, saleCol).setFormula(autoFormula);
}
// 🔥 2. PLATFORM STATUS ACTIONS (Channels/Pages/Insta)
if ((sheetName === "New Channels" || sheetName === "Insta_Deals" || sheetName === "FB_Deals") && col === 4) {
const statusValue = value ? value.toString().trim().toUpperCase() : "";
// Failed Deals Archive
const failedConditions = ["REJECTED", "CANCELLED"];
if (failedConditions.includes(statusValue)) {
const failedSheet = e.source.getSheetByName("Failed Deals");
if (failedSheet) {
const rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
failedSheet.appendRow(rowData);
const newRow = failedSheet.getLastRow();
failedSheet.getRange(newRow, 1, 1, failedSheet.getLastColumn()).setWrap(false);
failedSheet.setRowHeight(newRow, 21);
sheet.deleteRow(row);
return;
}
}
// Sold Out to Finance
if (statusValue === "SOLD OUT") {
const dateVal = sheet.getRange(row, 1).getValue();
const channelId = sheet.getRange(row, 5).getValue();
let purchPrice = 0;
let salePrice = 0;
let soldCol = 0;
if (sheetName === "New Channels") {
purchPrice = sheet.getRange(row, 19).getValue();
salePrice = sheet.getRange(row, 20).getValue();
soldCol = 22;
}
else if (sheetName === "Insta_Deals") {
purchPrice = sheet.getRange(row, 16).getValue();
salePrice = sheet.getRange(row, 17).getValue();
soldCol = 19;
}
else if (sheetName === "FB_Deals") {
purchPrice = sheet.getRange(row, 20).getValue();
salePrice = sheet.getRange(row, 21).getValue();
soldCol = 23;
}
let pPriceNum = parseFloat(purchPrice) || 0;
let sPriceNum = parseFloat(salePrice) || 0;
const financeSheet = e.source.getSheetByName("Finance");
if (financeSheet) {
financeSheet.appendRow([dateVal, channelId, "Income", "Channel Sale", sPriceNum, "", "Received from buyer via automated deal", dateVal]);
financeSheet.appendRow([dateVal, channelId, "Expense", "Seller Payout", "", pPriceNum, "Sent to original creator", dateVal]);
}
if (soldCol > 0) {
sheet.getRange(row, soldCol).setValue(new Date().toLocaleDateString('en-IN'));
}
}
// Auto-ID and Sorting for Approved
if (statusValue === "APPROVED") {
try {
sheet.getRange(row, 1, 1, sheet.getLastColumn()).setWrap(false);
sheet.setRowHeight(row, 21);
SpreadsheetApp.flush();
let prefix = "";
if (sheetName === "New Channels") prefix = "CD";
else if (sheetName === "Insta_Deals") prefix = "IG";
else if (sheetName === "FB_Deals") prefix = "FB";
const data = sheet.getDataRange().getValues();
let lastApprovedRow = 1;
let maxNum = 0;
let padLen = 5;
for (let i = 1; i < data.length; i++) {
let currentRowNum = i + 1;
if (currentRowNum === row) continue;
let rStatus = data[i][3] ? data[i][3].toString().trim().toUpperCase() : "";
if (["APPROVED", "SOLD OUT", "ACCESS_REMOVED"].includes(rStatus) || rStatus.includes("INVITED")) {
lastApprovedRow = currentRowNum;
let rId = data[i][4] ? data[i][4].toString().trim().toUpperCase() : "";
if (prefix !== "" && rId.startsWith(prefix)) {
let numPart = rId.substring(prefix.length);
if (!isNaN(numPart) && numPart !== "") {
let num = parseInt(numPart, 10);
if (num > 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);
}
}
}
// 🔥 3. PARTNERS DB ACTIONS (Payment Settlement)
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); }
}
}
// 🔥 4. LEADS DB ACTIONS (Status F/6, Commission G/7)
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;
// 🗑️ AUTO-MOVE REJECTED/CANCELLED LEADS TO 'Failed Leads' TAB
if (statusValue === "cancelled" || statusValue === "rejected") {
const failedLeadsSheet = e.source.getSheetByName("Failed Leads");
if (failedLeadsSheet) {
const rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
failedLeadsSheet.appendRow(rowData);
}
sheet.deleteRow(row);
return;
}
// 💰 COMMISSION ADDITION ON "CLOSED"
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 ✅"); // Prevent double triggering
break;
}
}
}
}
}
}
// UTILS
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; }
}
}
