Error while fetching data from TDL (TallyPrime) using Node.js

Question

I have a TDL which is loaded in tally the TDL and a node script I have pasted both below

The TDL is below

;; ============================================================
;; Inventory Walk (Flat) — one row per inventory line (all voucher types)
;; Report ID: RTS FlatVch
;; Columns:
;; DATE, PARTYNAME, VCHTYPE, VOUCHERNUMBER, COSTCENTRENAME,
;; STOCKITEMNAME, ACTUALQTY, RATE, AMOUNT, BATCHNAME
;; ============================================================

[Report: RTS FlatVch]
    Form        : RTS InvFlat Form
    Filtered    : Yes
    Export      : Yes

[Form: RTS InvFlat Form]
    Parts       : RTS Flat Part

[Part: RTS Flat Part]
    Lines       : RTS FlatRow
    Repeat      : RTS FlatRow : RTS FlatInv
    Vertical    : Yes
    Scroll      : Vertical

[Line: RTS FlatRow]
    XMLTag      : ROW
    Fields      : F_DATE, F_PARTY, F_VCHTYPE, F_VCHNO, F_COST, F_ITEM, F_QTY, F_RATE, F_AMOUNT, F_BATCH

; ---- voucher-level fields (now read from computed methods on the line) ----
[Field: F_DATE]
    Use         : Name Field
    Set As      : $V_Date
    XMLTag      : DATE

[Field: F_PARTY]
    Use         : Name Field
    Set As      : $V_Party
    XMLTag      : PARTYNAME

[Field: F_VCHTYPE]
    Use         : Name Field
    Set As      : $V_VchType
    XMLTag      : VCHTYPE

[Field: F_VCHNO]
    Use         : Name Field
    Set As      : $V_VchNo
    XMLTag      : VOUCHERNUMBER

[Field: F_COST]
    Use         : Name Field
    Set As      : $V_Cost
    XMLTag      : COSTCENTRENAME

; ---- item-level fields (current inventory entry) ----
[Field: F_ITEM]
    Use         : Name Field
    Set As      : $StockItemName
    XMLTag      : STOCKITEMNAME

[Field: F_QTY]
    Use         : Name Field
    Set As      : $ActualQty
    XMLTag      : ACTUALQTY

[Field: F_RATE]
    Use         : Name Field
    Set As      : $Rate
    XMLTag      : RATE

[Field: F_AMOUNT]
    Use         : Name Field
    Set As      : $Amount
    XMLTag      : AMOUNT

[Field: F_BATCH]
    Use         : Name Field
    Set As      : $BatchAllocations[1].BatchName
    XMLTag      : BATCHNAME

; -------- all vouchers (no filter) --------
[Collection: RTS AllVouchers]
    Type        : Voucher
    Fetch       : Date, PartyLedgerName, VoucherTypeName, VoucherNumber, CostCentreName, AllInventoryEntries.*

; -------- flat inventory-line collection --------
[Collection: RTS FlatInv]
    Source Collection : RTS AllVouchers
    Walk              : All Inventory Entries
    Belongs To        : RTS AllVouchers
    Fetch             : StockItemName, ActualQty, Rate, Amount, BatchAllocations.*
    ; Compute voucher-level methods onto each inventory line
    Compute           : V_Date    : $..Date
    Compute           : V_Party   : $..PartyLedgerName
    Compute           : V_VchType : $..VoucherTypeName
    Compute           : V_VchNo   : $..VoucherNumber
    Compute           : V_Cost    : $..CostCentreName

And the node script which fetches data from TallyPrime

#!/usr/bin/env node
"use strict";

/**
 * AllVoucher.js — Smart Append Tally -> Google Sheets
 * Report: RTS InvFlat
 * Sheet: AllVoucher (adds header if missing, appends data only, formats new rows)
 *
 * npm i axios fast-xml-parser googleapis
 * credentials.json must have Editor access on the sheet
 */

const axios = require("axios");
const { XMLParser } = require("fast-xml-parser");
const { google } = require("googleapis");
const fs = require("fs");

/* ---------- CONFIG (edit if needed) ---------- */
const TALLY_URL       = "myIP";
const COMPANY         = "Company";
const REPORT_ID       = "RTS FlatVch";

const DEFAULT_FROM_ISO = "2025-04-01"; // used only if sheet empty & --from not provided
const DEFAULT_TO_ISO = new Intl.DateTimeFormat('en-CA', {
  timeZone: 'Asia/Kolkata',
  year: 'numeric',
  month: '2-digit',
  day: '2-digit'
}).format(new Date());


const SPREADSHEET_ID  = "SheetID";
const TAB_NAME        = "AllVoucher";
const CREDENTIALS     = "credentials.json";

const BATCH_ROWS      = 20000;  // rows per append call
/* -------------------------------------------- */

const HEADERS = [
  "DATE","PARTYNAME","VCHTYPE","VOUCHERNUMBER","COSTCENTRENAME",
  "STOCKITEMNAME","ACTUALQTY","RATE","AMOUNT","BATCHNAME"
];

// ---------- small helpers ----------
function getArg(name, def = "") {
  const hit = process.argv.find(a => a.startsWith(`--${name}=`));
  return hit ? hit.split("=").slice(1).join("=") : def;
}
function ymdNoDashes(iso) {
  const [y, m, d] = iso.split("-");
  return `${y}${m}${d}`;
}
function addDaysISO(iso, days) {
  const dt = new Date(iso + "T00:00:00Z");
  dt.setUTCDate(dt.getUTCDate() + days);
  const y = dt.getUTCFullYear();
  const m = String(dt.getUTCMonth() + 1).padStart(2, "0");
  const d = String(dt.getUTCDate()).padStart(2, "0");
  return `${y}-${m}-${d}`;
}
function gsSerialToISO(n) {
  // Google serial dates are days since 1899-12-30
  const ms = (n - 25569) * 86400 * 1000 + Date.UTC(1970,0,1) - 0; // or:
  const epoch = Date.UTC(1899, 11, 30);
  const dt = new Date(epoch + n * 86400 * 1000);
  const y = dt.getUTCFullYear();
  const m = String(dt.getUTCMonth()+1).padStart(2,"0");
  const d = String(dt.getUTCDate()).padStart(2,"0");
  return `${y}-${m}-${d}`;
}

// ---------- XML & parsing ----------
function buildEnvelope(company, fromIso, toIso, reportId) {
  return `
<ENVELOPE>
  <HEADER>
    <VERSION>1</VERSION>
    <TALLYREQUEST>Export</TALLYREQUEST>
    <TYPE>Data</TYPE>
    <ID>${reportId}</ID>
  </HEADER>
  <BODY>
    <DESC>
      <STATICVARIABLES>
        <SVCURRENTCOMPANY>${company}</SVCURRENTCOMPANY>
        <SVFROMDATE>${ymdNoDashes(fromIso)}</SVFROMDATE>
        <SVTODATE>${ymdNoDashes(toIso)}</SVTODATE>
        <SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>
      </STATICVARIABLES>
    </DESC>
  </BODY>
</ENVELOPE>`.trim();
}

function cleanXml(s) {
  s = s.replace(/[\x00-\x08\x0B\x0C\x0E-\x1F\uD800-\uDFFF\uFFFE\uFFFF]/g, " ");
  s = s.replace(/&(?![a-zA-Z#][a-zA-Z0-9]*;)/g, "&amp;");
  return s;
}

function parseRows(xmlText) {
  const parser = new XMLParser({ ignoreAttributes: false, trimValues: true });
  const cleaned = cleanXml(xmlText);
  const json = parser.parse(cleaned);
  const rows = json?.ENVELOPE?.ROW;
  const arr = Array.isArray(rows) ? rows : (rows ? [rows] : []);
  return arr.map(r => ([
    r.DATE ?? "",
    r.PARTYNAME ?? "",
    r.VCHTYPE ?? "",
    r.VOUCHERNUMBER ?? "",
    r.COSTCENTRENAME ?? "",
    r.STOCKITEMNAME ?? "",
    r.ACTUALQTY ?? "",
    r.RATE ?? "",
    r.AMOUNT ?? "",
    r.BATCHNAME ?? "",
  ]));
}

// ---------- Value normalization ----------
function normalizeDate(val) {
  if (val == null) return "";
  let s = String(val).trim().replace(/\u00A0/g, " ");
  if (!s) return "";
  if (s.startsWith("'")) s = s.slice(1);
  if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
  if (/^\d{8}$/.test(s)) {
    const y = s.slice(0,4), m = s.slice(4,6), d = s.slice(6,8);
    return `${y}-${m}-${d}`;
  }
  let m;
  if ((m = s.match(/^(\d{1,2})-(\d{1,2})-(\d{2}|\d{4})$/))) {
    let [_, dd, mm, yy] = m;
    if (yy.length === 2) yy = String(2000 + Number(yy));
    dd = dd.padStart(2,"0"); mm = mm.padStart(2,"0");
    return `${yy}-${mm}-${dd}`;
  }
  if ((m = s.match(/^(\d{1,2})-([A-Za-z]{3})-(\d{2}|\d{4})$/))) {
    let [_, d, mon, y] = m;
    const monMap = {Jan:"01",Feb:"02",Mar:"03",Apr:"04",May:"05",Jun:"06",Jul:"07",Aug:"08",Sep:"09",Oct:"10",Nov:"11",Dec:"12"};
    const mm = monMap[mon.slice(0,3)] || "01";
    if (y.length === 2) y = String(2000 + Number(y));
    d = String(d).padStart(2,"0");
    return `${y}-${mm}-${d}`;
  }
  const dt = new Date(s);
  if (!isNaN(dt)) {
    const y = dt.getFullYear();
    const mm = String(dt.getMonth()+1).padStart(2,"0");
    const dd = String(dt.getDate()).padStart(2,"0");
    return `${y}-${mm}-${dd}`;
  }
  return s; // fallback
}

function toNumber(val) {
  if (val === null || val === undefined) return "";
  if (typeof val === "number") return val;

  let s = String(val)
    .trim()
    .replace(/\u00A0/g, " ")
    .replace(/[₹]/g, "");

  if (!s) return "";

  const isNeg = /^\(.*\)$/.test(s);
  if (isNeg) s = s.slice(1, -1);

  const slash = s.indexOf("/");
  const space = s.indexOf(" ");
  if (slash > -1) s = s.slice(0, slash);
  else if (space > -1) s = s.slice(0, space);

  s = s.replace(/,/g, "");
  s = s.replace(/[^0-9.-]/g, "");
  if (!s) return "";
  let n = Number(s);
  if (!Number.isFinite(n)) return "";
  if (isNeg) n = -n;
  return n;
}

// ---------- Google Sheets ----------
async function getSheetsClient(keyFile) {
  if (!fs.existsSync(keyFile)) throw new Error(`credentials file not found: ${keyFile}`);
  const auth = new google.auth.GoogleAuth({
    keyFile,
    scopes: ["https://www.googleapis.com/auth/spreadsheets"],
  });
  return google.sheets({ version: "v4", auth });
}

async function ensureTabGetId(sheets, spreadsheetId, tabName) {
  const meta = await sheets.spreadsheets.get({ spreadsheetId });
  const found = meta.data.sheets?.find(s => s.properties?.title === tabName);
  if (found) return found.properties.sheetId;
  const res = await sheets.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: { requests: [{ addSheet: { properties: { title: tabName } } }] }
  });
  return res.data.replies[0].addSheet.properties.sheetId;
}

async function ensureHeader(sheets, spreadsheetId, tabName) {
  // read first row
  const got = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range: `${tabName}!A1:J1`
  });
  const row = got.data.values?.[0] || [];
  const same = HEADERS.length === row.length && HEADERS.every((h, i) => h === row[i]);
  if (!same) {
    await sheets.spreadsheets.values.update({
      spreadsheetId,
      range: `${tabName}!A1:J1`,
      valueInputOption: "RAW",
      requestBody: { values: [HEADERS] }
    });
  }
}

async function getExistingDataInfo(sheets, spreadsheetId, tabName) {
  // Get whole A column as UNFORMATTED (dates => serials)
  const got = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range: `${tabName}!A:A`,
    valueRenderOption: "UNFORMATTED_VALUE",
  });
  const vals = got.data.values || [];
  if (vals.length === 0) return { dataRows: 0, lastDateISO: null }; // no header yet
  // If header present, data starts at row 2
  const header = vals[0]?.[0];
  let startIdx = (header === "DATE") ? 1 : 0;
  // find last non-empty
  let lastIdx = -1;
  for (let i = vals.length - 1; i >= startIdx; i--) {
    if (vals[i] && vals[i][0] != null && String(vals[i][0]).trim() !== "") { lastIdx = i; break; }
  }
  const dataRows = lastIdx >= startIdx ? (lastIdx - startIdx + 1) : 0;

  let lastDateISO = null;
  if (dataRows > 0) {
    const v = vals[lastIdx][0];
    if (typeof v === "number") {
      lastDateISO = gsSerialToISO(v);
    } else if (typeof v === "string") {
      lastDateISO = normalizeDate(v);
    }
  }
  return { dataRows, lastDateISO };
}

async function appendBatches(sheets, spreadsheetId, tabName, values, batchRows) {
  for (let i = 0; i < values.length; i += batchRows) {
    const chunk = values.slice(i, i + batchRows);
    await sheets.spreadsheets.values.append({
      spreadsheetId,
      range: tabName,
      valueInputOption: "USER_ENTERED",
      insertDataOption: "INSERT_ROWS",
      requestBody: { values: chunk },
    });
    console.log(`Appended ${chunk.length} rows (${Math.min(i + chunk.length, values.length)}/${values.length})`);
  }
}

async function formatNewRows(sheets, spreadsheetId, sheetId, startRowDataIdx, newRows) {
  if (newRows <= 0) return;
  const start = 1 + startRowDataIdx;         // +1 to account for header row at index 0
  const end   = 1 + startRowDataIdx + newRows;

  const requests = [
    // Freeze header (idempotent)
    {
      updateSheetProperties: {
        properties: { sheetId, gridProperties: { frozenRowCount: 1 } },
        fields: "gridProperties.frozenRowCount"
      }
    },
    // Header bold (idempotent)
    {
      repeatCell: {
        range: { sheetId, startRowIndex: 0, endRowIndex: 1, startColumnIndex: 0, endColumnIndex: 10 },
        cell: { userEnteredFormat: { textFormat: { bold: true } } },
        fields: "userEnteredFormat.textFormat.bold"
      }
    },
    // Date format (A)
    {
      repeatCell: {
        range: { sheetId, startRowIndex: start, endRowIndex: end, startColumnIndex: 0, endColumnIndex: 1 },
        cell: { userEnteredFormat: { numberFormat: { type: "DATE", pattern: "dd-mmm-yyyy" } } },
        fields: "userEnteredFormat.numberFormat"
      }
    },
    // Qty (G) -> "#,##0.0###"
    {
      repeatCell: {
        range: { sheetId, startRowIndex: start, endRowIndex: end, startColumnIndex: 6, endColumnIndex: 7 },
        cell: { userEnteredFormat: { numberFormat: { type: "NUMBER", pattern: "#,##0.0###" } } },
        fields: "userEnteredFormat.numberFormat"
      }
    },
    // Rate (H) -> "#,##0.00"
    {
      repeatCell: {
        range: { sheetId, startRowIndex: start, endRowIndex: end, startColumnIndex: 7, endColumnIndex: 8 },
        cell: { userEnteredFormat: { numberFormat: { type: "NUMBER", pattern: "#,##0.00" } } },
        fields: "userEnteredFormat.numberFormat"
      }
    },
    // Amount (I) -> "#,##0.00"
    {
      repeatCell: {
        range: { sheetId, startRowIndex: start, endRowIndex: end, startColumnIndex: 8, endColumnIndex: 9 },
        cell: { userEnteredFormat: { numberFormat: { type: "NUMBER", pattern: "#,##0.00" } } },
        fields: "userEnteredFormat.numberFormat"
      }
    },
    // Batchname (J) -> Text
    {
      repeatCell: {
        range: { sheetId, startRowIndex: start, endRowIndex: end, startColumnIndex: 9, endColumnIndex: 10 },
        cell: { userEnteredFormat: { numberFormat: { type: "TEXT" } } },
        fields: "userEnteredFormat.numberFormat"
      }
    },
    // Auto-size A..J (idempotent)
    {
      autoResizeDimensions: {
        dimensions: { sheetId, dimension: "COLUMNS", startIndex: 0, endIndex: 10 }
      }
    }
  ];

  await sheets.spreadsheets.batchUpdate({ spreadsheetId, requestBody: { requests } });
}

// ---------- MAIN ----------
(async () => {
  try {
    const argFrom = getArg("from", "").trim();
    const argTo   = getArg("to",   "").trim();

    const sheets = await getSheetsClient(CREDENTIALS);
    const sheetId = await ensureTabGetId(sheets, SPREADSHEET_ID, TAB_NAME);
    await ensureHeader(sheets, SPREADSHEET_ID, TAB_NAME);

    const { dataRows: existingRows, lastDateISO } = await getExistingDataInfo(sheets, SPREADSHEET_ID, TAB_NAME);

    let FROM_DATE_ISO, TO_DATE_ISO;

    if (argFrom) {
      FROM_DATE_ISO = normalizeDate(argFrom);
    } else if (lastDateISO) {
      FROM_DATE_ISO = addDaysISO(lastDateISO, 1); // start day after last date present
    } else {
      FROM_DATE_ISO = DEFAULT_FROM_ISO; // sheet empty, start default FY start
    }

    TO_DATE_ISO = argTo ? normalizeDate(argTo) : DEFAULT_TO_ISO;

    console.log(`Requesting Tally: ${FROM_DATE_ISO} → ${TO_DATE_ISO} @ ${TALLY_URL}`);
    const envelope = buildEnvelope(COMPANY, FROM_DATE_ISO, TO_DATE_ISO, REPORT_ID);

    const resp = await axios.post(TALLY_URL, envelope, {
      headers: { "Content-Type": "text/xml" },
      timeout: 300000,
      maxContentLength: Infinity,
      maxBodyLength:   Infinity,
    });

    const rawRows = parseRows(resp.data);

    // Normalize: DATE, numbers; BATCHNAME as text (prefix ')
    const rows = rawRows.map(r => {
      const out = [...r];
      out[0] = normalizeDate(r[0]);    // DATE -> ISO
      out[6] = toNumber(r[6]);         // ACTUALQTY
      out[7] = toNumber(r[7]);         // RATE
      out[8] = toNumber(r[8]);         // AMOUNT
      out[9] = r[9] == null ? "" : "'" + String(r[9]); // BATCHNAME as text
      return out;
    });

    console.log(`Parsed ${rows.length} rows from Tally.`);

    if (!rows.length) {
      console.log("No new data to append. DONE.");
      process.exit(0);
    }

    // Append & format only new rows
    const startDataRowIdx = existingRows; // 0-based count of existing data rows (excludes header)
    await appendBatches(sheets, SPREADSHEET_ID, TAB_NAME, rows, BATCH_ROWS);
    await formatNewRows(sheets, SPREADSHEET_ID, sheetId, startDataRowIdx, rows.length);

    console.log("DONE (appended only).");
  } catch (e) {
    console.error("Fatal:", e?.response?.data || e.message);
    process.exit(1);
  }
})();

My file is loaded in Tally with no errors,but as soon as i run this script i dont get any data when i debugged this code it gave me this error

 <ENVELOPE>
 <HEADER>
  <VERSION>1</VERSION>
  <STATUS>0</STATUS>
 </HEADER>
 <BODY>
  <DATA>
   <LINEERROR>Could not find Report &apos;RTS FlatVch&apos;!</LINEERROR>
  </DATA>
 </BODY>
</ENVELOPE>

So this code also worked for me before like a week back it would fetch the data to my sheet but lately its giving me this issue.

So to fix the issue i also tried to change the name of the Report which also didnt work out hence, the Form name and other variables are RTS InvFlat.

Thanks in advance

What do you see in the console log file for following two lines : console.log(Requesting Tally: ${FROM_DATE_ISO} → ${TO_DATE_ISO} @ ${TALLY_URL}); const envelope = buildEnvelope(COMPANY, FROM_DATE_ISO, TO_DATE_ISO, REPORT_ID); — jdweng
– jdweng, Commented Aug 25 at 9:23
"Appended 0 rows" but thats wrong because i have data that has to be fetched — Vansh Shah
– Vansh Shah, Commented Aug 25 at 9:33
You are getting to this lijne : await appendBatches(sheets, SPREADSHEET_ID, TAB_NAME, rows, BATCH_ROWS); You have a POST "const resp = await axios.post(TALLY_URL, envelope, {" You are making a HTTP REquest and Post means you are sending data in the body of the request. Normally when you are body data you are writting to the database. A report should using a GET to retrieve data from the database. Are you using the database to format the Report? — jdweng
– jdweng, Commented Aug 25 at 21:07
Yes, i have TallyPrime running and it acts as a database its a very old yet powerful software used for accounting and the developer reference is very confusing I found a workaround for this Thank you so much for you help though! — Vansh Shah
– Vansh Shah, Commented Aug 30 at 14:25

Collectives™ on Stack Overflow

Error while fetching data from TDL (TallyPrime) using Node.js

0

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest