// EJI Tally for Google Sheets. Paste this whole file into Extensions > Apps Script, save, then reload the sheet.
// Menu "EJI Tally" > Set API key stores your accounting key (acc_...) in this script's properties, never in a cell.
// Every formula reads your books as of the last sync from your Tally PC; =TALLY_LAST_SYNC() shows when that was.
//
// Amounts follow one rule: a DEBIT balance is positive, a CREDIT balance is negative (Cash 5,000 Dr = 5000; Sales 9,000 Cr = -9000).
// The last argument of every function is an optional company name. Leave it out when your key reads one company, or set a
// default with EJI Tally > Set default company.

var EJI_TALLY_BASE_ = "https://acc.ejiapi.com/api/tally/v1";
var EJI_TALLY_CACHE_SECONDS_ = 900;  // 15 minutes; EJI Tally > Refresh values fetches fresh answers at once

// ---------- plumbing ----------
function ejiTally_(method, path, payload) {
  var props = PropertiesService.getScriptProperties();
  var cacheKey = "t" + (props.getProperty("EJI_TALLY_CACHE_V") || "0") + ":" + method + ":" + path + (payload ? ":" + JSON.stringify(payload) : "");
  if (cacheKey.length > 240) cacheKey = cacheKey.slice(0, 200) + Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, cacheKey));
  var cache = CacheService.getScriptCache();
  var hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);
  var key = props.getProperty("EJI_TALLY_KEY");
  if (!key) throw new Error("Set your key first: menu EJI Tally > Set API key");
  var opts = { method: method, headers: { "X-API-Key": key, "X-Client": "sheets" }, muteHttpExceptions: true };
  if (payload) { opts.contentType = "application/json"; opts.payload = JSON.stringify(payload); }
  var res;
  try {
    res = UrlFetchApp.fetch(EJI_TALLY_BASE_ + path, opts);
  } catch (e) {
    throw new Error("Could not reach EJI (" + e.message + "). Run EJI Tally > Check setup once to give the script permission.");
  }
  var text = res.getContentText(), code = res.getResponseCode();
  if (code !== 200) {
    var detail = text;
    try { detail = JSON.parse(text).detail || text; } catch (e) {}
    throw new Error(code === 401 ? "The API key was not accepted. EJI Tally > Set API key." : String(detail).slice(0, 160));
  }
  if (text.length < 90000) cache.put(cacheKey, text, EJI_TALLY_CACHE_SECONDS_);  // a cached value may not exceed 100 KB
  return JSON.parse(text);
}

function ejiTallyCo_(company) {
  var c = (company === undefined || company === null || company === "") ? PropertiesService.getScriptProperties().getProperty("EJI_TALLY_COMPANY") : company;
  return encodeURIComponent(c ? String(c) : "-");
}

function ejiTallyDate_(v, field) {
  if (v === undefined || v === null || v === "") return "";
  if (Object.prototype.toString.call(v) === "[object Date]") return Utilities.formatDate(v, Session.getScriptTimeZone(), "yyyy-MM-dd");
  var s = String(v).trim();
  if (/^\d{4}-\d{2}-\d{2}$/.test(s) || /^\d{1,2}[-\/.]\d{1,2}[-\/.]\d{4}$/.test(s)) return s;
  throw new Error(field + " must be a date (a date cell, or text like 2026-03-31)");
}

function ejiTallyQs_(params) {
  var parts = [];
  for (var k in params) if (params[k] !== "" && params[k] !== undefined && params[k] !== null) parts.push(k + "=" + encodeURIComponent(params[k]));
  return parts.length ? "?" + parts.join("&") : "";
}

function ejiTallyFlat_(v) {  // a cell or a range, as a flat list of non-empty texts
  if (!Array.isArray(v)) return [v];
  var out = [];
  v.forEach(function (row) { (Array.isArray(row) ? row : [row]).forEach(function (x) { out.push(x); }); });
  return out;
}

function ejiTallyDay_(s) {  // '2026-03-31' from the API, as a date in the sheet
  return s ? new Date(s + "T00:00:00") : "";
}

// ---------- companies and sync ----------
/** Companies your key can read: id, name, last sync.  =TALLY_COMPANIES()
 * @customfunction */
function TALLY_COMPANIES() {
  var rows = ejiTally_("get", "/companies").data;
  return [["Id", "Company", "Last sync"]].concat(rows.map(function (c) {
    return [c.id, c.name, c.syncedAt ? new Date(c.syncedAt * 1000) : ""];
  }));
}

/** When the books were last uploaded from Tally.  =TALLY_LAST_SYNC()
 * @param {string} company optional company name
 * @customfunction */
function TALLY_LAST_SYNC(company) {
  var r = ejiTally_("get", "/companies/" + ejiTallyCo_(company));
  return r.syncedAt ? new Date(r.syncedAt * 1000) : "never";
}

// ---------- ledgers ----------
/** Balance of a ledger on a date (today if left out). Debit positive, credit negative. Works on a whole column:
 * =TALLY_BALANCE(A2:A200, "2026-03-31") fetches every balance in one request.
 * @param {string|Array} ledger ledger name, or a range of names
 * @param {Date|string} date optional
 * @param {string} company optional
 * @customfunction */
function TALLY_BALANCE(ledger, date, company) {
  var names = ejiTallyFlat_(ledger).map(function (x) { return x === null || x === undefined ? "" : String(x); });
  var r = [], on = ejiTallyDate_(date, "date");
  for (var i = 0; i < names.length; i += 500) {  // the API takes up to 500 ledgers per request
    r = r.concat(ejiTally_("post", "/companies/" + ejiTallyCo_(company) + "/balances", { ledgers: names.slice(i, i + 500), date: on }).data);
  }
  if (!Array.isArray(ledger)) {
    if (r[0].error) throw new Error("No ledger named '" + ledger + "'");
    return r[0].balance;
  }
  return r.map(function (x) { return [x.balance === null ? (x.error ? "#N/A no ledger" : "") : x.balance]; });
}

/** Balance with Dr / Cr, as text: "12,500.00 Dr".  =TALLY_BALANCE_TEXT("Cash")
 * @customfunction */
function TALLY_BALANCE_TEXT(ledger, date, company) {
  var b = TALLY_BALANCE(ledger, date, company);
  var one = function (v) { return typeof v === "number" ? Math.abs(v).toLocaleString("en-IN", { minimumFractionDigits: 2, maximumFractionDigits: 2 }) + (v > 0 ? " Dr" : v < 0 ? " Cr" : "") : v; };
  return Array.isArray(b) ? b.map(function (r) { return [one(r[0])]; }) : one(b);
}

/** Opening balance of a ledger (as entered in Tally).  =TALLY_OPENING("Capital")
 * @customfunction */
function TALLY_OPENING(ledger, company) {
  return ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/ledger" + ejiTallyQs_({ name: ledger })).data.opening;
}

/** One detail of a ledger: "gstin", "group", "primaryGroup", "state", "pan", "creditDays", "mailingName", "nature".
 * =TALLY_LEDGER_INFO("Ravi Traders", "gstin")
 * @customfunction */
function TALLY_LEDGER_INFO(ledger, field, company) {
  var d = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/ledger" + ejiTallyQs_({ name: ledger })).data;
  if (!(field in d)) throw new Error("field must be one of: " + Object.keys(d).join(", "));
  var v = d[field];
  return v === null || v === undefined ? "" : v;
}

/** Ledgers with group, balance and GSTIN; optionally only those under a group.  =TALLY_LEDGERS("Sundry Debtors")
 * @customfunction */
function TALLY_LEDGERS(group, date, company) {
  var rows = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/ledgers" + ejiTallyQs_({ group: group, date: ejiTallyDate_(date, "date") })).data;
  return [["Ledger", "Group", "Balance", "Dr/Cr", "GSTIN", "State"]].concat(rows.map(function (l) {
    return [l.name, l.group || "", l.balance, l.side, l.gstin || "", l.state || ""];
  }));
}

/** Ledger statement: every entry between two dates with a running balance.  =TALLY_STATEMENT("Ravi Traders", "2025-04-01", "2026-03-31")
 * @customfunction */
function TALLY_STATEMENT(ledger, from, to, company) {
  var s = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/statement" +
    ejiTallyQs_({ ledger: ledger, from: ejiTallyDate_(from, "from"), to: ejiTallyDate_(to, "to") })).data;
  var out = [["Date", "Type", "No.", "Against", "Narration", "Debit", "Credit", "Balance"],
             [ejiTallyDay_(s.from), "Opening balance", "", "", "", "", "", s.opening]];
  s.rows.forEach(function (r) { out.push([ejiTallyDay_(r.date), r.voucherType || "", r.number || "", r.against || "", r.narration || "", r.debit || "", r.credit || "", r.balance]); });
  out.push([ejiTallyDay_(s.to), "Closing balance", "", "", "", s.totalDebit, s.totalCredit, s.closing]);
  return out;
}

// ---------- reports ----------
/** Trial balance on a date. level "ledger" (default) or "group".  =TALLY_TRIAL_BALANCE("2026-03-31", "group")
 * @customfunction */
function TALLY_TRIAL_BALANCE(date, level, company) {
  var tb = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/trial-balance" + ejiTallyQs_({ date: ejiTallyDate_(date, "date"), level: level })).data;
  var out = [["Particulars", "Group", "Debit", "Credit"]];
  tb.rows.forEach(function (r) { out.push([r.name, r.primaryGroup || "", r.debit || "", r.credit || ""]); });
  out.push(["Total", "", tb.totalDebit, tb.totalCredit]);
  return out;
}

/** Net movement of every ledger under a group in a period (debit positive).  =TALLY_GROUP_TOTAL("Indirect Expenses", "2025-04-01", "2026-03-31")
 * @customfunction */
function TALLY_GROUP_TOTAL(group, from, to, company) {
  return ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/group-total" +
    ejiTallyQs_({ group: group, from: ejiTallyDate_(from, "from"), to: ejiTallyDate_(to, "to") })).data.net;
}

/** Closing balance of a group on a date (opening plus movement).  =TALLY_GROUP_BALANCE("Bank Accounts", "2026-03-31")
 * @customfunction */
function TALLY_GROUP_BALANCE(group, date, company) {
  return ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/group-total" +
    ejiTallyQs_({ group: group, to: ejiTallyDate_(date, "date") })).data.closing;
}

/** Sales in a period (Sales Accounts, as a positive number).  =TALLY_SALES("2026-04-01", "2026-04-30")
 * @customfunction */
function TALLY_SALES(from, to, company) {
  return -TALLY_GROUP_TOTAL("Sales Accounts", from, to, company);
}

/** Purchases in a period (Purchase Accounts).  =TALLY_PURCHASES("2026-04-01", "2026-04-30")
 * @customfunction */
function TALLY_PURCHASES(from, to, company) {
  return TALLY_GROUP_TOTAL("Purchase Accounts", from, to, company);
}

/** Vouchers between two dates, optionally of one type ("Sales", "Receipt", ...).  =TALLY_DAYBOOK("2026-04-01", "2026-04-07")
 * @customfunction */
function TALLY_DAYBOOK(from, to, type, company) {
  var out = [["Date", "Type", "No.", "Party", "Amount", "Narration"]], offset = 0, d;
  do {  // up to 5,000 vouchers, 1,000 per request
    d = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/daybook" +
      ejiTallyQs_({ from: ejiTallyDate_(from, "from"), to: ejiTallyDate_(to, "to"), type: type, limit: 1000, offset: offset })).data;
    d.vouchers.forEach(function (v) {
      out.push([ejiTallyDay_(v.date), v.voucherType + (v.cancelled ? " (cancelled)" : ""), v.number || "", v.party || "", v.amount, v.narration || ""]);
    });
    offset += d.vouchers.length;
  } while (offset < d.total && offset < 5000 && d.vouchers.length);
  return out;
}

// ---------- receivables and payables ----------
/** What a party owes you (debtor) or you owe them (creditor), from open bills.  =TALLY_OUTSTANDING("Ravi Traders")
 * @customfunction */
function TALLY_OUTSTANDING(party, date, company) {
  var names = ejiTallyFlat_(party);
  var one = function (p) {
    if (p === "" || p === null) return "";
    return ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/outstanding" +
      ejiTallyQs_({ side: "auto", party: p, date: ejiTallyDate_(date, "date") })).data.total;
  };
  if (!Array.isArray(party)) return one(party);
  if (names.length > 50) throw new Error("Up to 50 parties per formula; use TALLY_RECEIVABLES_BY_PARTY for everyone");
  return names.map(function (p) { return [one(p)]; });
}

/** Total receivable (Sundry Debtors) on a date.  =TALLY_RECEIVABLES()
 * @customfunction */
function TALLY_RECEIVABLES(date, company) {
  return ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/outstanding" + ejiTallyQs_({ side: "receivable", date: ejiTallyDate_(date, "date") })).data.total;
}

/** Total payable (Sundry Creditors) on a date.  =TALLY_PAYABLES()
 * @customfunction */
function TALLY_PAYABLES(date, company) {
  return ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/outstanding" + ejiTallyQs_({ side: "payable", date: ejiTallyDate_(date, "date") })).data.total;
}

/** Receivable per party, largest first.  =TALLY_RECEIVABLES_BY_PARTY()
 * @customfunction */
function TALLY_RECEIVABLES_BY_PARTY(date, company) {
  var d = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/outstanding" + ejiTallyQs_({ side: "receivable", date: ejiTallyDate_(date, "date") })).data;
  return [["Party", "Receivable"]].concat(d.parties.map(function (p) { return [p.party, p.amount]; }));
}

/** Open bills with due date and days overdue. side "receivable" (default) or "payable"; party optional.
 * =TALLY_BILLS("receivable", "Ravi Traders")
 * @customfunction */
function TALLY_BILLS(side, party, date, company) {
  var d = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/outstanding" +
    ejiTallyQs_({ side: side || "receivable", party: party, date: ejiTallyDate_(date, "date") })).data;
  return [["Party", "Bill", "Bill date", "Due date", "Days overdue", "Amount"]].concat(d.bills.map(function (b) {
    return [b.party, b.bill, ejiTallyDay_(b.billDate), ejiTallyDay_(b.dueDate), b.overdueDays === null ? "" : b.overdueDays, b.amount];
  }));
}

// ---------- stock ----------
/** Closing quantity of a stock item at the last sync.  =TALLY_STOCK("Battery 12V")
 * @customfunction */
function TALLY_STOCK(item, company) {
  var items = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/stock-items").data;
  var find = function (name) {
    var n = String(name).toLowerCase();
    for (var i = 0; i < items.length; i++) if (items[i].name.toLowerCase() === n) return items[i].closingQuantityAtSync;
    return "#N/A no item";
  };
  return Array.isArray(item) ? ejiTallyFlat_(item).map(function (x) { return [x === "" ? "" : find(x)]; }) : find(item);
}

/** Every stock item: group, unit, closing quantity, HSN.  =TALLY_STOCK_ITEMS()
 * @customfunction */
function TALLY_STOCK_ITEMS(company) {
  var items = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/stock-items").data;
  return [["Item", "Group", "Unit", "Closing qty", "HSN"]].concat(items.map(function (i) {
    return [i.name, i.group || "", i.unit || "", i.closingQuantityAtSync === null ? "" : i.closingQuantityAtSync, i.hsn || ""];
  }));
}

/** Quantity of a stock item that came in ("in"), went out ("out"), or their values ("in value", "out value") in a period.
 * =TALLY_STOCK_MOVEMENT("Battery 12V", "out", "2026-04-01", "2026-04-30")
 * @customfunction */
function TALLY_STOCK_MOVEMENT(item, what, from, to, company) {
  var m = ejiTally_("get", "/companies/" + ejiTallyCo_(company) + "/stock-movement" +
    ejiTallyQs_({ item: item, from: ejiTallyDate_(from, "from"), to: ejiTallyDate_(to, "to") })).data;
  var map = { "in": "inwardQuantity", "out": "outwardQuantity", "in value": "inwardValue", "out value": "outwardValue" };
  var k = map[String(what || "out").toLowerCase()];
  if (!k) throw new Error('what must be "in", "out", "in value" or "out value"');
  return m[k];
}

// ---------- menu ----------
function onOpen() {
  SpreadsheetApp.getUi().createMenu("EJI Tally")
    .addItem("Set API key", "ejiTallySetKey")
    .addItem("Set default company", "ejiTallySetCompany")
    .addItem("Check setup", "ejiTallyCheck")
    .addSeparator()
    .addItem("Refresh values", "ejiTallyRefresh")
    .addToUi();
}

function ejiTallyCheck_() {
  var key = PropertiesService.getScriptProperties().getProperty("EJI_TALLY_KEY");
  if (!key) return { ok: false, message: "No API key is saved yet. Use EJI Tally > Set API key." };
  try {
    var res = UrlFetchApp.fetch(EJI_TALLY_BASE_ + "/companies", { headers: { "X-API-Key": key, "X-Client": "sheets" }, muteHttpExceptions: true });
    var code = res.getResponseCode();
    if (code === 401) return { ok: false, message: "The key was not accepted. Copy the whole key again; it starts with acc_." };
    if (code === 429) return { ok: false, message: "Rate limit or monthly quota reached (HTTP 429)." };
    if (code !== 200) return { ok: false, message: "The API answered HTTP " + code + "." };
    var cos = JSON.parse(res.getContentText()).data;
    if (!cos.length) return { ok: false, message: "The key works, but no company is connected yet. Connect one in the EJI Tally Agent on your Tally PC." };
    return { ok: true, message: "Everything works. Companies: " + cos.map(function (c) { return c.name; }).join(", ") + "." };
  } catch (e) {
    return { ok: false, message: "Could not reach the API: " + e.message + " If Google asked for permission and you declined, run this again and allow it." };
  }
}

function ejiTallyCheck() {
  var r = ejiTallyCheck_();
  SpreadsheetApp.getUi().alert(r.ok ? "OK. " + r.message : "Problem. " + r.message);
  return r.ok;
}

function ejiTallySetKey() {
  var ui = SpreadsheetApp.getUi();
  var r = ui.prompt("EJI Tally API key", "Paste your accounting API key (acc_...). It is stored in this script's properties, not in the sheet.", ui.ButtonSet.OK_CANCEL);
  var key = r.getResponseText().trim();
  if (r.getSelectedButton() !== ui.Button.OK || !key) return;
  PropertiesService.getScriptProperties().setProperty("EJI_TALLY_KEY", key);
  var c = ejiTallyCheck_();
  ui.alert(c.ok ? "Saved. " + c.message : "The key is saved, but the check failed. " + c.message);
}

function ejiTallySetCompany() {
  var ui = SpreadsheetApp.getUi();
  var props = PropertiesService.getScriptProperties();
  var r = ui.prompt("Default company", "Company name exactly as in Tally (leave empty to clear). Current: " + (props.getProperty("EJI_TALLY_COMPANY") || "none"), ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return;
  var name = r.getResponseText().trim();
  if (name) props.setProperty("EJI_TALLY_COMPANY", name); else props.deleteProperty("EJI_TALLY_COMPANY");
  ejiTallyRefresh();
}

// Forget cached answers and enter this sheet's TALLY_ formulas again so they fetch fresh values. Nothing else changes.
function ejiTallyRefresh() {
  var props = PropertiesService.getScriptProperties();
  props.setProperty("EJI_TALLY_CACHE_V", String((Number(props.getProperty("EJI_TALLY_CACHE_V")) || 0) + 1));
  var sheet = SpreadsheetApp.getActiveSheet();
  var formulas = sheet.getDataRange().getFormulas();
  var mine = /\bTALLY_[A-Z_]+\s*\(/i, runs = [];
  formulas.forEach(function (row, i) {
    var j = 0;
    while (j < row.length) {
      if (!mine.test(row[j])) { j++; continue; }
      var start = j;
      while (j < row.length && mine.test(row[j])) j++;
      runs.push({ r: i + 1, c: start + 1, f: row.slice(start, j) });
    }
  });
  var cleared = [], failed = [];
  try {
    runs.forEach(function (x) { sheet.getRange(x.r, x.c, 1, x.f.length).clearContent(); cleared.push(x); });
    SpreadsheetApp.flush();
  } finally {
    cleared.forEach(function (x) {
      try { sheet.getRange(x.r, x.c, 1, x.f.length).setFormulas([x.f]); } catch (e) { failed.push(x.r + ":" + x.c); }
    });
  }
  if (failed.length) throw new Error("Could not enter the formulas again at row:column " + failed.join(", ") + ". Use Edit > Undo to bring them back.");
}