Recent leases
Pending commissions
Extensions then Apps Script. This opens the script editor in a new tab.// Ledgr Backend - Google Apps Script
// Paste this entire script into Apps Script, then click Deploy
const SHEET_ID = 'YOUR_SHEET_ID_HERE'; // Replace with your Sheet ID
const TABS = ['Properties','Reps','Leases','Overrides','CommLedger','InvLedger'];
function doGet(e) {
return handleRequest(e);
}
function doPost(e) {
return handleRequest(e);
}
function handleRequest(e) {
const action = e.parameter.action;
const tab = e.parameter.tab;
const ss = SpreadsheetApp.openById(SHEET_ID);
try {
if (action === 'init') return initSheets(ss);
if (action === 'read') return readTab(ss, tab);
if (action === 'write') return writeRow(ss, tab, JSON.parse(e.parameter.data));
if (action === 'update') return updateRow(ss, tab, e.parameter.id, JSON.parse(e.parameter.data));
if (action === 'delete') return deleteRow(ss, tab, e.parameter.id);
return resp({error: 'Unknown action'});
} catch(err) {
return resp({error: err.toString()});
}
}
function initSheets(ss) {
const headers = {
Properties: ['id','name','client','invoicingEmail','invoiceRate','renewalRate','weeklyFee','weeklyFeeType','repId','contractStart','contractEnd','invoiceType','address','status','notes'],
Reps: ['id','name','email','commPct','advanceAmt','minCommission','baseSalary','empType','role','status','initials','canSeeCalc'],
Leases: ['id','propertyId','repId','applicant','apt','rent','promo','promoType','promoValue','leaseTerm','effectiveTerm','miDate','status','invoiced','advancePaid','advancePaidDate','commBalPaid','commBalPaidDate','invoiceNum','invoiceAmt','splitRepId','splitPct','clawback','notes','dateLeased'],
Overrides: ['id','repId','propertyId','commPct','advanceAmt','minCommission'],
CommLedger: ['id','leaseId','repId','type','amount','date','note'],
InvLedger: ['id','leaseId','propertyId','type','amount','periodStart','periodEnd','date','invoiceNum','status','note']
};
TABS.forEach(t => {
let sheet = ss.getSheetByName(t);
if (!sheet) {
sheet = ss.insertSheet(t);
sheet.getRange(1, 1, 1, headers[t].length).setValues([headers[t]]);
sheet.getRange(1, 1, 1, headers[t].length).setFontWeight('bold').setBackground('#12111f').setFontColor('#78d649');
}
});
return resp({ok: true, message: 'Sheets initialized'});
}
function readTab(ss, tab) {
const sheet = ss.getSheetByName(tab);
if (!sheet) return resp({error: 'Tab not found: ' + tab});
const data = sheet.getDataRange().getValues();
if (data.length < 2) return resp({rows: []});
const headers = data[0];
const rows = data.slice(1).map(row => {
const obj = {};
headers.forEach((h, i) => obj[h] = row[i]);
return obj;
}).filter(r => r.id);
return resp({rows});
}
function writeRow(ss, tab, data) {
const sheet = ss.getSheetByName(tab);
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const row = headers.map(h => data[h] !== undefined ? data[h] : '');
sheet.appendRow(row);
return resp({ok: true});
}
function updateRow(ss, tab, id, data) {
const sheet = ss.getSheetByName(tab);
const allData = sheet.getDataRange().getValues();
const headers = allData[0];
const idIdx = headers.indexOf('id');
for (let i = 1; i < allData.length; i++) {
if (String(allData[i][idIdx]) === String(id)) {
const row = headers.map((h, j) => data[h] !== undefined ? data[h] : allData[i][j]);
sheet.getRange(i + 1, 1, 1, row.length).setValues([row]);
return resp({ok: true});
}
}
return resp({error: 'Row not found'});
}
function deleteRow(ss, tab, id) {
const sheet = ss.getSheetByName(tab);
const allData = sheet.getDataRange().getValues();
const idIdx = allData[0].indexOf('id');
for (let i = 1; i < allData.length; i++) {
if (String(allData[i][idIdx]) === String(id)) {
sheet.deleteRow(i + 1);
return resp({ok: true});
}
}
return resp({error: 'Row not found'});
}
function resp(data) {
return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}
YOUR_SHEET_ID_HERE with your actual Sheet ID. Your Sheet ID is in the URL: docs.google.com/spreadsheets/d/THIS_PART/edit1nmbUEVP1FK_corfnnahQRCpyI2HXiXQ1b7sOdinz3qQDeploy (top right) then New deployment. Set type to Web app. Set "Execute as" to Me and "Who has access" to Anyone. Click Deploy, authorize when prompted, then copy the Web App URL.