Channel deals Old Script with Gmail read logic (v7.40 - Final Sync)
// ==========================================
// CHANNEL DEALS & PT SEVA DEALS - COMPLETE BACKEND (v7.40 - Final Sync)
// ==========================================
const SHEET_ID = '1zLtoIoTeWDYUY8RXSCESpPuSYf6jNkpGw_HRAKOMzJo';
const ADMIN_WHATSAPP = "917987744656";
// Failsafe: 4-Slab Commission Auto-Calculator (Jab sheet formula missing ho)
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 <= 1000) return Math.round(p * 1.35);
if (p <= 10000) return Math.round(p * 1.25);
if (p <= 50000) return Math.round(p * 1.15);
return Math.round(p * 1.12);
}
return "";
}
// ==========================================
// 1. DATA DIKHANE KE LIYE (GET REQUEST)
// ==========================================
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() : "";
// BUG FIXED: row[18] is used for Purchase Price (Column S)
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") {
// ✅ 2-LAYER DISCOUNT FETCH LOGIC
let realDiscount = 0; // Cell B1 (Real Profit cut - hidden from user)
let fakeDiscount = 0; // Cell E4 (Website Illusion Discount)
let currentOfferName = "Special Offer"; // Cell B2
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
}));
// IG: Index 15 (P) is Purch, Index 16 (Q) is Sale, Index 14 (O) is Link
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
}));
// FB: Index 19 (T) is Purch, Index 20 (U) is Sale, Index 18 (S) is Link
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
}));
// ✅ Sending both discounts to Frontend
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][5], successDeals: pData[i][6], wallet: pData[i][7], paymentStatus: pData[i][9] });
}
}
return jsonResponse({ error: "Partner ID not found" });
}
// DEFAULT FALLBACK
return doGet({ parameter: { action: "getInventory" } });
} catch (error) {
return jsonResponse({ "error": error.message });
}
}
// ==========================================
// 2. DATA SAVE KARNE KE LIYE (POST REQUEST)
// ==========================================
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();
if (action === "sellChannel") {
const dbSheet = ss.getSheetByName("New Channels");
const newRow = dbSheet.getLastRow() + 1;
const channelId = "CH" + (1000 + newRow);
// ✅ 4-Slab Dynamic Commission Formula
const salePriceFormula = '=IF(ISNUMBER(INDIRECT("S" & ROW())), ROUND(IF(INDIRECT("S" & ROW())<=1000, INDIRECT("S" & ROW())*1.35, IF(INDIRECT("S" & ROW())<=10000, INDIRECT("S" & ROW())*1.25, IF(INDIRECT("S" & ROW())<=50000, INDIRECT("S" & ROW())*1.15, INDIRECT("S" & ROW())*1.12))), 0), "")';
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 // S=Purch, T=Sale, U=Partner, V=SoldDate, W=Message
]);
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 = '=IF(ISNUMBER(INDIRECT("P" & ROW())), ROUND(IF(INDIRECT("P" & ROW())<=1000, INDIRECT("P" & ROW())*1.35, IF(INDIRECT("P" & ROW())<=10000, INDIRECT("P" & ROW())*1.25, IF(INDIRECT("P" & ROW())<=50000, INDIRECT("P" & ROW())*1.15, INDIRECT("P" & ROW())*1.12))), 0), "")';
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 // P=Purch, Q=Sale, R=Partner, S=SoldDate, T=Message
]);
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 = '=IF(ISNUMBER(INDIRECT("T" & ROW())), ROUND(IF(INDIRECT("T" & ROW())<=1000, INDIRECT("T" & ROW())*1.35, IF(INDIRECT("T" & ROW())<=10000, INDIRECT("T" & ROW())*1.25, IF(INDIRECT("T" & ROW())<=50000, INDIRECT("T" & ROW())*1.15, INDIRECT("T" & ROW())*1.12))), 0), "")';
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 // T=Purch, U=Sale, V=Partner, W=SoldDate, X=Message
]);
return jsonResponse({ status: "success", message: "FB listed!" });
}
if (action === "registerPartner") {
const waNum = trimData(formData.whatsapp).replace(/\D/g, '');
const partnerId = "pts-" + waNum.slice(-5);
ss.getSheetByName("Partners_DB").appendRow([timestamp, partnerId, trimData(formData.name), waNum, trimData(formData.upi), 0, 0, 0, 0, "Active"]);
return jsonResponse({ status: "success", partnerId: partnerId });
}
if (action === "submitLead") {
const pId = trimData(formData.partnerId);
ss.getSheetByName("Leads_DB").appendRow([timestamp, pId, trimData(formData.leadType), trimData(formData.customerWhatsapp), trimData(formData.requirement), trimData(formData.budget), "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;
if ((sheetName === "New Channels" || sheetName === "Insta_Deals" || sheetName === "FB_Deals") && col === 4) {
const statusValue = value ? value.toString().trim().toUpperCase() : "";
// 🗑️ 'FAILED DEALS' MULTI-CONDITION 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 -> FINANCE DB & DATE WRITE (Double-Row Format matching historical data)
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;
// Har platform ke sahi columns se Data uthana
if (sheetName === "New Channels") {
purchPrice = sheet.getRange(row, 19).getValue(); // Col S
salePrice = sheet.getRange(row, 20).getValue(); // Col T
soldCol = 22; // Col V
}
else if (sheetName === "Insta_Deals") {
purchPrice = sheet.getRange(row, 16).getValue(); // Col P
salePrice = sheet.getRange(row, 17).getValue(); // Col Q
soldCol = 19; // Col S
}
else if (sheetName === "FB_Deals") {
purchPrice = sheet.getRange(row, 20).getValue(); // Col T
salePrice = sheet.getRange(row, 21).getValue(); // Col U
soldCol = 23; // Col W
}
let pPriceNum = parseFloat(purchPrice) || 0;
let sPriceNum = parseFloat(salePrice) || 0;
// Finance Sheet me purane format ke mutabiq 2 Rows append karna (Income & Expense)
const financeSheet = e.source.getSheetByName("Finance");
if (financeSheet) {
// Row 1: Income Entry
financeSheet.appendRow([dateVal, channelId, "Income", "Channel Sale", sPriceNum, "", "Received from buyer via automated deal", dateVal]);
// Row 2: Expense Entry
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'));
}
}
// 🚀 NEW MAGIC: AUTO-SORT & AUTO-ID FOR 'APPROVED'
if (statusValue === "APPROVED") {
try {
// 1. WhatsApp message aate hi Row height ko turant chota karna aur Lock karna
sheet.getRange(row, 1, 1, sheet.getLastColumn()).setWrap(false);
sheet.setRowHeight(row, 21);
SpreadsheetApp.flush(); // Ye command sheet ko turant update hone par majboor karti hai
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;
// 2. Aakhri Approved row aur pichli max ID dhundhna
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;
}
}
}
}
}
// 3. Nayi ID generate karke cell me likhna
if (prefix !== "") {
let nextId = prefix + (maxNum + 1).toString().padStart(padLen, '0');
sheet.getRange(row, 5).setValue(nextId);
}
// 4. Row ko exactly last approved ke niche khiskana
let targetDestination = lastApprovedRow + 1;
if (row !== targetDestination) {
let moveIndex = (row > targetDestination) ? targetDestination : targetDestination + 1;
sheet.moveRows(sheet.getRange(row, 1), moveIndex);
}
} catch(e) {
// Failsafe taaki koi error aane par poora code crash na ho
console.log(e);
}
}
}
// Partner Trigger
if (sheetName === "Partners_DB" && col === 10) {
if (value && value.toString().trim().toLowerCase() === "settled") {
const walletRange = sheet.getRange(row, 8), paidRange = sheet.getRange(row, 9);
const curWallet = parseFloat(walletRange.getValue()) || 0, curPaid = parseFloat(paidRange.getValue()) || 0;
if (curWallet > 0) { paidRange.setValue(curPaid + curWallet); walletRange.setValue(0); }
}
}
// Leads Trigger
if (sheetName === "Leads_DB" && (col === 7 || col === 8)) {
const statusValue = sheet.getRange(row, 7).getValue().toString().trim().toLowerCase();
const commission = parseFloat(sheet.getRange(row, 8).getValue()) || 0;
if (statusValue === "done" && 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, 7).setValue((parseInt(pData[i][6]) || 0) + 1);
pSheet.getRange(i + 1, 8).setValue((parseFloat(pData[i][7]) || 0) + commission);
pSheet.getRange(i + 1, 10).setValue("Pending");
sheet.getRange(row, 7).setValue("Done ✅");
break;
}
}
}
}
}
}
// ==========================================
// UTILS & GMAIL TRACKER
// ==========================================
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, 6).setValue((parseInt(data[i][5])||0)+1); break; }
}
}
function trackChannelAccessSafely() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("New Channels");
if (!sheet) return;
const searchQuery = 'is:unread (from:youtube OR from:google-my-business-noreply@google.com)';
const threads = GmailApp.search(searchQuery, 0, 15);
if (threads.length === 0) return;
const data = sheet.getDataRange().getValues();
for (let t = 0; t < threads.length; t++) {
let messages = threads[t].getMessages();
let lastMsg = messages[messages.length - 1];
let subject = lastMsg.getSubject().toLowerCase();
let channelNameFromEmail = "";
let emailProcessed = false;
if (subject.includes(" invited you to own ")) channelNameFromEmail = subject.split(/ invited you to own /i)[1].trim();
else if (subject.includes(" invited you to manage ")) channelNameFromEmail = subject.split(/ invited you to manage /i)[1].trim();
else if (subject.includes("you are now an owner of ")) channelNameFromEmail = subject.split(/you are now an owner of /i)[1].trim();
else if (subject.includes("you are now a manager of ")) channelNameFromEmail = subject.split(/you are now a manager of /i)[1].trim();
else if (subject.includes("you are no longer an owner of ")) channelNameFromEmail = subject.split(/you are no longer an owner of /i)[1].trim();
else if (subject.includes("you are no longer a manager of ")) channelNameFromEmail = subject.split(/you are no longer a manager of /i)[1].trim();
else if (subject.includes(" access to ")) channelNameFromEmail = subject.split(/ access to /i)[1].trim();
if (channelNameFromEmail !== "") {
if (subject.includes("invited you to")) {
let matchedRowIndex = -1, pendingRows = [];
for (let i = 1; i < data.length; i++) {
let currentStatus = data[i][3] ? data[i][3].toString().trim().toUpperCase() : "";
if (currentStatus === "PENDING" || currentStatus === "HOLD") {
pendingRows.push(i);
let sName = (data[i][5] ? data[i][5].toString().trim() : "").toLowerCase();
let eName = channelNameFromEmail.toLowerCase();
if (sName === eName || sName.includes(eName) || eName.includes(sName)) { matchedRowIndex = i; break; }
}
}
if (matchedRowIndex !== -1) {
sheet.getRange(matchedRowIndex + 1, 4).setValue("INVITED 📩");
sheet.getRange(matchedRowIndex + 1, 6).setValue(channelNameFromEmail);
MailApp.sendEmail("ramkishunsocialmedia@gmail.com", `📩 Invite Aaya: ${channelNameFromEmail}`, `Bhai, "${channelNameFromEmail}" ka invite aa gaya hai!\n\nSystem ne sheet update kar di hai.`);
emailProcessed = true;
}
else if (pendingRows.length === 1) {
let singleRow = pendingRows[0];
sheet.getRange(singleRow + 1, 4).setValue("INVITED 📩");
sheet.getRange(singleRow + 1, 6).setValue(channelNameFromEmail);
MailApp.sendEmail("ramkishunsocialmedia@gmail.com", `🔄 Name Auto-Corrected: ${channelNameFromEmail}`, `Name auto-corrected to INVITED.`);
emailProcessed = true;
}
else if (pendingRows.length > 1) {
MailApp.sendEmail("ramkishunsocialmedia@gmail.com", `⚠️ MANUAL CHECK REQUIRED: ${channelNameFromEmail}`, `Multiple pending channels. Manual check required.`);
emailProcessed = false;
}
else if (pendingRows.length === 0) emailProcessed = false;
}
else if (subject.includes("now an owner") || subject.includes("now a manager")) {
for (let i = 1; i < data.length; i++) {
let sheetChannelName = data[i][5] ? data[i][5].toString().trim() : "";
let currentStatus = data[i][3] ? data[i][3].toString().trim().toUpperCase() : "";
if (currentStatus.includes("INVITED") || currentStatus === "PENDING" || currentStatus === "HOLD") {
let sName = sheetChannelName.toLowerCase();
let eName = channelNameFromEmail.toLowerCase();
if (sName === eName || sName.includes(eName) || eName.includes(sName)) {
sheet.getRange(i + 1, 4).setValue("APPROVED");
sheet.getRange(i + 1, 6).setValue(channelNameFromEmail);
MailApp.sendEmail("ramkishunsocialmedia@gmail.com", `✅ Auto-Approved: ${channelNameFromEmail}`, `Channel APPROVED.`);
break;
}
}
}
emailProcessed = true;
}
else if (subject.includes("no longer") || subject.includes("access removed")) {
let nameFoundInSheet = false;
for (let i = 1; i < data.length; i++) {
let sheetChannelName = data[i][5] ? data[i][5].toString().trim() : "";
let currentStatus = data[i][3] ? data[i][3].toString().trim().toUpperCase() : "";
let sName = sheetChannelName.toLowerCase();
let eName = channelNameFromEmail.toLowerCase();
if ((sName === eName || sName.includes(eName) || eName.includes(sName)) && (currentStatus === "APPROVED" || currentStatus.includes("INVITED"))) {
sheet.getRange(i + 1, 4).setValue("ACCESS_REMOVED");
let serialId = data[i][4] ? data[i][4].toString().trim() : "N/A";
MailApp.sendEmail("ramkishunsocialmedia@gmail.com", `🚨 Auto-Removed: ${sheetChannelName}`, `🚨 URGENT ALERT!\n\nAccess removed.\nChannel: ${sheetChannelName}\nSerial: ${serialId}`);
nameFoundInSheet = true;
break;
}
}
if (nameFoundInSheet === false) MailApp.sendEmail("ramkishunsocialmedia@gmail.com", `⚠️ FRAUD ALERT: Unknown Channel Removed`, `Manual check required for: "${channelNameFromEmail}"`);
emailProcessed = true;
}
}
if (emailProcessed === true) threads[t].markRead();
}
}
