Welcome Founder! Initialize primary ownership for Teme Astegni (แฐแ แ แตแ แ). You will hold master cryptographic authority, manage staff admins, and control financial ledgers.
Ashenafi's Rule: As a university student founder, hire fellow high-achieving classmates as campus coordinators. Use these 4 golden rules to manage them without interfering with your classes:
Meet at 9:00 PM (Dorm or Telegram voice). Check calls made, tutors vetted, and pending families.
3. Pay Structure
Base stipend (1,800–2,000 ETB) + Performance bonus (150–200 ETB per matched family).
4. Exam Season Swap
50/50 study rotation during midterms and finals so student GPAs and business revenue never drop.
๐ผ Google Sheets Official Revenue & Matches Ledger
Account: ashenafitobe@gmail.com. Your Google Sheet acts as the Executive Financial Ledger (Matches_Ledger), recording only confirmed deals, agreed monthly tuition fees, and Ethio-21 25% commissions. (Raw applicant leads remain in Telegram & Firebase to keep your Google Sheet 100% clean and free of junk).
Tutor and parent ID cards, payment screenshots, and certificates stream directly to your private Telegram Channel or Coordinator Bot with zero storage limits and zero monthly bills.
Default Tutors Supergroup ID: -1004410573815 ("Tutors" group). Make sure @ethio21_official_bot is promoted to Administrator.
Default Parents Supergroup ID: -1003632707084 ("Parents" group). Make sure @ethio21_official_bot is promoted to Administrator.
Official Invite: https://t.me/+1nwxhaCqN9M4Y2E0 ("Teme Astegni parents payments"). If left blank, payment receipts will safely route to Parents Group (-1003632707084).
Official Invite: https://t.me/+u0IdrknHN5A4ZGI0 ("Toutor Matches"). All match contracts and pair notifications stream here.
๐ 1-Click Auto-Discovery: Connect Any Group Instantly
How to connect in 10 seconds: 1. Add @ethio21_official_bot as an Admin to your group โข 2. Type any message (e.g. /start or hello) in the group โข 3. Click "Auto-Detect Group IDs" above! The system discovers the numeric ID automatically.
๐ฆ Database Vault Backup & Restore (.json)
๐ Secure Portable Storage
Download a complete portable JSON snapshot of all registered Tutors, Parents, Exam Ratings, and System Settings. Restore backups seamlessly onto any device.
๐ Google Apps Script Webhook Code
Copy and paste this code into your Google Sheet's Extensions โ Apps Script editor.
๐ฅ Invite Coordinator Admin
Create an operational coordinator account with restricted privileges.
๐ค Create Tutoring Match
Pair an incoming parent request with a vetted university scholar.
KGโGr 4:
Gr 5โ8:
Gr 9โ12:
๐ผ Agency Payout & Escrow Model Breakdown
๐ฅ Month 1 (Matchmaking & Escrow):
Parent Pays Upfront:3,000 ETB
Agency Retained Cut (30%):900 ETB
Tutor Payout (70% at 30 Days):2,100 ETB
๐ Month 2 & Onwards (Monthly Protection):
Parent Pays Upfront:3,000 ETB
Agency Retained Cut (Flat per match):200 ETB
Tutor Payout (Fee - 200 ETB per match):2,800 ETB
โก Household Match Rule: 200 ETB is charged per matched student (e.g., if 1 tutor teaches 2 students in 1 house = 400 ETB agency fee from Month 2+).
๐ Escrow Rule: Tutor payout is strictly released after parent pays and each 30-day period completes.
Evaluate the university tutor's voice clarity, pedagogical patience, and communicative skills via Voice Call, Video Call, or In-Person. Passing immediately unlocks Level 1 & 1st Priority Dispatch in their Metal Tier!
Tutor Name
University โข CGPA
โญ Tutor Progression & Level Upgrade
Elevate vetted tutors through their 5-Level Tier Hierarchy (Golden, Silver, or Bronze). Higher levels get first-pick dispatch and higher prestige.
Tutor Name
Current Rank: Calculating Tier & Level...
Level 0
Platform Progression Fee:50 ETB
Collect 50 ETB via Telebirr from the tutor to activate this level booster and record in platform revenue ledger.
๐ ID & Credential Inspector
Tutor Verified
Person Name
Phone โข Location
Doc Type
National Fayda ID
๐ก๏ธDocument Status:
โ Active & Verified in Vault
๐ Name Consistency Audit
Single Identity
Target Legal Name:
๐ธ Attached Documents๐ Click photo to view large
Index Pointer Architecture: The Google Sheet records ONLY the Unique ID & Registration Date to use virtually 0% storage capacity. All complete applicant details, phone numbers, and verified ID documents are sent to Ethio - 21 Astegni Ops. Operators simply search the unique ID in Telegram to view the applicant's complete file.
Log into Google with ashenafitobe@gmail.com, open your Sheet, go to Extensions โ Apps Script, and paste this script:
/**
* Teme Astegni (แฐแ แ แตแ แ) - 100% Free Cloud Database Engine
* Ultra-Lean Index Pointer Architecture & Executive Revenue Ledger
* Primary Owner: ashenafitobe@gmail.com
* Organization: Ethio 21 Technologies
* Cost: 100% FREE FOREVER (Google Sheets API & Apps Script)
*
* EXACT SCHEMAS:
* 1. Matches_Ledger: [Timestamp, Parent ID, Tutor ID, Monthly Fee (ETB), Commission (ETB), Status] (Strict 6 Columns)
* 2. Parents_Index: [Registration Date, Parent ID] (Strict 2 Columns)
* 3. Tutors_Index: [Registration Date, Tutor ID] (Strict 2 Columns)
*/
function onOpen(e) {
try {
var ui = SpreadsheetApp.getUi();
if (ui) {
ui.createMenu('๐ Teme Astegni Admin')
.addItem('๐ง 1-Click Fix & Align Entire Google Sheet', 'fixAndAlignAllSheets')
.addItem('๐งน Remove All Duplicate Rows from All Sheets', 'deduplicateAllSheets')
.addItem('โก Format Sheets to Strict Matches Ledger', 'setupCleanArchitecture')
.addItem('๐๏ธ Delete All Legacy Sheets (Keep Only _ Tabs)', 'cleanLegacyTabs')
.addItem('โ๏ธ Send Test Alert to Telegram Bot', 'testTelegramNotification')
.addToUi();
}
} catch (err) {
Logger.log('Note: onOpen automatically renders when you refresh the Google Sheet tab. Ignored during manual editor test run: ' + err);
}
}
function doGet(e) {
var lock = LockService.getScriptLock();
try {
lock.waitLock(15000);
} catch (lockErr) {
return ContentService.createTextOutput(JSON.stringify({ status: 'busy', error: 'Server is busy, try again shortly.' }))
.setMimeType(ContentService.MimeType.JSON);
}
try {
var action = (e && e.parameter) ? e.parameter.action : '';
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (action === 'fixSheet' || action === 'fixAndAlign') {
var summary = fixAndAlignAllSheetsSilently(ss);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({
status: 'success',
summary: summary,
message: 'Google Sheet fixed, deduplicated, and 100% aligned!'
})).setMimeType(ContentService.MimeType.JSON);
}
if (action === 'cleanDuplicates' || action === 'dedup') {
var removed = deduplicateAllSheetsSilently(ss);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({
status: 'success',
removedRows: removed,
message: 'All duplicate IDs and records successfully removed from Google Sheet!'
})).setMimeType(ContentService.MimeType.JSON);
}
if (action === 'clean' || action === 'deleteLegacy') {
cleanLegacyTabsSilently(ss);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({
status: 'success',
message: 'All legacy tabs deleted! Only Parents_Index, Tutors_Index, and Matches_Ledger remain.'
})).setMimeType(ContentService.MimeType.JSON);
}
if (action === 'format' || action === 'setup') {
setupCleanArchitectureSilently(ss);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({
status: 'success',
message: 'Google Sheet automatically reformatted to strict Matches_Ledger!'
})).setMimeType(ContentService.MimeType.JSON);
}
if (action === 'getData') {
var pSheet = ss.getSheetByName('Parents_Index') || createParentsSheet(ss);
var tSheet = ss.getSheetByName('Tutors_Index') || createTutorsSheet(ss);
var mSheet = ss.getSheetByName('Matches_Ledger') || createMatchesSheet(ss);
var parents = rowsToJson(pSheet);
var tutors = rowsToJson(tSheet);
var matches = rowsToJson(mSheet);
var output = { parents: parents, tutors: tutors, matches: matches };
return ContentService.createTextOutput(JSON.stringify(output))
.setMimeType(ContentService.MimeType.JSON);
}
if (action === 'addTutor' || action === 'addTutorId') {
var tSheet = ss.getSheetByName('Tutors_Index') || createTutorsSheet(ss);
var p = (e && e.parameter) ? e.parameter : {};
var trackingId = (p.id || p.trackingId || '').toString().trim();
if (trackingId) {
if (checkExistsInSheet(tSheet, trackingId)) {
return ContentService.createTextOutput(JSON.stringify({ status: 'duplicate_skipped', id: trackingId }))
.setMimeType(ContentService.MimeType.JSON);
}
var ts = Utilities.formatDate(new Date(), 'GMT+3', 'yyyy-MM-dd HH:mm');
tSheet.appendRow([ts, trackingId]);
formatLastRow(tSheet, '#065f46');
SpreadsheetApp.flush();
sendTelegramNotification('๐ NEW TUTOR REGISTERED\n\n๐ท๏ธ Tutor ID:' + trackingId + '\n๐ Date: ' + ts);
}
return ContentService.createTextOutput(JSON.stringify({ status: 'success', id: trackingId }))
.setMimeType(ContentService.MimeType.JSON);
}
if (action === 'addParent' || action === 'addParentId') {
var pSheet = ss.getSheetByName('Parents_Index') || createParentsSheet(ss);
var p = (e && e.parameter) ? e.parameter : {};
var trackingId = (p.id || p.trackingId || '').toString().trim();
if (trackingId) {
if (checkExistsInSheet(pSheet, trackingId)) {
return ContentService.createTextOutput(JSON.stringify({ status: 'duplicate_skipped', id: trackingId }))
.setMimeType(ContentService.MimeType.JSON);
}
var ts = Utilities.formatDate(new Date(), 'GMT+3', 'yyyy-MM-dd HH:mm');
pSheet.appendRow([ts, trackingId]);
formatLastRow(pSheet, '#0f172a');
SpreadsheetApp.flush();
sendTelegramNotification('๐จโ๐ฉโ๐ง NEW PARENT REGISTERED\n\n๐ท๏ธ Parent ID:' + trackingId + '\n๐ Date: ' + ts);
}
return ContentService.createTextOutput(JSON.stringify({ status: 'success', id: trackingId }))
.setMimeType(ContentService.MimeType.JSON);
}
if (action === 'checkStatus') {
var queryId = (e.parameter.id || e.parameter.trackingId || '').toString().trim().toUpperCase();
var foundMatch = null;
var mSheet = ss.getSheetByName('Matches_Ledger');
if (mSheet && mSheet.getLastRow() > 1) {
var mData = mSheet.getRange(2, 1, mSheet.getLastRow() - 1, Math.min(mSheet.getLastColumn(), 6)).getValues();
for (var r = 0; r < mData.length; r++) {
var pId = (mData[r][1] || '').toString().trim().toUpperCase();
var tId = (mData[r][2] || '').toString().trim().toUpperCase();
if (queryId && (pId === queryId || tId === queryId)) {
foundMatch = {
date: mData[r][0],
parentId: mData[r][1],
tutorId: mData[r][2],
monthlyFee: mData[r][3],
commission: mData[r][4],
status: mData[r][5]
};
break;
}
}
}
return ContentService.createTextOutput(JSON.stringify({ status: 'success', match: foundMatch }))
.setMimeType(ContentService.MimeType.JSON);
}
return ContentService.createTextOutput(JSON.stringify({
status: 'OK',
message: 'Teme Astegni Cloud API Active (Strict Deduplication & Alignment)'
})).setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}
function doPost(e) {
var lock = LockService.getScriptLock();
try {
lock.waitLock(15000);
} catch (lockErr) {
return ContentService.createTextOutput(JSON.stringify({ status: 'busy', error: 'Server is busy, try again shortly.' }))
.setMimeType(ContentService.MimeType.JSON);
}
try {
var postData;
if (e && e.postData && e.postData.contents) {
try {
postData = JSON.parse(e.postData.contents);
} catch (parseErr) {
postData = e.parameter || {};
}
} else if (e && e.parameter) {
postData = e.parameter;
} else {
postData = {};
}
var action = postData.action;
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (action === 'fixSheet' || action === 'fixAndAlign') {
var summary = fixAndAlignAllSheetsSilently(ss);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({ status: 'success', summary: summary }))
.setMimeType(ContentService.MimeType.JSON);
}
else if (action === 'cleanDuplicates' || action === 'dedup') {
var removed = deduplicateAllSheetsSilently(ss);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({ status: 'success', removedRows: removed }))
.setMimeType(ContentService.MimeType.JSON);
}
else if (action === 'addParent') {
var sheet = ss.getSheetByName('Parents_Index') || createParentsSheet(ss);
var d = postData.data || postData;
var trackingId = (d.trackingId || d.id || d.parentId || '').toString().trim();
if (trackingId) {
if (checkExistsInSheet(sheet, trackingId)) {
return ContentService.createTextOutput(JSON.stringify({ status: 'duplicate_skipped', id: trackingId }))
.setMimeType(ContentService.MimeType.JSON);
}
var ts = Utilities.formatDate(new Date(), 'GMT+3', 'yyyy-MM-dd HH:mm');
sheet.appendRow([ts, trackingId]);
formatLastRow(sheet, '#0f172a');
SpreadsheetApp.flush();
sendTelegramNotification('๐จโ๐ฉโ๐ง NEW PARENT REGISTERED\n\n๐ท๏ธ Parent ID:' + trackingId + '\n๐ Date: ' + ts);
}
}
else if (action === 'addTutor') {
var sheet = ss.getSheetByName('Tutors_Index') || createTutorsSheet(ss);
var d = postData.data || postData;
var trackingId = (d.trackingId || d.id || d.tutorId || '').toString().trim();
if (trackingId) {
if (checkExistsInSheet(sheet, trackingId)) {
return ContentService.createTextOutput(JSON.stringify({ status: 'duplicate_skipped', id: trackingId }))
.setMimeType(ContentService.MimeType.JSON);
}
var ts = Utilities.formatDate(new Date(), 'GMT+3', 'yyyy-MM-dd HH:mm');
sheet.appendRow([ts, trackingId]);
formatLastRow(sheet, '#065f46');
SpreadsheetApp.flush();
sendTelegramNotification('๐ NEW TUTOR REGISTERED\n\n๐ท๏ธ Tutor ID:' + trackingId + '\n๐ Date: ' + ts);
}
}
else if (action === 'addMatch') {
var sheet = ss.getSheetByName('Matches_Ledger') || createMatchesSheet(ss);
var d = postData.data || postData;
// Extract clean Parent ID (e.g. AST-P-9207)
var pId = (d.parentId || '').toString().trim();
if (!pId && d.parentName) {
var pMatch = d.parentName.match(/AST-P-[A-Za-z0-9]+/i);
if (pMatch) pId = pMatch[0];
}
if (!pId && d.id && d.id.indexOf('AST-P-') !== -1) pId = d.id;
// Extract clean Tutor ID (e.g. AST-T-2339)
var tId = (d.tutorId || '').toString().trim();
if (!tId && d.tutorName) {
var tMatch = d.tutorName.match(/AST-T-[A-Za-z0-9]+/i);
if (tMatch) tId = tMatch[0];
}
var feeNum = Number(d.monthlyFee || d.fee || 3500);
var commNum = Number(d.commission || d.comm || Math.round(feeNum * 0.25));
var status = d.status || 'Active Contract โ';
var ts = Utilities.formatDate(new Date(), 'GMT+3', 'yyyy-MM-dd HH:mm');
// Exact 6-column row as requested
var newRow = [
ts,
pId,
tId,
feeNum,
commNum,
status
];
// Check if this match (parentId + tutorId) already exists in Matches_Ledger
var existingRowIndex = -1;
if (sheet.getLastRow() > 1) {
var mData = sheet.getRange(2, 1, sheet.getLastRow() - 1, Math.min(sheet.getLastColumn(), 6)).getValues();
for (var r = 0; r < mData.length; r++) {
var rowPId = (mData[r][1] || '').toString().trim().toUpperCase();
var rowTId = (mData[r][2] || '').toString().trim().toUpperCase();
if (pId && tId && rowPId === pId.toUpperCase() && rowTId === tId.toUpperCase()) {
existingRowIndex = r + 2; // 1-based, accounting for header
break;
}
}
}
if (existingRowIndex > 1) {
// Update existing row in place with latest status & details to eliminate duplicates!
sheet.getRange(existingRowIndex, 1, 1, 6).setValues([newRow]);
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({ status: 'updated_existing', parentId: pId, tutorId: tId }))
.setMimeType(ContentService.MimeType.JSON);
} else {
sheet.appendRow(newRow);
formatLastRow(sheet, '#1e40af');
SpreadsheetApp.flush();
sendTelegramNotification('๐ค NEW MATCH RECORDED IN GOOGLE SHEET\n\n๐จโ๐ฉโ๐ง Parent ID: ' + pId + '\n๐ Tutor ID: ' + tId + '\n๐ต Monthly Fee: ' + feeNum.toLocaleString() + ' ETB\n๐ผ Commission (25%): ' + commNum.toLocaleString() + ' ETB\n๐ Status: ' + status + '\n๐ Date: ' + ts);
}
}
else if (action === 'logAdminAction') {
sendTelegramNotification(postData.text || '');
}
SpreadsheetApp.flush();
return ContentService.createTextOutput(JSON.stringify({ status: 'success' }))
.setMimeType(ContentService.MimeType.JSON);
} catch(err) {
return ContentService.createTextOutput(JSON.stringify({ status: 'error', error: err.toString() }))
.setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}
function checkExistsInSheet(sheet, id) {
if (!sheet || sheet.getLastRow() <= 1) return false;
var lastRow = sheet.getLastRow();
var lastCol = Math.min(sheet.getLastColumn(), 4);
var data = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues();
var idClean = (id || '').toString().trim().toUpperCase();
for (var i = 0; i < data.length; i++) {
var colB = data[i][1] ? data[i][1].toString().trim().toUpperCase() : '';
var colA = data[i][0] ? data[i][0].toString().trim().toUpperCase() : '';
if (idClean && (colB === idClean || colA === idClean)) {
return true;
}
}
return false;
}
function fixAndAlignAllSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var summary = fixAndAlignAllSheetsSilently(ss);
SpreadsheetApp.getUi().alert('๐ง FIX & ALIGNMENT COMPLETE!\n\n' + summary);
}
function fixAndAlignAllSheetsSilently(ss) {
var log = [];
// ==========================================
// 1. FIX & ALIGN MATCHES_LEDGER (Strict 6-Column Layout & Merge Row 2 and Row 3)
// ==========================================
var mSheet = ss.getSheetByName('Matches_Ledger') || ss.insertSheet('Matches_Ledger');
var mHeaders = ['Timestamp', 'Parent ID', 'Tutor ID', 'Monthly Fee (ETB)', 'Commission (ETB)', 'Status'];
var existingData = [];
if (mSheet.getLastRow() > 1) {
existingData = mSheet.getRange(2, 1, mSheet.getLastRow() - 1, mSheet.getLastColumn()).getValues();
}
var cleanMatches = {};
var matchOrder = [];
for (var i = 0; i < existingData.length; i++) {
var r = existingData[i];
var rowText = r.join(' ');
// Extract Parent ID (e.g. AST-P-9207)
var pMatch = rowText.match(/AST-P-[A-Za-z0-9]+/i);
var pId = pMatch ? pMatch[0].toUpperCase() : (r[1] ? r[1].toString().trim() : '');
// Extract Tutor ID (e.g. AST-T-2339)
var tMatch = rowText.match(/AST-T-[A-Za-z0-9]+/i);
var tId = tMatch ? tMatch[0].toUpperCase() : (r[2] ? r[2].toString().trim() : '');
if (!pId && !tId) continue;
// Timestamp
var ts = r[0] || '2026-10-02 09:13';
if (ts instanceof Date) {
ts = Utilities.formatDate(ts, 'GMT+3', 'yyyy-MM-dd HH:mm');
} else {
ts = ts.toString().trim();
}
// Fee and Commission
var fee = 3500;
var comm = 875;
for (var c = 0; c < r.length; c++) {
var num = Number((r[c] || '').toString().replace(/[^0-9]/g, ''));
if (num >= 2000 && num <= 30000) fee = num;
if (num >= 500 && num <= 7500 && num !== fee) comm = num;
}
if (comm === 875 && fee !== 3500) {
comm = Math.round(fee * 0.25);
}
// Status
var status = 'Active Contract โ';
if (rowText.indexOf('Active') !== -1) {
status = 'Active Contract โ';
} else if (rowText.indexOf('Trial') !== -1 || rowText.indexOf('Pending') !== -1) {
status = '1-Day Trial Pending';
} else if (r[5] && r[5].toString().trim()) {
status = r[5].toString().trim();
}
var key = pId + '_' + tId;
if (!cleanMatches[key]) {
cleanMatches[key] = { ts: ts, pId: pId, tId: tId, fee: fee, comm: comm, status: status };
matchOrder.push(key);
} else {
// Merge duplicate: prefer Active Contract status and earliest timestamp
if (status.indexOf('Active') !== -1) cleanMatches[key].status = 'Active Contract โ';
if (fee) cleanMatches[key].fee = fee;
if (comm) cleanMatches[key].comm = comm;
}
}
mSheet.clear();
mSheet.getRange(1, 1, 1, mHeaders.length).setValues([mHeaders]);
applyModernHeader(mSheet, '#1e40af');
var mRowsWritten = 0;
for (var k = 0; k < matchOrder.length; k++) {
var m = cleanMatches[matchOrder[k]];
mSheet.appendRow([m.ts, m.pId, m.tId, m.fee, m.comm, m.status]);
formatLastRow(mSheet, '#1e40af');
mRowsWritten++;
}
log.push('โ Matches_Ledger: Strict 6 columns aligned, ' + mRowsWritten + ' unique deal(s) preserved (Row 2 and Row 3 merged into 1 clean active contract).');
// ==========================================
// 2. DEDUPLICATE TUTORS_INDEX (Strict 2 Columns: Registration Date | Tutor ID)
// ==========================================
var tSheet = ss.getSheetByName('Tutors_Index') || ss.insertSheet('Tutors_Index');
var tHeaders = ['Registration Date', 'Tutor ID'];
var tValues = [];
if (tSheet.getLastRow() > 1) {
tValues = tSheet.getRange(2, 1, tSheet.getLastRow() - 1, Math.min(tSheet.getLastColumn(), 3)).getValues();
}
var uniqueTutors = {};
var tutorOrder = [];
for (var ti = 0; ti < tValues.length; ti++) {
var tr = tValues[ti];
var tRowText = tr.join(' ');
var tMatch = tRowText.match(/AST-T-[A-Za-z0-9]+/i);
var tId = tMatch ? tMatch[0].toUpperCase() : (tr[1] ? tr[1].toString().trim().toUpperCase() : '');
if (!tId || tId === 'TUTOR ID') continue;
var tDate = tr[0];
if (tDate instanceof Date) {
tDate = Utilities.formatDate(tDate, 'GMT+3', 'yyyy-MM-dd HH:mm');
} else {
tDate = (tDate || '').toString().trim() || '2026-10-02 08:24';
}
if (!uniqueTutors[tId]) {
uniqueTutors[tId] = tDate;
tutorOrder.push(tId);
}
}
tSheet.clear();
tSheet.getRange(1, 1, 1, tHeaders.length).setValues([tHeaders]);
applyModernHeader(tSheet, '#065f46');
for (var toj = 0; toj < tutorOrder.length; toj++) {
var curTId = tutorOrder[toj];
tSheet.appendRow([uniqueTutors[curTId], curTId]);
formatLastRow(tSheet, '#065f46');
}
var tDuplicatesRemoved = Math.max(0, tValues.length - tutorOrder.length);
log.push('โ Tutors_Index: Strict 2 columns aligned. Removed ' + tDuplicatesRemoved + ' duplicate rows. Kept ' + tutorOrder.length + ' unique tutor(s).');
// ==========================================
// 3. DEDUPLICATE PARENTS_INDEX (Strict 2 Columns: Registration Date | Parent ID)
// ==========================================
var pSheet = ss.getSheetByName('Parents_Index') || ss.insertSheet('Parents_Index');
var pHeaders = ['Registration Date', 'Parent ID'];
var pValues = [];
if (pSheet.getLastRow() > 1) {
pValues = pSheet.getRange(2, 1, pSheet.getLastRow() - 1, Math.min(pSheet.getLastColumn(), 3)).getValues();
}
var uniqueParents = {};
var parentOrder = [];
for (var pi = 0; pi < pValues.length; pi++) {
var pr = pValues[pi];
var pRowText = pr.join(' ');
var pMatch = pRowText.match(/AST-P-[A-Za-z0-9]+/i);
var pId = pMatch ? pMatch[0].toUpperCase() : (pr[1] ? pr[1].toString().trim().toUpperCase() : '');
if (!pId || pId === 'PARENT ID') continue;
var pDate = pr[0];
if (pDate instanceof Date) {
pDate = Utilities.formatDate(pDate, 'GMT+3', 'yyyy-MM-dd HH:mm');
} else {
pDate = (pDate || '').toString().trim() || '2026-10-01 20:50';
}
if (!uniqueParents[pId]) {
uniqueParents[pId] = pDate;
parentOrder.push(pId);
}
}
pSheet.clear();
pSheet.getRange(1, 1, 1, pHeaders.length).setValues([pHeaders]);
applyModernHeader(pSheet, '#0f172a');
for (var poj = 0; poj < parentOrder.length; poj++) {
var curPId = parentOrder[poj];
pSheet.appendRow([uniqueParents[curPId], curPId]);
formatLastRow(pSheet, '#0f172a');
}
var pDuplicatesRemoved = Math.max(0, pValues.length - parentOrder.length);
log.push('โ Parents_Index: Strict 2 columns aligned. Removed ' + pDuplicatesRemoved + ' duplicate rows. Kept ' + parentOrder.length + ' unique parent(s).');
cleanLegacyTabsSilently(ss);
return log.join('\n\n');
}
function deduplicateAllSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var summary = fixAndAlignAllSheetsSilently(ss);
SpreadsheetApp.getUi().alert('๐งน CLEANUP COMPLETE!\n\n' + summary);
}
function deduplicateAllSheetsSilently(ss) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var summary = fixAndAlignAllSheetsSilently(ss);
SpreadsheetApp.flush();
return summary;
}
function setupCleanArchitecture() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
setupCleanArchitectureSilently(ss);
SpreadsheetApp.getUi().alert('๐ SUCCESS!\n\nYour Google Sheet has been converted to the Official Matches & Revenue Ledger!\n\nโข Matches_Ledger: [Timestamp, Parent ID, Tutor ID, Monthly Fee (ETB), Commission (ETB), Status]\nโข Parents_Index: [Registration Date, Parent ID]\nโข Tutors_Index: [Registration Date, Tutor ID]\n\nAll raw leads remain organized in Telegram & Firebase to keep this ledger 100% clean.');
}
function setupCleanArchitectureSilently(ss) {
createMatchesSheet(ss);
createParentsSheet(ss);
createTutorsSheet(ss);
cleanLegacyTabsSilently(ss);
}
function createMatchesSheet(ss) {
var s = ss.getSheetByName('Matches_Ledger') || ss.insertSheet('Matches_Ledger');
var mHeaders = ['Timestamp', 'Parent ID', 'Tutor ID', 'Monthly Fee (ETB)', 'Commission (ETB)', 'Status'];
if (s.getLastRow() === 0) {
s.appendRow(mHeaders);
applyModernHeader(s, '#1e40af');
} else {
var currentHeader = s.getRange(1, 1, 1, Math.min(s.getLastColumn(), 6)).getValues()[0];
if (!currentHeader[1] || currentHeader[1].toString().indexOf('Parent ID') === -1) {
s.getRange(1, 1, 1, mHeaders.length).setValues([mHeaders]);
applyModernHeader(s, '#1e40af');
}
}
return s;
}
function createParentsSheet(ss) {
var s = ss.getSheetByName('Parents_Index') || ss.insertSheet('Parents_Index');
var headers = ['Registration Date', 'Parent ID'];
if (s.getLastRow() === 0) {
s.appendRow(headers);
applyModernHeader(s, '#0f172a');
}
return s;
}
function createTutorsSheet(ss) {
var s = ss.getSheetByName('Tutors_Index') || ss.insertSheet('Tutors_Index');
var headers = ['Registration Date', 'Tutor ID'];
if (s.getLastRow() === 0) {
s.appendRow(headers);
applyModernHeader(s, '#065f46');
}
return s;
}
function cleanLegacyTabs() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
cleanLegacyTabsSilently(ss);
SpreadsheetApp.getUi().alert('๐งน Legacy unused sheets removed!\n\nOnly Matches_Ledger, Tutors_Index, and Parents_Index remain.');
}
function cleanLegacyTabsSilently(ss) {
var allowed = ['Matches_Ledger', 'Tutors_Index', 'Parents_Index'];
var sheets = ss.getSheets();
sheets.forEach(function(s) {
if (allowed.indexOf(s.getName()) === -1 && ss.getSheets().length > 1) {
try { ss.deleteSheet(s); } catch (e) {}
}
});
}
function sendTelegramNotification(text) {
var token = PropertiesService.getScriptProperties().getProperty('TG_BOT_TOKEN') || '';
var chatId = PropertiesService.getScriptProperties().getProperty('TG_CHAT_ID') || '';
if (!token || !chatId) return;
try {
var url = 'https://api.telegram.org/bot' + token + '/sendMessage';
UrlFetchApp.fetch(url, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({
chat_id: chatId,
text: text,
parse_mode: 'HTML'
}),
muteHttpExceptions: true
});
} catch (e) {}
}
function testTelegramNotification() {
sendTelegramNotification('๐ Teme Astegni Cloud Test Alert\n\nYour Google Sheet and Telegram Bot integration is online and active.');
SpreadsheetApp.getUi().alert('Telegram test alert sent!');
}
function applyModernHeader(sheet, bgColor) {
var lastCol = sheet.getLastColumn();
sheet.setFrozenRows(1);
var header = sheet.getRange(1, 1, 1, lastCol);
header.setBackground(bgColor);
header.setFontColor('#ffffff');
header.setFontWeight('bold');
header.setFontFamily('Arial');
header.setFontSize(10);
header.setHorizontalAlignment('center');
header.setVerticalAlignment('middle');
sheet.setRowHeight(1, 38);
for (var c = 1; c <= lastCol; c++) {
sheet.autoResizeColumn(c);
var w = sheet.getColumnWidth(c);
if (w < 160) sheet.setColumnWidth(c, 160);
if (w > 320) sheet.setColumnWidth(c, 320);
}
}
function formatLastRow(sheet, themeColor) {
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow <= 1) return;
var rowRange = sheet.getRange(lastRow, 1, 1, lastCol);
var isEven = (lastRow % 2 === 0);
rowRange.setBackground(isEven ? '#f8fafc' : '#ffffff');
rowRange.setFontFamily('Arial');
rowRange.setFontSize(9);
rowRange.setVerticalAlignment('middle');
sheet.setRowHeight(lastRow, 30);
sheet.getRange(lastRow, 1).setNumberFormat('yyyy-mm-dd hh:mm');
}
function rowsToJson(sheet) {
var data = sheet.getDataRange().getValues();
if (data.length <= 1) return [];
var headers = data[0];
var rows = [];
for (var i = 1; i < data.length; i++) {
var obj = {};
for (var j = 0; j < headers.length; j++) {
obj[headers[j]] = data[i][j];
}
rows.push(obj);
}
return rows;
}
๐ณ Parent Payment Receipt & Verification
Inspect Telebirr / CBE screenshot and verify tuition into agency escrow
โ๏ธ Telegram Verified: Receipt routed to the "parents payments" group with Parent ID permanently attached.
